DB2 Version 9.7 for Linux, UNIX, and Windows

CREATE VARIABLE statement

The CREATE VARIABLE statement defines a global variable.

Invocation

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 the package (SQLSTATE 42509).

Authorization

The privileges held by the authorization ID of the statement must include at least one of the following:

IMPLICIT_SCHEMA authority on the database, if the implicit or explicit schema name of the variable does not exist
CREATEIN privilege on the schema, if the schema name of the variable refers to an existing schema
DBADM authority

and any privileges that are necessary to execute the default expression.

To execute this statement with a cursor-value-constructor that uses a select-statement, the privileges held by the authorization ID of the statement must include the privileges necessary to execute the select-statement. See the Authorization section in "SQL queries".

Group privileges are not considered when checking authorization for objects referenced in the statement

To replace an existing variable, the authorization ID of the statement must be the owner of the existing variable (SQLSTATE 42501).

Syntax


>>-CREATE--+------------+--VARIABLE--variable-name--| data-type1 |-->
           '-OR REPLACE-'                                            

   .-DEFAULT NULL-----------------------------------------. (1)   
>--+------------------------------------------------------+-----><
   +-CONSTANT NULL----------------------------------------+       
   '-+-DEFAULT--+--+-constant---------------------------+-'       
     '-CONSTANT-'  +-special-register-------------------+         
                   +-global-variable--------------------+         
                   +-(--| cursor-value-constructor |--)-+         
                   '-(--expression--)-------------------'         

data-type1

|--+-| built-in-type |---------------+--------------------------|
   +-| anchored-variable-data-type |-+   
   +-array-type-name-----------------+   
   +-cursor-type-name----------------+   
   +-distinct-type-name--------------+   
   +-REF--(type-name)----------------+   
   '-row-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-' | |   
   | | '-+-VARCHAR----------------+--(integer)-'                   | |   
   | |   '-+-CHARACTER-+--VARYING-'                                | |   
   | |     '-CHAR------'                                           | |   
   | |                                  .-(1M)-------------.       | |   
   | '-+-CLOB------------------------+--+------------------+-------' |   
   |   '-+-CHARACTER-+--LARGE OBJECT-'  '-(integer-+---+-)-'         |   
   |     '-CHAR------'                             +-K-+             |   
   |                                               +-M-+             |   
   |                                               '-G-'             |   
   |            .-(1)-------.                                        |   
   +-+-GRAPHIC--+-----------+-------+--------------------------------+   
   | |          '-(integer)-'       |                                |   
   | +-VARGRAPHIC--(integer)--------+                                |   
   | |         .-(1M)-------------. |                                |   
   | '-DBCLOB--+------------------+-'                                |   
   |           '-(integer-+---+-)-'                                  |   
   |                      +-K-+                                      |   
   |                      +-M-+                                      |   
   |                      '-G-'                                      |   
   |                          .-(1M)-------------.                   |   
   +-+-BLOB----------------+--+------------------+-------------------+   
   | '-BINARY LARGE OBJECT-'  '-(integer-+---+-)-'                   |   
   |                                     +-K-+                       |   
   |                                     +-M-+                       |   
   |                                     '-G-'                       |   
   +-+-DATE-------------------------+--------------------------------+   
   | +-TIME-------------------------+                                |   
   | |            .-(--6--)-------. |                                |   
   | '-TIMESTAMP--+---------------+-'                                |   
   |              '-(--integer--)-'                                  |   
   |     (2)                                                         |   
   +-XML-------------------------------------------------------------+   
   +-BOOLEAN---------------------------------------------------------+   
   '-CURSOR----------------------------------------------------------'   

anchored-variable-data-type

           .-DATA TYPE-.  .-TO-.                                              
|--ANCHOR--+-----------+--+----+--+-variable-name2------------------------+--|
                                  +-table-name.column-name----------------+   
                                  |      .-OF-.                           |   
                                  '-ROW--+----+--+-table-name-----------+-'   
                                                 +-view-name------------+     
                                                 '-cursor-variable-name-'     

cursor-value-constructor

   .-ASENSITIVE--.                                                    
|--+-------------+--CURSOR--+-------------------------------------+-->
   '-INSENSITIVE-'          |    .-,-------------------------.    |   
                            |    V                           |    |   
                            '-(----| parameter-declaration |-+--)-'   

>--| holdability |--FOR--select-statement-----------------------|

parameter-declaration

|--parameter-name--| data-type2 |-------------------------------|

data-type2

|--+-| built-in-type |----------------+-------------------------|
   +-| anchored-parameter-data-type |-+   
   '-distinct-type-name---------------'   

anchored-parameter-data-type

           .-DATA TYPE-.  .-TO-.                               
|--ANCHOR--+-----------+--+----+--+-variable-name----------+----|
                                  '-table-name.column-name-'   

holdability

   .-WITHOUT HOLD-.   
|--+--------------+---------------------------------------------|
   '-WITH HOLD----'

Notes:

If data-type1 specifies a CURSOR built-in type or cursor-type-name, only NULL or cursor-value-constructor can be specified. Only DEFAULT NULL can be explicitly specified for array-type-name or row-type-name.
For Version 9.7 Fix Pack 5 or earlier fix pack releases, you can use the XML data type only as a parameter data type in a cursor value constructor. For Version 9.7 Fix Pack 6 or later fix pack releases, you can also use the XML data type to create global variables.

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. This option can be specified only by the owner of the object.

variable-name

Names the global variable. The name, including an implicit or explicit qualifier, must not identify a global variable that already exists at the current server (SQLSTATE 42710). If a qualifier is not specified, the current schema is implicitly assigned.

data-type1

Specifies the data type of the global variable. A structured type cannot be specified (SQLSTATE 42611).

built-in-type

Specifies a built-in data type. For a more complete description of each built-in data type except BOOLEAN and CURSOR, which cannot be specified for a table, see "CREATE TABLE". For Version 9.7 Fix Pack 5 or earlier fix pack releases, an XML data type cannot be specified (SQLSTATE 42611).

FOR BIT DATA can be specified as part of character string data types.

BOOLEAN: For a Boolean.
CURSOR: For a reference to an underlying cursor.

anchored-variable-data-type

Identifies another object used to determine the data type of the global variable. The data type of the anchor object has the same limitations that apply to specifying the data type directly, or in the case of a row, to creating a row type.

ANCHOR DATA TYPE TO

Indicates an anchored data type is used to specify the data type.

variable-name2

Identifies a global variable. The data type of the referenced variable is used as the data type for the global variable.

table-name.column-name

Identifies a column name of an existing table or view. The data type of the column is used as the data type for the global variable.

ROW OF table-name or view-name

Specifies that the global variable is a row of fields with names and data types that are based on the column names and column data types of the table identified by table-name or the view identified by view-name. The data type of the global variable is an unnamed row type.

ROW OF cursor-variable-name

Specifies a row of fields with names and data types that are based on the field names and field data types of the cursor variable identified by cursor-variable-name. The specified cursor variable must be one of the following (SQLSTATE 428HS):

A global variable with a strongly typed cursor data type
A global variable with a weakly typed cursor data type that was created or declared with a CONSTANT clause specifying a select-statement where all the result columns are named.

If the cursor type of the cursor variable is not strongly-typed using a named row type, the data type of the global variable is an unnamed row type.

array-type-name

Specifies the name of a user-defined array type. If array-type-name is specified without a schema name, the array type is resolved by searching the schemas in the SQL path.

cursor-type-name

Specifies the name of a cursor type. If cursor-type-name is specified without a schema name, the cursor type is resolved by searching the schemas in the SQL path.

distinct-type-name

Specifies the name of a distinct type. The length, precision, and scale of the declared variable are, respectively, the length, precision, and scale of the source type of the distinct type. If distinct-type-name is specified without a schema name, the distinct type is resolved by searching the schemas in the SQL path.

REF (type-name)

Specifies a reference type. If a type name is specified without a schema name, the type-name is resolved by searching the schemas in the SQL path.

row-type-name

Specifies the name of a user-defined row type. The fields of the variable are the fields of the row type. If row-type-name is specified without a schema name, the row type is resolved by searching the schemas in the SQL path.

DEFAULT or CONSTANT

Specifies a value for the global variable when it is first referenced in the session. The DEFAULT or CONSTANT clause value is determined on this first reference. If neither is specified, the default for the global variable is the null value. Only DEFAULT NULL can be explicitly specified if array-type-name or row-type-name is specified.

DEFAULT

Defines the default for the global variable. The default value must be assignment-compatible with the data type of the variable.

CONSTANT

Specifies that the global variable has a fixed value that cannot be changed. A global variable that is defined using CONSTANT cannot be used as the target of any assignment operation. The fixed value must be assignment-compatible with the data type of the variable.

NULL

Specifies NULL as the default for the global variable. If row-type-name is specified, the value for the global variable is a row where each field has the null value.

constant

Specifies the value of a constant as the default for the global variable. If data-type1 specifies a CURSOR built-in type or cursor-type-name, constant cannot be specified (SQLSTATE 42601).

special-register

Specifies the value of a special register as the default for the global variable. If data-type1 specifies a CURSOR built-in type or cursor-type-name, special-register cannot be specified (SQLSTATE 42601).

global-variable

Specifies the value of a global variable as the default for the global variable. If data-type1 specifies a CURSOR built-in type or cursor-type-name, global-variable cannot be specified (SQLSTATE 42601).

cursor-value-constructor

A cursor-value-constructor specifies the select-statement that is associated with the global variable. The assignment of a cursor-value-constructor to a cursor variable defines the underlying cursor of that cursor variable.

ASENSITIVE or INSENSITIVE

Specifies whether the cursor is asensitive or insensitive to changes. See "DECLARE CURSOR" for more information. The default is ASENSITIVE.

ASENSITIVE: Specifies that the cursor should be as sensitive as possible to insert, update, or delete operations made to the rows underlying the result table, depending on how the select-statement is optimized. This option is the default.
INSENSITIVE: Specifies that the cursor does not have sensitivity to insert, update, or delete operations that are made to the rows underlying the result table. If INSENSITIVE is specified, the cursor is read-only and the result table is materialized when the cursor is opened. As a result, the size of the result table, the order of the rows, and the values for each row do not change after the cursor is opened. The SELECT statement cannot contain a FOR UPDATE clause, and the cursor cannot be used for positioned updates or deletes.

(parameter-declaration, ...)

Specifies the input parameters of the cursor, including the name and the data type of each parameter.

parameter-name

Names the parameter for use as an SQL variable within select-statement. The name cannot be the same as any other parameter name for the cursor. Names should also be chosen to avoid any column names that could be used in select-statement, since column names are resolved before parameter names.

data-type2

Specifies the data type of the cursor parameter used within select-statement.

built-in-type: Specifies a built-in data type. For a more complete description of each built-in data type, see "CREATE TABLE". The BOOLEAN and CURSOR built-in types cannot be specified (SQLSTATE 429BB).

anchored-parameter-data-type

Identifies another object used to determine the data type of the cursor parameter. The data type of the anchor object is bound by the same limitations that apply when specifying the data type directly.

ANCHOR DATA TYPE TO

Indicates an anchored data type is used to specify the data type.

variable-name: Identifies a global variable. The data type of the referenced variable is used as the data type for the cursor parameter.
table-name.column-name: Identifies a column name of an existing table or view. The data type of the column is used as the data type for the cursor parameter.

distinct-type-name: Specifies the name of a distinct type. If distinct-type-name is specified without a schema name, the distinct type is resolved by searching the schemas in the SQL path.

holdability

Specifies whether the cursor is prevented from being closed as a consequence of a commit operation. See "DECLARE CURSOR" for more information. The default is WITHOUT HOLD.

WITHOUT HOLD: Does not prevent the cursor from being closed as a consequence of a commit operation.
WITH HOLD: Maintains resources across multiple units of work. Prevents the cursor from being closed as a consequence of a commit operation.

select-statement

Specifies the SELECT statement of the cursor. See "select-statement" for more information.

statement-name

Specifies the prepared select-statement of the cursor. See "PREPARE" for an explanation of prepared statements. The target cursor variable must not have a data type that is a strongly-typed user-defined cursor type (SQLSTATE 428HU).

expression

Specifies the value of an expression as the default for the global variable. The expression can be any expression of the type described in "Expressions". The expression must be assignment-compatible with the data type of the variable. The maximum size of the expression is 64K. The default expression must not modify SQL data (SQLSTATE 428FL) or perform external action (SQLSTATE 42845). If data-type1 specifies a CURSOR built-in type or cursor-type-name, expression cannot be specified (SQLSTATE 42601).

Rules

Use of anchored data types: An anchored data type cannot refer to (SQLSTATE 428HS): a nickname, typed table, typed view, declared temporary table, row definition associated with a weakly typed cursor, object with a code page or collation that is different from the database code page or database collation.

Notes

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.
Contexts for array, Boolean, cursor, and row global variables: Global variables that are array variables, Boolean variables, or row variables can only be used in compound SQL (compiled) statements or SET variable statements. Global variables that are cursor variables can only be used in compound SQL (compiled) statements.
Create with errors: If an object referenced in the default expression does not exist or is marked invalid, or the definer temporarily doesn't have privileges to access the object, and if the database configuration parameter auto_reval is not set to DISABLED, then the variable will still be created successfully. The variable will be marked invalid and will be revalidated the next time it is invoked.
Scope of global variable values: The values for global variables persist until they are updated in the current session, the global variable is dropped or altered, or the application session ends. The value is unaffected by COMMIT or ROLLBACK statements. The default value for a global variable can be not deterministic and dependent on when the default value is calculated for the global variable (for example, a reference to the time of day, or a reference to some data stored in a table).
A technique commonly used, especially for performance, is for an application or product to manage a set of connections and route transactions to an arbitrary connection. In these situations, the non-default value of a global variable or the not deterministic initial default value for a global variable should only be relied on until the end of the transaction. Examples of where this type of situation can occur include applications that: use XA protocols, use connection pooling, use the connection concentrator, and use HADR to achieve failover.
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 committed. However, the new global variable can be used within the session that created the variable before the unit of work commits.

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

Example 4: Create a global variable as a cursor on the STAFF table that returns the names of each employee for the specified job type. Order the results by the department number.

   CREATE VARIABLE STAFFJOBS CURSOR
     CONSTANT (CURSOR (WHICHJOB CHAR(5)) 
     FOR SELECT NAME, DEPT FROM STAFF WHERE JOB = WHICHJOB 
       ORDER BY DEPT)

Example 5: Create a global variable of the XML data type:

   CREATE VARIABLE MYSCHEMA.CUSTOMER_HISTORY_VAR XML