Identifying a costly SQL statement
To identify a costly SQL statement
Define a data source and time interval for your workload analysis:
Perform the procedure described in Preparing-for-workload-analysis through Step 7.
- At Step 8, select Db2 Subsystem ID to select the Subsystem Analysis report.
The SQL Workload Analysis Menu is displayed. (The panel is displayed in Preparing-for-workload-analysis.)
Select Workload Analysis, and press Enter.The Subsystem Analysis report sorts Db2 subsystems by elapsed time. The Db2 consuming the most resources is listed first.
Subsystem Analysis report
ASQEQRPW/I VIEW A REPORT LINE 1 OF 8
COMMAND ====> _______________________________________________ SCROLL ===> CSR_
BMCSftwr.SQMCACTS -- SUBSYSTEM ANALYSIS (DATA) -- 09/05 18:44:11
SOURCE : NC1I-ACTIVE INTVL : 09/05 00:00 - UNLIMITED
----------------------------------------------------------------------------------------------------------------------------
Zooms: G-APPGRP I-CLNTID 8-CONNTY E-ERROR F-HIELA O-OBJECT J-REQLOC
Y-ACCEL N-CLNTAP W-CLNTWS L-CORR X-EXCPTN K-IMPQUL P-PLAN S-STMT
M-ACCMTX 9-CLNTCT C-CONNID T-DETL H-HICPU V-INTVL R-PROG U-USER
Expands: A-AVHILO Z-ZIIP 1-MORE 2-ACCEL
DSGRP: PLAN: PROG:
SQL +----- TOTAL IN-SQL TIME -----+ +- SYNCIOS -+ ROLL- %ELAPSED
DB2 SSID MVS ID CALLS ELAPSED % CPU % GETPAGES TOTAL GP/SIO COMMITS BACKS I/O LOCK ERRORS XCPTNS
----------- -------- ------ --------------- --------------- -------- ------ ------ ------- ------ ---- ---- ------ ------
+ DJYA (11.1) ESAJ 87724K 11:02:15.01 74 5:32:59.209 38 438782K 8820K 49.7 35926 4845 1.0 2.0 2483 3950
+ DIYA (10.1) ESAJ 112M 2:47:28.659 19 8:16:29.573 57 194810K 11417K 17.1 92237 95913K 19.9 9.0 62839 1062
+ DLYA (12.1) ESAJ 45794K 47:27.89692 5 43:55.07508 5 63322220 202492 312.7 22338 2144 0.4 0.0 3933 557Type S over the + (plus sign) beside a Db2 subsystem, and press Enter.The SQL Statement Analysis report is displayed. This report is also sorted by elapsed time, so the most costly SQL statement is easily identified at the top of the list.
SQL Statement Analysis report
ASQEQRPW/I VIEW A REPORT LINE 1 OF 341
COMMAND ====> _______________________________________________ SCROLL ===> CSR_
BMCSftwr.SQMCACTX -- SQL STATEMENT ANALYSIS (DATA) -- 09/05 18:45:22
SOURCE : NC1I-ACTIVE INTVL : 09/05 00:00 - UNLIMITED MORE: +
-----------------------------------------------------------------------------------------------------------------------------------
Zooms: Q-CATSQL I-CLNTID 8-CONNTY B-DSGRP X-EXCPTN O-OBJECT J-REQLOC
M-ACCMTX N-CLNTAP W-CLNTWS L-CORR T-DETL K-IMPQUL P-PLAN S-SQLTXT
G-APPGRP 9-CLNTCT C-CONNID D-DB2 E-ERROR V-INTVL R-RECMND U-USER
Expands: A-AVHILO F-FULL H-HDR Z-TIMES 1-MORE 2-ACCEL
DB2: DIY8 DSGRP: PLAN:
DYN/STAT SECT STMT SQL +----- TOTAL IN-SQL TIME -----+ GET- +- SYNCIOS -+ +- %ELAPSED -+ AVG
PROGRAM STMT TYPE NO. NO. CALLS ELAPSED % CPU % PAGES TOTAL GP/SIO I/O LOCK NEST TASKS ERRS XCPTS ACCL
-------- ---------- ---- ------ ----- --------------- --------------- ------ ------ ------ ---- ---- ---- ----- ----- ----- ----
+ DSNREXX D UPDATE 233 3747 16 08:31.64046 76 00:00.01994 0 40 18 2.2 0.0 99.9 0.0 0 8 8 N
+ SELECT02 S SELECT 1 184 1600K 00:59.21553 9 00:33.09258 89 3199K 8 400K 0.1 0.2 0.0 0 0 0 N
+ ERRLOOP S SELECT 1 159 296 00:09.97115 1 00:00.10775 0 296 0 0.0 0.0 0.0 0 320 0 NType S over the + (plus sign) beside the statement, and press Enter.A report showing the text of the SQL statement is displayed. The report displayed differs depending on the type of SQL statement selected. The SQL Statement Text report—static statement is displayed for static statements.
SQL Statement Text report—static statement
ASQEQRPW/I VIEW A REPORT LINE 1 OF 14
COMMAND ====> _______________________________________________ SCROLL ===> CSR_
BMCSftwr.SQMCACST -- SQL STATEMENT TEXT -- 01/28 18:37:43
SOURCE : DAC1-ACTIVE INTVL : 01/28 16:19 - UNLIMITED MORE: +
------------------------------------------------------------------------------
ACTIONS FOR +: T-DETAIL E-ERRORS H-HEADER Q-CATSQL I-HISTEXPL O-OBJECT
FOR *: X-EXPLAIN SQL TEXT C-EXPLAIN COMPARE
SUBSYS: DEDK CORRID: PLAN: ACT101DM CLNTAP:
CONNID: USER: CLNTID:
APPGRP: CLNTWS:
CALL SECT STMT +--- SQL --+ +- TOTAL IN-SQL TIME -+
PROGRAM TYPE NO. NO. CALLS ERRS ELAPSED CPU GETPAGE
-------- -------- ----- ------ ------ ----- ----------- ----------- -------
+ ACSBQSY FETCH 10 1077 455 0 03:58.96296 00:00.03845 109
AVERAGES: 00:00.52519 00:00.00008
STATEMENT TYPE: STATIC +----------------------------------------------
SQL STATEMENT TEXT:
* DECLARE SYSYRCRSR CURSOR FOR
SELECT A.*
FROM SYSIBM.SYSSYNONYMS A
WHERE A.CREATOR = :H
AND A.NAME = :HIf you select a dynamic SQL statement from the SQL Statement Analysis report, the product displays the SQL Statement Text report—dynamic statement.
SQL Statement Text report—dynamic statement
ASQEQRPW/I VIEW A REPORT LINE 1 OF 15
COMMAND ====> _______________________________________________ SCROLL ===> CSR_
BMCSftwr.SQMCACTY -- SQL STATEMENT TEXT -- 01/28 18:39:54
SOURCE : DAC1-ACTIVE INTVL : 01/28 16:19 - UNLIMITED MORE: +
-------------------------------------------------------------------------------
ACTIONS FOR +: T-DETAIL E-ERRORS H-HEADER O-OBJECTS
FOR *: X-EXPLAIN SQL TEXT
SUBSYS: DEDK CORRID: PLAN: ACT101DM CLNTAP:
CONNID: USER: SECTNO: 1 CLNTID:
APPGRP: CLNTWS:
STMT +-------- SQL --------+ +- TOTAL IN-SQL TIME -+
PROGRAM NO. CALLS OPEN FETCH ERRS ELAPSED CPU GETPAGES
-------- ------- ------ ----- ----- ---- ----------- ----------- --------
+ ACSBQTB 445 2 0 0 0 01:57.30968 00:00.00253 3
CALL TYPE: EXECUTE AVERAGES: 00:58.65484 00:00.00126 2
STMT TYPE: DYNAMIC +-----------------------------------------------
DYNAMIC SQL STATEMENT TEXT:
* SELECT A.*
FROM "ACT101 "."CATTABLES" A
WHERE A.CREATOR LIKE '%'
AND A.NAME LIKE 'QCHT01_DP2S02'Type X over the * (asterisk) beside an SQL statement, and press Enter.The Explain Results panel is displayed. You can either Explain the text or modify the statement text before you issue a subsequent Explain.
Explain Results panel
FILE COMMANDS OPTIONS HELP
-------------------------------------------------------------------------------
PSSPW200 Explain Results for SQLTEXT
Command ===> Scroll ===> CSR
More: >
Actions: S H R RS RW RI XD XP XS W P T C D U IM SA
LBL STMTNO COST*RATE SQL-STATEMENT
W XD01 438 16.114349 SELECT NAME, CREATOR, UNIQUERULE, CLUSTERING, CLU
LEFT-HAND TYPE RIGHT-HAND STG1 MISMATCH JOIN KEY FI
TBCREATOR EQUAL VALUE Y N Y 0.00
TBNAME EQUAL VALUE Y N Y 0.00
COST*RATE QB PL MIX QTYPE METH ACC MTCH IX TBNAME IXNAME
XD01 16.114349 1 1 0 SELECT 0 I 2 N SYSINDEXES DSNDXX0
******************************** Bottom of Data *******************************Type W in the field beside an XD01 (Dynamic Explain), and press Enter.
An Explain ISPF Edit session is displayed. You can modify the statement text in this ISPF Edit session before issuing an Explain.
Explain ISPF Edit sessionFile Edit Edit_Settings Menu Utilities Compilers Test Help
ISREDDE2 EXPLAIN Columns 00001 00072
Command ===> Scroll ===> CSR
****** ***************************** Top of Data ******************************
000001 SELECT NAME
000002 ,CREATOR
000003 ,UNIQUERULE
000004 ,CLUSTERING
000005 ,CLUSTERED
000006 ,NLEAF
000007 ,NLEVELS
000008 ,BPOOL
000009 ,PGSIZE
000010 ,IOFACTOR
000011 ,PREFETCHFACTOR
000012 ,STATSTIME
000013 ,INDEXTYPE
000014 ,FIRSTKEYCARDF
000015 ,FULLKEYCARDF
000016 ,CLUSTERRATIOF
000017 ,PADDED
000018 ,IX_EXTENSION_TYPE
000019 FROM SYSIBM.SYSINDEXES
000020 WHERE TBCREATOR = ?
000021 AND TBNAME = ?
000022 ORDER BY CREATOR, NAME
000023 FOR FETCH ONLY
****** **************************** Bottom of Data ****************************