Tuning the CICS Db2 attachment facility

The CICS® Db2® attachment facility provides a multithread connection to Db2. The DB2CONN, DB2ENTRY, and DB2TRAN definitions of the CICS Db2 attachment facility define the authorization and access attributes on a transaction and transaction group basis. You can optimize performance between CICS and Db2 by adjusting the transaction class limits, MXT system parameters of CICS, and the THREADWAIT, TCBLIMIT, THREADLIMIT, and PRIORITY attributes of DB2CONN and DB2ENTRY.

A number of topics provide more information about the CICS Db2 attachment and performance considerations:

Defining the CICS Db2 connection explains the recommendations for defining the CICS Db2 connection for optimum performance.
How threads are created, used, and terminated explains threads and the use of the THREADWAIT, TCBLIMIT, and THREADLIMIT parameters with Db2.
Application design and development considerations for CICS Db2 has recommendations for application design.
Tuning a CICS application that accesses Db2 has recommendations for tuning CICS Db2 applications.

In summary, the objectives in tuning the CICS attachment facility are to:

Optimize the number of threads in the connection.
The total number of threads in the connection, and the number of threads for each dedicated entry and the pool must be optimized. A larger number of threads than is needed requires additional processor time to dispatch the TCBs and additional storage for plans, data, and control blocks. If an insufficient number of threads is defined, response time increases.
Optimize the assignment and reuse of threads.
Reusing threads avoids the thread creation and termination process, including plan allocation and authorization checks. Thread creation and termination represent a significant part of the processing time for a simple transaction. Thread reuse can be measured using CICS Db2 statistics.

Limit conversational transactions either through transaction classes or by using a dedicated DB2ENTRY (THREADLIMIT greater than 0) with THREADWAIT=YES specified. Otherwise, they tie up the pool. Do not allow conversational transactions to use the pool.
For pool and entry threads, choose the priority assigned to the subtask thread TCBs, using the PRIORITY parameter.
The PRIORITY parameter controls the priority of the CICS open L8 thread TCBs relative to the CICS main TCB (QR TCB). There are three options: PRIORITY=HIGH, PRIORITY=LOW, and PRIORITY=EQUAL.

When PRIORITY=HIGH is specified, transactions run at a higher priority than CICS, saving virtual storage, releasing locks, and avoiding other transactions deadlocking or timing out. However, if all threads are specified with PRIORITY=HIGH, CICS itself might be at too low a priority, so for example, a complex SQL call could spend a long time in Db2, and the CICS TCB might not be dispatched.

Set PRIORITY=HIGH for your transactions with the highest weighted average number of SQL calls. The highest weighted average is equal to the number of SQL calls per transaction multiplied by the frequency of transaction. Set PRIORITY=LOW or EQUAL for other transactions. If the CPU usage per call is high, you should not set PRIORITY=HIGH.
Choose the best authorization strategy to avoid or minimize the process of signon by each thread.
Minimize the number of DB2ENTRYs. Use wildcarding and dynamic plan selection where relevant to combine appropriate transactions in an entry. Allow low use transactions to default to the pool. However, it should be noted that defining transaction IDs using wildcard characters removes the ability to collect CICS Db2 statistics on a per transaction basis as statistics are collected for each DB2ENTRY which will now represent a group of transactions.

For information about tuning Db2 tables and the Db2 subsystem, and for general considerations when tuning a Db2 application, see Managing Db2 performance in Db2 for z/OS product documentation.