The PREPARE statement creates an executable form of an SQL statement from a character-string form of the statement. The character-string form is called a statement string, and the executable form is called a prepared statement.
This statement can only be embedded in an application program, SQL function, SQL procedure, or trigger. It is an executable statement that cannot be dynamically prepared. It must not be specified in Java™.
The authorization rules are the same as those defined for the SQL statement specified by the PREPARE statement. For example, see select-statement for the authorization rules that apply when a SELECT statement is prepared.
If DLYPRP(*NO) is specified on the CRTSQLxxx command, the authorization checking is performed when the statement is prepared, except:
If a DROP, COMMENT, or LABEL of a FUNCTION, PROCEDURE,
SEQUENCE, TYPE, TRIGGER, VARIABLE, or XSROBJECT statement is prepared,
authorities are not checked until the statement is executed.
If DLYPRP(*YES) is specified on the CRTSQLxxx command, all authorization checking is deferred until the statement is executed or used in an OPEN statement.
The authorization ID of the statement is the run-time authorization ID unless DYNUSRPRF(*OWNER) was specified on the CRTSQLxxx command when the program was created. For more information, see Authorization IDs and authorization names.
If a
global variable is referenced in a statement, the privileges held
by the authorization ID of the statement must include at least one
of the following:

>>-PREPARE--statement-name--------------------------------------> >--+-----------------------------------------------------------------+--> | .-SQL-. .-LOCAL--. | '-+-USING--+-----+--DESCRIPTOR--+--------+--SQL-descriptor-name-+-' | '-GLOBAL-' | '-INTO----descriptor-name----+-------------------------+------' '-USING--+-NAMES--------+-' +-SYSTEM NAMES-+ +-LABELS-------+ +-ANY----------+ +-BOTH---------+ '-ALL----------' >--+-FROM--string-expression-----------------------+----------->< '-+---------------------------+--FROM--variable-' '-ATTRIBUTES--attr-variable-'
attribute-string .--------------------------------------------------------. V | (1) |----+----------------------------------------------------+-+------| +-+-ASENSITIVE-------------+-------------------------+ | +-INSENSITIVE------------+ | | | .-DYNAMIC-. | | | '-SENSITIVE--+---------+-' | +-+-NO SCROLL-+--------------------------------------+ | '-SCROLL----' | +-+-WITHOUT HOLD-+-----------------------------------+ | '-WITH HOLD----' | +-+-WITHOUT RETURN-------------+---------------------+ | | .-TO CALLER-. | | | '-WITH RETURN--+-----------+-' | | '-TO CLIENT-' | +-fetch-first-clause---------------------------------+ +-+-read-only-clause-+-------------------------------+ | '-update-clause----' | +-optimize-clause------------------------------------+ +-isolation-clause-----------------------------------+ +-concurrent-access-resolution-clause----------------+ +-+-WITHOUT EXTENDED INDICATORS-+--------------------+ | '-WITH EXTENDED INDICATORS----' | '-+-WITHOUT ROW CHANGE COLUMNS---------------------+-' '-WITH ROW CHANGE COLUMNS--+-POSSIBLY DISTINCT-+-' '-ALWAYS DISTINCT---'
EXEC SQL PREPARE S1 USING SQL DESCRIPTOR :sqldescriptor FROM :V1;
is equivalent to:
EXEC SQL PREPARE S1 FROM :V1; EXEC SQL DESCRIBE S1 USING SQL DESCRIPTOR :sqldescriptor;
See GET DESCRIPTOR for an explanation of the information that is placed in the SQL descriptor.
EXEC SQL PREPARE S1 INTO :SQLDA FROM :V1;
is equivalent to:
EXEC SQL PREPARE S1 FROM :V1; EXEC SQL DESCRIBE S1 INTO :SQLDA;
See DESCRIBE for an explanation of the information that is placed in the SQLDA.
If the same SQLDA is used on a subsequent FETCH statement, set SQLN to n after the PREPARE is complete.
If the same SQLDA is used on a subsequent FETCH statement, set SQLN to n after the PREPARE is complete.
Specifies the attributes for this cursor that
are in effect if a corresponding attribute has not been specified
as part of the outermost fullselect of the associated SELECT statement.
If attributes are specified for the outermost fullselect, they are
used instead of the corresponding attributes specified on the PREPARE
statement. In turn, if attributes are specified in the PREPARE statement,
they are used instead of the corresponding attributes specified on
a DECLARE CURSOR statement.
All attributes other
than USE CURRENTLY COMMITTED and WAIT FOR OUTCOME are ignored if the
prepared statement is not a select-statement.
attr-variable must identify a character-string or Unicode graphic variable that is declared in the program in accordance with the rules for declaring string variables. attr-variable must be a string variable (either fixed-length or varying-length) that has a length attribute that does not exceed the maximum length of a VARCHAR. Leading and trailing blanks are removed from the value of the variable. The variable must contain a valid attribute-string.
If SENSITIVE is specified, then a fetch-first-clause must not be specified. If INSENSITIVE is specified, then an update-clause must not be specified.
If a fetch-first-clause is specified, then an update-clause must not be specified.
concurrent-access-resolution-clause
Specifies the concurrent access resolution
to use for the select statement. For more information, see concurrent-access-resolution-clause.
A global variable may only be used if the current connection
is a local connection (not a DRDA® connection).
The statement string must be one of the following SQL statements:
ALLOCATE CURSOR![]() |
HOLD LOCATOR | SET CURRENT DEBUG MODE |
| ALTER | INSERT | SET CURRENT DECFLOAT ROUNDING MODE |
ASSOCIATE LOCATORS![]() |
LABEL | SET CURRENT DEGREE |
| CALL | LOCK TABLE | SET CURRENT IMPLICIT XMLPARSE
OPTION![]() |
| COMMENT | MERGE![]() |
SET ENCRYPTION PASSWORD |
| COMMIT | REFRESH TABLE | SET PATH |
| CREATE | RELEASE SAVEPOINT | SET SCHEMA |
| DECLARE GLOBAL TEMPORARY TABLE | RENAME | SET SESSION AUTHORIZATION |
| DELETE | REVOKE | SET TRANSACTION |
| DROP | ROLLBACK | UPDATE |
| FREE LOCATOR | SAVEPOINT | VALUES INTO |
| GRANT | select-statement |
The statement string must not:
Parameter markers: Although a statement string cannot include references to variables, it may include parameter markers. These can be replaced by the values of variables when the prepared statement is executed. A parameter marker is a question mark (?) that is used where a variable could be used if the statement string were a static SQL statement. For an explanation of how parameter markers are replaced by values, see OPEN and EXECUTE.
There are two types of parameter markers:
CAST(? AS data-type)This notation is not a function call, but a “promise” that the type of the parameter at run time will be of the data type specified or some data type that can be converted to the specified data type. For example, in:
UPDATE EMPLOYEE SET LASTNAME = TRANSLATE(CAST(? AS VARCHAR(12))) WHERE EMPNO = ?the value of the argument of the TRANSLATE function will be provided at run time. The data type of that value will either be VARCHAR(12), or some type that can be converted to VARCHAR(12). For more information, refer to CAST specification.
Typed parameter markers can be used in dynamic SQL statements wherever a variable is supported and the data type is based on the promise made in the CAST function.
These locations and the resulting data type are found
in the following tables:
| Untyped Parameter Marker Location | Data Type |
|---|---|
| Alone in a select list that is not in a subquery | Error |
| Alone in a select list that is in an EXISTS subquery | Error |
| Alone in a select list that is in a subquery | The data type of the other operand of the subquery.1 |
| Both operands of a single arithmetic
operator, after considering operator precedence and order of operation
rules. Includes cases such as:
? + ? + 10 |
DECFLOAT(34)![]() |
| One operand of a single operator
in an arithmetic expression (not a datetime expression) Includes
cases such as:
? + ? * 10 |
The data type of the other operand. |
| Labelled duration within a datetime expression. (Note that the portion of a labelled duration that indicates the type of units cannot be a parameter marker.) | DECIMAL(15,0) |
| Any other operand of a datetime expression (for instance 'timecol + ?' or '? - datecol'). | Error |
Both operands of a CONCAT operator![]() |
DBCLOB(1G) CCSID 1200![]() |
One operand of a CONCAT operator
when the other operand is a non-CLOB character data type![]() |
VARCHAR(32740)![]() |
One operand of a CONCAT operator,
when the other operand is a non-DBCLOB graphic data type![]() |
VARGRAPHIC(16370)![]() |
One operand of a CONCAT operator
when the other operand is a non-BLOB binary type![]() |
VARBINARY(32740)![]() |
One operand of a CONCAT operator,
when the other operand is a large object string![]() |
Same as that of the other operand![]() |
The expression following the CASE
keyword in a simple CASE expression![]() |
Result of applying the Rules for result data types to the expressions following the WHEN
keyword that are other than untyped parameter markers![]() |
| At least one of the result-expressions in a CASE expression (both Simple and Searched) with the rest of the result-expressions either untyped parameter marker or NULL. | Error |
| Any or all expressions following WHEN in a simple CASE expression. | Result of applying the Rules for result data types to the expression following CASE and the expressions following WHEN that are not untyped parameter markers. |
| A result-expression in a CASE expression (both simple and searched) where at least one result-expression is not NULL and not an untyped parameter marker. | Result of applying the Rules for result data types to all result-expressions that are other than NULL or untyped parameter markers. |
Alone as a column-expression in
a single-row VALUES clause that is not within an INSERT statement
and not within the VALUES clause of in insert operation of a MERGE
statement.![]() |
Error![]() |
| Alone as a column-expression in a multi-row VALUES clause that is not within an INSERT statement, and for which the column-expressions in the same position in all other row-expressions are untyped parameter markers. | Error |
| Alone as a column-expression in a multi-row VALUES clause that is not within an INSERT statement, and for which a column-expression in the same position of at least one other row-expression is not an untyped parameter marker or NULL. | Result of applying the Rules for result data types to all operands that are other than untyped parameter markers. |
| Alone as a column-expression in a single-row VALUES clause within an INSERT statement. | The data type of the column. If the column is defined as a user-defined distinct type, then it is the source data type of the user-defined distinct type. 1 |
Alone as a column-expression in
a multi-row VALUES clause within an INSERT statement.![]() |
The data type of the column. If the
column is defined as a user-defined distinct type, then it is the
source data type of the user-defined distinct type. 1![]() |
Alone as a column-expression in
a values-single-row or values-multiple-row clause of source-table
for a MERGE statement![]() |
Error![]() |
Alone as a column-expression in
the VALUES clause of an insert operation of a MERGE statement![]() |
The data type of the column. If the
column is defined as a user-defined distinct type, then it is the
source data type of the user-defined distinct type. 1![]() |
Alone as a column-expression on
the right side of assignment-clause for an update operation of a MERGE
statement![]() |
The data type of the column. If the
column is defined as a user-defined distinct type, then it is the
source data type of the user-defined distinct type. 1![]() |
| Alone as a value on the right hand side of a SET clause of an UPDATE statement. | The data type of the column. If the column is defined as a user-defined distinct type, then it is the source data type of the user-defined distinct type. 1 |
| As a value in an insert-multiple-rows of an INSERT statement. | INTEGER |
| As a value on the right side of a SET special register statement | The data type of the special register. |
| As a value in the INTO clause of the VALUES INTO statement | The data type of the associated expression. 1 |
| As a value in a FREE LOCATOR or HOLD LOCATOR statement | Locator. |
| As a value for the password in a SET ENCRYPTION PASSWORD statement | VARCHAR(128) |
| As a value for the hint in a SET ENCRYPTION PASSWORD statement | VARCHAR(32) |
| Untyped Parameter Marker Location | Data Type |
|---|---|
| Both operands of a comparison operator | VARGRAPHIC(16370) CCSID
1200![]() |
| One operand of a comparison operator where the other operand is other than an untyped parameter marker or a distinct type. | The data type of the other operand.1 |
| One operand of a comparison operator where the other operand is a distinct type. | Error |
| All operands of a BETWEEN predicate | VARGRAPHIC(16370) CCSID
1200![]() |
| Two operands of a BETWEEN predicate | Same as that of the only non-parameter marker. |
| Only one operand of a BETWEEN predicate | Result of applying the Rules for result data types on all operands that are other than untyped parameter markers, except the CCSID attribute is the CCSID of the value specified at execution time. |
| All operands of an IN predicate, for example, ? IN (?,?,?) | VARGRAPHIC(16370) CCSID
1200![]() |
| The first operand of an IN predicate where the right hand side is a fullselect, for example, ? IN (fullselect). | Data type of the selected column |
| The first operand of an IN predicate where the right hand side is not a fullselect, for example, ? IN (?,A,B) or for example, ? IN (A,?,B,?). | Result of applying the Rules for result data types on all operands of the IN list (operands to the right of IN keyword) that are other than untyped parameter markers, except the CCSID attribute is the CCSID of the value specified at execution time. |
| Any or all operands of the IN list of the IN predicate, for example, for example, A IN (?,B,?). | Result of applying the Rules for result data types on all operands of the IN predicate (operands to the left and right of the IN predicate) that are other than untyped parameter markers, except the CCSID attribute is the CCSID of the value specified at execution time. |
| Any operands in a row-value-expression of an IN predicate, for example, (c1,?) IN ... | Error |
| Any select list items in a subquery if a row-value-expression is specified in an IN predicate, for example, (c1,c2) IN (SELECT ?, c1 FROM ...) | Error |
| All three operands of the LIKE predicate. | Match expression (operand
1) and pattern expression (operand 2) VARCHAR(32740); escape expression
(operand 3) is VARCHAR(1)![]() |
| The match expression of the LIKE predicate. | Either VARCHAR(32740)
or VARGRAPHIC(16370) or VARBINARY(32740) depending on the data type
of the first operand that is not an untyped parameter marker![]() |
| The pattern expression of the LIKE predicate when either the pattern expression or the escape expression is other than an untyped parameter marker. | Either VARCHAR(32740) or VARGRAPHIC(16370)
or VARBINARY(32740) depending on the data type of the match expression.
For information about using fixed-length variables for the value of the pattern, see LIKE predicate. |
| The escape expression of the LIKE predicate when either the pattern expression or the escape expression is other than an untyped parameter marker. | Either VARCHAR(1) or VARGRAPHIC(1) or VARBINARY(1) depending on the data type of the match expression. |
| Operand of the NULL or DISTINCT predicate | VARGRAPHIC(16370) CCSID 1200 |
| Untyped Parameter Marker Location | Data Type |
|---|---|
All operands of COALESCE,
IFNULL, LAND, LOR, MIN, MAX, NULLIF, VALUE, or XOR![]() |
Error |
Any operand of COALESCE,
IFNULL, LAND, LOR, MIN, MAX, NULLIF, VALUE, or XOR where at least
one operand is other than an untyped parameter marker.![]() |
Result of applying the Rules for result data types on all operands that are other than untyped parameter markers. |
| All operands of COMPARE_DECFLOAT, DECFLOAT_SORTKEY, NORMALIZE_DECFLOAT, QUANTIZE, and TOTALORDER | DECFLOAT(34) |
Both arguments of LOCATE, POSITION,
or POSSTR![]() |
DBCLOB(1G) CCSID 1200![]() |
One argument of LOCATE, POSITION,
or POSSTR when the other argument is a character data type.![]() |
VARCHAR(32740)![]() |
One argument of LOCATE, POSITION,
or POSSTR when the other argument is a graphic data type.![]() |
VARGRAPHIC(16370)![]() |
One argument of LOCATE, POSITION,
or POSSTR when the other argument is a binary data type.![]() |
VARBINARY(32740)![]() |
| The operand of UPPER, LOWER, UCASE, and LCASE | DBCLOB(1G) CCSID 1200![]() |
First or second argument of MONTHS_BETWEEN ![]() |
TIMESTAMP![]() |
SUBSTR (first argument)![]() |
DBCLOB(1G) CCSID 1200![]() |
SUBSTR (second and third arguments)![]() |
INTEGER![]() |
The first argument of TRANSLATE![]() |
Error![]() |
The second and third arguments of
TRANSLATE ![]() |
VARCHAR(32740) if the first argument
is a character type; VARGRAPHIC(16370) if the first argument is a
graphic type![]() |
The fourth argument of TRANSLATE![]() |
VARCHAR(1) if the first argument
is a character type; VARGRAPHIC(1) if the first argument is a graphic
type![]() |
The first argument of TIMESTAMP or
TIMESTAMP_ISO![]() |
Error![]() |
The second argument of TIMESTAMP![]() |
TIME![]() |
| The first argument of VARCHAR_FORMAT | TIMESTAMP |
The first argument of TIMESTAMP_FORMAT![]() |
VARGRAPHIC(16370) CCSID 1200![]() |
| The second operand of TIMESTAMP_FORMAT or VARCHAR_FORMAT | Error |
First argument of XMLVALIDATE![]() |
XML 2![]() |
First argument of XMLPARSE![]() |
CLOB(2G) or DBCLOB(1G) based on
the CCSID value for the query option SQL_XML_DATA_CCSID![]() |
First argument of XMLCOMMENT![]() |
VARCHAR(32740) or VARGRAPHIC(16370)
based on the CCSID value for the query option SQL_XML_DATA_CCSID![]() |
First argument of XMLTEXT![]() |
VARCHAR(32740) or VARGRAPHIC(16370)
based on the CCSID value for the query option SQL_XML_DATA_CCSID![]() |
Second argument of XMLPI![]() |
VARCHAR(36740) or VARGRAPHIC(16370)
based on the CCSID value for the query option SQL_XML_DATA_CCSID![]() |
First argument of XMLSERIALIZE![]() |
XML 3![]() |
All arguments of XMLDOCUMENT![]() |
XML 2![]() |
All arguments of XMLCONCAT![]() |
XML 2![]() |
First, second, and third arguments
of XSLTRANSFORM![]() |
XML 3![]() |
Second argument of TRIM_ARRAY![]() |
BIGINT![]() |
Subindex of an ARRAY![]() |
BIGINT![]() |
Unary minus![]() |
DECFLOAT(34)![]() |
Unary plus![]() |
DECFLOAT(34)![]() |
All other operands
of all other scalar functions.![]() |
Error |
| Operand of an aggregate function | Error |
Error checking: When a PREPARE statement is executed, the statement string is parsed and checked for errors. If the statement string is not valid, a prepared statement is not created and an error is returned.
In local and remote processing, the DLYPREP(*YES) option can cause some SQL statements to receive "delayed" errors. For example, DESCRIBE, EXECUTE, and OPEN might receive an SQLCODE that normally occurs during PREPARE processing.
Reference and execution rules: Prepared statements can be referred to in the following kinds of statements, with the following restrictions shown:
Statement The prepared statement restrictions DESCRIBE None DECLARE CURSOR Must be SELECT when the cursor is opened EXECUTE Must not be SELECT
A prepared statement can be executed many times. If a prepared statement is not executed more than once and does not contain parameter markers, it is more efficient to use the EXECUTE IMMEDIATE statement rather than the PREPARE and EXECUTE statements.
Extended indicator usage: The EXTENDED INDICATORS clause indicates whether extended indicator variable values are enabled in the SET assignment-clause of an UPDATE statement or the VALUES expression-list of an INSERT statement.
Extended indicator variables and deferred error checks: When extended indicator variables are enabled, the UNASSIGNED indicator variable value effectively causes its target column to be omitted from the statement. Because of this, validation that is normally done during statement preparation is delayed until statement execution.
Prepared statement persistence: All prepared statements are destroyed when:4
Scope of a statement: The scope of statement-name is the source program in which it is defined. You can only reference a prepared statement by other SQL statements that are precompiled with the PREPARE statement. For example, a program called from another separately compiled program cannot use a prepared statement that was created by the calling program.
The scope of statement-name is also limited to the thread in which the program that contains the statement is running. For example, if the same program is running in two separate threads in the same job, the second thread cannot use a statement that was prepared by the first thread.
Although the scope of a statement is the program in which it is defined, each package created from the program includes a separate instance of the prepared statement and more than one prepared statement can exist at run time. For example, assume a program using CONNECT (Type 2) statements connects to location X and location Y in the following sequence:
EXEC SQL CONNECT TO X; EXEC SQL PREPARE S FROM :hv1; EXEC SQL EXECUTE S; . . . EXEC SQL CONNECT TO Y; EXEC SQL PREPARE S FROM :hv1; EXEC SQL EXECUTE S;
The second prepare of S prepares another instance of S at Y.
A prepared statement can only be referenced in the same instance of the program in the program stack, unless CLOSQLCSR(*ENDJOB), CLOSQLCSR(*ENDACTGRP), or CLOSQLCSR(*ENDSQL) is specified on the CRTSQLxxx commands.
Allocating the SQL descriptor: If a USING clause is specified, before the PREPARE statement is executed, the SQL descriptor must be allocated using the ALLOCATE DESCRIPTOR statement. If the number of descriptor items allocated is less than the number of result columns, a warning (SQLSTATE 01005) is returned.
PREPARE and *LIBL: Normally, any unqualified names of objects are resolved when a statement is prepared. Hence, any changes to the CURRENT SCHEMA or CURRENT PATH after the statement has been prepared have no effect on which objects will be referenced when the statement is executed or opened. However, if system naming is used and an object name is implicitly qualified with *LIBL, the object is resolved at execute or open time. Any changes to the library list after the statement is prepared but before execute or open time will affect which objects will be referenced when the statement is executed or opened.
Example 1: Prepare and execute a non-select-statement in a COBOL program. Assume the statement is contained in a variable HOLDER and that the program will place a statement string into the variable based on some instructions from the user. The statement to be prepared does not have any parameter markers.
EXEC SQL PREPARE STMT_NAME FROM :HOLDER END-EXEC. EXEC SQL EXECUTE STMT_NAME END-EXEC.
Example 2: Prepare and execute a non-select-statement as in example 1, except assume the statement to be prepared can contain any number of parameter markers.
EXEC SQL PREPARE STMT_NAME FROM :HOLDER END-EXEC. EXEC SQL EXECUTE STMT_NAME USING DESCRIPTOR :INSERT_DA END-EXEC.
Assume that the following statement is to be prepared:
INSERT INTO DEPARTMENT VALUES(?, ?, ?, ?)
To insert department number G01 named COMPLAINTS, which has no manager and reports to department A00, the structure INSERT_DA should have the following values before executing the EXECUTE statement.

The CCSID is determined based on the
attributes of the data-type specified on the AS clause as described
in CAST specification. If the data-type is
a binary string or bit data, then the SQL_XML_DATA_CCSID is used for
the CCSID attribute.