KEEPDYNAMIC bind option

The KEEPDYNAMIC option determines whether DB2® keeps dynamic SQL statements after commit points. .

KEEPDYNAMIC ( NO )( YES ) On: BIND and REBIND PLAN and PACKAGE

Start of changeNot valid for REBIND of a native SQL procedure package.End of change
( NO )
Specifies that DB2 does not keep dynamic SQL statements after commit points.
( YES )
Specifies that DB2 keeps dynamic SQL statements after commit points.
If you specify KEEPDYNAMIC(YES), the application does not need to prepare an SQL statement after every commit point, DB2 keeps the dynamic SQL statement until one of the following events occurs:
  • The application process ends.
  • A rollback operation occurs.
  • The application executes an explicit PREPARE statement with the same statement identifier.

If you specify KEEPDYNAMIC(YES), and the prepared statement cache is active, DB2 keeps a copy of the prepared statement in the cache. If the prepared statement cache is not active, DB2 keeps only the SQL statement string past a commit point. DB2 then implicitly prepares the SQL statement if the application executes an OPEN, EXECUTE, or DESCRIBE operation for that statement.

If you specify KEEPDYNAMIC(YES), DDF server threads that are used to execute KEEPDYNAMIC(YES) packages will remain active. Active DDF server threads are subject to idle thread timeouts.

If you specify KEEPDYNAMIC(YES), you must not specify REOPT(ALWAYS). KEEPDYNAMIC(YES) and REOPT(ALWAYS) are mutually exclusive. However, you can use KEEPDYNAMIC(YES) with REOPT(ONCE).

Performance hint: KEEPDYNAMIC(YES) results in improved performance if your DRDA client application uses a cursor defined WITH HOLD. DB2 automatically closes a held cursor when there are no more rows to retrieve, which eliminates an extra network message.

Defaults:

Process Default value
BIND PLAN NO
BIND PACKAGE NO
REBIND PLAN Existing value
REBIND PACKAGE Existing value

Catalog record: Column KEEPDYNAMIC of table SYSPLAN and SYSPACKAGE.