
The CREATE VARIABLE statement defines a global variable at the application server.
This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared.
The privileges held by the authorization ID of the statement must include at least one of the following:
The privileges held by the authorization ID of the statement must include at least one of the following:
If a distinct type or sequence is referenced, the privileges held by the authorization ID of the statement must include at least one of the following:
If a function is referenced, the privileges held by the authorization ID of the statement must include at least one of the following:
If a global variable is referenced, the privileges held by the authorization ID of the statement must include at least one of the following:
If a table or view is referenced directly or indirectly, the privileges held by the authorization ID of the statement must include at least one of the following:
To replace an existing variable, the privileges
held by the authorization ID of the statement must include at least
one of the following: 
The system authority *READ to the SYSVARIABLES catalog
table
For information about the system authorities corresponding to SQL privileges, see Corresponding System Authorities When Checking Privileges to a Sequence and Corresponding System Authorities When Checking Privileges to a Distinct Type.
>>-CREATE--+------------+--VARIABLE--variable-name--data-type---> '-OR REPLACE-' .-DEFAULT NULL------------------. >--+-------------------------------+--------------------------->< '-DEFAULT--+-constant---------+-' +-special-register-+ +-global-variable--+ '-(--expression--)-'
data-type |--+-built-in-type------+---------------------------------------| '-distinct-type-name-'
built-in-type |--+-+---SMALLINT---+--------------------------------------------------------------------------+--| | +-+-INTEGER-+--+ | | | '-INT-----' | | | '---BIGINT-----' | | .-(5,0)------------------------. | +-+-+-DECIMAL-+-+--+------------------------------+-----------------------------------------+ | | '-DEC-----' | | .-,0--------. | | | '-+-NUMERIC-+-' '-(--integer--+-----------+--)-' | | '-NUM-----' '-, integer-' | | .-(--53--)------. | +-+-FLOAT--+---------------+-+--------------------------------------------------------------+ | | '-(--integer--)-' | | | +-REAL---------------------+ | | | .-PRECISION-. | | | '-DOUBLE--+-----------+----' | | .-(--34--)-. | +---DECFLOAT--+----------+------------------------------------------------------------------+ | '-(--16--)-' | | .-(--1--)-------. | +-+-+-+-CHARACTER-+--+---------------+----------+--+----------------+---------------------+-+ | | | '-CHAR------' '-(--integer--)-' | +-FOR BIT DATA---+ | | | | '-+-+-CHARACTER-+--VARYING-+--(--integer--)-' +-FOR SBCS DATA--+ | | | | | '-CHAR------' | +-FOR MIXED DATA-+ | | | | '-VARCHAR----------------' '-ccsid-clause---' | | | | .-(--1M--)-------------. | | | '-----+-+-CHARACTER-+--LARGE OBJECT-+------+----------------------+--+----------------+-' | | | '-CHAR------' | '-(--integer--+---+--)-' +-FOR SBCS DATA--+ | | '-CLOB------------------------' +-K-+ +-FOR MIXED DATA-+ | | +-M-+ '-ccsid-clause---' | | '-G-' | | .-(--1--)-------. | +-+---GRAPHIC----+---------------+-------+--+--------------+--------------------------------+ | | '-(--integer--)-' | '-ccsid-clause-' | | +-+-GRAPHIC VARYING-+--(--integer--)---+ | | | '-VARGRAPHIC------' | | | | .-(--1M--)-------------. | | | '---DBCLOB----+----------------------+-' | | '-(--integer--+---+--)-' | | +-K-+ | | +-M-+ | | '-G-' | | .-(--1--)-------. | +-+-+-+-NATIONAL CHARACTER-+--+---------------+----------+---------------------+------------+ | | | +-NATIONAL CHAR------+ '-(--integer--)-' | | | | | | '-NCHAR--------------' | | | | | '-+-+-NATIONAL CHARACTER-+--VARYING-+--(--integer--)-' | | | | | +-NATIONAL CHAR------+ | | | | | | '-NCHAR--------------' | | | | | '-NVARCHAR------------------------' | | | | .-(--1M--)-------------. | | | '-----+-+-NATIONAL CHARACTER-+--LARGE OBJECT-+------+----------------------+-' | | | '-NCHAR--------------' | '-(--integer--+---+--)-' | | '-NCLOB--------------------------------' +-K-+ | | +-M-+ | | '-G-' | | .-(--1--)-------. | +-+-+-BINARY--+---------------+---------+-----------------+---------------------------------+ | | | '-(--integer--)-' | | | | | '-+-BINARY VARYING-+--(--integer--)-' | | | | '-VARBINARY------' | | | | .-(--1M--)-------------. | | | '---+-BLOB----------------+----+----------------------+-' | | '-BINARY LARGE OBJECT-' '-(--integer--+---+--)-' | | +-K-+ | | +-M-+ | | '-G-' | +-+-DATE-------------------+----------------------------------------------------------------+ | | .-(--0--)-. | | | +-TIME--+---------+------+ | | | .-(--6--)-. | | | '-TIMESTAMP--+---------+-' | '---XML--+--------------+-------------------------------------------------------------------' '-ccsid-clause-' ccsid-clause |--CCSID--integer-----------------------------------------------|
OR REPLACE
Specifies
to replace the definition for the variable if one exists at the current
server. The existing definition is effectively dropped before the
new definition is replaced in the catalog with the exception that
privileges that were granted on the variable are not affected. This
option is ignored if a definition for the variable does not exist
at the current server.
If SQL names were specified, the variable will be created in the schema specified by the implicit or explicit qualifier.
If system names were specified, the variable will be created in the schema that is specified by the qualifier. If not qualified:
The global variable's definition is saved in the associated service program object. If the *SRVPGM object is saved and then restored to this or another system, the catalogs are automatically updated with the definition.
If a global variable and an SQL routine have the same name, naming conflicts can be avoided by creating the global variable first.
Variable ownership: The owner of the variable is the user profile or group user profile of the job executing the statement.
Variable authority: If SQL names are used, variables are created with the system authority of *EXCLUDE on *PUBLIC. If system names are used, variables are created with the authority to *PUBLIC as determined by the create authority (CRTAUT) parameter of the schema.
If the owner of the variable is a member of a group profile (GRPPRF keyword) and group authority is specified (GRPAUT keyword), that group profile will also have authority to the variable.
Variable instantiation authorization: When
a global variable is instantiated, the DEFAULT clause is evaluated
using the authority of the owner of the global variable.


Example 1: Create a global variable to indicate what printer to use for the session.
CREATE VARIABLE MYSCHEMA.MYJOB_PRINTER VARCHAR(30) DEFAULT 'Default printer'
Example 2: Create a global variable to indicate the department where an employee works.
CREATE VARIABLE SCHEMA1.GV_DEPTNO INTEGER DEFAULT ((SELECT DEPTNO FROM HR.EMPLOYEES WHERE EMPUSER = SESSION_USER))
Example 2: Create a global variable to indicate the security level of the current user.
CREATE VARIABLE SCHEMA2.GV_SECURITY_LEVEL INTEGER DEFAULT ( GET_SECURITY_LEVEL ( SESSION_USER))
