Tuning a CICS application that accesses Db2

CICS® applications that access Db2® must be tuned before they move to production and periodically whist they are in production.

About this task

When moving a CICS application that accesses Db2 to production, add these checks to those already performed for CICS :
  • Check that all the application programs that make Db2 requests are threadsafe. If they are, you will be exploiting the open transaction environment (OTE), and improving the performance of the application. See Enabling CICS Db2 applications to use OTE through threadsafe programming for an explanation of how application programs work in the open transaction environment.
  • Ensure that the number and type of SQL statements used meet the program specifications (use the Db2 accounting facility).
  • Check if the number of get and updated pages in the buffer pool is higher than expected (use the Db2 accounting facility).
  • Check that planned indexes are being used (use EXPLAIN), and that inefficient SQL statements are not being used.
  • Check if DDL is being used and, if so, the reasons for using it (use the Db2 accounting facility).
  • Check if conversational transactions are being used.

    Determine whether pseudoconversational transactions can be used instead. If conversational design is needed, check the Db2 objects that are locked across conversations. Check also that the number of new threads needed because of this conversational design is acceptable.

  • Check the locks used and their duration.
    Make sure that tablespace locks are not being used because of incorrect or suboptimal specification of, for example:
    • LOCK TABLE statement
    • LOCKSIZE=TS specification
    • ISOLATION LEVEL(RR) specification
    • Lock escalation.
    This information is available in the catalog tables, except for lock escalation, which is an installation parameter (DSNZPARM).
  • Check the plans used and their sizes. Even though the application plans are segmented, the more DBRMs used in the plan, the longer the time needed to BIND and REBIND the plans in case of modification. Try to use packages whenever possible. Packages were designed to solve the problems of:
    • Binding the whole plan again after modifying your SQL application. (This was addressed by dynamic plan selection, at the cost of performance.)
    • Binding each application plan if the modified SQL application is used by many applications.

When this tuning is complete, use the expected transaction load to decide on the DB2ENTRY definitions required, and the number of threads required. Check also the impact of these transactions on the Db2 and CICS subsystems.

When tuning a CICS application that accesses Db2 in production:
  • Check that the CICS applications use the planned indexes by monitoring the number of GET PAGES in the buffer pool (use the Db2 accounting facility). The reasons for an index not being used may be that the index has been dropped, or that the index was created after the plan was bound.
  • Use the lock manager data from the accounting facility to check on suspensions, deadlocks, and timeouts.