How to Write SQL to Create Global Variables

You need global variables to interact with database effectively. The usage is much different from how you will use SQL.

When You Need Global Variables

Users of such systems are forced to put supporting logic inside their applications to access and transfer user application information and internal database information within a relational database system. Ensuring the security of the information that is transferred and accessed is also left to the user to enforce in their application logic.

How to create global variables

CREATE VARIABLE BATCH_START_TS TIMESTAMP  
DEFAULT 
CURRENT TIMESTAMP;

Where these Global Variables information saved

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

How a global variable response can be validated in different contexts

— Initial execution of the SQL
SELECT BATCH_START_TS, CURRENT TIMESTAMP
FROM SYSIBM.SYSDUMMY1
;
— Result set from the initial execution
BATCH_START_TS CURRENT TIMESTAMP
2013-08-02-14.59.46.423414 2013-08-02-14.59.46.423414
— Second execution of the same SQL statement in the same SPUFI session

SELECT BATCH_START_TS, CURRENT TIMESTAMP
FROM SYSIBM.SYSDUMMY1
;
— Result set from the second execution
BATCH_START_TS CURRENT TIMESTAMP

2013-08-02-14.59.46.423414 2013-08-02-14.59.46.424678

— Third execution of the same SQL statement in the same SPUFI session

SELECT BATCH_START_TS, CURRENT TIMESTAMP
FROM SYSIBM.SYSDUMMY1
;
— Result set from the third execution
BATCH_START_TS CURRENT TIMESTAMP