|
|
|
Displaying the DB2 Statements panel from the DataView panel shows the monitored activity of DB2 and which SQL statements were consuming CPU during the monitoring period. The display shows measurements collected from only sampled SQL. To view DB2 Intercept data on the DB2 Statements panel, you can use the S (Select SQL) line command.
Use the following steps to see the SQL statements for a DB2 job:
CA MAT --------------------------- DataView ----------------- Row 1 to 8 of 8 COMMAND ===> SCROLL ===> HALF Primary commands: LINklist on/off Profile: PROFT9 Options: NORMAL Line commands: S - Select Linklist: OFF LC DD name Dataset name Delay% Visual ________ ____________________________________________ ______ __________ >------------------------------------------------------------------------------ __ .DB2 DB2 Statement Delay 97.68 =========> __ STEPLIB DB2.DIA.SDSNEXIT 0.00 __ STEPLIB DSN910.SDSNLOAD 0.00 __ SYSABEND USRMMM1.USRDB2T9.JOB07313.D0000104.? 0.00 __ SYSPRINT USRMMM1.USRDB2T9.JOB07313.D0000103.? 0.00 __ SYSTSIN USRMMM1.USRDB2T9.JOB07313.D0000101.? 0.00 __ SYSTSPRT USRMMM1.USRDB2T9.JOB07313.D0000102.? 0.00 __ SYS00001 USRMMM.TSO.LOAD 0.00 ********************************* End of Table ********************************
The DB2 Statements panel displays:
DB2 Statements panel selected from DataView panel (part 1 of 3) CA MAT ---------------------- DB2 Statements ------------- Row 1 to 11 of 11 COMMAND ===> SCROLL ===> CSR Primary Commands: ADDHelp, SQL all/sampled, SEQ (sort) Profile: DB2STPRO Options: NORMAL Line commands: S - Select SQL E - Explain SQL SQL: Sampled SD - Show Declare I - Explain Information Sort: Activity C - Code Details D - Statement Detail DB2 SSID: DB9G DB2 Rel: 9.1.0 DBRM or D Data Declare Total Call LC Package S From Stmt Num Type Stmt Samps Actv% Wait% Totl% Count ________ _ ____ ________ ________ _______ ______ _____ _____ _____ _________ >------------------------------------------------------------------------------ __ D91@PROG S B 632 FETCH 100900 3057 27.98 1.57 29.54 1000001 __ D91@PROG S B 460 OPEN 100826 591 4.49 1.22 5.71 1 __ D91@PROG S B 469 FETCH 100826 243 2.27 0.08 2.35 76924 __ D91@PROG S B 580 OPEN 100881 220 1.57 0.56 2.13 1 __ D91@PROG S B 201 SELECT NA 101 0.83 0.14 0.98 1 __ D91@PROG S B 232 SELECT NA 100 0.81 0.15 0.97 1 __ D91@PROG S B 263 SELECT NA 89 0.71 0.15 0.86 1 __ D91@PROG S B 538 OPEN 100862 87 0.75 0.09 0.84 1 __ D91@PROG S B 589 FETCH 100881 22 0.00 0.21 0.21 1 __ D91@PROG S B 388 SELECT NA 3 0.01 0.02 0.03 1 __ D91@PROG S B 623 OPEN 100900 1 0.00 0.01 0.01 1
The DB2 Statements panel is sorted by the Totl% field. Statements with the highest overall delay appear at the top.
DB2 Statements panel selected from DataView panel (part 2 of 3) CA MAT ---------------------- DB2 Statements ------------- Row 1 to 11 of 11 COMMAND ===> SCROLL ===> CSR Primary Commands: ADDHelp, SQL all/sampled, SEQ (sort) Profile: DB2STPRO Options: NORMAL Line commands: S - Select SQL E - Explain SQL SQL: Sampled SD - Show Declare I - Explain Information Sort: Activity C - Code Details D - Statement Detail DB2 SSID: DB9G DB2 Rel: 9.1.0 DBRM or D Data Total LC Package S From Stmt Num Type Total CPU CPU-P-Call Resp Time ________ _ ____ ________ ________ __________ __________ __________ <>----------------------------------------------------------------------------- __ D91@PROG S B 632 FETCH 30.286899 0.000030 37.179097 __ D91@PROG S B 460 OPEN 9.063884 9.063884 13.145094 __ D91@PROG S B 469 FETCH 2.575384 0.000033 2.934821 __ D91@PROG S B 580 OPEN 2.968967 2.968967 4.958089 __ D91@PROG S B 201 SELECT 1.582823 1.582823 2.285674 __ D91@PROG S B 232 SELECT 1.363766 1.363766 2.285488 __ D91@PROG S B 263 SELECT 1.302925 1.302925 2.143666 __ D91@PROG S B 538 OPEN 1.403580 1.403580 2.040191 __ D91@PROG S B 589 FETCH 0.000356 0.000356 0.459179 __ D91@PROG S B 388 SELECT 0.006287 0.006287 0.076504 __ D91@PROG S B 623 OPEN 0.002196 0.002196 0.015715
DB2 Statements panel selected from DataView panel (part 3 of 3) CA MAT ---------------------- DB2 Statements ------------- Row 1 to 11 of 11 COMMAND ===> SCROLL ===> CSR Primary Commands: ADDHelp, SQL all/sampled, SEQ (sort) Profile: DB2STPRO Options: NORMAL Line commands: S - Select SQL E - Explain SQL SQL: Sampled SD - Show Declare I - Explain Information Sort: Activity C - Code Details D - Statement Detail DB2 SSID: DB9G DB2 Rel: 9.1.0 DBRM or D Data Average LC Package S From Stmt Num Type Resp Time ________ _ ____ ________ ________ __________ <------------------------------------------------------------------------------ __ D91@PROG S B 632 FETCH 0.000037 __ D91@PROG S B 460 OPEN 13.145094 __ D91@PROG S B 469 FETCH 0.000038 __ D91@PROG S B 580 OPEN 4.958089 __ D91@PROG S B 201 SELECT 2.285674 __ D91@PROG S B 232 SELECT 2.285488 __ D91@PROG S B 263 SELECT 2.143666 __ D91@PROG S B 538 OPEN 2.040191 __ D91@PROG S B 589 FETCH 0.459179 __ D91@PROG S B 388 SELECT 0.076504 __ D91@PROG S B 623 OPEN 0.015715
After you identify the statement that caused an excessive delay, use the Explain function to perform further analysis of how DB2 might process the SQL statement. For information, see Using the Explain Function.
| Copyright © 2011 CA. All rights reserved. | Tell Technical Publications how we can improve this information |