× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



Thanks for the reply Sue
My reaction is
Hmmmmmmmm
In your example you mentioned timestamp, but what about a 10 character field?
Can I set the default value within this 0 character sql variable?



Sent via the Samsung GALAXY S® 5, an AT&T 4G LTE smartphone


-------- Original message --------
From: Sue Romano <slromano@xxxxxxxxxx>
Date: 1/7/21 21:14 (GMT-05:00)
To: midrange-l@xxxxxxxxxxxxxxxxxx
Subject: [EXTERNAL] 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
--
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


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.