Monitoring DB2 when used with CICS

Using SMF and/or GTF records produced by DB2®, the user can monitor DB2 when used with CICS® . The DB2 performance monitor (DB2PM) program product is useful to provide reports based on:

The reports in this topic are shown as examples. Refer to the documentation of the DB2PM release you are using for the format and meaning of the fields involved in the reports.

Monitoring DB2 using the DB2 statistics facility

DB2 produces statistical data on a subsystem basis at the end of each time interval, as specified at installation time. This data is collected and written to the SMF and GTF data set only if the facility is active. For more information about activating these facilities and directing the output to SMF and GTF, see Issuing commands to DB2 using DSNC, and Starting GTF for DB2 accounting, statistics and tuning.

Data related to the system services address space is written as SMF instrumentation facility component identifier (IFCID) 0001 records. Data related to the database services address space is written as SMF IFCID 0002 records. Refer to the DB2 Universal Database™ for OS/390® and z/OS® Administration Guide for a description of these records.

These statistics are useful for tuning the DB2 subsystem, since they reflect the activity for all subsystems connected to DB2.

It is difficult to interpret this data when more than one subsystem is connected to DB2 (that is, both CICS and TSO). However, the counts obtained while running the CICS DB2 attachment facility in a controlled environment (that is, with CICS as the only subsystem connected, or with limited TSO activity) can be very useful.

The DB2 Universal Database for OS/390 and z/OS Administration Guide shows and analyzes, from a DB2 viewpoint, the statistical data reported for the database and system services address spaces. Included here is a reduced version of the statistics report. You can use this report to monitor the average CICS transaction. Figure 37 shows a small part of the report provided by DB2PM. Refer to the DB2 Universal Database for OS/390 and z/OS Administration Guide for additional information on these reports.

Figure 37. Sample statistics report from DB2PM
   LOCATION: DSN710P2               DB2 PERFORMANCE MONITOR (V7)
      GROUP: DSN710P2               STATISTICS REPORT - LONG    
     MEMBER: DF2D                                               
  SUBSYSTEM: DF2D                                               
DB2 VERSION: V7                                  SCOPE: MEMBER  

SQL DML                      QUANTITY  /SECOND  /THREAD  /COMMIT
---------------------------  --------  -------  -------  -------
SELECT                           1.00     0.00     0.05     0.02
INSERT                           9.00     0.00     0.41     0.21
UPDATE                           0.00     0.00     0.00     0.00
DELETE                           0.00     0.00     0.00     0.00
                                                                
PREPARE                         17.00     0.00     0.77     0.40
DESCRIBE                        34.00     0.00     1.55     0.79
DESCRIBE TABLE                   0.00     0.00     0.00     0.00
OPEN                            31.00     0.00     1.41     0.72
CLOSE                           26.00     0.00     1.18     0.60
FETCH                          827.00     0.00    37.59    19.23
                                                                
TOTAL                          945.00     0.00    42.95    21.98
                                                                

SQL DCL                      QUANTITY  /SECOND  /THREAD  /COMMIT
---------------------------  --------  -------  -------  -------
LOCK TABLE                       0.00     0.00     0.00     0.00
GRANT                            0.00     0.00     0.00     0.00
REVOKE                           0.00     0.00     0.00     0.00
SET HOST VARIABLE                0.00     0.00     0.00     0.00
SET CURRENT SQLID                0.00     0.00     0.00     0.00
SET CURRENT DEGREE               0.00     0.00     0.00     0.00
SET CURRENT RULES                0.00     0.00     0.00     0.00
SET CURRENT PATH                 0.00     0.00     0.00     0.00
SET CURRENT PRECISION            0.00     0.00     0.00     0.00

CONNECT TYPE 1                   0.00     0.00     0.00     0.00
CONNECT TYPE 2                  29.00     0.00     1.32     0.67
RELEASE                          0.00     0.00     0.00     0.00
SET CONNECTION                   0.00     0.00     0.00     0.00

ASSOCIATE LOCATORS               0.00     0.00     0.00     0.00
ALLOCATE CURSOR                  0.00     0.00     0.00     0.00

HOLD LOCATOR                     0.00     0.00     0.00     0.00
FREE LOCATOR                     0.00     0.00     0.00     0.00

TOTAL                           29.00     0.00     1.32     0.67
SUBSYSTEM SERVICES           QUANTITY  /SECOND  /THREAD  /COMMIT
---------------------------  --------  -------  -------  -------
IDENTIFY                        23.00     0.00     1.05     0.53
CREATE THREAD                   22.00     0.00     1.00     0.51
SIGNON                          39.00     0.00     1.77     0.91
TERMINATE                       57.00     0.00     2.59     1.33
ROLLBACK                         8.00     0.00     0.36     0.19
  
COMMIT PHASE 1                   0.00     0.00     0.00     0.00
COMMIT PHASE 2                   0.00     0.00     0.00     0.00
READ ONLY COMMIT                 0.00     0.00     0.00     0.00

UNITS OF RECOVERY INDOUBT        0.00     0.00     0.00     0.00
UNITS OF REC.INDBT RESOLVED      0.00     0.00     0.00     0.00

SYNCHS(SINGLE PHASE COMMIT)     35.00     0.00     1.59     0.81
QUEUED AT CREATE THREAD          0.00     0.00     0.00     0.00
SUBSYSTEM ALLIED MEMORY EOT      0.00     0.00     0.00     0.00
SUBSYSTEM ALLIED MEMORY EOM      0.00     0.00     0.00     0.00
SYSTEM EVENT CHECKPOINT          3.00     0.00     0.14     0.07


CPU TIMES                               TCB TIME         SRB TIME       TOTAL TIME          /THREAD          /COMMIT
-------------------------------  ---------------  ---------------  ---------------  ---------------  ---------------
SYSTEM SERVICES ADDRESS SPACE       17:40.602755      1:09.182200     18:49.784954        51.353862        26.274069
DATABASE SERVICES ADDRESS SPACE         6.100449        11.626277        17.726726         0.805760         0.412249
IRLM                                    0.051894      2:43.867972      2:43.919867         7.450903         3.812090
DDF ADDRESS SPACE                       1.195607         0.212343         1.407950         0.063998         0.032743

TOTAL                               17:47.950705      4:04.888792     21:52.839497        59.674523        30.531151

Figure 37 includes information about:

Further useful information in the statistics reports, not shown in Figure 37, is:

This statistical data is not checkpointed and is not retained by DB2 across restarts.

Monitoring DB2 using the DB2 accounting facility

The DB2 accounting facility output for a single transaction can be used for monitoring and tuning purposes. For information on using the DB2 accounting facility, see DB2 accounting reports.

Monitoring DB2 using the DB2 performance facility

The DB2 performance facility trace provides detailed information on the flow of control inside DB2. While the main purpose of this trace is to supply debugging information, it can also be used as a monitoring tool because of the timing data provided with each entry.

Due to high resource consumption, the DB2 performance trace should be used only in specific cases, where it becomes difficult to use any other tool to monitor DB2-oriented transactions.

Even in this case, only the needed classes of performance trace should be started, for only a limited time and for only the transactions that need to be carefully monitored.

Related concepts
Accounting and monitoring in a CICS DB2 environment
Monitoring the CICS DB2 attachment facility
Monitoring CICS transactions that access DB2 resources
Monitoring the CICS system in a CICS DB2 environment
Accounting in a CICS DB2 environment: Overview
[[ Contents Previous Page | Next Page Index ]]