Global Variables: Back to Basics

There is no easy way to share information between the SQL statements among applications. This creates a need for applications to put supporting logic inside their code to access and transfer data between SQL statements.  And this is where Global Variables come to rescue.

These created global variables work very much the same as created global temporary tables. Global variable names are qualified two-part names. For unqualified global variables, the qualifier bind parameter is used to facilitate the naming resolution.

Global Variables:

• Enable the sharing of data between SQL statements without the need for application logic.
• Are maintained by Db2 and are available throughout the entire application scope.
• Have access controlled by GRANT and REVOKE statements.
• If no DEFAULT is specified, NULL is used as the default value
• Can appear in expression, predicates, and select list.
• Cannot be used in, Check Constraints, MQTs, Definition expressions for indexes
• The value can be changed by SET, SELECT INTO, or VALUES INTO, an argument of an OUT or INOUT parameter in a CALL statement
• The content is not affected by COMMIT or ROLLBACK statements

Global Variables in Stored Procedures, UDFs, etc.:

• The content of the Global Variables are always inherited from the caller.
• Stored Procedures can make changes to the Global Variables, but not if the SP was called by a UDF or trigger.
• The changes always propagate to the caller.
  
  

Global Variables Sample

CREATE VARIABLE GV_TMS

   TIMESTAMP

   DEFAULT CURRENT TIMESTAMP

;

GRANT ALL PRIVILEGES

  ON VARIABLE GV_TMS TO PUBLIC

;

Global Variables definitions are shared across different connections, much like a special register. Their contents are set and shared only within the same connections meaning each connection maintains its own settings, instantiation, and processing. Their contents are not affected by Commit or Rollback statements.

Global Variables are great for sharing across SQL statements, stored procedures and triggers creating greater flexibility within applications.

The following catalog tables keep information about the variables:

• SYSIBM.SYSVARIABLES:  Contains one row for each global variable that is created.
• SYSIBM.SYSVARIABLEAUTH: Contains one row for each privilege of each authorization ID that has privileges on a global variable.
• SYSIBM.SYSVARIABLES_TEXT:  Is an auxiliary table for the DEFAULTTEXT column.

Global Variable Creation

Global variables are created by a DBA and the definitions are stored in a the SYSIBM.SYSVARIABLES catalog table.  The privileges READ or WRITE may be granted on each variable.  If a user has only READ privileges then they will only be able to see its default value.  In the example above we have granted ALL PRIVILEGES which includes both write and read.  This should be appropriate in most situations.

In this example the default for a timestamp variable is set to the “current” timestamp.  This will be the timestamp when the variable is instantiated or first referenced by an application.

The definitions of these variables (i.e. names, data types, defaults) are shared among threads but each thread that uses a global variable will have its own copy and the values used will be distinct to that thread.  All programs within a thread share this global variable.

Note, that however for protected threads (thread reuse) in CICS, transactions can ‘reuse’ threads and a ‘new’ different transaction running under the same thread as the previous transaction will be able to see the global variable value set by the previous transaction. This can lead to unpredictable results where both the transactions or multiple invocations of the same transaction are using the global variable in the application logic. To get around this the global variable value should be reset at the transaction/commit boundaries. For DDF threads it gets a bit more complex in that the global variable value persists across the entire connection and the same considerations apply, i.e. reset the value at the transaction boundaries.

A naming convention should be chosen that easily distinguishes global variables from regular column names or stored procedure variables.  EX)  GV_TIMESTAMP

Global Variable Usage Examples

SET GV_TMS = ‘2024-01-01.00.00.00.000000’

;

SELECT EMPNO, LASTNAME, WORKDEPT

  FROM EMP

WHERE HIREDATE >= GV_TMS

;

Then elsewhere in code, or if a stored procedure is called, another query can reference the GV_TMS and not lose the value.

SELECT ORDER_NUM, ORDER_DATE, ITEM_NUM

   FROM ORDERS

WHERE ORDER_DATE >= DATE(GV_TMS)

;

Here is an example of a COBOL application instantiating and using a global variable.  Notice that it is difficult to distinguish the variable from Db2 column names except for the naming convention chosen (in this case global variables start with GV_).  Also notice that in COBOL the global variable is not prefixed with a ‘:’ to denote a host variable.

These global variables cannot be displayed directly, but can be moved to a host variable via SET statement.

EXEC SQL

  SET GV_TMS = ‘2024-01-01.00.00.00.000000’

END-EXEC

;

EXEC SQL

  SELECT EMPNO, LASTNAME, WORKDEPT

  FROM EMP

  WHERE HIREDATE >= GV_TMS

END-EXEC

;

Again:  The content is not affected by COMMIT or ROLLBACK statements.