Including dynamic SQL for varying-list SELECT statements in your program

Examples

Preparing a varying-list SELECT statement

Suppose that your program dynamically executes SQL statements, but this time without any limits on their form. Your program reads the statements from a terminal, and you know nothing about them in advance. They might not even be SELECT statements.

As with non-SELECT statements, your program puts the statements into a varying-length character variable; call it DSTRING. Your program goes on to prepare a statement from the variable and then give the statement a name; call it STMT.

Now, the program must find out whether the statement is a SELECT. If it is, the program must also find out how many values are in each row, and what their data types are. The information comes from an SQL descriptor area (SQLDA).

SQL descriptor area (SQLDA)

The SQLDA is a structure that is used to communicate with your program, and storage for it is usually allocated dynamically at run time.

To include the SQLDA in a PL/I or C program, use:

EXEC SQL INCLUDE SQLDA;

For assembler, use this in the storage definition area of a CSECT:

EXEC SQL INCLUDE SQLDA

For COBOL, use:

EXEC SQL INCLUDE SQLDA END-EXEC.

You cannot include an SQLDA in a Fortran, or REXX program.

Obtaining information about the SQL statement

An SQLDA can contain a variable number of occurrences of SQLVAR, each of which is a set of five fields that describe one column in the result table of a SELECT statement.

The number of occurrences of SQLVAR depends on the following factors:

• The number of columns in the result table you want to describe.
• Whether you want the PREPARE or DESCRIBE to put both column names and labels in your SQLDA. This is the option USING BOTH in the PREPARE or DESCRIBE statement.
• Whether any columns in the result table are LOB types or distinct types.

The following table shows the minimum number of SQLVAR instances you need for a result table that contains n columns.

Table 1. Minimum number of SQLVARs for a result table with n columns

Type of DESCRIBE and contents of result table	Not USING BOTH	USING BOTH
No distinct types or LOBs	n	2*n
Distinct types but no LOBs	2*n	3*n
LOBs but no distinct types	2*n	2*n
LOBs and distinct types	2*n	3*n

An SQLDA with n occurrences of SQLVAR is referred to as a single SQLDA, an SQLDA with 2*n occurrences of SQLVAR a double SQLDA, an SQLDA with 3*n occurrences of SQLVAR a triple SQLDA.

A program that admits SQL statements of every kind for dynamic execution has two choices:

• Provide the largest SQLDA that it could ever need. The maximum number of columns in a result table is 750, so an SQLDA for 750 columns occupies 33 016 bytes for a single SQLDA, 66 016 bytes for a double SQLDA, or 99 016 bytes for a triple SQLDA. Most SELECT statements do not retrieve 750 columns, so the program does not usually use most of that space.
• Provide a smaller SQLDA, with fewer occurrences of SQLVAR. From this the program can find out whether the statement was a SELECT and, if it was, how many columns are in its result table. If more columns are in the result than the SQLDA can hold, Db2 returns no descriptions. When this happens, the program must acquire storage for a second SQLDA that is long enough to hold the column descriptions, and ask Db2 for the descriptions again. Although this technique is more complicated to program than the first, it is more general.

  How many columns should you allow? You must choose a number that is large enough for most of your SELECT statements, but not too wasteful of space; 40 is a good compromise. To illustrate what you must do for statements that return more columns than allowed, the example in this discussion uses an SQLDA that is allocated for at least 100 columns.

Declaring a cursor for the statement

As before, you need a cursor for the dynamic SELECT. For example, write:

EXEC SQL
  DECLARE C1 CURSOR FOR STMT;

Preparing the statement using the minimum SQLDA

Suppose that your program declares an SQLDA structure with the name MINSQLDA, having 100 occurrences of SQLVAR and SQLN set to 100. To prepare a statement from the character string in DSTRING and also enter its description into MINSQLDA, write this:

EXEC SQL PREPARE STMT FROM :DSTRING;
EXEC SQL DESCRIBE STMT INTO :MINSQLDA;

Equivalently, you can use the INTO clause in the PREPARE statement:

EXEC SQL
  PREPARE STMT INTO :MINSQLDA FROM :DSTRING;

Do not use the USING clause in either of these examples. At the moment, only the minimum SQLDA is in use. The following figure shows the contents of the minimum SQLDA in use.

SQLN determines what SQLVAR gets

The SQLN field, which you must set before using DESCRIBE (or PREPARE INTO), tells how many occurrences of SQLVAR the SQLDA is allocated for. If DESCRIBE needs more than that, the results of the DESCRIBE depend on the contents of the result table. Let n indicate the number of columns in the result table. Then:

• If the result table contains at least one distinct type column but no LOB columns, you do not specify USING BOTH, and n<=SQLN<2*n, then Db2 returns base SQLVAR information in the first n SQLVAR occurrences, but no distinct type information. Base SQLVAR information includes:
  • Data type code
  • Length attribute (except for LOBs)
  • Column name or label
  • Host variable address
  • Indicator variable address
• Otherwise, if SQLN is less than the minimum number of SQLVARs specified in the table above, then Db2 returns no information in the SQLVARs.

Regardless of whether your SQLDA is big enough, whenever you execute DESCRIBE, Db2 returns the following values, which you can use to build an SQLDA of the correct size:

• SQLD is 0 if the SQL statement is not a SELECT. Otherwise, SQLD is the number of columns in the result table. The number of SQLVAR occurrences you need for the SELECT depends on the value in the seventh byte of SQLDAID.
• The seventh byte of SQLDAID is 2 if each column in the result table requires two SQLVAR entries. The seventh byte of SQLDAID is 3 if each column in the result table requires three SQLVAR entries.

If the statement is not a SELECT

To find out if the statement is a SELECT, your program can query the SQLD field in MINSQLDA. If the field contains 0, the statement is not a SELECT, the statement is already prepared, and your program can execute it. If no parameter markers are in the statement, you can use:

EXEC SQL EXECUTE STMT;

(If the statement does contain parameter markers, you must use an SQL descriptor area)

Acquiring storage for a second SQLDA if needed

Now you can allocate storage for a second, full-size SQLDA; call it FULSQLDA. The following figure shows its structure.

FULSQLDA has a fixed-length header of 16 bytes in length, followed by a varying-length section that consists of structures with the SQLVAR format. If the result table contains LOB columns or distinct type columns, a varying-length section that consists of structures with the SQLVAR2 format follows the structures with SQLVAR format. All SQLVAR structures and SQLVAR2 structures are 44 bytes long. The number of SQLVAR and SQLVAR2 elements you need is in the SQLD field of MINSQLDA, and the total length you need for FULSQLDA (16 + SQLD * 44) is in the SQLDABC field of MINSQLDA. Allocate that amount of storage.

Describing the SELECT statement again

After allocating sufficient space for FULSQLDA, your program must take these steps:

1. Put the total number of SQLVAR and SQLVAR2 occurrences in FULSQLDA into the SQLN field of FULSQLDA. This number appears in the SQLD field of MINSQLDA.
2. Describe the statement again into the new SQLDA:

   EXEC SQL DESCRIBE STMT INTO :FULSQLDA;

After the DESCRIBE statement executes, each occurrence of SQLVAR in the full-size SQLDA (FULSQLDA in our example) contains a description of one column of the result table in five fields. If an SQLVAR occurrence describes a LOB column or distinct type column, the corresponding SQLVAR2 occurrence contains additional information specific to the LOB or distinct type.

The following figure shows an SQLDA that describes two columns that are not LOB columns or distinct type columns.

Acquiring storage to hold a row

Before fetching rows of the result table, your program must:

1. Analyze each SQLVAR description to determine how much space you need for the column value.
2. Derive the address of some storage area of the required size.
3. Put this address in the SQLDATA field.

If the SQLTYPE field indicates that the value can be null, the program must also put the address of an indicator variable in the SQLIND field. The following figures show the SQL descriptor area after you take certain actions.

In the previous figure, the DESCRIBE statement inserted all the values except the first occurrence of the number 200. The program inserted the number 200 before it executed DESCRIBE to tell how many occurrences of SQLVAR to allow. If the result table of the SELECT has more columns than this, the SQLVAR fields describe nothing.

The first SQLVAR pertains to the first column of the result table (the WORKDEPT column). SQLVAR element 1 contains fixed-length character strings and does not allow null values (SQLTYPE=452); the length attribute is 3.

The following figure shows the SQLDA after your program acquires storage for the column values and their indicators, and puts the addresses in the SQLDATA fields of the SQLDA.

The following figure shows the SQLDA after your program executes a FETCH statement.

The following table describes the values in the descriptor area.

Table 2. Values inserted in the SQLDA

Value	Field	Description
SQLDA	SQLDAID	An eye-catcher
8816	SQLDABC	The size of the SQLDA in bytes (16 + 44 * 200)
200	SQLN	The number of occurrences of SQLVAR, set by the program
200	SQLD	The number of occurrences of SQLVAR actually used by the DESCRIBE statement
452	SQLTYPE	The value of SQLTYPE in the first occurrence of SQLVAR. It indicates that the first column contains fixed-length character strings, and does not allow nulls.
3	SQLLEN	The length attribute of the column
Undefined or CCSID value	SQLDATA	Bytes 3 and 4 contain the CCSID of a string column. Undefined for other types of columns.
Undefined	SQLIND
8	SQLNAME	The number of characters in the column name
WORKDEPT	SQLNAME+2	The column name of the first column

Putting storage addresses in the SQLDA

After analyzing the description of each column, your program must replace the content of each SQLDATA field with the address of a storage area large enough to hold values from that column. Similarly, for every column that allows nulls, the program must replace the content of the SQLIND field. The content must be the address of a halfword that you can use as an indicator variable for the column. The program can acquire storage for this purpose, of course, but the storage areas used do not have to be contiguous.

Figure 4 shows the content of the descriptor area before the program obtains any rows of the result table. Addresses of fields and indicator variables are already in the SQLVAR.

Changing the CCSID for retrieved data

All Db2 string data has an encoding scheme and CCSID associated with it. When you select string data from a table, the selected data generally has the same encoding scheme and CCSID as the table. If the application uses some method, such as issuing the DECLARE VARIABLE statement, to change the CCSID of the selected data, the data is converted from the CCSID of the table to the CCSID that is specified by the application.

You can set the default application encoding scheme for a plan or package by specifying the value in the APPLICATION ENCODING field of the panel DEFAULTS FOR BIND PACKAGE or DEFAULTS FOR BIND PLAN. The default application encoding scheme for the Db2 subsystem is the value that was specified in the APPLICATION ENCODING field of installation panel DSNTIPF.

If you want to retrieve the data in an encoding scheme and CCSID other than the default values, you can use one of the following techniques:

• For dynamic SQL, set the CURRENT APPLICATION ENCODING SCHEME special register before you execute the SELECT statements. For example, to set the CCSID and encoding scheme for retrieved data to the default CCSID for Unicode, execute this SQL statement:

  EXEC SQL SET CURRENT APPLICATION ENCODING SCHEME ='UNICODE';

  The initial value of this special register is the application encoding scheme that is determined by the BIND option.
• For static and dynamic SQL statements that use host variables and host-variable arrays, use the DECLARE VARIABLE statement to associate CCSIDs with the host variables into which you retrieve the data. See Setting the CCSID for host variables for information about this technique.
• For static and dynamic SQL statements that use a descriptor, set the CCSID for the retrieved data in the SQLDA. The following text describes that technique.

To change the encoding scheme for SQL statements that use a descriptor, set up the SQLDA, and then make these additional changes to the SQLDA:

1. Put the character + in the sixth byte of field SQLDAID.
2. For each SQLVAR entry:
   1. Set the length field of SQLNAME to 8.
   2. Set the first two bytes of the data field of SQLNAME to X'0000'.
   3. Set the third and fourth bytes of the data field of SQLNAME to the CCSID, in hexadecimal, in which you want the results to display, or to X'0000'. X'0000' indicates that Db2 should use the default CCSID If you specify a nonzero CCSID, it must meet one of the following conditions:
      • A row in catalog table SYSSTRINGS has a matching value for OUTCCSID.
      • The Unicode conversion services support conversion to that CCSID. See Building and using Dynamic Link Libraries (DLLs) for information about the conversions supported.

      If you are modifying the CCSID to retrieve the contents of an ASCII, EBCDIC, or Unicode table on a Db2 for z/OS® system, and you previously executed a DESCRIBE statement on the SELECT statement that you are using to retrieve the data, the SQLDATA fields in the SQLDA that you used for the DESCRIBE contain the ASCII or Unicode CCSID for that table. To set the data portion of the SQLNAME fields for the SELECT, move the contents of each SQLDATA field in the SQLDA from the DESCRIBE to each SQLNAME field in the SQLDA for the SELECT. If you are using the same SQLDA for the DESCRIBE and the SELECT, be sure to move the contents of the SQLDATA field to SQLNAME before you modify the SQLDATA field for the SELECT.

   For REXX, you set the CCSID in the stem.n.SQLUSECCSID field instead of setting the SQLDAID and SQLNAME fields.

For example, suppose that the table that contains WORKDEPT and PHONENO is defined with CCSID ASCII. To retrieve data for columns WORKDEPT and PHONENO in ASCII CCSID 437 (X'01B5'), change the SQLDA as shown in the following figure.

Specifying that DESCRIBE use column labels in the SQLNAME field

By default, DESCRIBE describes each column in the SQLNAME field by the column name. You can tell it to use column labels instead.

Restriction: You cannot use column labels with set operators (UNION, INTERSECT, and EXCEPT).

To specify that DESCRIBE use column labels in the SQLNAME field, specify one of the following options when you issue the DESCRIBE statement:

USING LABELS

Specifies that SQLNAME is to contain labels. If a column has no label, SQLNAME contains nothing.

USING ANY

Specifies that SQLNAME is to contain labels wherever they exist. If a column has no label, SQLNAME contains the column name.

USING BOTH

Specifies that SQLNAME is to contain both labels and column names, when both exist.

In this case, FULSQLDA must contain a second set of occurrences of SQLVAR. The first set contains descriptions of all the columns with column names; the second set contains descriptions with column labels.

If you choose this option, perform the following actions:

• Allocate a longer SQLDA for the second DESCRIBE statement ((16 + SQLD * 88 bytes) instead of (16 + SQLD * 44))
• Put double the number of columns (SLQD * 2) in the SQLN field of the second SQLDA.

These actions ensure that enough space is available. Otherwise, if not enough space is available, DESCRIBE does not enter descriptions of any of the columns.

EXEC SQL
  DESCRIBE STMT INTO :FULSQLDA USING LABELS;

Some columns, such as those derived from functions or expressions, have neither name nor label; SQLNAME contains nothing for those columns. For example, if you use a UNION to combine two columns that do not have the same name and do not use a label, SQLNAME contains a string of length zero.

Describing tables with LOB and distinct type columns

In general, the steps that you perform when you prepare an SQLDA to select rows from a table with LOB and distinct type columns are similar to the steps that you perform if the table has no columns of this type. The only difference is that you need to analyze some additional fields in the SQLDA for LOB or distinct type columns.

For example, Suppose that you want to execute this SELECT statement:

SELECT USER, A_DOC FROM DOCUMENTS;

The USER column cannot contain nulls and is of distinct type ID, defined like this:

CREATE DISTINCT TYPE SCHEMA1.ID AS CHAR(20);

The A_DOC column can contain nulls and is of type CLOB(1M).

The result table for this statement has two columns, but you need four SQLVAR occurrences in your SQLDA because the result table contains a LOB type and a distinct type. Suppose that you prepare and describe this statement into FULSQLDA, which is large enough to hold four SQLVAR occurrences. FULSQLDA looks like the following figure .

The next steps are the same as for result tables without LOBs or distinct types:

1. Analyze each SQLVAR description to determine the maximum amount of space you need for the column value.

   For a LOB type, retrieve the length from the SQLLONGL field instead of the SQLLEN field.

2. Derive the address of some storage area of the required size.

   For a LOB data type, you also need a 4-byte storage area for the length of the LOB data. You can allocate this 4-byte area at the beginning of the LOB data or in a different location.

3. Put this address in the SQLDATA field.

   For a LOB data type, if you allocated a separate area to hold the length of the LOB data, put the address of the length field in SQLDATAL. If the length field is at beginning of the LOB data area, put 0 in SQLDATAL. When you use a file reference variable for a LOB column, the indicator variable indicates whether the data in the file is null, not whether the data to which SQLDATA points is null.

4. If the SQLTYPE field indicates that the value can be null, the program must also put the address of an indicator variable in the SQLIND field.

The following figure shows the contents of FULSQLDA after you enter pointers to the storage locations.

The following figure shows the contents of FULSQLDA after you execute a FETCH statement.

Setting an XML host variable in an SQLDA

Instead of specifying host variables to store XML values from a table, you can create an SQLDA to point to the data areas where Db2 puts the retrieved data. The SQLDA needs to describe the data type for each data area.

To set an XML host variable in an SQLDA:

1. Allocate an appropriate SQLDA.
2. Issue a DESCRIBE statement for the SQL statement whose result set you want to store. The DESCRIBE statement populates the SQLDA based on the column definitions. In the SQLDA, an SQLVAR entry is populated for each column in the result set. (Multiple SQLVAR entries are populated for LOB columns and columns with distinct types.) For columns of type XML the associated SQLVAR entry is populated as follows:

   Table 3. SQLVAR field values for XML columns

   SQLVAR field	Value for an XML column
   sqltype SQLTYPE	988 for a column that is not nullable or 989 for a nullable column
   sqllen SQLLEN	0
   sqldata SQLDATA	0
   sqlind SQLIND	0
   sqlname SQLNAME	The unqualified name or label of the column

3. Check the SQLTYPE field of each SQLVAR entry. If the SQLTYPE field is 988 or 989, the column in the result set is an XML column.
4. For each XML column, make the following changes to the associated SQLVAR entry:
   1. Change the SQLTYPE field to indicate the data type of the host variable to receive the XML data. You can retrieve the XML data into a host variable of type XML AS BLOB, XML AS CLOB, or XML AS DBCLOB, or a compatible string data type.
      If the target host variable type is XML AS BLOB, XML AS CLOB, or XML AS DBCLOB, set the SQLTYPE field to one of the following values:

      404

      XML AS BLOB

      405

      nullable XML AS BLOB

      408

      XML AS CLOB

      409

      nullable XML AS CLOB

      412

      XML AS DBCLOB

      413

      nullable XML AS DBCLOB

      If the target host variable type is a string data type, set the SQLTYPE field to a valid string value.

      Restriction: You cannot use the XML type (988/989) as a target host variable type.
   2. If the target host variable type is XML AS BLOB, XML AS CLOB, or XML AS DBCLOB, change the first two bytes in the SQLNAME field to X'0000' and the fifth and sixth bytes to X'0100'. These bytes indicate that the value to be received is an XML value.
5. Populate the extended SQLVAR fields for each XML column as you would for a LOB column, as indicated in the following table.

   Table 4. Fields for an extended SQLVAR entry for an XML host variable

   SQLVAR field	Value for an XML host variable
   len.sqllonglen SQLLONGL SQLLONGLEN	length attribute for the XML host variable
   *	Reserved
   sqldatalen SQLDATAL SQLDATALEN	pointer to the length of the XML host variable
   sqldatatype_name SQLTNAME SQLDATATYPENAME	not used

You can now use the SQLDA to retrieve the XML data into a host variable of type XML AS BLOB, XML AS CLOB, or XML AS DBCLOB, or a compatible string data type.

Executing a varying-list SELECT statement dynamically

You can easily retrieve rows of the result table using a varying-list SELECT statement. The statements differ only a little from those for the fixed-list example.

1. Open the cursor. If the SELECT statement contains no parameter marker, this step is simple enough. For example:

   EXEC SQL OPEN C1;

2. Fetch rows from the result table. This statement differs from the corresponding one for the case of a fixed-list select. Write:

   EXEC SQL
     FETCH C1 USING DESCRIPTOR :FULSQLDA;

   The key feature of this statement is the clause USING DESCRIPTOR :FULSQLDA. That clause names an SQL descriptor area in which the occurrences of SQLVAR point to other areas. Those other areas receive the values that FETCH returns. It is possible to use that clause only because you previously set up FULSQLDA to look like Figure 3.

   Figure 5 shows the result of the FETCH. The data areas identified in the SQLVAR fields receive the values from a single row of the result table.

   Successive executions of the same FETCH statement put values from successive rows of the result table into these same areas.

3. Close the cursor. This step is the same as for the fixed-list case. When no more rows need to be processed, execute the following statement:

   EXEC SQL CLOSE C1;

   When COMMIT ends the unit of work containing OPEN, the statement in STMT reverts to the unprepared state. Unless you defined the cursor using the WITH HOLD option, you must prepare the statement again before you can reopen the cursor.

Executing arbitrary statements with parameter markers

Consider, as an example, a program that executes dynamic SQL statements of several kinds, including varying-list SELECT statements, any of which might contain a variable number of parameter markers. This program might present your users with lists of choices: choices of operation (update, select, delete); choices of table names; choices of columns to select or update. The program also enables the users to enter lists of employee numbers to apply to the chosen operation. From this, the program constructs SQL statements of several forms, one of which looks like this:

SELECT .... FROM DSN8C10.EMP
  WHERE EMPNO IN (?,?,?,...?);

The program then executes these statements dynamically.

When the number and types of parameters are known

In the preceding example, you do not know in advance the number of parameter markers, and perhaps the kinds of parameter they represent. You can use techniques described previously if you know the number and types of parameters, as in the following examples:

• If the SQL statement is not SELECT, name a list of host variables in the EXECUTE statement:

  WRONG:     EXEC SQL EXECUTE STMT;
   
  RIGHT:     EXEC SQL EXECUTE STMT USING :VAR1, :VAR2, :VAR3;

• If the SQL statement is SELECT, name a list of host variables in the OPEN statement:

  WRONG:     EXEC SQL OPEN C1;
   
  RIGHT:     EXEC SQL OPEN C1 USING :VAR1, :VAR2, :VAR3;

In both cases, the number and types of host variables named must agree with the number of parameter markers in STMT and the types of parameter they represent. The first variable (VAR1 in the examples) must have the type expected for the first parameter marker in the statement, the second variable must have the type expected for the second marker, and so on. There must be at least as many variables as parameter markers.

When the number and types of parameters are not known

When you do not know the number and types of parameters, you can adapt the SQL descriptor area. Your program can include an unlimited number of SQLDAs, and you can use them for different purposes. Suppose that an SQLDA, arbitrarily named DPARM, describes a set of parameters.

The structure of DPARM is the same as that of any other SQLDA. The number of occurrences of SQLVAR can vary, as in previous examples. In this case, every parameter marker must have one SQLVAR. Each occurrence of SQLVAR describes one host variable that replaces one parameter marker at run time. Db2 replaces the parameter markers when a non-SELECT statement executes or when a cursor is opened for a SELECT statement.

You must enter certain fields in DPARM before using EXECUTE or OPEN; you can ignore the other fields.

Field

Use when describing host variables for parameter markers

SQLDAID

The seventh byte indicates whether more than one SQLVAR entry is used for each parameter marker. If this byte is not blank, at least one parameter marker represents a distinct type or LOB value, so the SQLDA has more than one set of SQLVAR entries.

You do not set this field for a REXX SQLDA.

SQLDABC

The length of the SQLDA, which is equal to SQLN * 44 + 16. You do not set this field for a REXX SQLDA.

SQLN

The number of occurrences of SQLVAR allocated for DPARM. You do not set this field for a REXX SQLDA.

SQLD

The number of occurrences of SQLVAR actually used. This number must not be less than the number of parameter markers. In each occurrence of SQLVAR, put information in the following fields: SQLTYPE, SQLLEN, SQLDATA, SQLIND.

SQLTYPE

The code for the type of variable, and whether it allows nulls.

SQLLEN

The length of the host variable.

SQLDATA

The address of the host variable.

For REXX, this field contains the value of the host variable.

SQLIND

The address of an indicator variable, if needed.

For REXX, this field contains a negative number if the value in SQLDATA is null.

SQLNAME

Ignore.

Using the SQLDA with EXECUTE or OPEN

To indicate that the SQLDA called DPARM describes the host variables substituted for the parameter markers at run time, use a USING DESCRIPTOR clause with EXECUTE or OPEN.

• For a non-SELECT statement, write:

  EXEC SQL EXECUTE STMT USING DESCRIPTOR :DPARM;

• For a SELECT statement, write:

  EXEC SQL OPEN C1 USING DESCRIPTOR :DPARM;

How bind options REOPT(ALWAYS), REOPT(AUTO) and REOPT(ONCE) affect dynamic SQL

When you specify the bind option REOPT(ALWAYS), Db2 reoptimizes the access path at run time for SQL statements that contain host variables, parameter markers, or special registers. The option REOPT(ALWAYS) has the following effects on dynamic SQL statements:

• When you specify the option REOPT(ALWAYS), Db2 automatically uses DEFER(PREPARE), which means that Db2 waits to prepare a statement until it encounters an OPEN or EXECUTE statement.
• When you execute a DESCRIBE statement and then an EXECUTE statement on a non-SELECT statement, Db2 prepares the statement twice: Once for the DESCRIBE statement and once for the EXECUTE statement. Db2 uses the values in the input variables only during the second PREPARE. These multiple PREPAREs can cause performance to degrade if your program contains many dynamic non-SELECT statements. To improve performance, consider putting the code that contains those statements in a separate package and then binding that package with the option REOPT(NONE).
• If you execute a DESCRIBE statement before you open a cursor for that statement, Db2 prepares the statement twice. If, however, you execute a DESCRIBE statement after you open the cursor, Db2 prepares the statement only once. To improve the performance of a program bound with the option REOPT(ALWAYS), execute the DESCRIBE statement after you open the cursor. To prevent an automatic DESCRIBE before a cursor is opened, do not use a PREPARE statement with the INTO clause.
• If you use predictive governing for applications bound with REOPT(ALWAYS), Db2 does not return a warning SQLCODE when dynamic SQL statements exceed the predictive governing warning threshold. Db2 does return an error SQLCODE when dynamic SQL statements exceed the predictive governing error threshold. Db2 returns the error SQLCODE for an EXECUTE or OPEN statement.

When you specify the bind option REOPT(AUTO), Db2 optimizes the access path for SQL statements at the first EXECUTE or OPEN. Each time a statement is executed, Db2 determines if a new access path is needed to improve the performance of the statement. If a new access path will improve the performance, Db2 generates one. The option REOPT(AUTO) has the following effects on dynamic SQL statements:

• When you specify the bind option REOPT(AUTO), Db2 optimizes the access path for SQL statements at the first EXECUTE or OPEN. Each time a statement is executed, Db2 determines if a new access path is needed to improve the performance of the statement. If a new access path will improve the performance, Db2 generates one.
• When you specify the option REOPT(ONCE), Db2 automatically uses DEFER(PREPARE), which means that Db2 waits to prepare a statement until it encounters an OPEN or EXECUTE statement.
• When Db2 prepares a statement using REOPT(AUTO), it saves the access path in the dynamic statement cache. This access path is used each time the statement is run, until Db2 determines that a new access path is needed to improve the performance or the statement that is in the cache is invalidated (or removed from the cache) and needs to be rebound.
• The DESCRIBE statement has the following effects on dynamic statements that are bound with REOPT(AUTO):
  • When you execute a DESCRIBE statement before an EXECUTE statement on a non-SELECT statement, Db2 prepares the statement an extra time if it is not already saved in the cache: Once for the DESCRIBE statement and once for the EXECUTE statement. Db2 uses the values of the input variables only during the second time the statement is prepared. It then saves the statement in the cache. If you execute a DESCRIBE statement before an EXECUTE statement on a non-SELECT statement that has already been saved in the cache, Db2 will always prepare the non-SELECT statement for the DESCRIBE statement, and will prepare the statement again on EXECUTE only if Db2 determines that a new access path different from the one already saved in the cache can improve the performance.
  • If you execute DESCRIBE on a statement before you open a cursor for that statement, Db2 always prepares the statement on DESCRIBE. However, Db2 will not prepare the statement again on OPEN if the statement has already been saved in the cache and Db2 does not think that a new access path is needed at OPEN time. If you execute DESCRIBE on a statement after you open a cursor for that statement, Db2 prepared the statement only once if it is not already saved in the cache. If the statement is already saved in the cache and you execute DESCRIBE after you open a cursor for that statement, Db2 does not prepare the statement, it used the statement that is saved in the cache.
• If you use predictive governing for applications that are bound with REOPT(AUTO), Db2 does not return a warning SQLCODE when dynamic SQL statements exceed the predictive governing warning threshold. Db2 does return an error SQLCODE when dynamic SQL statements exceed the predictive governing error threshold. Db2 returns the error SQLCODE for an EXECUTE or OPEN statement.

When you specify the bind option REOPT(ONCE), Db2 optimizes the access path only once, at the first EXECUTE or OPEN, for SQL statements that contain host variables, parameter markers, or special registers. The option REOPT(ONCE) has the following effects on dynamic SQL statements:

• When you specify the option REOPT(ONCE), Db2 automatically uses DEFER(PREPARE), which means that Db2 waits to prepare a statement until it encounters an OPEN or EXECUTE statement.
• When Db2 prepares a statement using REOPT(ONCE), it saves the access path in the dynamic statement cache. This access path is used each time the statement is run, until the statement that is in the cache is invalidated (or removed from the cache) and needs to be rebound.
• The DESCRIBE statement has the following effects on dynamic statements that are bound with REOPT(ONCE):
  • When you execute a DESCRIBE statement before an EXECUTE statement on a non-SELECT statement, Db2 prepares the statement twice if it is not already saved in the cache: Once for the DESCRIBE statement and once for the EXECUTE statement. Db2 uses the values of the input variables only during the second time the statement is prepared. It then saves the statement in the cache. If you execute a DESCRIBE statement before an EXECUTE statement on a non-SELECT statement that has already been saved in the cache, Db2 prepares the non-SELECT statement only for the DESCRIBE statement.
  • If you execute DESCRIBE on a statement before you open a cursor for that statement, Db2 always prepares the statement on DESCRIBE. However, Db2 will not prepare the statement again on OPEN if the statement has already been saved in the cache. If you execute DESCRIBE on a statement after you open a cursor for that statement, Db2 prepared the statement only once if it is not already saved in the cache. If the statement is already saved in the cache and you execute DESCRIBE after you open a cursor for that statement, Db2 does not prepare the statement, it used the statement that is saved in the cache.
  To improve the performance of a program that is bound with REOPT(ONCE), execute the DESCRIBE statement after you open a cursor. To prevent an automatic DESCRIBE before a cursor is opened, do not use a PREPARE statement with the INTO clause.
• If you use predictive governing for applications that are bound with REOPT(ONCE), Db2 does not return a warning SQLCODE when dynamic SQL statements exceed the predictive governing warning threshold. Db2 does return an error SQLCODE when dynamic SQL statements exceed the predictive governing error threshold. Db2 returns the error SQLCODE for an EXECUTE or OPEN statement.