EXECUTE IMMEDIATE

EXECUTE IMMEDIATE combines the basic functions of the PREPARE and EXECUTE statements. It can be used to prepare and execute an SQL statement that contains neither host variables nor parameter markers.

The EXECUTE IMMEDIATE statement:

  • Prepares an executable form of an SQL statement from a string form of the statement
  • Executes the SQL statement
  • Destroys the executable form

Invocation for EXECUTE IMMEDIATE

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 for EXECUTE IMMEDIATE

The authorization rules are those defined for the dynamic preparation of the SQL statement specified by EXECUTE IMMEDIATE. For example, see INSERT for the authorization rules that apply when an INSERT statement is executed using EXECUTE IMMEDIATE.

Syntax for EXECUTE IMMEDIATE

Read syntax diagramSkip visual syntax diagram EXECUTE IMMEDIATE variablestring-expression

Description for EXECUTE IMMEDIATE

Start of changevariableEnd of change
Start of changeFor languages other than PL/I, variable must be specified. It must identify a variable that is described in the application program in accordance with the rules for declaring character or graphic string variables. If the source string is over 32KB in length, the variable must be a CLOB or DBCLOB variable. The maximum source length is 2MB, although the variable can be declared larger than 2MB. An indicator variable must not be specified with a host variable. In Assembler, C, COBOL, and PL/I, a host variable must be a varying-length string variable. In C, it must not be a NUL-terminated string. In SQL PL, an SQL variable, SQL parameter, or transition variable can be used, and the value must not be null.End of change
string-expression
string-expression is any PL/I expression that yields a string. string-expression cannot be preceded by a colon. Variables that are within string-expression that include operators or functions should not be preceded by a colon. When string-expression is specified, the precompiler-generated structures for string-expression use an EBCDIC CCSID and an informational message is returned.

Notes for EXECUTE IMMEDIATE

Rules for statement strings:

The value of the identified host variable or the specified string-expression is called the statement string.

The statement string must be one of the following SQL statements, and cannot be a select-statement:
Statements Statements
  • ALLOCATE CURSOR
  • ALTER
  • ASSOCIATE LOCATORS
  • COMMENT
  • COMMIT
  • CREATE
  • DECLARE GLOBAL TEMPORARY TABLE
  • DELETE
  • DROP
  • EXPLAIN
  • FREE LOCATOR
  • GRANT
  • HOLD LOCATOR
  • INSERT
  • LABEL
  • LOCK TABLE
  • MERGE
  • REFRESH TABLE
  • RELEASE SAVEPOINT
  • RENAME
  • REVOKE
  • ROLLBACK
  • SET assignment-statement
  • SAVEPOINT
  • Start of changeSET CURRENT ACCELERATOREnd of change
  • SET CURRENT APPLICATION COMPATIBILITY
  • SET CURRENT DEGREE
  • SET CURRENT DECFLOAT ROUNDING MODE
  • SET CURRENT DEBUG MODE
  • SET CURRENT EXPLAIN MODE
  • SET CURRENT LOCALE LC_CTYPE
  • SET CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION
  • SET CURRENT OPTIMIZATION HINT
  • SET CURRENT PRECISION
  • SET CURRENT QUERY ACCELERATION
  • SET CURRENT REFRESH AGE
  • SET CURRENT ROUTINE VERSION
  • SET CURRENT RULES
  • SET CURRENT SQLID
  • SET ENCRYPTION PASSWORD
  • SET PATH
  • SET SCHEMA
  • SET CURRENT TEMPORAL BUSINESS_TIME
  • SET CURRENT TEMPORAL SYSTEM_TIME
  • SET SESSION TIME ZONE
  • SIGNAL
  • Start of changeTRANSFER OWNERSHIPEnd of change
  • TRUNCATE
  • UPDATE

The statement string must not have any of the following attributes:

  • Begin with EXEC SQL
  • End with END-EXEC or a semicolon
  • Include references to variables, other than global variables
  • Include parameter markers
  • Include references to transition tables
Errors and error handling:
When an EXECUTE IMMEDIATE statement is executed, the specified statement string is parsed and checked for errors. If the SQL statement is invalid, it is not executed and the error condition that prevents its execution is reported in the SQLCA. If the SQL statement is valid, but an error occurs during its execution, that error condition is reported in the SQLCA.

Db2 can stop the execution of a prepared SQL statement if the statement is taking too much CPU 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.

Effect of the CURRENT EXPLAIN MODE special register:
If the CURRENT EXPLAIN MODE special register is set to EXPLAIN, the statement is prepared for explain only and is not executable, unless the statement is a SET statement. Attempting to execute the prepared statement will return an error. See the CURRENT EXPLAIN MODE special register special register for more information.
Performance considerations:
If the same SQL statement is to be executed more than once, it is more efficient to use the PREPARE and EXECUTE statements rather than the EXECUTE IMMEDIATE statement.

Examples for EXECUTE IMMEDIATE

Example 1
In this PL/I example, the EXECUTE IMMEDIATE statement is used to execute a DELETE statement in which the rows to be deleted are determined by a search-condition specified by the value of PREDS. 
EXEC SQL EXECUTE IMMEDIATE 'DELETE FROM DSN8C10.DEPT
  WHERE' || PREDS;
Example 2
Use C to execute the SQL statement in the host variable Qstring. 
EXEC SQL INCLUDE SQLCA;
void main ()
  {                                                                  
   EXEC SQL BEGIN DECLARE SECTION;
   char Qstring[100] =
     "INSERT INTO WORK_TABLE SELECT * FROM EMPPROJACT WHERE ACTNO >= 100";
   EXEC SQL END DECLARE SECTION; 
  .
  .
  . 
   EXEC SQL EXECUTE IMMEDIATE :Qstring;
   return; 
  }

: