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 and the privileges that
are associated with them are defined in the system catalog.
User-defined session global variables are
global variables that are created using an SQL data definition statement
and registered to the database manager in the catalog. A global variable
resides in the schema in which it was created or in the module where
it was added or published. Schema variables are created using
the CREATE VARIABLE statement. For more information, see "CREATE VARIABLE". Module
variables are created using the ADD module-variable-definition clause
or PUBLISH module-variable-definition clause
of the ALTER MODULE statement. For more information, see "ALTER MODULE".
Resolving a global variable reference depends on
the context where the global variable is referenced and how the global
variable name is qualified. A variable reference that is intended
to be a global variable could also resolve to an SQL variable, an
SQL parameter, or a column name depending on the context of the reference
and how the reference is qualified in that context. The following
resolution steps assumes that the global variable reference does not
resolve to an SQL variable, an SQL parameter, or a column name:
- If the global variable name is qualified, resolution is performed
by the database manager using the following steps:
- If the global variable reference is from within a module and the
qualifier matches the name of the module from within which the global
variable is referenced, the module is searched for a matching module
variable. If the qualifier is a single identifier, then the schema
name of the module is ignored when matching the module name. If the
qualifier is a two part identifier, then it is compared to the schema-qualified
module name when determining a match. If a module variable matches
the unqualified global variable name in the reference, resolution
is complete. If the qualifier does not match or there is no matching
module variable, then resolution continues with the next step.
- The qualifier is considered as a schema name and that schema is
searched for a matching schema variable. If a schema variable matches
the unqualified global variable name in the reference, resolution
is complete. If the schema does not exist or there are no matching
schema variables in the schema, and the qualifier matched the name
of the module in the first step, then an error is returned (SQLSTATE
42703). Otherwise, resolution continues with the next step.
- The qualifier is considered as a module name.
- If the module name is qualified with a schema name, then that
module is searched for a matching published module variable.
- If the module name is not qualified with a schema name, then the
schema for the module is the first schema in the SQL path that has
a matching module name. If found, then that module is searched for
a matching published module variable.
- If the module is not found using the SQL path, then the existence
of a module public alias that matches the name of the global variable
qualifier is considered. If found, then the module associated with
the module public alias is searched for a matching published module
variable.
If a published module variable matches the unqualified global
variable name in the global variable reference, resolution is complete.
If a matching module is not found or there is no matching module variable
in the matching module, an error is returned (SQLSTATE 42703).
- If the global variable name is unqualified, resolution is performed
by the database manager using the following steps:
- If an unqualified global variable reference is from within a module
object, the module is searched for a matching module variable. If
a module variable matches the global variable name in the reference,
resolution is complete. If there is no matching module variable, then
resolution continues with the next step.
- The schemas in the SQL path are searched in order from left to
right for a matching schema variable. If a schema variable matches
the global variable name in the reference, resolution is complete.
If no matching global variable is found after completing step
2, an error is returned (SQLSTATE 42703).
When a global variable is referenced within an SQL statement
or within a trigger, view, or routine, a dependency on the fully qualified
global variable name is recorded for the statement or object.
The authorization required for a global variable
depends on where it is defined and how it is used.- The authorization ID of an SQL statement
which references a schema variable and retrieves the value must have
the READ privilege on the global variable.
- The authorization ID of an SQL statement
which references a schema variable and assigns a value must have the
WRITE privilege on the global variable.
- The authorization ID of an SQL statement
which references a module variable and either retrieves the value
or assigns a value must have the EXECUTE privilege on the module of
the global variable.
Global variables can be referenced within any expression
that does not need to be deterministic. Deterministic expressions
are required in the following situations, which preclude the use of
global variables:
- Check constraints
- Definitions of generated columns
- Refresh immediate materialized query tables (MQTs)
The value of a global variable
can be changed using the EXECUTE, FETCH, 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 or
function invocation.
The following
table shows at what point the value of a global variable is read for
the indicated reference of the global variable.
Table 1. When
the value of a global variable is read based on the context| Context of a global variable reference: |
The reference uses the value of the global variable
at the beginning of: |
|---|
| A statement in a compound SQL (inlined) statement |
The compound SQL (inlined) statement |
| A statement in a compound SQL (compiled) statement |
The statement in the compound SQL (compiled)
statement |
| A statement, that possibly invokes a function
or activates a trigger1 |
The SQL statement |
| A statement in an invoked inlined SQL function |
The SQL statement invoking the inlined SQL function |
| A statement in an activated inlined trigger |
The SQL statement activating the inlined trigger |
| A statement in an invoked inlined SQL method |
The SQL statement invoking the SQL method |
| A statement in an invoked compiled SQL function |
The SQL statement in the compiled SQL function |
| A statement in an activated compiled trigger |
The SQL statement in the compiled trigger |
| A statement in an invoked external routine |
The SQL statement in the external program |
Note: In this table,
the SQL statement, which might invoke a function or activates a trigger,
does not include compound SQL (inlined) statement and compound SQL
(compiled) statement.
|
If the data type
of the global variable is a cursor type, 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.
DB2 Version 9.7 for Linux, UNIX, and Windows