KEEPDYNAMIC bind option

Start of change The KEEPDYNAMIC option determines whether Db2 keeps dynamic SQL statements after the point of commit or rollback. End of change .

Command option	Option values	Used with
KEEPDYNAMIC	( NO ) ( YES )	BIND PACKAGE BIND PLAN REBIND PACKAGE ¹ REBIND PLAN

Note:

The KEEPDYNAMIC bind option is not valid for REBIND of packages for native SQL procedures, native REST services, advanced triggers.

(NO): db2z_bindoptisolation
Specifies that Db2 does not keep dynamic SQL statements after the point of commit or rollback.
(YES): Specifies that Db2 keeps dynamic SQL statements after the point of commit or rollback.

If you specify both KEEPDYNAMIC(NO) and RELEASE(DEALLOCATE), some dynamic SQL statements that reference declared temporary tables are held across commit points. See the description of the RELEASE(DEALLOCATE) option for more information.

If you specify KEEPDYNAMIC(YES), the application does not need to prepare an SQL statement after every commit or rollback point, Db2 keeps the dynamic SQL statement until one of the following events occurs: End of change

The application process ends.
The application executes an explicit PREPARE statement with the same statement identifier.

If you specify KEEPDYNAMIC(YES), and the dynamic statement cache is active, Db2 keeps a copy of the prepared statement in the dynamic statement cache. Start of change If the dynamic statement cache is not active, Db2 keeps only the SQL statement string past the point of commit or rollback. End of change 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.

Default values for KEEPDYNAMIC

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

Catalog records for KEEPDYNAMIC

See the KEEPDYNAMIC column in SYSPACKAGE catalog table and SYSPLAN catalog table.