SET assignment-statement
The SET assignment-statement statement assigns values to variables and array elements.
Invocation
This statement can be embedded in an application program or issued interactively. It is an executable statement that can by dynamically prepared if the targets of all assignments are global variables.
Authorization
The privileges that are held by the privilege set must include those required to execute any of the expressions.
If a
row-subselect is specified, see Queries for an explanation of the authorization
that is required for each subselect.
If a global variable is
referenced, the privilege set must include at least one of the following:

- The READ privilege on the variable
- Ownership of the variable
- DATAACCESS authority
- SYSADM authority
For an assignment to a global
variable, the privilege set must include at least one of the following:

- The WRITE privilege on the variable
- Ownership of the variable
- DATAACCESS authority
- SYSADM authority
For an assignment
to a transition variable, the privilege set must include at least one of the following:

- The UPDATE privilege on the table or view on which the trigger that contains the assignment statement is defined
- The UPDATE privilege on the column corresponding to the transition variable to be assigned a value
- Ownership of the table or view on which the trigger that contains the assignment statement is defined
- DBADM authority on the database that contains the table on which the trigger that contains the assignment statement is defined
- DATAACCESS authority
- SYSADM authority
Privilege set: If the statement is embedded in an application program, the
privilege set is the privileges that are held by the owner of the package. If the statement is
dynamically prepared, the privilege set is determined by the DYNAMICRULES behavior in effect (run,
bind, define, or invoke) and is summarized in DYNAMICRULES behaviors
and authorization checking. For more information on these behaviors, including a list of the
DYNAMICRULES bind option values that determine them, see Authorization IDs and dynamic SQL.

Syntax
- 1 target-variable must not be an array type in this context.
- 2 When the target is not a transition variable, SQL parameter, or an SQL variable that is used in a native SQL procedure or compiled SQL function, these special registers can be referenced only as a source value in this form of the syntax for this statement.
- 3 DEFAULT must only be specified when the corresponding target is a transition variable.
- 4 The number of source value specifications (expression, NULL, or DEFAULT) on the right side of the equal sign must match the number of target specifications on the left side of the statement.
- 5 row-subselect can be specified only in the outermost subselect within SQL PL. target-variable cannot be a global-variable-name or transition-variable-name.

Description
- array-variable [array-index]
- Specifies an array element that is the target of the assignment.
An array element must not be specified as the target for an assignment if common-table-expression is also specified in the statement.
- array-variable
- Specifies an array variable.
- [array-index]
- An expression that specifies which element in the array is the
target of the assignment.
For an ordinary array, the array index expression must be castable to INTEGER, and must not be the null value. The index value must be between 1 and the maximum cardinality that is defined for the array.
For an associative array, the array index expression must be castable to the index data type of the associative array, and must not be the null value.
array-index must not be:
- An expression that references the CURRENT DATE, CURRENT TIME, or CURRENT TIMESTAMP special register
- A nondeterministic function
- A function that is defined with EXTERNAL ACTION
- A function that is defined with MODIFIES SQL DATA
- A sequence expression
The value that is to be assigned to an array element must be specified immediately following the array element. For example:
array-variable[array-index]=expression - expression
Specifies the value that is to be assigned
to the corresponding assignment target. The expression is any expression
of the type described in Expressions. The expression cannot contain a reference to
the CURRENT PACKAGESET, CURRENT PACKAGE PATH, or CURRENT SERVER special
register, unless the expression appears in a native SQL procedure
or compiled SQL function. All expressions are evaluated before
any result is assigned to a target. If an expression refers to a variable
or array element that is used in the list of assignment targets, the
value of the variable or array element in the expression is the value
of the variable or array element prior to any assignments.Each assignment to a target is made according to the assignment rules described in Assignment and comparison. When the target variables and expressions are in the following form, the first value is assigned to the first target variable in the list, the second value is assigned to the second target variable in the list, and so on.(target-variable,target-variable,…)=(expression,expression,…)
- DEFAULT
- Specifies that the default value is used based on how the corresponding column is defined in the
table. The value that is assigned depends on how the column is defined.
- If the column is defined using the IDENTITY clause, the column is generated by the Db2 system.
- If the column is defined as a row change timestamp column, the column value is generated by the Db2 system.
- If the column is defined using the WITH DEFAULT clause, the value is set to the default that is defined for the column.
- If the column is defined without specifying the WITH DEFAULT clause, the GENERATED clause, or the NOT NULL clause, the value is NULL
- If the column is specified in the INCLUDE column list, the column value is set to null.
A ROWID column must not be set to the DEFAULT keyword.
An identity column or a row change timestamp column that is defined as GENERATED ALWAYS can be set only to the DEFAULT keyword.
If the column is defined using the NOT NULL clause and the GENERATED clause is not used, or the WITH DEFAULT clause is not used, the DEFAULT keyword cannot be specified for that column.
- NULL
- Specifies the null value and can only be specified for host variables that have an associated indicator variable.
- VALUES
- Specifies the values that are to be assigned to the corresponding
assignment targets. When more than one value is specified, the values
must be enclosed in parentheses. Each value can be an expression or
NULL, as previously described. The following syntaxes are equivalent:
- (target-variable, target-variable) = (VALUES(expression, NULL))
- (target-variable, target-variable) = (expression, NULL)
The CURRENT APPLICATION ENCODING SCHEME, CURRENT PACKAGESET, CURRENT PACKAGE PATH, or CURRENT SERVER special registers can be referenced only in a SET assignment-statement statement or SQL PL assignment-statement statement that results in the assignment of a single target.
A parameter marker must not be specified.
- target-variable
- Identifies one or more targets for the assignment of values. The number of targets must equal
the number of values that are to be assigned.
The value that is to be assigned to each target variable can be specified immediately following the variable. For example:
variable=expression, variable=expressionAlternatively, sets of parentheses can be used to specify all of the target variables, and then all of the values. For example:
(variable,variable)=(expression,expression)The data type of each variable in the variable list must be compatible with its corresponding result column. Each assignment to a target-variable is made in sequence through the list, according to the rules described in Assignment and comparison.
The value 'W' is assigned to the SQLWARN3 field of the SQLCA if the number of targets is less than the number of result column values.
If an error occurs on any assignment, the value is not assigned to the target, and no more values are assigned to the specified targets. Any values that have already been assigned remain assigned.
- global-variable-name
- Identifies the global variable that is the assignment target.
- host-variable-name
- Identifies the host variable that is the assignment target. For LOB output values, the target can be a regular host variable (if it is large enough), a LOB locator variable, or a LOB file reference variable.
- SQL-parameter-name
- Identifies the parameter that is the assignment target.
- SQL-variable-name
- Identifies the SQL variable that is the assignment target. SQL variables must be declared before they are used.
- transition-variable-name
- Identifies the column that is to be updated in the transition row. A transition variable name
must identify a column in the subject table of a trigger, and is optionally qualified by a
correlation name that identifies the new value.
transition-variable-name must not correspond to a begin column or end column of a BUSINESS_TIME period, and must not be specified if the statement contains a period-clause.
Notes
- Multiple assignments:
- If more than one assignment is included in the same SET statement, all expressions and row-subselects are completely evaluated before the assignments are performed. Thus, references to a target variable in an expression or row-subselect are always the value of the target variable prior to any assignment in the SET statement.
- LOBs assignments:
- Normally, you use LOB locators to assign and retrieve data from LOB columns. However, because of compatibility rules, you can also use LOB locators to assign data to targets with other data types. For more information on using locators, see Saving storage when manipulating LOBs by using LOB locators.
- Default encoding scheme:
- The default encoding scheme for the data is the value in the bind option ENCODING, which is the option for application encoding. If this statement is used with functions such as LENGTH or SUBSTRING that are operating on LOB locators, and the LOB data that is specifies by the locator is in a different encoding scheme from the ENCODING bind option, LOB materialization and character conversion occur. To avoid LOB materialization and character conversion, select the LOB data from the SYSIBM.SYSDUMMYA, SYSIBM.SYSDUMMYE, or SYSIBM.SYSDUMMYU sample table.
Examples
SET :HVL = CURRENT PATH; SET :SERVER = CURRENT PATH,
:XTIME = CURRENT TIME,
:MEM = CURRENT MEMBER; SET :DETAILS = SUBSTR(:LOCATOR,1,35); SELECT SUBSTR(:LOCATOR,1,35)
INTO :DETAILS
FROM SYSIBM.SYSDUMMYU; SET (:HV1:IND1, :HV2) =
(CALC_SALARY(:HV3, :HF4), CURRENT PATH); SET (SALARY, COMMISSION) = (50000, 8000); SET NEWROW.COMMISSION = 1.1 * OLDROW.COMMISSION;SET CANADACAPITALS['British Columbia'] = 'Victoria';
SET CANADACAPITALS['Alberta'] = 'Edmonton';
SET CANADACAPITALS['Manitoba'] = 'Winnipeg';
SET CANADACAPITALS['Ontario'] = 'Toronto';
SET CANADACAPITALS['Nova Scotia'] = 'Halifax';In the CANADACAPITALS array, the array index values are province names, and the associated array element values are the names of the corresponding capital cities. The order in which values are assigned to associative array elements does not matter. The elements of an associative array are stored in the array in ascending order of the associated array index values.
SET CANADACAPITALSA['British Columbia'] = 'Victoria';
SET CANADACAPITALSA['Alberta'] = 'Edmonton';
SET CANADACAPITALSA['Manitoba'] = 'Winnipeg';
SET CANADACAPITALSA['Ontario'] = 'Toronto';
SET CANADACAPITALSA['Nova Scotia'] = 'Halifax';Use a single SET assignment-statement statement to assign all of the values that are in CANADACAPITALSA to CANADACAPITALSB.
SET CANADACAPITALSB = CANADACAPITALSA;SET P_PHONENUMBERS = ARRAY[9055553907, 4165554213, 4085553678];SET P_PHONENUMBERS =
ARRAY [SELECT NUMBER
FROM PHONENUMBERS
WHERE EMPID = 624];SET P_PHONENUMBERS[1] = P_MYNUMBER;
SET P_PHONENUMBERS[10] = P_MYNUMBER;

