Methods for keeping prepared statements after commit points

If your program issues the same dynamic SQL statement in different commit scopes, consider specifying that DB2® keeps the prepared versions of these statements after commit points. This behavior can improve performance. By default, DB2 does not keep these statements after commit points.

Introductory concepts
Begin general-use programming interface information.

KEEPDYNAMIC(YES) bind option

The KEEPDYNAMIC(YES) bind option lets you hold dynamic statements past a commit point for an application process. 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.
COMMIT
⋮
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.

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 a commit point.On a subsequent OPEN, EXECUTE, or DESCRIBE, DB2 must prepare the statement again before performing the requested operation. The following example illustrates this concept.
PREPARE STMT1 FROM ...     Statement is prepared and put in memory.
EXECUTE STMT1
COMMIT
⋮
EXECUTE STMT1              Application does not issue PREPARE.
COMMIT                     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. If the application issues an OPEN, EXECUTE, or DESCRIBE after a commit point, the application process uses its local copy of the prepared statement to avoid a PREPARE and a search of the cache. 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.
COMMIT                     DB2 uses the prepared statement in memory.
⋮
PREPARE STMT1 FROM ...     Again, no PREPARE needed.
COMMIT                     DB2 uses the prepared statement in memory.
The local instance of the prepared SQL statement is kept in ssnmDBM1 storage until one of the following events occurs:
  • The application process ends.
  • A rollback operation occurs.
  • 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, the package at the DB2 for z/OS® server must be bound with KEEPDYNAMIC(YES).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.