This feature can also result in a significant Db2 CPU time reduction for cases where creating and ending threads constitutes a large part of the IMS transaction cost. 

The reduction in Db2 CPU usage can come at the cost of increased real storage usage. The storage increase on the Db2 side for connection pools themselves is negligible. However, the increase in storage usage for Db2 to keep plans and packages that are allocated and reusable is application-dependent. Any increase in storage usage should be weighed against the CPU savings that are achieved by using this feature. On the IMS side, a storage increase of about 10 KB per pooled entry per IMS region is to be expected. 

## Db2 JCC Statement caching 

For IMS applications that are using the IMS External Subsystem Attach Facility (ESAF) connection pooling and JDBC calls (Db2 JCC using dynamic SQL), another option is available to cache the Db2 JCC prepared statements, combined with the use of the Db2 local dynamic statement cache. To enable this feature, the following set-up tasks must be completed: 

GLYPH<SM590000> Specify DSNMIN20 (instead of DSNMIN10) as external subsystem module table (ESMT) parameter to enable ESAF connection pooling. 

GLYPH<SM590000> Set the maxStatements property in com.ibm.db2.jcc.DB2BaseDataSource  to a value greater than 0 . The default value of maxStatements is 0 , which means the statement pooling is disabled. 

GLYPH<SM590000> Set the keepDynamic  property in com.ibm.db2.jcc.DB2BaseDataSource  to true. 

GLYPH<SM590000> Set the Db2 system parameter CACHEDYN=YES to enable the global dynamic statement cache. Size the DSC pool by using the EDMSTMTC system parameter. 

GLYPH<SM590000> Bind the Db2 packages that are started with the KEEPDYNAMIC(YES) option. 

GLYPH<SM590000> Specify DB2JCC_ESAF_THREAD_NOTIFICATION = YES in the IMS DFSJVMEV (ENVIRON=) member. The ENVIRON parameter specifies the name of the IMS PROCLIB data set member that contains the JVM environment settings. 

The DB2JCC_ESAF_THREAD_NOTIFICATION parameter specifies whether you intend to invoke JDBC calls from any application that runs in the IMS-dependent region. When you set the parameter to YES, it also indicates that IMS notifies the Db2 JCC driver when pooled threads are in use or purged from the pool. If the application is issuing JCC calls, Db2 IMS attachment connection pooling is enabled, and maxStatements are used, you must specify YES. 

The DB2JCC_ESAF_THREAD_NOTIFICATION property tells IMS to notify Db2 JCC which pool thread or connection token is in use. This setting allows JCC to set context to the proper connection and to find any previously cached statements (triggered by setting the keepDynamic and maxStatements properties). 

The DB2JCC_ESAF_THREAD_NOTIFICATION property also allows JCC to clean up connections or statements that were purged from the ESAF Db2 thread pool and is required to keep things in sync. 

