Methods for keeping prepared statements after the point of commit or rollback

Start of change If your program issues the same dynamic SQL statement in different transaction scopes for COMMIT or ROLLBACK, consider specifying that Db2 keeps the prepared versions of these statements after the point of commit or rollback. This behavior can improve performance. By default, Db2 does not keep these statements after the point of commit or rollback. End of change

Introductory concepts

Begin general-use programming interface information.

Start of change Two bind options can be used to keep prepared dynamic statements across transactions. The KEEPDYNAMIC(YES) bind option can be used to keep prepared dynamic statements after COMMIT or ROLLBACK. The RELEASE(DEALLOCATE) bind option can be used to keep prepared dynamic statements that reference declared global temporary tables after COMMIT. End of change For most dynamic statements, you can use the KEEPDYNAMIC(YES) bind option. For statements that reference declared global temporary tables, you can use the RELEASE(DEALLOCATE) bind option.

KEEPDYNAMIC(YES) bind option

Start of change The KEEPDYNAMIC(YES) bind option lets you hold dynamic statements past the point of commit or rollback for an application process. End of change An application can issue a PREPARE for a statement once and omit subsequent PREPARE statements for that statement. The following example illustrates an application that is written to use KEEPDYNAMIC(YES).

PREPARE STMT1 FROM ...     Statement is prepared.
EXECUTE STMT1
COMMIT
⋮
EXECUTE STMT1              Application does not issue PREPARE.
ROLLBACK
⋮
EXECUTE STMT1              Again, no PREPARE needed.
COMMIT

To understand how the KEEPDYNAMIC bind option works, you need to differentiate between the executable form of a dynamic SQL statement, which is the prepared statement, and the character string form of the statement, which is the statement string.

Example: Relationship between KEEPDYNAMIC(YES) and statement caching

When the dynamic statement cache is not active, and you run an application bound with KEEPDYNAMIC(YES), Db2 saves only the statement string for a prepared statement after the point of commit or rollback. End of change

On a subsequent OPEN, EXECUTE, or DESCRIBE, Db2 must prepare the statement again before performing the requested operation. The following example illustrates this concept. Start of change

PREPARE STMT1 FROM ...     Statement is prepared and put in memory.
EXECUTE STMT1
COMMIT
⋮
EXECUTE STMT1              Application does not issue PREPARE.
ROLLBACK                   Db2 prepares the statement again.
⋮
EXECUTE STMT1              Again, no PREPARE needed.
COMMIT

When the dynamic statement cache is active, and you run an application bound with KEEPDYNAMIC(YES), Db2 retains a copy of both the prepared statement and the statement string. The prepared statement is cached locally for the application process. In general, the statement is globally cached in the EDM pool, to benefit other application processes. Start of change If the application issues an OPEN, EXECUTE, or DESCRIBE after the point of commit or rollback, the application process uses its local copy of the prepared statement to avoid a PREPARE and a search of the cache. End of change The following example illustrates this process.

PREPARE STMT1 FROM ...     Statement is prepared and put in memory.
EXECUTE STMT1
COMMIT
⋮
EXECUTE STMT1              Application does not issue PREPARE.
COMMIT                     Db2 uses the prepared statement in memory.
⋮
EXECUTE STMT1              Again, no PREPARE needed.
ROLLBACK                   Db2 uses the prepared statement in memory.
⋮
PREPARE STMT1 FROM ...     Application issues PREPARE on the same statement.
COMMIT                     Db2 discards the locally cached copy of the 
                           prepared statement from memory, and replaces it with
                           the copy of the prepared statement from the dynamic
                           statement cache.

The local instance of the prepared SQL statement is kept in ssnmDBM1 storage until one of the following events occurs:

The application process ends.
The application issues an explicit PREPARE statement with the same statement name.
If the application does issue a PREPARE for the same SQL statement name that has a kept dynamic statement associated with it, the kept statement is discarded and Db2 prepares the new statement.
The statement is removed from memory because the statement has not been used recently, and the number of kept dynamic SQL statements reaches the subsystem default as set during installation.

Handling implicit prepare errors

If a statement is needed during the lifetime of an application process, and the statement has been removed from the local cache, Db2 might be able to retrieve it from the global cache. If the statement is not in the global cache, Db2 must implicitly prepare the statement again. The application does not need to issue a PREPARE statement. However, if the application issues an OPEN, EXECUTE, or DESCRIBE for the statement, the application must be able to handle the possibility that Db2 is doing the prepare implicitly. Any error that occurs during this prepare is returned on the OPEN, EXECUTE, or DESCRIBE.

How KEEPDYNAMIC affects applications that use distributed data

If a requester does not issue a PREPARE after a COMMIT or ROLLBACK, the package at the Db2 for z/OS® server must be bound with KEEPDYNAMIC(YES). End of change

If both requester and server are Db2 for z/OS subsystems, the Db2 requester assumes that the KEEPDYNAMIC value for the package at the server is the same as the value for the plan at the requester.

The KEEPDYNAMIC option has performance implications for DRDA clients that specify WITH HOLD on their cursors:

If KEEPDYNAMIC(NO) is specified, a separate network message is required when the DRDA client issues the SQL CLOSE for the cursor.
If KEEPDYNAMIC(YES) is specified, the Db2 for z/OS server automatically closes the cursor when SQLCODE +100 is detected, which means that the client does not have to send a separate message to close the held cursor. This reduces network traffic for DRDA applications that use held cursors. It also reduces the duration of locks that are associated with the held cursor.

Note: If one member of a data sharing group has enabled the cache but another has not, and an application is bound with KEEPDYNAMIC(YES), Db2 must implicitly prepare the statement again if the statement is assigned to a member without the cache. This can mean a slight reduction in performance.

RELEASE(DEALLOCATE) bind option

The RELEASE(DEALLOCATE) bind option retains prepared INSERT, UPDATE, DELETE, and MERGE statements that reference declared global temporary tables past commit points. If this option is used, the number of required PREPARE operations in an application that references declared global temporary tables can be reduced, resulting in a potential performance improvement.

To take advantage of this potential performance improvement, alter existing applications to include one PREPARE operation for the dynamic statement and omit subsequent PREPARE operations for the statement. The following example illustrates an application that is written to take advantage of the RELEASE(DEALLOCATE) performance enhancement and to keep prepared statements after commit points.

DECLARE GLOBAL TEMPORARY TABLE SESSION.tablename

PREPARE STMT1 FROM ...     Statement is prepared and put in memory.
EXECUTE STMT1
COMMIT
⋮
EXECUTE STMT1              Application does not issue PREPARE.
COMMIT                     DB2 uses the prepared statement in memory.
⋮
EXECUTE STMT1              Again, no PREPARE needed.
COMMIT                     DB2 uses the prepared statement in memory.

End general-use programming interface information.