Re: No way to ALTER VARIABLE besides CREATE/DROP

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
yet).

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
granted
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
that
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
default
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
attempts.

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.

Mike

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
SCHEMA.VARIABLE FROM SYSIBM.SYSDUMY1? Is DROP/CREATE in a transaction the
appropriate behavior? Is there a risk that the SELECT might occur when the
variable doesn't exist?