Global variables in DB2 for z/OS

Global variable declaration

A user-defined global variable is created once, stored in SYSIBM.SYSVARIABLES, and then referenced by SQL running at that server. It is not a COBOL host variable, not a special register, and not a column. It lives in a schema. Unqualified CREATE, DROP, COMMENT, GRANT, and REVOKE names follow ordinary authorization-ID qualification. Other references use the SQL path, similar to functions and distinct types.

Typical data types are the built-in types you already use on columns, plus distinct types and array types. Array-typed global variables are a common way to hold a list in SQL PL without a declared temporary table.

CREATE VARIABLE

CREATE VARIABLE names the variable, gives it a data type, and optionally a DEFAULT. If you omit DEFAULT, the default is NULL.

1
2
3
4
5
CREATE VARIABLE HR.GV_DEPT CHAR(3) DEFAULT 'A00';
 
CREATE VARIABLE HR.GV_ASOF DATE DEFAULT CURRENT DATE;
 
CREATE VARIABLE HR.GV_NOTE VARCHAR(100);  -- default NULL

Rules beginners hit first:

• The name, including schema, must not collide with another variable at the current server
• DEFAULT can be a constant, NULL, a special register, or another expression the SQL Reference allows for that form
• Creating the variable does not put a shared “current value” in the catalog. It defines the type and default. Each session instantiates its own value
• You need the privilege to create objects in the schema (CREATEIN or equivalent authority)

Naming convention: many shops prefix names with GV_ so a SELECT list does not look like a missing column qualifier. If a column and a global variable share an unqualified name, column resolution usually wins in a table context—qualify the variable.

ALTER VARIABLE and DROP VARIABLE

ALTER VARIABLE changes the definition, most often the default. It does not rewrite every session’s current value; sessions keep what they already SET until they end or they assign again. New sessions pick up the new default.

1
2
ALTER VARIABLE HR.GV_DEPT
  SET DEFAULT 'B01';

DROP VARIABLE removes the catalog object. Privileges recorded in SYSIBM.SYSVARIABLEAUTH go away with it. Dependent SQL that still names the variable fails to prepare.

1
DROP VARIABLE HR.GV_NOTE;

Global variable assignment

You change the session’s copy with assignment, not with UPDATE of a catalog table.

• SET — SET HR.GV_DEPT = 'C01'
• SELECT INTO — select a column into the variable
• VALUES INTO — assign an expression
• An OUT or INOUT parameter of CALL can target a global variable in some SQL PL patterns

1
2
3
4
5
6
7
8
9
SET HR.GV_DEPT = 'E21';
 
SELECT WORKDEPT
  INTO HR.GV_DEPT
  FROM HR.EMPLOYEE
 WHERE EMPNO = '000010';
 
VALUES CURRENT DATE
  INTO HR.GV_ASOF;

Assignment requires WRITE privilege on the variable. The new value must be assignable to the declared type (including CCSID and distinct-type casting rules). Setting a variable to NULL is allowed unless the type or default rules forbid it for that object.

Global variable references

Once set (or left at default), you reference the name anywhere an expression of that type is legal in a query or SET statement:

1
2
3
4
5
6
7
8
9
SELECT EMPNO, LASTNAME
  FROM HR.EMPLOYEE
 WHERE WORKDEPT = HR.GV_DEPT
 ORDER BY LASTNAME;
 
SELECT HR.GV_DEPT, COUNT(*)
  FROM HR.EMPLOYEE
 WHERE WORKDEPT = HR.GV_DEPT
 GROUP BY HR.GV_DEPT;

This is the interactive-SQL payoff: run a SET in SPUFI, then several SELECTs that reuse the value, without host variables. Inside SQL PL procedures the same pattern shares a value across statements that are not in one compound block’s DECLARE list.

Places you cannot use a global variable include check constraints, materialized query table definitions, and index key expressions. Those objects must be deterministic with respect to the table, not a session scratchpad.

Session scope

IBM’s wording is: global variables have a session scope. They are available to all sessions at the current server, but the value is private for each session. Two users who both SET HR.GV_DEPT do not overwrite each other.

Details that surprise people:

• All programs on the same thread share the same copies. A COBOL program and an SQL procedure called on that thread see one GV_DEPT
• COMMIT and ROLLBACK do not reset the value
• Thread reuse (CICS protected threads, DDF inactive threads that become a new logical connection incorrectly assumed to be clean, and similar) can leave the previous occupant’s values. Reset sensitive variables at end of transaction
• REST is the clean case: end of transaction restores defaults

Global variable privileges

Access is not “if you can CREATE you can SET.” After CREATE, control READ and WRITE with GRANT and REVOKE.

1
2
3
4
5
6
7
8
GRANT READ ON VARIABLE HR.GV_DEPT TO PUBLIC;
 
GRANT READ, WRITE ON VARIABLE HR.GV_DEPT TO ROLE HR_APP;
 
GRANT ALL PRIVILEGES ON VARIABLE HR.GV_ASOF TO HRDBA
  WITH GRANT OPTION;
 
REVOKE WRITE ON VARIABLE HR.GV_DEPT FROM PUBLIC;

• READ — reference the variable in expressions (you see your session’s value, or the default if you never SET)
• WRITE — assign a new session value
• ALL PRIVILEGES — READ and WRITE
• WITH GRANT OPTION — pass those privileges on

Catalog table SYSIBM.SYSVARIABLEAUTH records who has which privilege from which grantor. Only an explicitly granted privilege can be revoked (the usual Db2 GRANT/REVOKE rule), unless an authority such as SECADM or ACCESSCTRL revokes using BY.

Global variable security

Treat a global variable like a session memory slot, not like a table with audit columns.

• Do not put passwords, PANs, or national IDs in a global variable on a reused thread unless you SET it back to NULL in the same unit of work’s cleanup
• Grant WRITE only to processes that must change the value. Many reporting IDs need READ only
• Prefer a role or secondary authorization ID over granting WRITE to dozens of primary IDs
• Qualify names in dynamic SQL so a hostile column name cannot shadow the variable
• Remember DATAACCESS authority includes access to global variables along with tables; it is a blunt instrument

Functions and procedures inherit the session’s global variable values from the invoking environment. A UDF that reads HR.GV_DEPT sees whatever the caller SET, which is useful for “current department” filters and dangerous if the function is SECURED and used in a row permission without careful design.

Explain It Like I'm Five

A global variable is a labeled sticky note on your desk, not on the classroom wall. The teacher prints one pad of sticky notes for everyone (CREATE VARIABLE). Each kid writes their own number on their own note (SET). Your neighbor cannot read your number, and you cannot read theirs. If you leave your desk and someone else sits down (thread reuse), your sticky note might still be there—so wipe it before you go. Putting the note away for the night (COMMIT) does not erase what you wrote.

Exercises

1. Create a VARCHAR(30) global variable with DEFAULT 'HELLO', SET it to a table name, and use it in a predicate against SYSIBM.SYSCOLUMNS (as a filter on TBNAME).
2. Grant READ to PUBLIC and WRITE only to your personal ID. Try SET from another ID and record the SQLCODE.
3. SET a variable, COMMIT, then SELECT it. SET it again, ROLLBACK, then SELECT it. Explain the results.
4. List two reasons you would reset a global variable to NULL at the end of a CICS transaction.
5. Query SYSIBM.SYSVARIABLES and SYSIBM.SYSVARIABLEAUTH for the variable you created.