DB2 10.5 for Linux, UNIX, and Windows

CREATE VARIABLE statement

The CREATE VARIABLE statement defines a session 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 authorities:
  • 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

Read syntax diagramSkip visual syntax diagram
>>-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-+-------------+-)-'          |  |              (2) | | |   
   | | |                           +-OCTETS------+              |  '-FOR BIT DATA-----' | |   
   | | |                           '-CODEUNITS32-'              |                       | |   
   | | '-+-VARCHAR----------------+--(integer-+-------------+-)-'                       | |   
   | |   '-+-CHARACTER-+--VARYING-'           +-OCTETS------+                           | |   
   | |     '-CHAR------'                      '-CODEUNITS32-'                           | |   
   | |                                  .-(1M)-----------------------------.            | |   
   | '-+-CLOB------------------------+--+----------------------------------+------------' |   
   |   '-+-CHARACTER-+--LARGE OBJECT-'  '-(integer-+---+-+-------------+-)-'              |   
   |     '-CHAR------'                             +-K-+ +-OCTETS------+                  |   
   |                                               +-M-+ '-CODEUNITS32-'                  |   
   |                                               '-G-'                                  |   
   |            .-(1)------------------------.                                            |   
   +-+-GRAPHIC--+----------------------------+------+-------------------------------------+   
   | |          '-(integer-+-------------+-)-'      |                                     |   
   | |                     +-CODEUNITS16-+          |                                     |   
   | |                     '-CODEUNITS32-'          |                                     |   
   | +-VARGRAPHIC--(integer-+-------------+-)-------+                                     |   
   | |                      +-CODEUNITS16-+         |                                     |   
   | |                      '-CODEUNITS32-'         |                                     |   
   | |         .-(1M)-----------------------------. |                                     |   
   | '-DBCLOB--+----------------------------------+-'                                     |   
   |           '-(integer-+---+-+-------------+-)-'                                       |   
   |                      +-K-+ +-CODEUNITS16-+                                           |   
   |                      +-M-+ '-CODEUNITS32-'                                           |   
   |                      '-G-'                                                           |   
   |                          .-(1M)-------------.                                        |   
   +-+-BLOB----------------+--+------------------+----------------------------------------+   
   | '-BINARY LARGE OBJECT-'  '-(integer-+---+-)-'                                        |   
   |                                     +-K-+                                            |   
   |                                     +-M-+                                            |   
   |                                     '-G-'                                            |   
   +-+-DATE-------------------------+-----------------------------------------------------+   
   | +-TIME-------------------------+                                                     |   
   | |            .-(--6--)-------. |                                                     |   
   | '-TIMESTAMP--+---------------+-'                                                     |   
   |              '-(--integer--)-'                                                       |   
   |     (3)                                                                              |   
   +-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:
  1. 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.
  2. The FOR BIT DATA clause can be specified in any order with the other column constraints that follow. The FOR BIT DATA clause cannot be specified with string units CODEUNITS32 (SQLSTATE 42613).
  3. For Version 10.1, you can use the XML data type only as a parameter data type in a cursor value constructor. For Version 10.1 Fix Pack 1 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. If the global variable name is explicitly qualified with a schema name, the schema name must not begin with the characters 'SYS' (SQLSTATE 42939).
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. BOOLEAN and CURSOR cannot be specified for a table. For Version 10.1, an XML data type cannot be specified (SQLSTATE 42611). The XML data type support starts in Version 10.1 Fix Pack 1. For a more complete description of each built-in data type, see "CREATE TABLE".

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 elements (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. 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

Notes

Examples