|
|
|
The DB2 Plan Performance Report provides an extract of the most pertinent detail information from the DB2 Plan Activity File (DB2DSP), which is created from the SMF type 101 record. It details the activity of active DB2 plans. The Plan Performance 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(DB2PLNPT), which produces the Plan Performance Report. This module also invokes the Plan Performance Summary report (DB2PLNSM) if summary reporting has been requested. The program will produce a separate report for each combination of SYSID, DB2 system, plan, user, jobname, and facility. The system default is to set the User, Jobname and Facility variables DB2AUTH, DB2CORR and DB2CONN to *ALL*. This results in a detail report by plan within a DB2 subsystem for all plans being monitored. To activate this report for use in DYDB2MBO or MNDB2MBO, you must set the macro variable PLANREP=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 PLANREP is set equal to Y in member #DB2MOBJ of your unit library prefix.MICS.USER.SOURCE. If you require the DDF reporting block for this report you must also set the macro variable DDPREP=Y. Set all other macro variables to N to produce ONLY the DB2 Plan Performance Report. Finally, you must execute the program DYDB2MBO or MNDB2MBO, as is done in the DAILYRPT and MONTHRPT jobs.
CA PAGE: 5 CA MICS/DB2 DB2 PLAN PERFORMANCE REPORT SUBPAGE: 1 RUN DATE: Monday, July 18, YYYY ================================================================================================================================= ! ! ! ! SYSID: A1A1 ! DB2 SYSTEM: RA1A ! PLAN: CSMHR01 ! CORREL.: * ALL * ! THREADS: 11 ! RELEASE: 9.1 ! ! FACILITY: * ALL * ! ================================================================================================================================= CPU TIME CLASS 1 CLASS 2 TOTAL AVERAGE MAXIMUM TOTAL AVERAGE MAXIMUM ---------- ---------- ---------- ---------- ---------- ---------- ELAPSED TIME: 0:00:04.592 0:00:00.424 0:00:00.492 0:00:00.802 0:00:00.070 0:00:00.102 STANDARD CP CPU TIME: 0:00:00.816 0:00:00.074 0:00:00.084 0:00:00.734 0:00:00.067 0:00:00.076 zIIP CPU TIME: 0:00:00.145 0:00:00.013 0:00:00.024 0:00:00.112 0:00:00.010 0:00:00.014 zIIP ELIGIBLE TIME: 0:00:00.000 0:00:00.000 0:00:00.000 N/A N/A N/A --------------------------------------------------------------------------------------------------------------------------------- ACCOUNTING CLASS 3 EVENTS TOTAL AVERAGE MAXIMUM ----------- ----------- ----------- I/O TIME 0:00:00.03 0:00:00.00 0:00:00.01 LOCK AND LATCH TIME 0:00:00.00 0:00:00.00 0:00:00.00 OTHER READ TIME 0:00:00.00 0:00:00.00 0:00:00.00 OTHER WRITE TIME 0:00:00.00 0:00:00.00 0:00:00.00 SERVICE TASK TIME 0:00:00.00 0:00:00.00 0:00:00.00 ARCHIVE LOG TIME 0:00:00.00 0:00:00.00 0:00:00.00 ENTRY/EXIT EVENTS 4074 370.4 404 WAIT TRACE EVENTS 44 4.0 14 --------------------------------------------------------------------------------------------------------------------------------- THREAD STATUS NORMAL TERMINATION ABNORMAL TERMINATION WORK UNIT IN DOUBT END OF TASK 0 APPLICATION ABENDED 0 APPLICATION ABENDED 0 APPLICATION ENDED 0 END OF MEMORY 0 END OF MEMORY 0 DEALLOCATION 0 RESOLVE INDOUBT 0 RESOLVE INDOUBT 0 NEW USER 0 FORCE COMMAND 0 FORCE COMMAND 0 COMMITS 11 ABORT REQUESTS 0 --------------------------------------------------------------------------------------------------------------------------------- REPORTING PERIOD Friday, July 15, YYYY 9:23:44 TO Friday, July 15, YYYY 9:23:49 ---------------------------------------------------------------------------------------------------------------------------------
Figure 3-6. DB2 Plan Performance Report Sample (Part 1 of 4)
CA PAGE: 6 CA MICS/DB2 DB2 PLAN PERFORMANCE REPORT SUBPAGE: 2 RUN DATE: Monday, July 18, YYYY ================================================================================================================================= ! ! ! ! SYSID: A1A1 ! DB2 SYSTEM: RA1A ! PLAN: CSMHR01 ! CORREL.: * ALL * ! THREADS: 11 ! RELEASE: 9.1 ! ! FACILITY: * ALL * ! ================================================================================================================================= SQL CALLS MANIPULATIVE STATEMENTS CONTROL STATEMENTS DEFINITIONAL STATEMENTS SELECT 666 LOCK TABLE 0 CREATE DROP ALTER INSERT 0 GRANT 0 UPDATE 0 REVOKE 0 TABLE 0 0 0 DELETE 684 INCREMENTAL BIND 0 INDEX 0 0 0 DESCRIBE 0 COMMENT 0 TABLESPACE 0 0 0 PREPARE 0 SET SQLID 0 STOREGROUP 0 0 0 OPEN 665 SET HOST VARIABLE 0 DATA BASE 0 0 0 CLOSE 0 SYNONYM 0 0 FETCH 665 VIEW 0 0 LABEL 0 ALIAS 0 0 DESCRIBE TABLE 0 PACKAGE 0 --------------------------------------------------------------------------------------------------------------------------------- MULTIPLE INDEX ACTIVITY MIX INDEX NOT USED MIX INDEX NOT USED MIX INDEX USED STORAGE LIMIT EXCEEDED RID LIMIT EXCEEDED _______________ ______________________ ___________________ 0 0 0 --------------------------------------------------------------------------------------------------------------------------------- REPORTING PERIOD Friday, July 15, YYYY 9:23:44 TO Friday, July 15, YYYY 9:23:49 ---------------------------------------------------------------------------------------------------------------------------------
Figure 3-6. DB2 Plan Performance Report Sample (Part 2 of 4)
CA PAGE: 7 CA MICS/DB2 DB2 PLAN PERFORMANCE REPORT SUBPAGE: 3 RUN DATE: Monday, July 18, YYYY ================================================================================================================================= ! ! ! ! SYSID: A1A1 ! DB2 SYSTEM: RA1A ! PLAN: CSMHR01 ! CORREL.: * ALL * ! THREADS: 11 ! RELEASE: 9.1 ! ! FACILITY: * ALL * ! ================================================================================================================================= LOCKING TOTAL AVERAGE MAXIMUM DEADLOCKS 0 0.0 0 SUSPENSIONS 0 0.0 0 TIMEOUTS 0 0.0 0 MAX. CONCUR. PAGELOCK 22 2.0 2 LOCK/LATCH TIME 0 0.0 0 LATCH SUSPEND COUNT 0 0.0 0 OTHER SUSPEND COUNT 0 0.0 0 ESCALATE TO SHARED 0 0.0 0 ESCALATE TO EXCLUSIVE 0 0.0 0 LOCK REQUEST COUNT 1,369 0.0 145 QUERY REQUEST COUNT 0 0.0 0 CHANGE LOCK REQUESTS 16 1.5 2 IRLM LOCK REQUEST (OTHER) 0 0.0 0 RESOURCE TABLE ID HOW LIMIT WAS DETERMINED UNKNOWN SERVICE UNIT TIME LIMIT 0 CPU TIME LIMIT 0:00:00.000 HIGHEST CPU TIME USED 0:00:00.000 --------------------------------------------------------------------------------------------------------------------------------- REPORTING PERIOD Friday, July 15, 2006 9:23:44 TO Friday, July 15, 2006 9:23:49 ---------------------------------------------------------------------------------------------------------------------------------
Figure 3-6. DB2 Plan Performance Report Sample (Part 3 of 4)
CA PAGE: 8 CA MICS/DB2 DB2 PLAN PERFORMANCE REPORT SUBPAGE: 4 RUN DATE: Monday, July 18, YYYY ================================================================================================================================= ! ! ! ! SYSID: A1A1 ! DB2 SYSTEM: RA1A ! PLAN: CSMHR01 ! CORREL.: * ALL * ! THREADS: 11 ! RELEASE: 9.1 ! ! FACILITY: * ALL * ! ================================================================================================================================= BUFFER MANAGER SUMMARY TOTAL AVERAGE MAXIMUM GETPAGE REQUESTS 21,748 1,977.1 2,284 RECORD IN SYSTEM PAGE UPDATED 0 0.0 0 SYNCHRONOUS READ I/O 25 2.3 10 SYNCHRONOUS WRITE I/O 0 0.0 0 SEQUENTIAL PREFETCH REQUESTS 10 0.9 7 LIST PREFETCH REQUESTS 0 0.0 0 DYNAMIC PREFETCH REQUESTS 33 3.0 3 UNSUCCESSFUL GET PAGE OPERATIONS 0 0.0 0 ASYNC PAGES READ BY PREFETCH 128 11.6 128 --------------------------------------------------------------------------------------------------------------------------------- REPORTING PERIOD Friday, July 15, YYYY 9:23:44 TO Friday, July 15, YYYY 9:23:49 --------------------------------------------------------------------------------------------------------------------------------- DDF PERFORMANCE STATISTICS --------------------------------------------------------------------------------------------------------------- REMOTE REQUESTING! DDF ! ABORT ! COMMIT ! CONVERS. ! SQL ! ROWS !------ MESSAGES ------!-- BYTES/1000 ----- LOCATION(S) ! THRDS ! RECEIVED ! RECEIVED ! RECEIVED ! RECEIVED ! SENT ! SENT ! RECEIVED ! SENT ! RECEIVED --------------------------------------------------------------------------------------------------------------------------------- DEGNO50R_DSN8 ! 11! 0 0 1 2,015 666 2,026 2,036 384 323 ! AVG ! 0.0 0.0 0.1 183.2 60.5 184.2 185.1 34 29@ ! MAX ! 0 0 1 200 71 201 202 39 32 ! ! +-------------------------------------------------------------------------------------------------------------- @ - INDICATES THE REPORTED BYTE COUNT IS NOT DIVIDED BY 1000 --------------------------------------------------------------------------------------------------------------------------------- REPORTING PERIOD Friday, July 15, YYYY 13:42:32 TO Friday, July 15, YYYY 13:42:32 ---------------------------------------------------------------------------------------------------------------------------------
Figure 3-6. DB2 Plan Performance Report Sample (Part 4 of 4)
| Copyright © 2012 CA. All rights reserved. | Tell Technical Publications how we can improve this information |