CREATE VARIABLE

The CREATE VARIABLE statement defines a global variable at the application server.

Invocation

This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared.

Authorization

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:

  • For the SYSVARIABLES catalog table:
    • The INSERT privilege on the table, and
    • The system authority *EXECUTE on library QSYS2
  • Start of changeDatabase administrator authorityEnd of change

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:

  • For the distinct type or sequence identified in the statement:
    • The USAGE privilege on the distinct type or sequence, and
    • The system authority *EXECUTE on the library containing the distinct type or sequence
  • Start of changeDatabase administrator authorityEnd of change

If a function is referenced, the privileges held by the authorization ID of the statement must include at least one of the following:

  • For the function identified in the statement:
    • The EXECUTE privilege on the function, and
    • The system authority *EXECUTE on the library containing the function
  • Start of changeDatabase administrator authorityEnd of change

If a global variable is referenced, 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, and
    • The system authority *EXECUTE on the library containing the global variable
  • Start of changeDatabase administrator authorityEnd of change

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:

  • For each table and view referenced directly or indirectly:
    • The SELECT privilege on the table or view, and
    • The system authority *EXECUTE on the library containing the table or view
  • Start of changeDatabase administrator authorityEnd of change

To replace an existing variable, the privileges held by the authorization ID of the statement must include at least one of the following:

  • The following system authorities:
    • The system authority of *OBJMGT on the service program for the variable
    • All authorities needed to DROP the variable
    • The system authority *READ to the SYSVARIABLES catalog table
  • Start of changeDatabase administrator authorityEnd of change

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.

Syntax

Read syntax diagramSkip visual syntax diagramCREATEOR REPLACEVARIABLEvariable-name FOR SYSTEM NAMEsystem-object-identifier data-type DEFAULT NULLDEFAULTconstantspecial-registerglobal-variable(expression)
data-type
Read syntax diagramSkip visual syntax diagrambuilt-in-typedistinct-type-name
built-in-type
Read syntax diagramSkip visual syntax diagramSMALLINTINTEGERINTBIGINTDECIMALDECNUMERICNUM(5,0)(integer,0, integer)FLOAT(53)(integer)REALDOUBLEPRECISIONDECFLOAT(34)(16)CHARACTERCHAR(1)(integer)CHARACTERCHARVARYINGVARCHAR(integer)FOR BIT DATAFOR SBCS DATAFOR MIXED DATAccsid-clauseCHARACTERCHARLARGE OBJECTCLOB(1M)(integerKMG)FOR SBCS DATAFOR MIXED DATAccsid-clauseGRAPHIC(1)(integer)GRAPHIC VARYINGVARGRAPHIC(integer)DBCLOB(1M)(integerKMG)ccsid-clauseNATIONAL CHARACTERNATIONAL CHARNCHAR(1)(integer)NATIONAL CHARACTERNATIONAL CHARNCHARVARYINGNVARCHAR(integer)NATIONAL CHARACTERNCHARLARGE OBJECTNCLOB(1M)(integerKMG)BINARY(1)(integer)BINARY VARYINGVARBINARY(integer)BLOBBINARY LARGE OBJECT(1M)(integerKMG)DATETIME(0)TIMESTAMP(6)(integer)XMLccsid-clause
ccsid-clause
Read syntax diagramSkip visual syntax diagramCCSIDinteger

Description

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.
variable-name
Names the global variable. The name, including the implicit or explicit qualifier, must not identify a global variable that already exists at the current server. If a qualified variable name is specified, the schema-name cannot be QSYS2, QSYS, QTEMP, or SYSIBM.

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:

  • If the value of the CURRENT SCHEMA special register is *LIBL, the variable will be created in the current library (*CURLIB).
  • Otherwise, the variable will be created in the current schema.
Start of changeFOR SYSTEM NAME system-object-identifierEnd of change
Start of changeIdentifies the system-object-identifier of the global variable. system-object-identifier must not be the same as a global variable that already exists at the current server. The system-object-identifier must be an unqualified system identifier.

When system-object-identifier is specified, variable-name must not be a valid system object name.

End of change
data-type
Specifies the data type or the global variable.
built-in-type
Specifies a built-in data type. See CREATE TABLE for a more complete description of each built-in data type.
distinct-type-name
Specifies a distinct type. The length, precision, and scale of the global variable are, respectively, and length, precision, and scale of the distinct type. If a distinct type name is specified without a schema name, the distinct type name is resolved by searching the schemas on the SQL path. The same limitations that apply to built-in types apply to the source type of the distinct type.
DEFAULT
Specifies a default value for the global variable. The value can be a constant, a special register, a global variable, an expression, or the keyword NULL. Start of changeThe default value is determined on its first reference if a value is not explicitly specified.End of change If a default value is not specified, the variable is initialized to the null value.
The default expression must not modify SQL data or perform an external action. The expression must be assignment compatible with the data type of the variable.
All tables, views, aliases, distinct types, sequences, global variables, and user-defined functions referenced in the default expression must exist at the current server when the global variable is created. The table or view that an alias refers to must also exist when the variable is created. This includes objects in library QTEMP. While objects in QTEMP can be referenced in the default expression, dropping those objects in QTEMP will not cause the global variable to be dropped.

Notes

Session scope: Global variables have a session scope. This means that although they are available to all sessions that are active on the database, their value is private for each session.

Modifications to the value of a global variable: Modifications to the value of a global variable are not under transaction control. The value of the global variable is preserved when a transaction ends with either a COMMIT or a ROLLBACK statement.

Privileges to use a global variable: An attempt to read from or to write to a global variable created by this statement requires that the authorization ID attempting this action hold the appropriate privilege on the global variable. The definer of the variable is implicitly granted all privileges on the variable.

Setting of the default value: A created global variable is instantiated to its default value when it is first referenced within its given scope. Note that if a global variable is referenced in a statement, it is instantiated independently of the control flow for that statement.

Using a newly created session global variable: If a global variable is created within a session, it cannot be used by other sessions until the unit of work has been committed. However, the new global variable can be used within the session that created the variable before the unit of work commits.

Once a global variable is instantiated for a session, changes to the global variable in another session (such as DROP or GRANT) might not affect the variable that has been instantiated.

Creating the global variable: A global variable is created as a *SRVPGM object. If the variable name is a valid system name but a *SRVPGM already exists with that name, an error is issued. If the variable name is not a valid system name, a unique name is generated using the rules for generating system table names. For information about the rules for generating a name, see Rules for Table Name Generation.

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.

During restore of the global variable:
  • If a *SRVPGM object with the same system name exists, the *SRVPGM will be replaced.

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 Start of changethreadEnd of change 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.

REPLACE rules: When a variable is recreated by REPLACE:
  • Any existing comment or label is discarded.
  • Authorized users are maintained. The object owner could change.
  • Current journal auditing is preserved.

Examples

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 3: 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))