Global variables
Global variables are named memory variables that you can access and modify through SQL statements.
- Built-in global variable
- A built-in global variable is part of the database management system, and is available to any SQL statement that runs on the database manager. For a list of the built-in global variables and information on these variables, see Built-in global variables.
- User-defined global variable
- A user-defined global variable enables you to share relational data between SQL statements
without the need for application logic to support this data transfer.
A user-defined global variable is associated with a specific session1 and contains a value that is specific to that session. A user-defined session global variable is available to any active SQL statement running against the database on which the variable was defined. A user-defined global variable can be associated with more than one session, but its value will be specific to each session. User-defined global variables are defined in the system catalog.
You can control access to global variables through the GRANT (Global Variable Privileges) and REVOKE (Global Variable Privileges) statements.
Global variable names are qualified names. If an unqualified global variable name is the main object of an ALTER, CREATE, COMMENT, DROP, GRANT, or REVOKE statement, the name is implicitly qualified using the same rules as for qualifying unqualified table names. Otherwise, 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)
- Indexes
- 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
- Database administrator authority
The value of a global variable can be changed using the 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.