Using global variables

Using global variables requires an understanding of the usage restrictions, the rules for assignment to global variables, and the rules for retrieving global variable values.

Usage restrictions

Global variables can be referenced from within any SQL expression, unless the context of the expression requires that the expression be deterministic. The following situations are examples of contexts that require deterministic expressions and therefore preclude the use of global variables:
  • In a table check constraint or data type check constraint
  • In the definition of a generated expression column
  • In a refresh-immediate materialized query table (MQT)

If the data type of the global variable is a cursor type, then the underlying cursor of the global cursor variable can be referenced anywhere that a cursor-variable-name can be specified.

If the data type of the global variable is a row type, a field of the global row variable can be referenced anywhere that a global variable with the same type as the field can be referenced. The global variable name that qualifies the field name is resolved in the same way as any other global variable name.

Assignment

The value of a global variable can be changed if both of the following conditions are true:
  • The global variable is not a read-only variable.
  • The authorization ID of the statement is authorized to write to the global variable.
A value can be assigned to a global variable using any of the following SQL statements:
  • A SET variable statement with a global variable as the target variable
  • An EXECUTE, FETCH, SELECT INTO, or VALUE INTO statement with a global variable as an assignment target in the INTO clause
  • A CALL statement with a global variable as an argument for an OUT or INOUT parameter of the procedure
  • A function invocation with a global variable as an argument for an OUT or INOUT parameter of the function (this is supported only for the source expression of a SET variable statement).

Retrieval

The value of a global variable is obtained by referencing the variable from within the SQL context where the value is needed.

The following table shows when the value of a global variable is read, for the indicated reference of that global variable.

Table 1. When the value of a global variable is read, based on the reference context
Context of the global variable reference The reference uses the value of the global variable at the beginning of:
An SQL statement in a compound SQL (inlined) statement The compound SQL (inlined) statement
An SQL statement in a compound SQL (compiled) statement The SQL statement within the compound SQL (compiled) statement
An SQL statement, possibly with a function invocation or a trigger activation1 The SQL statement
An SQL statement in an invoked inlined SQL function The SQL statement invoking the inlined SQL function
An SQL statement in an activated inlined trigger The SQL statement activating the inlined trigger
An SQL statement in an invoked inlined SQL method The SQL statement invoking the inlined SQL method
An SQL statement in an invoked compiled SQL function The SQL statement in the compiled SQL function
An SQL statement in an activated compiled trigger The SQL statement in the compiled trigger
An SQL statement in an invoked external routine The SQL statement in the external routine
Note: 1 In this table, the SQL statement that might call a function or activate a trigger does not include the compound SQL (inlined) statement or the compound SQL (compiled) statement.