CREATE VARIABLE statement

The CREATE VARIABLE statement creates a global variable at the current server.

Invocation for CREATE VARIABLE

This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared only if DYNAMICRULES RUN behavior is in effect. For more information, see Authorization IDs and dynamic SQL.

Authorization for CREATE VARIABLE

The privilege set that is defined below must include at least one of the following:

  • The CREATEIN privilege on the schema
  • System DBADM authority
  • SYSADM authority
  • SYSCTRL authority
  • Start of changeInstallation SYSOPR authority (when the current SQLID of the process is set to SYSINSTL)End of change

Privilege set: The authorization ID that matches the schema name implicitly has the CREATEIN privilege on the schema. If the statement is embedded in an application program, the privilege set is the set of privileges that are held by the owner of the package. If the owner is a role, the implicit schema does not apply and this role needs to include one of the previously listed privileges or authorities.

If the statement is dynamically prepared, the privilege set is the privileges that are held by the SQL authorization ID of the process unless the process is within a trusted context and the ROLE AS OBJECT OWNER AND QUALIFIER clause is in effect. If the schema name is not the same as the SQL authorization ID of the process, one of the following conditions must be met:

  • The privilege set includes SYSADM or SYSCTRL authority.
  • The SQL authorization ID of the process has the CREATEIN privilege on the schema.

When the ROLE AS OBJECT OWNER AND QUALIFIER clause is in effect, the privilege set is the set privileges that are held by the role. If the schema name does not match this role, one of the following conditions must be met:

  • The privilege set includes SYSADM or SYSCTRL authority.
  • This role has the CREATEIN privilege on the schema.

Syntax for CREATE VARIABLE

Read syntax diagramSkip visual syntax diagram CREATE VARIABLE variable-name data-type DEFAULT NULLDEFAULTconstantspecial-register

data-type:

Read syntax diagramSkip visual syntax diagrambuilt-in-typearray-type-name

built-in-type:

Read syntax diagramSkip visual syntax diagramSMALLINTINTEGERINTBIGINTDECIMALDECNUMERIC(5,0)( integer, integer)FLOAT(53)( integer)REALDOUBLEPRECISIONDECFLOAT(34)(16)CHARACTERCHAR(1)( integer)CHARACTERCHARVARYINGVARCHAR( integer)FORSBCSMIXEDBITDATACHARACTERCHARLARGE OBJECTCLOB(1M)( integerKMG)FORSBCSMIXEDDATAGRAPHIC(1)( integer)VARGRAPHIC(integer)DBCLOB(1M)( integerKMG)BINARY(1)( integer)BINARY VARYINGVARBINARY( integer)BINARY LARGE OBJECTBLOB(1M)( integerKMG)DATETIMETIMESTAMP(6)(integer)WITHOUT TIME ZONEWITH TIME ZONE

Description for CREATE VARIABLE

variable-name
Names the global variable. The name, including the implicit or explicit qualifier, must not identify a global variable that exists at the current server. If the qualifier is not specified, the contents of the CURRENT SCHEMA special register is used.

Start of changeThe schema name must not begin with 'SYS' unless the schema name is 'SYSADM'.End of change

data-type
Specifies the data type of the global variable.
built-in-type
The data type of the global variable is a built-in type. For information about the data types, see built-in-type. The data type cannot be ROWID or XML.
Start of changearray-type-nameEnd of change
Start of changeSpecifies the name of a user-defined array type. The variable is an array that is defined with the CREATE TYPE statement (array type) statement.

If you specify array-type-name without a schema name, Db2 resolves the array type by searching the schemas in the SQL path.

An array type with elements that are defined as character strings must be defined with CCSID UNICODE.

End of change
DEFAULT, DEFAULT NULL, DEFAULT constant, or DEFAULT special-register
The default value that is assigned to the global variable if a value is not explicitly specified. Specifies a value for the global variable when it is first referenced in the session. The default value is determined on this first reference. If the DEFAULT clause is not specified, the default for the global variable is the null value. Start of changeOnly DEFAULT NULL can be explicitly specified if array-type-name is specified.End of change
DEFAULT NULL
Specifies null as the default value for the global variable. The value of a global variable is always nullable.
DEFAULT constant
Specifies that the value of the constant is the default value for the global variable. The value of the constant must conform to the rules for assigning that value to the global variable. constant cannot be any of the constants NAN, SNAN, or INFINITY.
DEFAULT special-register
Specifies that the value of the special register, when the global variable is instantiated, is used as the default value of the global variable. The value of the specified special register must conform to the rules for assigning that value to the global variable. The following special registers must not be specified:
  • CURRENT GET_ACCEL_ARCHIVE
  • CURRENT QUERY_ACCELERATION
  • CURRENT TEMPORAL BUSINESS_TIME
  • CURRENT TEMPORAL SYSTEM_TIME

Notes for CREATE VARIABLE

Session scope:
Global variables have a session scope. Although they are available for use to all sessions that are active at the current server, the value of the global variable is private for each session.
Modifications to the value of a global variable:
Start of changeGlobal variables are not under transaction control. Modifications to the value of a global variable are not affected by either a COMMIT or ROLLBACK statement.End of change
Privileges to use a global variable:
Reading from or writing to a global variable requires that the authorization ID or role that is in effect have the appropriate privileges on the global variable. The owner of the variable is implicitly granted all privileges on the variable.
Setting the default value:
After a global variable has been created, it is instantiated to its default value when it is first referenced within a given scope. If a global variable is referenced within a statement, it is instantiated independently from the execution of that statement.
Using a newly created global variable:
If a global variable is created within a session, it cannot be used by other sessions until the unit of work has committed. However, the newly created global variable can be used within the session in which it is created before the unit of work commits.

Examples for CREATE 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 'Unassigned';

Start of changeExample 3: Create user-defined array type myArrayIntType as an array of integers. Create global variable myGlobalVar with type myArrayIntType.End of change

Start of change
CREATE TYPE myArrayIntType AS INT ARRAY[];
CREATE VARIABLE myGlobalVar myArrayIntType;
End of change