Global variables

A global variable is a named memory variable that you access through SQL statements. Global variables let you share relational data between SQL statements without the need for application logic to support this data transfer.

The Db2 database management system supports the following types of global variables:

Built-in global variable

Built-in global variables are provided with the database manager and are used in SQL statements to retrieve scalar values associated with the variables. A built-in global variable is available to any SQL statement that runs on the database manager. Built-in global variables reside in the SYSIBM and SYSIBMADM schemas. As an example, the CLIENT_IPADDR built-in global variable can be referenced in an SQL statement to retrieve the IP address of the current client.

For a list of the built-in global variables and information on these variables, see Built-in global variables.

The READ privilege on a built-in global variable is required for any statement that retrieves the value of that variable. The READ privilege for the built-in global variables is implicitly granted to PUBLIC during installation.

The WRITE privilege on an updatable built-in global variable is required for any statement that assigns a value to that variable. You can control write access to an updatable built-in global variable through the GRANT (variable privileges) and REVOKE (variable privileges) statements.

Examples: Using built- in global variables

The value of a built-in global variable can be obtained by referencing the variable in the context in which the value is needed.

SELECT C1, C2 
  FROM T1
  WHERE C3 = CLIENT_IPADDR

To access the global variable CLIENT_HOST, run the following query:

SELECT SYSIBM.CLIENT_IPADDR
  FROM SYSIBM.SYSDUMMY1

The query returns the IP address of the current client.

1111:2222:3333:4444:5555:6666:7777:8888

To change the value of an updatable built-in global variable, issue the "SET variable" statement: 

SET SYSIBMADM.GET_ARCHIVE = 'Y'
User-defined global variable
A user-defined global variable is available to any active SQL statement that is running on the database manager on which the variable was defined. The value of a user-defined global variable is associated with a specific session, and contains a value that is unique to that session. User-defined global variables let users extend the functionality of the database management system by adding their own or third-party vendor variable definitions.

A user-defined global variable is created using the CREATE VARIABLE statement, and registered to the database manager in the catalog. A user-defined global variable resides in the schema in which it was created. For more information, see CREATE VARIABLE statement.

You can control access to a user-defined global variable through the GRANT statement (variable privileges) and REVOKE (variable privileges) statements. The authorization that is required to use a global variable depends on where it is defined and how it is used.

  • The authorization ID of an SQL statement that references a user-defined global variable and retrieves the value must have the READ privilege on the global variable.
  • The authorization ID of an SQL statement that references a user-defined global variable and assigns a value to that variable must have the WRITE privilege on the global variable.

The value of a user-defined global variable can be obtained by referencing the variable in the context in which the value is needed. The value of a user-defined global variable can be changed with the following statements:

  • EXECUTE
  • FETCH
  • SET
  • SELECT INTO
  • VALUES INTO
  • CALL, if the variable is an argument to a procedure in which the corresponding parameter is defined as an OUT or INOUT parameter.

A reference, with an unqualified name, that is intended to resolve to a global variable could also resolve to an SQL variable, an SQL parameter, or even a column name, depending on the context of the reference and how the reference is qualified within that context. The implicit schema name for an unqualified variable name depends on the context in which the name appears:

  • If an unqualified variable name is used in a CREATE VARIABLE, or the object of a DROP, COMMENT, GRANT, or REVOKE statement, the normal process of qualification by the authorization ID is used to determine the schema name.
  • If an unqualified variable name is used in any other context, the SQL path is used to determine the schema name. The database manager searches the schemas in the SQL path in sequence. The schema that is selected is the first schema in the SQL path for which the global variable exists in the schema, and the user has authorization to use the variable. For a description of the SQL path, see SQL path.