Hi Justin,

You can specify CREATE OR REPLACE VARIABLE if your system is current
enough, but I see no ALTER VARIABLE present.

I've only recently tinkered with SQL global variables (no production use

These two comments from the V7R1 Reference manual don't clearly state what
will happen to job B's copy of the variable, if job A does a REPLACE.

"The existing definition is effectively dropped before the new definition
is replaced in the catalog with the exception that privileges that were
on the variable are not affected."

"Once a global variable is instantiated for a session, changes to the
global variable
in another session (such as DROP or GRANT) might not affect the variable
has been instantiated."

The first comment makes me think job B might die, and the second comment
makes me think it might not.

I think a REPLACE should only be necessary if the data type, size, or
default value needed to be changed.

I did some quick tests.

Test #1
1) Created variable in job A
2) Instantiated a copy of it in job B
3) Did a DROP of the variable in job A
4) Tried access in job B and global variable was not found

Test #2
1) Created variable in job A
2) Instantiated a copy of it in job B, and modified its value from the
3) Did a REPLACE of the variable in job A
4) Tried access in job B and the variable was found, and it retained its
modified value

Test #3
1) Tried a REPLACE in job A, while attempting to simultaneously access it
in job B, and job B never failed to retrieve the variable, across about 5

IBM's use of the word "might" makes me think there is some, minimal
concurrency risk for REPLACE..

It you can't tolerate that risk, consider create a new column or row in a
frequently accessed control table to hold it, instead of using a variable.


date: Thu, 17 Dec 2015 03:19:33 +0000
from: Justin Dearing <zippy1981@xxxxxxxxx>
subject: No way to ALTER VARIABLE besides CREATE/DROP?

If I CREATE VARIABLE foo INT DEFAULT 5 it will always be 5 until I do a
SET, but the SET foo = 6; is only scoped to the session.

So, its actually not a big deal for my current application because the
variable (a start date for new business rules) should be a constant, and
I'm only going to change it in QA. If the business rules get delayed, then
a CREATE/DROP is fine. However, what if I had a variable that was a counter
of some kind? What if I expected some job to be constantly changing the
value of a global variable and several other jobs to be periodically SELECT
appropriate behavior? Is there a risk that the SELECT might occur when the
variable doesn't exist?

This thread ...


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

This mailing list archive is Copyright 1997-2019 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].