Thanks for your reply Rob
Im using CLLE as it's the driving program for this particular job
I then used RUNSQLSTM etc. as it seemed convenient, not knowing/understanding the intricacies of SQL variables
Looks like I will have to rethink
Alan Shore
E-mail : ASHORE@xxxxxxxx
Phone [O] : (631) 200-5019
Phone [C] : (631) 880-8640
'If you're going through hell, keep going.'
Winston Churchill
-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx] On Behalf Of Rob Berendt
Sent: Friday, January 8, 2021 7:23 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: [EXTERNAL] RE: Problem with SQL variables
Let me reiterate stuff that I've learned on my journey.
Stop thinking of a sql variable as a sql variable. Think of them instead in the terms of an RPG variable. This RPG variable is in a service program called MYSRVPGM. And the MYSRVPGM is set with
Activation group attribute . . . . . . . . . . . : *CALLER
Shared activation group . . . . . . . . . . . . : *
So, when you run the following in SQL
SET MYLIB.MYSRVPGM = 'HRSHIPPEDD';
It basically calls MYSRVPGM with that as a parameter and it stores that into an appropriate RPG variable.
When you run the following
VALUES MYLIB.MYSRVPGM;
It calls MYSRVPGM and retrieves the content of that variable.
However if you use the SET from something running in one activation group, and you use the VALUES from something running in a different activation group, you are going to run into issues. Basically what will happen is that VALUES will return a null variable.
This is exactly what is happening.
Yes, you can rewrite this to stop using sql variables.
Then again, you can stop trying to duct tape SQL into CL and just use a different language, one that can do SET, retrieves, etc all in the same activation group.
I come from a slightly different background. I was pretty adept at CL. Then, back when it was languishing way behind and had no structured programming operations, etc I abandoned it. I abandoned it to the extreme. Even for "system" type programs I would use RPG with QCMDEXC or some of the better API's for calling commands. So, if I had to write something which needed to use SQL and system commands even today I would not write it in CL but I would write it in RPG.
Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600 Mail to: 7310 Innovation Blvd, Suite 104
Ft. Wayne, IN 46818
Ship to: 7310 Innovation Blvd, Dock 9C
Ft. Wayne, IN 46818
http://www.dekko.com
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Charles Wilt
Sent: Thursday, January 7, 2021 11:52 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: Problem with SQL variables
CAUTION: This email originated from outside of the organization. Do not click links or open attachments unless you recognize the sender and know the content is safe.
Sue,
If you look back at the original post, it would seem that Alan is doing
RUNSQLSTMT to declare & set some variables
RUNSQLSMTM to declare & set some more variable
RUNSQL to run some statements that use the variables set in the prior two
steps..
All three steps are run by a single CLLE program.
So when you said "Every session (= connection, which is almost the same as
a job)", it seems that a session may be a bit less the same as a job than
we would expect.
Playing with it, STRSQL at least seems to start a new session during each
invocation. As when I SET SESSION_USER, exit and go back into STRSQL, the
session user is reset to my user profile.
Now having said that, your point about defaults is on target. I don't use
variables much, but when I do, I've always done so with a default, rather
than setting them after the fact. Probably part of the reason I've never
seen an issue.
So Alan, try your variables like so...
CREATE or REPLACE VARIABLE NBJDTA.HIRADAS400FILENAMESHPD CHARACTER (10)
default('HRSHIPPEDD');
CREATE or REPLACE VARIABLE NBJDTA.HIRADSTATUSSHPD CHARACTER (1)
default('N');
Charles
On Thu, Jan 7, 2021 at 7:14 PM Sue Romano <slromano@xxxxxxxxxx> wrote:
I think some basic global variable education is needed. The discussion
I've been reading does not appear to match the way global variables work.
A global variable is created one time. Every session (= connection, which
is almost the same as a job) gets its own copy of the variable. Its
initial value for the session is set the first time it is referenced. That
value is either the DEFAULT defined for the variable or NULL. If you SET a
variable in a job, that value is only visible in that job. The value is
NOT stored in the service program for the variable. Each job's global
variable values are maintained in some storage used only for that job.
I believe the discussion started by mentioning that the SETs for the global
variables were done in one job, then another job went to use the variables
and discovered they were null. This is how global variables work. You CAN
however set a value to be used across jobs by modifying the global variable
definition to set the DEFAULT for the global variable.
What this might look like is:
job 1 could run a dynamic statement that sets the default:
execute immediate 'create or replace variable mylib.var1 timestamp DEFAULT
' concat current timestamp;
This statement will recreate the variable and use the timestamp from the
job running this statement as the value that any other job will see for
this global variable. (Note that setting the default to CURRENT TIMESTAMP
is completely valid, too. What that gives you, however, is the current
timestamp for the job when the global variable is first referenced, not the
value from when the variable was created. A completely different answer,
and probably not what you want!)
Now when job 2 references mylib.VAR1 the first time, it will have it's
initial value set to the default value that was set in job 1. I believe
this is the solution needed from what I read in the scenario.
I hope this clears up at least some of the confusion over what these
mysterious global variables are.
Sue Romano
Db2 for IBM i Development
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.
Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
https://amazon.midrange.com
midrange.com
