Re: Problem with SQL variables

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