SET host-variable assignment

The SET host-variable assignment statement assigns values, either of expressions or NULL values, to host variables.

Invocation

This statement can be embedded only in an application program. It is an executable statement that cannot by dynamically prepared.

Authorization

The privileges that are held by the current authorization ID must include those required to execute any of the expressions.

Syntax

>>-SET---------------------------------------------------------->

>--+-host-variable=-+-CURRENT SERVER-------+---------------------------------------+-><
   |                +-CURRENT PACKAGESET---+                                       |   
   |                '-CURRENT PACKAGE PATH-'                                       |   
   | .-,-------------------------------------------------------------------------. |   
   | V                                                                           | |   
   '---+-host-variable=-+-expression-+-----------------------------------------+-+-'   
       |                '-NULL-------'                                         |       
       |   .-,-------------.       .-,--------------.                          |       
       |   V               |       V                |                      (1) |       
       '-(---host-variable-+-)=(-+---+-expression-+-+------------------+-)-----'       
                                 |   '-NULL-------'                    |               
                                 '-VALUES-+-+-expression-+-----------+-'               
                                          | '-NULL-------'           |                 
                                          |    .-,--------------.    |                 
                                          |    V                |    |                 
                                          '-(----+-expression-+-+--)-'                 
                                                 '-NULL-------'

Notes:

The number of expressions and NULL keywords must match the number of host-variables.

Description

host-variable

Identifies one or more host variables or transition variables that are used to receive the corresponding expression or NULL value on the right side of the statement.

For the triggered action of a CREATE TRIGGER statement, use the SET transition-variable instead.

The value to be assigned to each host-variable can be specified immediately following the item reference, for example, host-variable = expression, host-variable=expression. Or, sets of parentheses can be used to specify all the host-variables and then all the values, for example, (host-variable, host-variable) = (expression, expression).

host-variable: Identifies one or more host variables that are used to receive the corresponding expression or NULL value on the right side of the statement. Each host variable must be defined in the program as described under the rules for declaring host variables. A parameter marker must not be specified in place of host-variable.
The value to be assigned to each host-variable can be specified immediately following the host variable, for example, host-variable = expression, host-variable =expression. Or, sets of parentheses can be used to specify all the host-variables and then all the values, for example, (host-variable, host-variable) = (expression, expression).

expression

Specifies the value to be assigned to the corresponding host-variable. The expression is any expression of the type described in Expressions, except it cannot contain a reference to CURRENT PACKAGE PATH or to local special registers (CURRENT SERVER or CURRENT PACKAGESET).

Each assignment to a host variable is made according to the assignment rules described in Assignment and comparison. Assignments are made in sequence through the list. When the host-variables are enclosed within parentheses, for example, (host-variable, host-variable, …) = (expression, expression, …), the first value is assigned to the first host variable in the list, the second value to the second host variable in the list, and so on.

NULL

Specifies the null value and can only be specified for host variables that have an associated indicator variable.

VALUES

Specifies the value to be assigned to the corresponding host variable. When more than one value is specified, the values must be enclosed in parentheses. Each value can be an expression or NULL, as described above. The following syntax is equivalent:

(host-variable, host-variable) = (VALUES(expression, NULL))
(host-variable, host-variable) = (expression, NULL)

Local special registers and the CURRENT PACKAGE PATH special register can be referenced only in a VALUES host-variable statement that results in the assignment of a single host variable and not those that result in setting more than one value.

Notes

Multiple assignments:: If more than one assignment is included in the same SET statement, all expressions are completely evaluated before the assignments are performed. Thus, references to a target host variable in an expression are always the value of the target host variable prior to any assignment in the SET statement.
LOB 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 host variables with other data types. For more information on using locators, see DB2 Application Programming and SQL Guide.
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

Example 1: Set the host variable HVL to the value of the CURRENT PATH special register.

   SET :HVL = CURRENT PATH;

Example 2: Set the host variable PATH to the contents of the SQL PATH special register, the host variable XTIME to the local time at the current server, and the host variable MEM to the current member of the data sharing environment.

   SET :SERVER = CURRENT PATH,
       :XTIME = CURRENT TIME,
       :MEM = CURRENT MEMBER;

Example 3: Set the host variable DETAILS to a portion of a LOB value, using a LOB expression with a LOB locator to refer the extracted portion of the value.

   SET :DETAILS = SUBSTR(:LOCATOR,1,35);

If the LOB data that is specified by the LOB locator LOCATOR is in a different encoding scheme from the value of the ENCODING bind option, and you want to avoid LOB materialization and character conversion, use the following statement instead of the SET statement:

   SELECT SUBSTR(:LOCATOR,1,35)
      INTO :DETAILS
      FROM SYSIBM.SYSDUMMYU;

Example 4: Set host variable HV1 to the results of external function CALC_SALARY and host variable HV2 to the value of special register CURRENT PATH. Use an indicator value with HV1 in case CALC_SALARY returns a null value.

   SET (:HV1:IND1, :HV2) =
       (CALC_SALARY(:HV3, :HF4), CURRENT PATH);