|
|
|
The DB2 Data Base Services Report provides an extract of the most pertinent information from the DB2 Data Base Activity File (DB2DSD) that is created from the SMF Type 100 Subtype 1 record. It details the data base activity of each active DB2 subsystem. The DB2 Data Base Services Report is normally produced as a part of the DAILYRPT and MONTHRPT jobs, which are automatically submitted during the DAILY and MONTHLY runs, respectively. DAILYRPT executes the SAS program found in sharedprefix.MICS.SOURCE(DYDB2MBO) while MONTHRPT executes sharedprefix.MICS.SOURCE(MNDB2MBO). Both of these programs prepare data and then execute the actual report generation program located in sharedprefix.MICS.SOURCE(DB2DBMPT), which produces the report. To activate this report for use in DYDB2MBO or MNDB2MBO you must set the macro variable DBMREP=Y in the #DB2MOBJ member of your unit library prefix.MICS.USER.SOURCE. The system default report macro variable values are in sharedprefix.MICS.SOURCE member #DB2MOBJ. To create this report directly, ensure the macro variable DBMREP is set equal to Y in member #DB2MOBJ of your unit library prefix.MICS.USER.SOURCE. Set all other macro variables to N to produce ONLY the Data Base Services report. Finally, you must execute the program DYDB2MBO or MNDB2MBO as is done in the DAILYRPT and MONTHRPT jobs.
CA PAGE: 1 CA MICS/DB2 DB2 DATA BASE SERVICES REPORT SUBPAGE: 1 RUN DATE: Monday, September 5, YYYY ================================================================================================================================= ! ! ! ! SYSID: A1A1 ! DB2 SYSTEM: RA1A ! THREADS: 20,451 ! ! DB2 RELEASE: 9.1 ! ================================================================================================================================= SQL CALLS MANIPULATIVE STATEMENTS CONTROL STATEMENTS DEFINITIONAL STATEMENTS SELECT 2,203 LOCK TABLE 0 CREATE DROP ALTER INSERT 3,948 GRANT 2 UPDATE 2,251 REVOKE 0 TABLE 0 0 0 DELETE 3,357 INCREMENTAL BIND 0 INDEX 0 0 0 DESCRIBE 14,500 COMMENT 0 TABLESPACE 0 0 0 PREPARE 31,404 SET SQLID 22,654 STOGROUP 0 0 0 OPEN 47,919 SET HOST VARIABLE 5 DATABASE 0 0 0 CLOSE 19,929 SYNONYM 0 0 FETCH 68,061 VIEW 0 0 LABEL 0 PACKAGE 0 DESCRIBE TABLE 0 --------------------------------------------------------------------------------------------------------------------------------- RESOURCE LIMIT FACILITY ! LOCKING ! RESOURCE TABLE ID ! DEADLOCKS 0 LATCH SUSPEND COUNT 116 HOW LIMIT WAS DETERMINED UNKNOWN ! SUSPENSIONS 21 OTHER SUSPEND COUNT 12,291 SERVICE UNIT TIME LIMIT 0 ! TIMEOUTS 0 LOCK REQUEST COUNT 808,542 CPU TIME LIMIT 0:00:00.000 ! ESCALATED TO SHARED 0 QUERY REQUEST COUNT 0 HIGHEST CPU TIME USED 0:00:00.000 ! ESCALATED TO EXCLUSIVE 1 CHANGE REQUEST COUNT 181,345 ! MAX. PAGE LOCKS HELD 0 OTHER IRLM REQUEST COUNT 0 --------------------------------------------------------------------------------------------------------------------------------- REPORTING PERIOD Friday, September 2,YYYY 6:02:23 TO Friday, September 2,YYYY 17:53:23 ---------------------------------------------------------------------------------------------------------------------------------
Figure 3-2. DB2 Data Base Services Report Sample (Part 1 of 4)
CA PAGE: 2 CA MICS/DB2 DB2 DATA BASE SERVICES REPORT SUBPAGE: 2 RUN DATE: Monday, September 5, YYYY ================================================================================================================================= ! ! ! ! SYSID: A1A1 ! DB2 SYSTEM: RA1A ! THREADS: 20,451 ! ! DB2 RELEASE: 9.1 ! ================================================================================================================================= PLAN ACTIVITY ALLOCATE BIND REBIND FREE ATTEMPTS 20,587 ATTEMPTS 0 COMMANDS 0 COMMANDS 0 SUCCESS 20,587 SUCCESS 0 ATTEMPTS 0 ATTEMPTS 0 NON-EXISTENT 0 ADD 0 SUCCESS 0 SUCCESS 0 PLANS REPLACE 0 TEST 0 BOUND AND KEPT 0 --------------------------------------------------------------------------------------------------------------------------------- PACKAGE ACTIVITY ALLOCATE BIND REBIND FREE ATTEMPTS 27,796 ATTEMPTS 7 COMMANDS 0 COMMANDS 0 SUCCESS 22,303 SUCCESS 7 ATTEMPTS 0 ATTEMPTS 0 ADD 0 SUCCESS 0 SUCCESS 0 REPLACE 0 BOUND AND KEPT 0 --------------------------------------------------------------------------------------------------------------------------------- REPORTING PERIOD Friday, September 2, YYYY 6:02:23 TO Friday, September 2, YYYY 17:53:23 ---------------------------------------------------------------------------------------------------------------------------------
Figure 3-2. DB2 Data Base Services Report Sample (Part 2 of 4)
CA PAGE: 3 CA MICS/DB2 DB2 DATA BASE SERVICES REPORT SUBPAGE: 3 RUN DATE: Monday, September 5, YYYY ================================================================================================================================= ! ! ! ! SYSID: A1A1 ! DB2 SYSTEM: RA1A ! THREADS: 20,451 ! ! DB2 RELEASE: 9.1 ! ================================================================================================================================= BUFFER MANAGER SUMMARY PHYSICAL DATA BASE PAGE REQUESTS 2,186,996 MEDIA MANAGER READ REQUESTS 8,793 USABLE BUFFER NOT LOCATED BECAUSE VPOOL FULL 0 VIRTUAL STORAGE SHORTAGE EXPANSION FAILURES 0 UPDATES OF DATA BASE SYSTEM PAGES 185,094 SYSTEM PAGES WRITTEN TO DASD 28,546 WRITE I/OS BY MEDIA MANAGER 3,863 PAGES DEQUEUED FOR DESTRUCTIVE READ 2,499 PAGES DESTRUCTIVELY READ 2,499 ACTIVE BUFFERS IN POOL 4,390 READ PAGEFIXED WITHOUT REAL STORAGE 2 WRITE PAGEFIXED WITHOUT REAL STORAGE 0 BUFFER POOL DATA SET OPENED 1,478 MERGE PASSES FOR SORT 477 WORKFILES FOR MERGE PASSES 954 IMMEDIATE WRITES SYSTEM PAGES 3,372 SEQUENTIAL PREFETCH REQUESTS 12,450 LIST PREFETCH REQUESTS 3,836 PAGES READ DUE TO PREFETCH 158,238 MAX. WORKFILES CONCURRENTLY USED FOR MERGE 152 WORKFILES REJECTED DUE TO NO BUFFERS 0 INEFFICIENT MERGE DUE TO NO BUFFERS 0 PREFETCH DISABLED DUE TO NO BUFFERS 0 PREFETCH DISABLED DUE TO NO READ ENGINE 0 WRITE ENGINE UNAVAILABLE 0 DEFERRED WRITE THRESHOLD REACHED 0 DATA MANAGER THRESHOLD REACHED 0 MIGRATED DATASET ENCOUNTERED 0 VERTICAL DEFERRED WRITE THRESHOLD REACHED 1,818 DATASET RECALL TIMEOUTS 0 PREFETCH ASYNC READ I/OS 4,505 GETPAGE FAILURES DUE TO VPOOL FULL 0 GETPAGE FAILURES DUE TO VPOOL FULL 0 DYNAMIC PREFETCH REQUESTED 12,985 SUCCESSFUL VPOOL EXPANSION/CONTRACTION 0 BUFFERS ALLOCATED FOR VPOOL 3,358,300 PAGES READ DYNAMIC PREFETCH 2,221 PAGES READ LIST PREFETCH 17 DYNAMIC PREFETCH READ I/OS 149 LIST PREFETCH READ I/OS 10 GETPAGES ISSUED BY SEQUENTIAL REQUEST 678,135 SYNC READ I/OS PERFORMED BY SEQUENTIAL REQ 1,633 UNSUCCESSFUL GETPAGE CONDITIONAL REQUEST 0 HIGHEST PREFETCH I/O STREAMS ALLOCATED 0 PREFETCH I/O STREAMS DENIED SHORT ON STORAGE 0 TOTAL PARALLEL QUERIES 0 I/O PARALLELISM DOWNGRADED SHORT ON STORAGE 0 PREFETCH QUANTITY REDUCED TO 50% NORMAL 0 PREFETCH QUANTITY REDUCED TO 25% NORMAL 0 PAGES WRITTEN ASYNC VPOOL TO HPOOL VIA ADMF 0 UNSUCCESSFUL VPOOL TO HPOOL WRITES VIA ADMF 0 HPOOL BUFFERS BACKED BY EXPANDED STORAGE 0 --------------------------------------------------------------------------------------------------------------------------------- REPORTING PERIOD Friday, September 2, YYYY 6:02:23 TO Friday, September 2, YYYY 17:53:23 ---------------------------------------------------------------------------------------------------------------------------------
Figure 3-2. DB2 Data Base Services Report Sample (Part 3 of 4)
CA PAGE: 4 CA MICS/DB2 DB2 DATA BASE SERVICES REPORT SUBPAGE: 4 RUN DATE: Monday, September 5, YYYY ================================================================================================================================= ! ! ! ! SYSID: A1A1 ! DB2 SYSTEM: RA1A ! THREADS: 20,451 ! ! DB2 RELEASE: 9.1 ! ================================================================================================================================= ENVIRONMENTAL DESCRIPTOR MANAGEMENT POOL AVERAGE MAXIMUM NUMBER OF FAILURES DUE TO FULL EDM POOL 0 0 NUMBER OF PAGES IN EDM POOL 8,192 8,192 NUMBER OF REQUESTS FOR CURSOR TABLE SECTIONS 530 574 NUMBER OF LOADED CURSOR TABLE SECTIONS 1 5 NUMBER OF PAGES USED FOR CURSOR TABLE 1 2 NUMBER OF REQUESTS FOR PACKAGE TABLE SECTIONS 380 2,241 NUMBER OF LOADED PACKAGE TABLE SECTIONS 1 20 NUMBER OF PAGES USED FOR PACKAGE TABLE 1 5 MINIMUM NUMBER OF PAGES IN FREE CHAIN 101 7,259 NUMBER OF PAGES USED FOR DATA BASE DESCRIPTORS 0 0 NUMBER OF PAGES USED FOR SKELETON CURSOR TABLE 118 130 NUMBER OF PAGES USED FOR SKELETON PACKAGE TABLE 889 951 NUMBER OF REQUESTS FOR DATA BASE DESCRIPTORS 1,136 2,689 NUMBER OF LOADS OF DATA BASE DESCRIPTORS 2 46 --------------------------------------------------------------------------------------------------------------------------------- DATASET ACTIVITY AVERAGE MAXIMUM NUMBER OF DATASETS OPEN 1,110 1,274 NUMBER OF DATASETS AVAILABLE TO DRAIN 924 1,201 NUMBER OF DATASETS CLOSED VIA DRAIN 0 0 NUMBER OF SUCCESSFUL REOPENS 2,447 23,984 --------------------------------------------------------------------------------------------------------------------------------- REPORTING PERIOD Friday, September 2, YYYY 6:02:23 TO Friday, September 2, YYYY 17:53:23 ---------------------------------------------------------------------------------------------------------------------------------
Figure 3-2. DB2 Data Base Services Report Sample (Part 4 of 4)
| Copyright © 2012 CA. All rights reserved. | Tell Technical Publications how we can improve this information |