EXECUTE

The EXECUTE statement executes a prepared SQL statement.

Invocation

This statement can only be embedded in an application program. It is an executable statement that cannot be dynamically prepared. It must not be specified in Java™.

Authorization

See PREPARE for the authorization required to create a prepared statement.

Syntax

Read syntax diagramSkip visual syntax diagram EXECUTE statement-name USING,variableUSING DESCRIPTORdescriptor-namesource-row-data1
Notes:
  • 1 This option can be specified only when statement-name refers to a dynamic INSERT or MERGE statement that is prepared with FOR MULTIPLE ROWS and is specified as part of the ATTRIBUTES clause on the PREPARE statement.

source-row-data:

Read syntax diagramSkip visual syntax diagramUSING,host-variable-arrayhost-variableUSING DESCRIPTORdescriptor-nameFORhost-variableinteger-constantROWS1
Notes:
  • 1 The FOR n ROWS clause is required on the EXECUTE statement if it is not specified as part of the MERGE statement and a host-variable array is specified. The FOR n ROWS clause is also required if MERGE is used with multiple rows of source data. For an INSERT statement, the FOR n ROWS clause can only be specified for a dynamic statement that contains only a single multiple-row INSERT statement.

Description

statement-name
Identifies the prepared statement to be executed. statement-name must identify a statement that was previously prepared within the unit of work and the prepared statement must not be a select-statement.
USING
Introduces a list of variables whose values are substituted for the parameter markers (question marks) in the prepared statement. (For an explanation of parameter markers, see PREPARE.) If the prepared statement includes parameter markers, you must include USING in the EXECUTE statement. USING is ignored if there are no parameter markers.

The nth value corresponds to the nth parameter marker in the prepared statement. Where appropriate, locator variables and file reference variables can be provided as the source of values for parameter markers. Start of changeA global variable must not be specified.End of change

For more on the substitution of values for parameter markers, see Parameter marker replacement.
variable,...
Identifies a variable or a host structure that is declared in the application program in accordance with the rules for declaring variables and host structures. When the statement is executed, a reference to a structure is replaced by a reference to each of its variables. The number of variables must be the same as the number of parameter markers in the prepared statement.
USING DESCRIPTOR descriptor-name
Identifies an SQLDA that contains a valid description of the input host variables.

Before invoking the EXECUTE statement, you must set the following fields in the SQLDA:

  • SQLN to indicate the number of SQLVAR occurrences that are provided in the SQLDA

    A REXX SQLDA does not contain this field.

  • SQLABC to indicate the number of bytes of storage that are allocated for the SQLDA
  • SQLD to indicate the number of variables that are used in the SQLDA when processing the statement
  • SQLVAR entries to indicate the attributes of the variables

The SQLDA must have enough storage to contain all SQLVAR entries. If an SQLVAR entry includes a LOB value or a distinct type based on a LOB, there must be additional SQLVAR entries for each parameter. For more information on the SQLDA, which includes a description of the SQLVAR and an explanation on how to determine the number of SQLVAR entries, see SQL descriptor area (SQLDA).

SQLD must be set to a value that is greater than or equal to zero and less than or equal to SQLN. It must be the same as the number of parameter markers in the prepared statement. The nth variable described by the SQLDA corresponds to the nth parameter marker in the prepared statement.

See Identifying an SQLDA in C or C++ for how to represent descriptor-name in C.

source-row-data
The prepared statement must be an INSERT or MERGE statement for which the FOR MULTIPLE ROWS clause is specified as part of the ATTRIBUTES clause on the PREPARE statement.
USING host-variable-array or host-variable
Introduces a list of host variables or host-variable arrays whose values are substituted for the parameter markers (question marks) in the prepared INSERT or MERGE statement. The number of columns specified in the INSERT or MERGE statement must be less than or equal to the total number of host variables or host-variable arrays that are specified.
host-variable-array
Identifies a host-variable array that must be defined in the application program in accordance with the rules for declaring a host-variable array. A reference to a structure is replaced by a reference to each of its variables. The number of variables must be the same as the number of parameter markers in the prepared statement. The nth variable supplies the value for the nth parameter marker in the prepared statement.

host-variable-array is supported in C/C++, COBOL, and PL/I. For more information, see Host-variable arrays in PL/I, C, C++, and COBOL.

host-variable
Identifies a variable that must be described in the application program in accordance with the rules for declaring host variables.
USING DESCRIPTOR descriptor-name
Identifies an SQLDA that must contain a valid description of the host-variable arrays or host variables that contain the values to insert.

Before invoking the EXECUTE statement for a dynamic INSERT or MERGE statement, you must set the following fields in the SQLDA:

  • SQLN to indicate the number of SQLVAR entries that are provided in the SQLDA.
  • SQLABC to indicate the number of bytes of storage that are allocated for the SQLDA.
  • SQLD to indicate the number of variables, plus one, that are used in the SQLDA that provide values for columns that are the source of the INSERT or MERGE statement. SQLD must be set to a value that is greater than or equal to zero and less than or equal to SQLN.
  • SQLVAR entries to indicate the attributes of an element of the host-variable array for the SQLVAR entries that correspond to values that are provided for the source columns of the INSERT or MERGE statement. Within each SQLVAR, the following fields are set:
    • SQLTYPE indicates the data type of the elements of the host-variable array.
    • SQLDATA points to the corresponding host-variable array.
    • SQLLEN and SQLLONGLEN indicate the length of a single element of the array.
  • SQLNAME, the fifth and sixth bytes must contain a flag field and the seventh and eighth bytes must contain a binary small integer (halfword) that contains the dimension of the host-variable array and, if specified, the corresponding indicator array.

The SQLDA must have enough storage to contain a SQLVAR entry for each target column for which values are provided, plus an additional SQLVAR entry for the number of rows. The Db2 system generates code to enter the required information for this extra SQLVAR entry. Each SQLVAR entry describes a host variable, host-variable array, or buffer that contains the values for a column of the source table. The last SQLVAR entry contains the number of rows of data. For example, if the INSERT or MERGE statement is providing values for five columns of the target table, six SQLVAR entries must be provided. If any value is a LOB value, twice as many SQLVAR entries must be provided, and SQLN must be set to the number of SQLVAR entries. Thus, if the INSERT or MERGE statement is providing values for five columns of the source table, and some of the values to insert are LOB values, 12 SQLVAR entries must be provided.

The SQLVAR entry for the number of rows must also contain a flag value. See Field descriptions of an occurrence of a base SQLVAR for more information.

You set the SQLDATA and SQLIND pointers to the beginning of the corresponding arrays.

FOR host-variable or integer-constant ROWS
Specifies the number of rows of source data. The values for the insert or merge operation are specified in the USING clause.

host-variable or integer-constant is assigned to an integral value k. If host-variable is specified, it must be an exact numeric type with a scale of zero and must not include an indicator variable. k must be in the range 0 to 32767.

FOR n ROWS cannot be specified on the EXECUTE statement if the statement being processed is a dynamic INSERT or MERGE statement that includes a FOR n ROWS clause.

Notes

Excessive processor time:
Db2 can stop the execution of a prepared SQL statement if the statement is taking too much processor time to finish. When this happens, an error occurs. The application that issued the statement is not terminated; it is allowed to issue another SQL statement.
Parameter marker replacement:
Before the prepared statement is executed, each parameter marker in the statement is effectively replaced by its corresponding host variable. The replacement is an assignment operation in which the source is the value of the host variable and the target is a variable within Db2. The assignment rules are those described for assignment to a column in Assignment and comparison. For a typed parameter marker, the attributes of the target variable are those specified by the CAST specification. For an untyped parameter marker, the attributes of the target variable are determined according to the context of the parameter marker. For the rules that affect parameter markers, see Parameter markers.

Let V denote a host variable that corresponds to parameter marker P. The value of V is assigned to the target variable for P in accordance with the rules for assigning a value to a column:

  • V must be compatible with the target.
  • If V is a string, its length must not be greater than the length attribute of the target.
  • If V is a number, the absolute value of its integral part must not be greater than the maximum absolute value of the integral part of the target.
  • If the attributes of V are not identical to the attributes of the target, the value is converted to conform to the attributes of the target.
  • If the target cannot contain nulls, V must not be null.

When the prepared statement is executed, the value used in place of P is the value of the target variable for P. For example, if V is CHAR(6) and the target is CHAR(8), the value used in place of P is the value of V padded on the right with two blanks.

Errors occurring on EXECUTE:
In local and remote processing, the DEFER(PREPARE) and REOPT(ALWAYS)/REOPT(ONCE) bind options can cause some errors that are normally issued during PREPARE processing to be issued on EXECUTE.
Start of changeConsiderations for executing data definition statements written in native SQL language:End of change
Start of changeA data definition statement written in native SQL language can only be executed one time. To execute the data definition statement multiple times, issue the PREPARE statement prior to each use of the EXECUTE statement for the data definition statement.End of change

Examples

Example 1
In this example, an INSERT statement with parameter markers is prepared and executed. S1 is a structure that corresponds to the format of DSN8B10.DEPT.
   EXEC SQL PREPARE DEPT_INSERT FROM
     'INSERT INTO DSN8B10.DEPT VALUES(?,?,?,?)';
   -- Check for successful execution and read values into S1
   EXEC SQL EXECUTE DEPT_INSERT USING :S1;
Example 2
Assume that the IWH.PROGPARM table has 9 columns. Prepare and execute a dynamic INSERT statement that inserts 5 rows of data into the IWH.PROGPARM table. The values to be inserted are provided in arrays, where all the values for a column are provided in an host-variable-array with the EXECUTE statement.
STMT = 'INSERT INTO IWH.PROGPARM  (IWHID, UPDATE_BY,UPDATE_TS,NAME,
                                   SHORT_DESCRIPTION, ORDERNO, PARMDATA, 
                                   PARMDATALONG, VWPROGKEY)  
VALUES ( ? , ? , ? , ? , ? , ? , ? , ? , ? )';  
ATTRVAR = 'FOR MULTIPLE ROWS';
EXEC SQL PREPARE INS_STMT ATTRIBUTES :ATTRVAR FROM :STMT;
NROWS = 5;
EXEC SQL EXECUTE INS_STMT FOR :NROWS ROWS 
           USING :V1, :V2, :V3, :V4, :V5, :V6, :V7, :V8, :V9; 

In this example, each host variable in the USING clause represents an array of values for the corresponding column of the target of the INSERT statement.

Example 3
Using dynamically supplied values for an employee row, update the master EMPLOYEE table if the data is for an existing employee or insert a new row if the data is for a new employee.
hv_stmt = 
  "MERGE INTO EMPLOYEE AS T
    USING (VALUES (CAST (? AS CHAR(6)), CAST (? AS VARCHAR(12)),
                  CAST (? AS CHAR(1)), CAST (? AS VARCHAR(15)),
                  CAST (? AS INTEGER)))
    AS S (EMPNO, FIRSTNAME, MI, LASTNAME, SALARY)
    ON T.EMPNO = S.EMPNO
    WHEN MATCHED THEN UPDATE
        SET SALARY = S.SALARY
    WHEN NOT MATCHED THEN INSERT (EMPNO, FIRSTNAME, MI, LASTNAME, SALARY)
        VALUES (S.EMPNO, S.FIRSTNAME, S.MI, S.LASTNAME, S.SALARY)
    NOT ATOMIC CONTINUE ON SQLEXCEPTION";
hv_attr = 'FOR MULTIPLE ROWS';
EXEC SQL 
    PREPARE merge_stmt 
     ATTRIBUTES :hv_attr FROM :hv_stmt;
hv_nrows = 5;
/* Initialize the hostvar array of hv_empno, hv_firstname... */
EXEC SQL 
   EXECUTE merge_stmt 
   USING :hv_empno, :hv_firstname, :hv_mi, 
         :hv_lastname, :hv_salary
      FOR :hv_nrows ROWS;
Example 4
Start of change

Start of change Suppose that the following array type, array variable, and table have been defined.End of change

Start of change
CREATE TYPE INTARRAY AS INTEGER ARRAY[100];
CREATE TYPE STRINGARRAY AS VARCHAR(10) ARRAY[100];
CREATE TABLE T1 (COL1 CHAR(10), COL2 INT);
End of change

Start of changeUse as an array variable as an input value for an expression in an EXECUTE statement.End of change

Start of change
CREATE PROCEDURE PROCESSPERSONS (OUT WITHO STRINGARRAY, INOUT INT0 INT)
BEGIN
 DECLARE INTA INTARRAY;
 DECLARE STMT CHAR(100);
-- Initialize the array
 SET INTA = ARRAY[1,INTEGER(2),3+0,4,5,6] ;
-- Use dynamic sql with an array parameter marker to
--  provide a value for a dynamic INSERT statement
 SET STMT = 'INSERT INTO T1 VALUES('XYZ', CARDINALITY(CAST(? AS INTARRAY)))';
 PREPARE INS_STMT FROM STMT;
 EXECUTE INS_STMT USING INTA; 
-- INTA is an array variable used as input for the 
-- INSERT statement
…
END
End of change End of change