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.
About this task
For more information about activating these facilities and directing the output to SMF and GTF, see Issuing commands to DB2 using the DSNC transaction, 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 for z/OS: Application Programming Guide and Reference for Java 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 for z/OS: Application Programming Guide and Reference for Java 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 1 shows a small part of the report provided by DB2PM. Refer to the DB2 for z/OS: Application Programming Guide and Reference for Java 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
- SQL DML. This information can be used to monitor the SQL requests issued.
- SQL DCL. This information can be used to check whether the application is using LOCK statements.
- Subsystem Services. This section provides information on thread usage and signon activity. For performance reasons, thread reuse is recommended in CICS environments, to avoid the overhead of creating a thread for each CICS transaction.
- Locking can be used to monitor the number of timeouts and deadlocks. For more information about deadlocks, see Handling deadlocks in the CICS DB2 environment.
- Buffer Pool provides information on:
- The number of data sets opened (Data sets Opened)
- The number of pages retrieved (GETPAGE Requests)
- Number of I/Os (Read Operations and Write I/O Operations)
This statistical data is not checkpointed and is not retained by DB2 across restarts.