Dynamically executing an SQL statement by using PREPARE and EXECUTE

As an alternative to executing an SQL statement immediately after it is read, you can prepare and execute the SQL statement in two steps. This two-step method is useful when you need to execute an SQL statement multiple times with different values.

About this task

Suppose that you want to execute DELETE statements repeatedly using a list of employee numbers. Consider how you would do it if you could write the DELETE statement as a static SQL statement:

< Read a value for EMP from the list. >
DO UNTIL (EMP = 0);
  EXEC SQL
    DELETE FROM DSN8A10.EMP WHERE EMPNO = :EMP ;
  < Read a value for EMP from the list. >
END;

The loop repeats until it reads an EMP value of 0.

If you know in advance that you will use only the DELETE statement and only the table DSN8A10.EMP, you can use the more efficient static SQL. Suppose further that several different tables have rows that are identified by employee numbers, and that users enter a table name as well as a list of employee numbers to delete. Although variables can represent the employee numbers, they cannot represent the table name, so you must construct and execute the entire statement dynamically. Your program must now do these things differently:

Use parameter markers instead of host variables
Use the PREPARE statement
Use EXECUTE instead of EXECUTE IMMEDIATE

Parameter markers with PREPARE and EXECUTE: Dynamic SQL statements cannot use host variables. Therefore, you cannot dynamically execute an SQL statement that contains host variables. Instead, substitute a parameter marker, indicated by a question mark (?), for each host variable in the statement.

You can indicate to DB2® that a parameter marker represents a host variable of a certain data type by specifying the parameter marker as the argument of a CAST specification. When the statement executes, DB2 converts the host variable to the data type in the CAST specification. A parameter marker that you include in a CAST specification is called a typed parameter marker. A parameter marker without a CAST specification is called an untyped parameter marker.

Recommendation: Because DB2 can evaluate an SQL statement with typed parameter markers more efficiently than a statement with untyped parameter markers, use typed parameter markers whenever possible. Under certain circumstances you must use typed parameter markers.

Example using parameter markers: Suppose that you want to prepare this statement:

DELETE FROM DSN8A10.EMP WHERE EMPNO = :EMP;

You need to prepare a string like this:

DELETE FROM DSN8A10.EMP WHERE EMPNO = CAST(? AS CHAR(6))

You associate host variable :EMP with the parameter marker when you execute the prepared statement. Suppose that S1 is the prepared statement. Then the EXECUTE statement looks like this:

EXECUTE S1 USING :EMP;

Using the PREPARE statement: Before you prepare an SQL statement, you can assign it to a host variable. If the length of the statement is greater than 32 KB, you must declare the host variable as a CLOB or DBCLOB.

You can think of PREPARE and EXECUTE as an EXECUTE IMMEDIATE done in two steps. The first step, PREPARE, turns a character string into an SQL statement, and then assigns it a name of your choosing.

Example using the PREPARE statement: Assume that the character host variable :DSTRING has the value “DELETE FROM DSN8A10.EMP WHERE EMPNO = ?”. To prepare an SQL statement from that string and assign it the name S1, write:

EXEC SQL PREPARE S1 FROM :DSTRING;

The prepared statement still contains a parameter marker, for which you must supply a value when the statement executes. After the statement is prepared, the table name is fixed, but the parameter marker enables you to execute the same statement many times with different values of the employee number.

Using the EXECUTE statement: The EXECUTE statement executes a prepared SQL statement by naming a list of one or more host variables, one or more host variable arrays, or a host structure. This list supplies values for all of the parameter markers.

After you prepare a statement, you can execute it many times within the same unit of work. In most cases, COMMIT or ROLLBACK destroys statements prepared in a unit of work. Then, you must prepare them again before you can execute them again. However, if you declare a cursor for a dynamic statement and use the option WITH HOLD, a commit operation does not destroy the prepared statement if the cursor is still open. You can execute the statement in the next unit of work without preparing it again.

Example using the EXECUTE statement: To execute the prepared statement S1 just once, using a parameter value contained in the host variable :EMP, write:

EXEC SQL EXECUTE S1 USING :EMP;

Preparing and executing the example DELETE statement: The example in this topic began with a DO loop that executed a static SQL statement repeatedly:

< Read a value for EMP from the list. >
DO UNTIL (EMP = 0);
  EXEC SQL
    DELETE FROM DSN8A10.EMP WHERE EMPNO = :EMP ;
  < Read a value for EMP from the list. >
END;

You can now write an equivalent example for a dynamic SQL statement:

< Read a statement containing parameter markers into DSTRING.>
EXEC SQL PREPARE S1 FROM :DSTRING;
< Read a value for EMP from the list. >
DO UNTIL (EMPNO = 0);
  EXEC SQL EXECUTE S1 USING :EMP;
  < Read a value for EMP from the list. >
  END;

The PREPARE statement prepares the SQL statement and calls it S1. The EXECUTE statement executes S1 repeatedly, using different values for EMP.

Using more than one parameter marker: The prepared statement (S1 in the example) can contain more than one parameter marker. If it does, the USING clause of EXECUTE specifies a list of variables or a host structure. The variables must contain values that match the number and data types of parameters in S1 in the proper order. You must know the number and types of parameters in advance and declare the variables in your program, or you can use an SQLDA (SQL descriptor area).

Parent topic: Including dynamic SQL in your program

Related concepts:

Programming assembler applications that issue SQL statements

Programming C and C++ applications that issue SQL statements

Programming COBOL applications that issue SQL statements

Programming Fortran applications that issue SQL statements

Programming PL/I applications that issue SQL statements

Programming REXX applications that issue SQL statements

Related reference:

PREPARE