Global variables
Global variables are named memory variables that you can access and modify through SQL statements.
Global variables enable you to share relational data between SQL statements without the need for application logic to support this data transfer. You can control access to global variables through the GRANT (Global Variable Privileges) and REVOKE (Global Variable Privileges) statements.
DB2® supports
created session global
variables. A session global variable is associated with
a specific session and contains a value that is unique to that session.
A created session global variable is available
to any active SQL statement running against the database on which
the variable was defined. A session global variable
can be associated with more than one session, but its value will be
specific to each session. Created session global variables
are defined in the system catalog.
Global variable names are qualified names. When a global variable is referenced without the schema name, the SQL path is used for name resolution.
For static SQL statements and SQL routines, global variables are resolved for a statement the first time all table references are resolved. In views, triggers, and other global variables, they are resolved when the object is created. If resolution were to be performed again on any global variable, it could change the behavior if, for example, a new global variable had been added with the same name in a different schema that is also in the SQL path.
Global variables that are referenced in dynamic statements will be resolved when the statement is initially prepared. They will not be resolved again unless the statement needs to be refreshed because a table has changed.
- The name is checked to see if it is the name of a column of any existing table or view referenced in the statement at the current server.
- If used in an SQL routine, the name is checked to see if it is the name of an SQL variable, SQL parameter, or transition variable.
- If not found by either of these rules, it is assumed to be a global variable.
- If the SQL_GVAR_BUILD_RULE QAQQINI option is *EXIST
and the global variable does not exist at precompile time or when
an SQL routine is being created, an error will be issued.
When a global variable is referenced in
a trigger, view, routine, or global variable, a dependency on the
fully qualified global variable name is recorded for the statement
or object. Also, if applicable, the authorization ID being used for
the statement is checked for the appropriate privilege
on the global variable.
- Check constraints
- Materialized query tables (MQTs)
- Derived index expressions
- A global variable is not allowed if the query specifies:
- a distributed table,
- a table with a read trigger, or
- a logical file built over multiple physical file members.
Authorization: If a global variable is referenced in a statement, the privileges held by the authorization ID of the statement must include at least one of the following:
- For the global variable identified in the statement,
- The READ privilege on the global variable if the
global variable is referenced, and
- The WRITE privilege on the global variable if the
global variable is assigned a value, and
- The system authority *EXECUTE on the library containing the global variable
- Administrative authority
The value of a global variable can be changed using the SET, SELECT INTO, or VALUES INTO statement. It can also be changed if it is an argument of an OUT or INOUT parameter in a CALL statement.