Re: Problem with SQL variables -- MIDRANGE-L

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