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.
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.
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.
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.
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.