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 Not valid for REBIND of a native SQL procedure package. |
- ( NO )
- Specifies that DB2 does not keep dynamic SQL statements after commit points.
- ( YES )
- Specifies that DB2 keeps dynamic SQL statements after commit points.
- 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.