SQL Reference

EXECUTE

The EXECUTE statement executes a prepared SQL statement.

Invocation

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

Authorization

For statements where authorization checking is performed at statement execution time (DDL, GRANT, and REVOKE statements), the privileges held by the authorization ID of the statement must include those required to execute the SQL statement specified by the PREPARE statement. For statements where authorization checking is performed at statement preparation time (DML), no authorization is required to use this statement.

Syntax

>>-EXECUTE--statement-name-------------------------------------->
 
>-----+------------------------------------+-------------------><
      |        .-,----------------.        |
      |        V                  |        |
      +-USING-----host-variable---+--------+
      '-USING DESCRIPTOR--descriptor-name--'
 

Description

statement-name

Identifies the prepared statement to be executed. The statement-name must identify a statement that was previously prepared and the prepared statement must not be a SELECT statement.

USING

Introduces a list of host variables for which 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, USING must be used.

host-variable, ...

Identifies a host variable that is declared in the program in accordance with the rules for declaring host variables. The number of variables must be the same as the number of parameter markers in the prepared statement. The nth variable corresponds to the nth parameter marker in the prepared statement. Locator variables and file reference variables, where appropriate, can be provided as the source of values for parameter markers.

DESCRIPTOR descriptor-name

Identifies an input SQLDA that must contain a valid description of host variables.

Before the EXECUTE statement is processed, the user must set the following fields in the input SQLDA:

• SQLN to indicate the number of SQLVAR occurrences provided in the SQLDA
• SQLDABC to indicate the number of bytes of storage allocated for the SQLDA
• SQLD to indicate the number of variables used in the SQLDA when processing the statement
• SQLVAR occurrences to indicate the attributes of the variables.

The SQLDA must have enough storage to contain all SQLVAR occurrences. Therefore, the value in SQLDABC must be greater than or equal to 16 + SQLN*(N), where N is the length of an SQLVAR occurrence.

If LOB input data needs to be accommodated, there must be two SQLVAR entries for every parameter marker.

SQLD must be set to a value greater than or equal to zero and less than or equal to SQLN. For more information, see Appendix C, SQL Descriptor Area (SQLDA).

Notes

• Before the prepared statement is executed, each parameter marker is effectively replaced by the value of its corresponding host variable. 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. See Rules for the rules affecting 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. Thus:

  • 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.

  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 with two blanks.

• Dynamic SQL Statement Caching: 

  The information required to execute dynamic and static SQL statements is placed in the database package cache when static SQL statements are first referenced or when dynamic SQL statements are first prepared. This information stays in the package cache until it becomes invalid, the cache space is required for another statement, or the database is shut down.

  When an SQL statement is executed or prepared, the package information relevant to the application issuing the request is loaded from the system catalog into the package cache. The actual executable section for the individual SQL statement is also placed into the cache: static SQL sections are read in from the system catalog and placed in the package cache when the statement is first referenced; Dynamic SQL sections are placed directly in the cache after they have been created. Dynamic SQL sections can be created by an explicit statement, such as a PREPARE or EXECUTE IMMEDIATE statement. Once created, sections for dynamic SQL statements may be recreated by an implicit prepare of the statement performed by the system if the original section has been deleted for space management reasons or has become invalid due to changes in the environment.

  Each SQL statement is cached at a database level and can be shared among applications. Static SQL statements are shared among applications using the same package; Dynamic SQL statements are shared among applications using the same compilation environment and the exact same statement text. The text of each SQL statement issued by an application is cached locally within the application for use in the event that an implicit prepare is required. Each PREPARE statement in the application program can cache one statement. All EXECUTE IMMEDIATE statements in an application program share the same space and only one cached statement exists for all these EXECUTE IMMEDIATE statements at a time. If the same PREPARE or any EXECUTE IMMEDIATE statement is issued multiple times with a different SQL statement each time, only the last statement will be cached for reuse. The optimal use of the cache is to issue a number of different PREPARE statements once at the start of the application and then to issue an EXECUTE or OPEN statement as required.

  With the caching of dynamic SQL statements, once a statement has been created, it can be reused over multiple units of work without the need to prepare the statement again. The system will recompile the statement as required if environment changes occur.

  The following events are examples of environment or data object changes which can cause cached dynamic statements to be implicitly prepared on the next PREPARE, EXECUTE, EXECUTE IMMEDIATE, or OPEN request:

  • ALTER NICKNAME
  • ALTER SERVER
  • ALTER TABLE
  • ALTER TABLESPACE
  • ALTER TYPE
  • CREATE FUNCTION
  • CREATE FUNCTION MAPPING
  • CREATE INDEX
  • CREATE TABLE
  • CREATE TEMPORARY TABLESPACE
  • CREATE TRIGGER
  • CREATE TYPE
  • DROP (all objects)
  • RUNSTATS on any table or index
  • any action that causes a view to become inoperative
  • UPDATE of statistics in any system catalog table
  • SET CURRENT DEGREE
  • SET PATH
  • SET QUERY OPTIMIZATION
  • SET SCHEMA
  • SET SERVER OPTION

  The following list outlines the behavior that can be expected from cached dynamic SQL statements:

  • PREPARE Requests: Subsequent preparations of the same statement will not incur the cost of compiling the statement if the section is still valid. The cost and cardinality estimates for the current cached section will be returned. These values may differ from the values returned from any previous PREPARE for the same SQL statement.

    There will be no need to issue a PREPARE statement subsequent to a COMMIT or ROLLBACK statement.

  • EXECUTE Requests: EXECUTE statements may occasionally incur the cost of implicitly preparing the statement if it has become invalid since the original PREPARE. If a section is implicitly prepared, it will use the current environment and not the environment of the original PREPARE statement.
  • EXECUTE IMMEDIATE Requests: Subsequent EXECUTE IMMEDIATE statements for the same statement will not incur the cost of compiling the statement if the section is still valid.
  • OPEN Requests: OPEN requests for dynamically defined cursors may occasionally incur the cost of implicitly preparing the statement if it has become invalid since the original PREPARE statement. If a section is implicitly prepared, it will use the current environment and not the environment of the original PREPARE statement.
  • FETCH Requests: No behavior changes should be expected.
  • ROLLBACK: Only those dynamic SQL statements prepared or implicitly prepared during the unit of work affected by the rollback operation will be invalidated.
  • COMMIT: Dynamic SQL statements will not be invalidated but any locks acquired will be freed. Cursors not defined as WITH HOLD cursors will be closed and their locks freed. Open WITH HOLD cursors will hold onto their package and section locks to protect the active section during, and after, commit processing.

  If an error occurs during an implicit prepare, an error will be returned for the request causing the implicit prepare (SQLSTATE 56098).

Examples

Example 1:  In this C example, an INSERT statement with parameter markers is prepared and executed. h1 - h4 are host variables that correspond to the format of TDEPT.

   strcpy (s,"INSERT INTO TDEPT VALUES(?,?,?,?)");
   EXEC SQL PREPARE DEPT_INSERT FROM :s;
      .
      .
   (Check for successful execution and put values into :h1, :h2, :h3, :h4)
      .
      .
   EXEC SQL EXECUTE DEPT_INSERT USING :h1, :h2,
   :h3, :h4;

Example 2:  This EXECUTE statement uses an SQLDA.

   EXECUTE S3 USING DESCRIPTOR :sqlda3