DB2 SQL Global variables top usage

Traditionally within a relational database system, most interactions between an application and the DBMS are in the form of SQL statements within a connection.

  • Using SQL you can communicate – then, why you need Global Variables

To share information between SQL statements within the same application context, the application that issued the SQL statements has to do this work by copying the values from the output arguments, such as host variables, of one statement to the input host variables of another.

Similarly, when…

Applications issue host-language calls to another application, host variables need to be passed among applications as input or output parameters for the applications to share common variable. Furthermore, SQL statements that are defined and contained within the DBMS, such as the SQL statements in the trigger bodies, cannot access this shared information.

  • Global Variables Introduced in DB2 version 11

To overcome this restriction and to maximize the flexibility of a DBMS, global variables are introduced in DB2 11 for z/OS.

Creation of Sample Global variables:

CREATE VARIABLE BATCH_START_TS TIMESTAMP
DEFAULT CURRENT TIMESTAMP;

Catalog Tables

  • 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.

Ref IBM Db2 v11

Read more..