Monitoring the CICS DB2 attachment facility using CICS DB2 statistics

In addition to the limited statistics output by the DSNC DISP STAT command and those output to the STATSQUEUE destination of the DB2CONN during attachment facility shutdown, a more comprehensive set of CICS DB2 statistics can be collected using standard CICS statistics interfaces.

About this task

The CICS DB2 global and resource statistics are described in detail in CICS DB2 statistics.

CICS DB2 statistics are supported for all types of CICS statistics, namely:
  • Requested statistics - CICS DB2 statistics are written as a result of an EXEC CICS PERFORM STATISTICS RECORD command with the DB2 keyword.
  • Requested reset statistics - a special case of requested statistics in which the statistics counters are reset after collection.
  • Interval statistics - statistics written when a requested interval expires.
  • End of day statistics - a special case of interval statistics.
  • Unsolicited statistics - CICS writes DB2 global and resource statistics to SMF when the attachment facility is shut down. Also, DB2 resource statistics are written to SMF when a DB2ENTRY is discarded.

Procedure

  1. Use one of the following methods to collect DB2 statistics.
    • Use the EXEC CICS COLLECT statistics command with the DB2CONN keyword to allow CICS DB2 global statistics to be collected. CICS DB2 global statistics are mapped by the DFHD2GDS DSECT.
    • Use the EXEC CICS COLLECT statistics command with the DB2ENTRY() keyword to allow CICS DB2 resource statistics to be collected for a particular DB2ENTRY. CICS DB2 resource statistics are mapped by the DFHD2RDS DSECT.
    • Use the EXEC CICS PERFORM STATISTICS command with the DB2 keyword to allow the user to request that CICS DB2 global and resource statistics to be written out to SMF.
    Alternatively you can use the CICS sample statistics program DFH0STAT to collect DB2 statistics. It uses EXEC CICS COLLECT STATISTICS commands with the DB2CONN and DB2ENTRY keywords to collect statistics. It also uses EXEC CICS INQUIRE commands for the DB2CONN and DB2ENTRYs to collect data. An example of the output from DFH0STAT is shown in Figure 1.
  2. When you have collected the statistics, the important fields to look at as regards performance and tuning are:
    1. The number of calls made using a thread from a DB2ENTRY or the pool, and the number of thread reuses (that is, the number of times an existing thread was reused). Thread reuse is reported for each DB2ENTRY, and (in the DB2 Connection statistics) for the pool. Thread reuse is good for performance, because it avoids the overhead of creating a thread for each CICS transaction or each unit of work. The use of protected threads can increase thread reuse.
    2. If THREADWAIT(YES) is specified, the peak number of tasks on the readyq waiting for a thread. It is better to limit transactions using a transaction class rather than allow them to queue for threads.
    3. In the DB2 Connection statistics, check the field “Peak number of tasks on Pool Readyq”, and also the field “Peak number of Tasks on TCB Readyq”. If the latter is nonzero, tasks were queued waiting for a DB2 connection to use with their open TCBs, rather than waiting for a thread. The tasks were queued because the TCBLIMIT, the maximum number of TCBs that can be used to control threads into DB2, had been reached. This shows that the number of threads available (the sum of the THREADLIMIT values for the pool, for command threads and for all DB2ENTRYs) exceeds the number of TCBs allowed. TCBLIMIT or the THREADLIMIT values should be adjusted in this case.

Example

Figure 1. Example output from DFH0STAT: the DB2 Connection report
Applid IYK2Z2G1  Sysid JOHN  Jobname CI13JTD5    Date 09/09/2001  Time 10:38:50                 CICS 6.2.0                PAGE     2
 
DB2 Connection
 
  DB2 Connection Name. . . . . . . . . . . :  RCTJT
  DB2 Group ID . . . . . . . . . . . . . . :                       Resync Group Member. . . . . . . . . . . : N/A
  DB2 Sysid. . . . . . . . . . . . . . . . :  DE2D
  DB2 Release. . . . . . . . . . . . . . . :   6.2.0

  DB2 Connection Status. . . . . . . . . . :  CONNECTED            DB2 Connect Date and Time . . . :  09/09/2001  10:37:19.21354
  DB2 Connection Error . . . . . . . . . . :  SQLCODE
  DB2 Standby Mode . . . . . . . . . . . . :  RECONNECT

  DB2 Pool Thread Plan Name. . . . . . . . :
  DB2 Pool Thread Dynamic Plan Exit Name . :  DSNCUEXT

  Pool Thread Authtype . . . . . . . . . . :  USERID               Command Thread Authtype. . . . . . . . . :  N/A
  Pool Thread Authid . . . . . . . . . . . :                       Command Thread Authid. . . . . . . . . . :  JTILLI1

  Signid for Pool/Entry/Command Threads. . :  SSSSSSSS

  Create Thread Error. . . . . . . . . . . :  ABEND                Message TD Queue 1. . . . . . . . . . . :  CDB2
  Protected Thread Purge Cycle . . . . . . :  00.30                Message TD Queue 2. . . . . . . . . . . :
  Deadlock Resolution. . . . . . . . . . . :  ROLLBACK             Message TD Queue 3. . . . . . . . . . . :
  Non-Terminal Intermediate Syncpoint. . . :  NORELEASE
  Pool Thread Wait Setting . . . . . . . . :  WAIT                 Statistics TD Queue . . . . . . . . . . :  CDB2

  Pool Thread Priority . . . . . . . . . . :  HIGH                 DB2 Accounting records by . . . . . . . :  NONE

  Current TCB Limit. . . . . . . . . . . . :         100
  Current number of TCBs . . . . . . . . . :          10
  Peak number of TCBs. . . . . . . . . . . :          10

  Current number of free TCBs. . . . . . . :           5

  Current number of tasks on TCB Readyq. . :           0
  Peak number of tasks on TCB Readyq . . . :           0

  Pool Thread Limit. . . . . . . . . . . . :           3           Number of Calls using Pool Threads. . . . . :         0
  Current number of Pool Threads . . . . . :           0           Number of Pool Thread Signons . . . . . . . :         0
  Peak number of Pool Threads. . . . . . . :           2           Number of Pool Thread Partial Signons . . . :         0
  Number of Pool Thread Waits. . . . . . . :           0           Number of Pool Thread Commits . . . . . . . :         0
                                                                   Number of Pool Thread Aborts. . . . . . . . :         0
  Current number of Pool Tasks . . . . . . :           0           Number of Pool Thread Single Phase. . . . . :         0
  Peak number of Pool Tasks. . . . . . . . :           0           Number of Pool Thread Reuses. . . . . . . . :         0
  Current Total number of Pool Tasks . . . :           0           Number of Pool Thread Terminates. . . . . . :         0

  Current number of Tasks on Pool Readyq . :           0
  Peak number of Tasks on Pool Readyq. . . :           0

  Current number of DSNC Command threads . :           0           Number of DSNC Command Calls. . . . . . . . :         0
  Peak number of DSNC Command threads. . . :           0           Number of DSNC Command Signons. . . . . . . :         0
  DSNC Command Thread Limit. . . . . . . . :           2           Number of DSNC Command Thread Terminates. . :         0
                                                                   Number of DSNC Command Thread Overflows . . :         0
Figure 2. Example output from DFH0STAT: the DB2 Entries report
Applid IYK2Z2G1  Sysid JOHN  Jobname CI13JTD5    Date 09/09/2001  Time 10:38:50                 CICS 6.2.0                PAGE     3
 
DB2 Entries
 
  DB2Entry Name. . . . . . . . . . . . . . :  XP05                 DB2Entry Status . . . . . . . . . . . . :  ENABLED
  DB2Entry Static Plan Name. . . . . . . . :  TESTP05              DB2Entry Disabled Action. . . . . . . . :  POOL
  DB2Entry Dynamic Plan Exit Name. . . . . :                       DB2Entry Deadlock Resolution. . . . . . :  ROLLBACK

  DB2Entry Authtype. . . . . . . . . . . . :  N/A                  DB2Entry Accounting records by. . . . . :  NONE
  DB2Entry Authid. . . . . . . . . . . . . :  JTILLI1
                                                                   Number of Calls using DB2Entry. . . . . . . :      16,500
  DB2Entry Thread Wait Setting . . . . . . :  WAIT                 Number of DB2Entry Signons. . . . . . . . . :          10
                                                                   Number of DB2Entry Partial Signons. . . . . :           0
  DB2Entry Thread Priority . . . . . . . . :  HIGH                 Number of DB2Entry Commits. . . . . . . . . :           0
  DB2Entry Thread Limit. . . . . . . . . . :          10           Number of DB2Entry Aborts . . . . . . . . . :           0
  Current number of DB2Entry Threads . . . :           0           Number of DB2Entry Single Phase . . . . . . :       5,500
  Peak number of DB2Entry Threads. . . . . :          10           Number of DB2Entry Thread Reuses. . . . . . :       5,031
                                                                   Number of DB2Entry Thread Terminates. . . . :         464
  DB2Entry Protected Thread Limit. . . . . . . . . :         5     Number of DB2Entry Thread Waits/Overflows . :         306
  Current number of DB2Entry Protected Threads . . :         5
  Peak number of DB2Entry Protected Threads. . . . :         5

  Current number of DB2Entry Tasks . . . . . . . . :         0
  Peak number of DB2Entry Tasks. . . . . . . . . . :        28
  Current Total number of DB2Entry Tasks . . . . . :     5,500

  Current number of Tasks on DB2Entry Readyq . . . :         0
  Peak number of Tasks on DB2Entry Readyq. . . . . :        18


dfhtk5h.html | Timestamp icon Last updated: Sunday, 4 June 2017