Enabling KEEPDYNAMIC refresh for DBATs

You can configure clients such as Db2 Connect to enable KEEPDYNAMIC refresh support in a Db2 for z/OS® server. The KEEPDYNAMIC refresh support enables applications to take advantage of keeping dynamic statements prepared beyond transaction boundaries, while also enabling Db2 to periodically clean up the database access threads (DBATs) transparently to the applications.

About this task

The KEEPDYNAMIC(YES) bind option lets applications hold dynamic prepared statements past the point of commit or rollback for an application process. This behavior can improve performance, but it can also result in problems when the same database access threads (DBATs) process the application for too long.

However, when a client is configured with automatic client reroute, Db2 can use process called KEEPDYNAMIC refresh to periodically terminate the existing DBATs for packages that are bound with the KEEPDYAMIC(YES) bind option. When these terminations occur, the work seamlessly reroutes to another DBAT for further processing.

Procedure

To enable KEEPDYNAMIC refresh for database access threads, use one of following approaches to configure the client for automatic client reroute:

Enable sysplex workload balancing with automatic client reroute. For more information, see Configuration of sysplex workload balancing and automatic client reroute for Java clients.
Configure the client to use client affinities with seamless failover for the Db2 data sharing group. For more information, see the enableClientAffinitiesList and enableSeamlessFailover property descriptions in Common IBM Data Server Driver for JDBC and SQLJ properties for all supported database products.

Results

When a client is configured to use KEEPDYNAMIC refresh, Db2 automatically terminates the DBATs at commit boundaries based on the following time limits, and the client seamlessly reroutes the work to another DBAT, without any impact to the application.

The maximum use time exceeds 1 hour.
The maximum idle wait time for the next transaction request exceeds 20 minutes.