Extended explain in DB2 z/OS
In 2010 I did extensive research on extended explain tables. This post shows the results of my research.
At regular intervals ABIS publishes sets of articles relating to DB2. This series of publications is titled Exploring DB2. Unfortunately for most readers, these publications are written entirely in the Dutch language.
In the Januari 2010 issue Peter Vanroose challenged us – the readers – to send in a query for presenting information from the extended explain tables. Of course, I could not resist such a challenge. Here are the results of my research on extended explain tables. This document contains the following parts:
1. The Query
For a bit of variety, I wanted to create a query using a Common Table Expression and at least one partitioned table. Therefore a catalog query seemed less appropriate. So I created a query on the IVP database in stead. After all, the IVP database contains a partitioned table and most installations do have the IVP database installed. Hopefully therefore everybody will be able to use the queries below. It is entirely evident that this query is not very efficient. This time we’re looking for explain data, not performance. Query optimization is outside this article’s scope. The same holds true for creating the extended explain tables. These are described in the Performance Monitoring and Tuning Guide and will be created for you by OSC. Alternatively, you can use member DSNTIJOS in the SDSNSAMP library.--
with managers
(mgrno,
mgrname
)
as (select distinct
mgr.empno
, strip(coalesce(firstnme, '')) CONCAT
' ' CONCAT
strip(coalesce(lastname, '???')) as mgrname
from DSN8810.DEPT dept
join DSN8810.EMP mgr
on mgr.empno = dept.mgrno
)
select mgr.mgrname
, dept.deptname
, emp.lastname
, emp.firstnme
from DSN8810.EMP emp
left outer join
DSN8810.DEPT dept
on dept.deptno = emp.workdept
left outer join
managers mgr
on mgr.mgrno = dept.mgrno
where emp.empno between '000000' and '150000'
and emp.workdept between 'A' and 'EEE'
order by case when dept.deptno IS NULL
then '*No dept'
when mgr.mgrno IS NULL
then '*No mgr'
else mgr.mgrname
end,
emp.empno
;
The result:
---------+---------+---------+---------+---------+---------+---------+---------+---------+--------- MGRNAME DEPTNAME LASTNAME FIRSTNME ---------+---------+---------+---------+---------+---------+---------+---------+---------+--------- CHRISTINE HAAS SPIFFY COMPUTER SERVICE DIV. HAAS CHRISTINE CHRISTINE HAAS SPIFFY COMPUTER SERVICE DIV. LUCCHESI VINCENZO CHRISTINE HAAS SPIFFY COMPUTER SERVICE DIV. O'CONNELL SEAN EVA PULASKI ADMINISTRATION SYSTEMS PULASKI EVA EVA PULASKI ADMINISTRATION SYSTEMS JEFFERSON JAMES EVA PULASKI ADMINISTRATION SYSTEMS MARINO SALVATORE EVA PULASKI ADMINISTRATION SYSTEMS SMITH DANIEL EVA PULASKI ADMINISTRATION SYSTEMS JOHNSON SYBIL EVA PULASKI ADMINISTRATION SYSTEMS PEREZ MARIA IRVING STERN MANUFACTURING SYSTEMS STERN IRVING IRVING STERN MANUFACTURING SYSTEMS ADAMSON BRUCE IRVING STERN MANUFACTURING SYSTEMS PIANKA ELIZABETH IRVING STERN MANUFACTURING SYSTEMS YOSHIMURA MASATOSHI IRVING STERN MANUFACTURING SYSTEMS SCOUTTEN MARILYN IRVING STERN MANUFACTURING SYSTEMS WALKER JAMES IRVING STERN MANUFACTURING SYSTEMS BROWN DAVID IRVING STERN MANUFACTURING SYSTEMS JONES WILLIAM IRVING STERN MANUFACTURING SYSTEMS LUTZ JENNIFER MICHAEL THOMPSON PLANNING THOMPSON MICHAEL SALLY KWAN INFORMATION CENTER KWAN SALLY SALLY KWAN INFORMATION CENTER QUINTANA DOLORES SALLY KWAN INFORMATION CENTER NICHOLLS HEATHER DSNE610I NUMBER OF ROWS DISPLAYED IS 22 DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
2. Explain with Range information
After executingEXPLAIN ALL SET QUERYNO=114 FORwith the above query the various explain tables will be supplied with appropriate data. A basic query on just PLAN_TABLE tells us there are 7 steps (table rows) required for executing this query. From DSN_SORT_TABLE we learn there are 4 sorts: two for the order-by clause, 1 for the distinct clause in the Common Table Expression, and 1 for executing the join with the CTE’s result-set. As a first step I wanted to extend my standard query for PLAN_TABLE with data on a potential page-range scan. After all, that would make a tablespace scan (accesstype=R) slightly less expensive… To show this, we need to use data from the DSN_PGRANGE_TABLE. I have decided to add the number of partitions to the access column. Some points of note regarding the below query and its results:
- Formatting is not ideal – especially the leftmost 5 columns could use some leading spaces. However, presentation was not the primary purpose of this exercise.
- Result rows are sorted – within each query – descending on Parent Qblockno. I started out sorting them ascending, but that resulted in a – to my taste – illogical sequence. With descending sort the result more closely matches the actual order of processing by DB2.
- In column Access you may note the I(2) occurrence – accompanied by LST in the PREF column indicating List Prefetch – which means that DB2 will perform an Index Scan on 2 partitions. Quite a bit better than a full Index Scan which would have had to process all 5 partitions.
- In the very same Access column you may also note the occurrence of T/R. This means the result set of the CTE will be materialized and subsequently accessed using a sparse index. This is the meaning of the T. Should this fail to work out, DB2 will perform a full scan of the CTE’s result set; this alternative being indicated by the R.
- Information on locking is not shown. Anyone interested can easily add the relevant colums.
--
-- Query on PLAN_TABLE with Page-Range info
--
SELECT SUBSTR(STRIP(CHAR(PLAN.QUERYNO)), 1, 5) AS QUERY
, CASE PLAN.PARENT_QBLOCKNO
WHEN 0 THEN ' '
ELSE
SUBSTR(STRIP(CHAR(PLAN.PARENT_QBLOCKNO)), 1, 4)
END AS PBLK
, SUBSTR(STRIP(CHAR(PLAN.QBLOCKNO)), 1, 4) AS QBLK
, SUBSTR(STRIP(CHAR(PLAN.PLANNO)), 1, 4) AS PLNO
, CASE PLAN.MIXOPSEQ
WHEN 0 THEN ' '
ELSE SUBSTR(STRIP(CHAR(PLAN.MIXOPSEQ)), 1, 4)
END AS OPSQ
, PLAN.QBLOCK_TYPE AS TYPE
, CASE PLAN.JOIN_TYPE
WHEN 'F' THEN 'Full'
WHEN 'P' THEN 'Pair'
WHEN 'S' THEN 'Star'
WHEN 'L' THEN CASE PLAN.METHOD
WHEN 1 THEN 'L/R'
WHEN 2 THEN 'L/R'
WHEN 4 THEN 'L/R'
ELSE '?'
END
WHEN ' ' THEN CASE PLAN.METHOD
WHEN 1 THEN 'Innr'
WHEN 2 THEN 'Innr'
WHEN 4 THEN 'Innr'
ELSE ' '
END
ELSE '*ERR*'
END AS JOIN
, CASE PLAN.METHOD
WHEN 0 THEN 'First'
WHEN 1 THEN 'NLjoin'
WHEN 2 THEN 'MSjoin'
WHEN 3 THEN 'Sort'
WHEN 4 THEN 'Hybrid'
ELSE 'UNKNWN'
END AS METHOD
, CASE PLAN.TABLE_TYPE
WHEN 'B' THEN 'Buffer'
WHEN 'C' THEN 'CTE'
WHEN 'F' THEN 'TabFun'
WHEN 'M' THEN 'MQT'
WHEN 'Q' THEN 'Temp'
WHEN 'R' THEN 'Recurs'
WHEN 'T' THEN 'Table'
WHEN 'W' THEN 'Work'
ELSE ' '
END AS TYPE
, SUBSTR(PLAN.CREATOR, 1, 8) AS CREATOR
, SUBSTR(PLAN.TNAME, 1, 18) AS TABLE
, SUBSTR(PLAN.ACCESSNAME, 1, 8) AS NDXNAME
, CASE PLAN.PRIMARY_ACCESSTYPE
WHEN 'D' THEN 'D/'
WHEN 'T' THEN 'T/'
ELSE ''
END CONCAT
STRIP(PLAN.ACCESSTYPE) CONCAT
CASE WHEN PLAN.PAGE_RANGE = 'Y'
THEN '(' CONCAT
STRIP(SUBSTR(STRIP(CHAR(RANGE.NUMPARTS)), 1, 4))
CONCAT ')'
ELSE ''
END AS ACCESS
, CASE PLAN.PREFETCH
WHEN 'S' THEN 'SEQ'
WHEN 'L' THEN 'LST'
WHEN 'D' THEN 'DYN'
ELSE ' '
END AS PREF
, CASE PLAN.INDEXONLY
WHEN 'Y' THEN 'XO'
ELSE ' '
END AS XO
, CASE PLAN.MATCHCOLS
WHEN 0 THEN ''
ELSE SUBSTR(STRIP(CHAR(PLAN.MATCHCOLS)), 1, 2)
END AS MC
, CASE PLAN.SORTC_GROUPBY
WHEN 'Y' THEN 'G'
ELSE ' '
END CONCAT
CASE PLAN.SORTC_JOIN
WHEN 'Y' THEN 'J'
ELSE ' '
END CONCAT
CASE PLAN.SORTC_ORDERBY
WHEN 'Y' THEN 'O'
ELSE ' '
END CONCAT
CASE PLAN.SORTC_UNIQ
WHEN 'Y' THEN 'U'
ELSE ' '
END CONCAT
CASE PLAN.SORTN_GROUPBY
WHEN 'Y' THEN 'G'
ELSE ' '
END CONCAT
CASE PLAN.SORTN_JOIN
WHEN 'Y' THEN 'J'
ELSE ' '
END CONCAT
CASE PLAN.SORTN_ORDERBY
WHEN 'Y' THEN 'O'
ELSE ' '
END CONCAT
CASE PLAN.SORTN_UNIQ
WHEN 'Y' THEN 'U'
ELSE ' '
END AS GJOUGJOU
, CASE PLAN.PARALLELISM_MODE
WHEN 'I' THEN 'I/O'
WHEN 'C' THEN 'CPU'
WHEN 'X' THEN 'SYS'
ELSE ' '
END AS PAR
, CASE PLAN.CTEREF
WHEN 0 THEN ' '
ELSE STRIP(CHAR(PLAN.CTEREF))
END AS CTEREF
FROM PLAN_TABLE PLAN
LEFT OUTER JOIN
DSN_PGRANGE_TABLE RANGE
ON RANGE.QUERYNO = PLAN.QUERYNO
AND RANGE.QBLOCKNO = PLAN.QBLOCKNO
AND RANGE.TABNO = PLAN.TABNO
WHERE PLAN.QUERYNO = 114
ORDER BY PLAN.QUERYNO
, PLAN.PARENT_QBLOCKNO DESC
, PLAN.QBLOCKNO
, PLAN.PLANNO
, PLAN.MIXOPSEQ
;
The result:
---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+--------- QUERY PBLK QBLK PLNO OPSQ TYPE JOIN METHOD TYPE CREATOR TABLE NDXNAME ACCESS PREF XO MC GJOUGJOU PAR CTEREF ---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+--------- 114 1 3 1 TABLEX First Table DSN8810 DEPT XDEPT2 I XO 114 1 3 2 TABLEX Innr NLjoin Table DSN8810 EMP XEMP1 I 1 114 1 3 3 TABLEX Sort U 114 1 1 SELECT First Table DSN8810 EMP XEMP2 I(2) LST 1 114 1 2 SELECT L/R NLjoin Table DSN8810 DEPT XDEPT1 I 1 114 1 3 SELECT L/R NLjoin Work TU00001 MANAGERS T/R J 114 1 4 SELECT Sort O DSNE610I NUMBER OF ROWS DISPLAYED IS 7 DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
3. Explain with Range and Sort information
Next I wished to see whether I could add information from DSN_SORT_TABLE to this query in order to get a more complete overview of explained queries. This means that rows from a different table will have to be added which implies a UNION ALL will have to be used. Not at all a problem in itself, but it does have a few complicating consequences: First of all the ORDER BY clause needs to be changed. Column names can no longer be used, in stead we need to specify column numbers. This reduces the query’s legibility and understandability, but so be it. At the same time we now have two subqueries each having a WHERE clause that needs to select the correct query for explaining. I don’t like such solutions; not just because it’s redundant, but mainly because it happens all too often that these WHERE clauses get out of sync, rendering the end result of the complete query useless. Therefore I have added a CTE named QUERIES to hold the number(s) of the query/queries to be explained. As it turned out I needed two more CTEs in order to find first the numbers and subsequently the names of the tables to be sorted. In order to limit the size of the result sets of these new CTE’s, I reused the QUERIES CTE, which holds the relevant query numbers. Thus the QUERIES CTE now replaces a total of 4 WHERE clauses. Thus I added three CTEs in order to extend my query. This, however, entailed a risk: when using three or more CTEs a CONCAT function in the SELECT clause may result in column values being prefixed with an extraneous byte containing a value of X’00′, which can be quite ugly. Luckily, this query does not suffer from this problem because it does not comply with the “requirement” that a specific relation exist between the CTEs involved. After verifying this I could start modifying the actual query. The first part remained almost the same.Only the WHERE clause needed to be changed. Selection on query-number had to be replaced by an inner join with relevant query numbers, as supplied by the CTE QUERIES. And – since data on sorting now will be obtained from DSN_SORT_TABLE – rows with METHOD=3 should now be skipped. In order to add the data on sorting a second query is added to the existing one. Of course this second query has to create a result set with the same columns, although it obtains its data from DSN_SORT_TABLE in stead of PLAN_TABLE. With this query and its result set, as with the previous one, some points of note are:- Columns PAR and CTEREF have been removed to make room. These columns were empty in this example anyway…
- Compared with the prior version two rows have disappeared. These are the rows indicating a sort (method = 3). I might have let them remain, but they’d have been redundant. It’s a matter of preference.
- For the join-with-sort some of the information does appear double. I might have suppressed it, but I didn’t feel it was worth the effort. Again: a matter of taste.
- 4 new rows ahve been added, based on the content of DSN_SORT_TABLE. The net result being an increase of two rows. Thus we now have more information available than with the prior query.
- Results are sorted slightly awkward. For a JOIN requiring a sort, the JOIN is shown before the SORT, while of course DB2 will perform the SORT before the JOIN, rather than afterwards. A solution for this sorting problem falls outside this article’s scope.
- Column OPSQ (or MIXOPSEQ) now contains numbers for the rows originating from the PLAN_TABLE (not visible in our example) and characters for the various sort-steps (SORTNO) from the sorting table.This was done primarily to prevent numbers from being assigned and appearing more than once. As a secondary advantage we can now readily distinguish between the two types of rows.
- We can now more easily see what the Access T/R entails: the CTE’s result-set is being sorted, creating an intermediate result set, which can be accessed efficiently using a sparse index. Should DB2, for whatever reason, be unable to perform the sort, DB2 will use a “normal” tablespace scan in stead.
- It looks like the Order by clause is being implemented using two sorts. If you look more closely, however, you’ll notice that the first of these sorts is exactly identical to the sort for DISTINCT in the CTE. Further investigation by IBM-Belgium’s Bart Steegmans has revealed that the first sort is not actually executed. The pertinent rows in DSN_SORT_TABLE and DSN_SORTKEY_TABLE have been created in error by explain. To amend this situation IBM have opened APAR PM16586.
--
-- Query on PLAN_TABLE with Page-Range info
-- and sorting info
--
WITH QUERIES
(QUERYNO)
AS (SELECT 114
FROM SYSIBM.SYSDUMMY1
)
, SORTKEYS
AS (SELECT DISTINCT
SKEY.QUERYNO
, SKEY.QBLOCKNO
, SKEY.PLANNO
, SKEY.SORTNO
, SKEY.TABNO
FROM DSN_SORTKEY_TABLE SKEY
JOIN QUERIES QRYS
ON QRYS.QUERYNO = SKEY.QUERYNO
WHERE SKEY.TABNO <> 0
)
, TABLES
AS (SELECT DISTINCT
PLAN.QUERYNO
, PLAN.TABNO
, PLAN.TABLE_TYPE
, PLAN.CREATOR
, PLAN.TNAME
FROM PLAN_TABLE PLAN
JOIN QUERIES QRYS
ON QRYS.QUERYNO = PLAN.QUERYNO
WHERE PLAN.TABNO <> 0
)
SELECT SUBSTR(STRIP(CHAR(PLAN.QUERYNO)), 1, 5) AS QUERY
, CASE PLAN.PARENT_QBLOCKNO
WHEN 0 THEN ' '
ELSE
SUBSTR(STRIP(CHAR(PLAN.PARENT_QBLOCKNO)), 1, 4)
END AS PBLK
, SUBSTR(STRIP(CHAR(PLAN.QBLOCKNO)), 1, 4) AS QBLK
, SUBSTR(STRIP(CHAR(PLAN.PLANNO)), 1, 4) AS PLNO
, CASE PLAN.MIXOPSEQ
WHEN 0 THEN ' '
ELSE SUBSTR(STRIP(CHAR(PLAN.MIXOPSEQ)), 1, 4)
END AS OPSQ
, PLAN.QBLOCK_TYPE AS TYPE
, CASE PLAN.JOIN_TYPE
WHEN 'F' THEN 'Full'
WHEN 'P' THEN 'Pair'
WHEN 'S' THEN 'Star'
WHEN 'L' THEN CASE PLAN.METHOD
WHEN 1 THEN 'L/R'
WHEN 2 THEN 'L/R'
WHEN 4 THEN 'L/R'
ELSE '?'
END
WHEN ' ' THEN CASE PLAN.METHOD
WHEN 1 THEN 'Innr'
WHEN 2 THEN 'Innr'
WHEN 4 THEN 'Innr'
ELSE ' '
END
ELSE '*ERR*'
END AS JOIN
, CASE PLAN.METHOD
WHEN 0 THEN 'First'
WHEN 1 THEN 'NLjoin'
WHEN 2 THEN 'MSjoin'
WHEN 3 THEN 'Sort'
WHEN 4 THEN 'Hybrid'
ELSE 'UNKNWN'
END AS METHOD
, CASE PLAN.TABLE_TYPE
WHEN 'B' THEN 'Buffer'
WHEN 'C' THEN 'CTE'
WHEN 'F' THEN 'TabFun'
WHEN 'M' THEN 'MQT'
WHEN 'Q' THEN 'Temp'
WHEN 'R' THEN 'Recurs'
WHEN 'T' THEN 'Table'
WHEN 'W' THEN 'Work'
ELSE ' '
END AS TYPE
, SUBSTR(PLAN.CREATOR, 1, 8) AS CREATOR
, SUBSTR(PLAN.TNAME, 1, 18) AS TABLE
, SUBSTR(PLAN.ACCESSNAME, 1, 8) AS NDXNAME
, CASE PLAN.PRIMARY_ACCESSTYPE
WHEN 'D' THEN 'D/'
WHEN 'T' THEN 'T/'
ELSE ''
END CONCAT
STRIP(PLAN.ACCESSTYPE) CONCAT
CASE WHEN PLAN.PAGE_RANGE = 'Y'
THEN '(' CONCAT
STRIP(SUBSTR(STRIP(CHAR(RANGE.NUMPARTS)), 1, 4))
CONCAT ')'
ELSE ''
END AS ACCESS
, CASE PLAN.PREFETCH
WHEN 'S' THEN 'SEQ'
WHEN 'L' THEN 'LST'
WHEN 'D' THEN 'DYN'
ELSE ' '
END AS PREF
, CASE PLAN.INDEXONLY
WHEN 'Y' THEN 'XO'
ELSE ' '
END AS XO
, CASE PLAN.MATCHCOLS
WHEN 0 THEN ''
ELSE SUBSTR(STRIP(CHAR(PLAN.MATCHCOLS)), 1, 2)
END AS MC
, CASE PLAN.SORTC_GROUPBY
WHEN 'Y' THEN 'G'
ELSE ' '
END CONCAT
CASE PLAN.SORTC_JOIN
WHEN 'Y' THEN 'J'
ELSE ' '
END CONCAT
CASE PLAN.SORTC_ORDERBY
WHEN 'Y' THEN 'O'
ELSE ' '
END CONCAT
CASE PLAN.SORTC_UNIQ
WHEN 'Y' THEN 'U'
ELSE ' '
END CONCAT
CASE PLAN.SORTN_GROUPBY
WHEN 'Y' THEN 'G'
ELSE ' '
END CONCAT
CASE PLAN.SORTN_JOIN
WHEN 'Y' THEN 'J'
ELSE ' '
END CONCAT
CASE PLAN.SORTN_ORDERBY
WHEN 'Y' THEN 'O'
ELSE ' '
END CONCAT
CASE PLAN.SORTN_UNIQ
WHEN 'Y' THEN 'U'
ELSE ' '
END AS GJOUGJOU
FROM PLAN_TABLE PLAN
JOIN QUERIES QRYS
ON QRYS.QUERYNO = PLAN.QUERYNO
LEFT OUTER JOIN
DSN_PGRANGE_TABLE RANGE
ON RANGE.QUERYNO = PLAN.QUERYNO
AND RANGE.QBLOCKNO = PLAN.QBLOCKNO
AND RANGE.TABNO = PLAN.TABNO
WHERE PLAN.METHOD <> 3
UNION ALL
SELECT SUBSTR(STRIP(CHAR(PLAN.QUERYNO)), 1, 5) AS QUERY
, CASE PLAN.PARENT_QBLOCKNO
WHEN 0 THEN ' '
ELSE
SUBSTR(STRIP(CHAR(PLAN.PARENT_QBLOCKNO)), 1, 4)
END AS PBLK
, SUBSTR(STRIP(CHAR(PLAN.QBLOCKNO)), 1, 4) AS QBLK
, SUBSTR(STRIP(CHAR(PLAN.PLANNO)), 1, 4) AS PLNO
, CASE WHEN PLAN.MIXOPSEQ <> 0
THEN SUBSTR(STRIP(CHAR(PLAN.MIXOPSEQ)), 1, 4)
ELSE ''
END CONCAT
CASE WHEN SORT.SORTNO > 0
THEN SUBSTR('ABCDEFGHIJKLMNOPQRSTUVWXYZ',
SORT.SORTNO, 1)
ELSE ''
END AS OPSQ
, PLAN.QBLOCK_TYPE AS TYPE
, '' AS JOIN
, 'Sort' AS METHOD
, CASE TBLS.TABLE_TYPE
WHEN 'B' THEN 'Buffer'
WHEN 'C' THEN 'CTE'
WHEN 'F' THEN 'TabFun'
WHEN 'M' THEN 'MQT'
WHEN 'Q' THEN 'Temp'
WHEN 'R' THEN 'Recurs'
WHEN 'T' THEN 'Table'
WHEN 'W' THEN 'Work'
ELSE ' '
END AS TYPE
, SUBSTR(TBLS.CREATOR, 1, 8) AS CREATOR
, SUBSTR(TBLS.TNAME, 1, 18) AS TABLE
, SUBSTR(PLAN.ACCESSNAME, 1, 8) AS NDXNAME
, CASE PLAN.PRIMARY_ACCESSTYPE
WHEN 'D' THEN 'D/'
WHEN 'T' THEN 'T/'
ELSE ''
END CONCAT
STRIP(PLAN.ACCESSTYPE)
AS ACCESS
, CASE PLAN.PREFETCH
WHEN 'S' THEN 'SEQ'
WHEN 'L' THEN 'LST'
WHEN 'D' THEN 'DYN'
ELSE ' '
END AS PREF
, CASE PLAN.INDEXONLY
WHEN 'Y' THEN 'XO'
ELSE ' '
END AS XO
, CASE PLAN.MATCHCOLS
WHEN 0 THEN ''
ELSE SUBSTR(STRIP(CHAR(PLAN.MATCHCOLS)), 1, 2)
END AS MC
, SUBSTR(SORT.SORTC, 1, 4) CONCAT
SUBSTR(SORT.SORTN, 1, 4) AS GJOUGJOU
FROM DSN_SORT_TABLE SORT
JOIN QUERIES QRYS
ON QRYS.QUERYNO = SORT.QUERYNO
LEFT OUTER JOIN -- Obtain nr of table being sorted
SORTKEYS SKEY
ON SKEY.QUERYNO = SORT.QUERYNO
AND SKEY.QBLOCKNO = SORT.QBLOCKNO
AND SKEY.PLANNO = SORT.PLANNO
AND SKEY.SORTNO = SORT.SORTNO
LEFT OUTER JOIN -- Obtain name of table being sorted
TABLES TBLS
ON TBLS.QUERYNO = SKEY.QUERYNO
AND TBLS.TABNO = SKEY.TABNO
LEFT OUTER JOIN -- Join back to relevant PLAN_TABLE row
PLAN_TABLE PLAN
ON PLAN.QUERYNO = SORT.QUERYNO
AND PLAN.QBLOCKNO = SORT.QBLOCKNO
AND PLAN.PLANNO = SORT.PLANNO
AND( PLAN.METHOD = 3
OR( PLAN.SORTC_JOIN = 'Y'
AND SUBSTR(SORT.SORTC, 2, 1) = 'J'
)
OR( PLAN.SORTN_JOIN = 'Y'
AND SUBSTR(SORT.SORTN, 2, 1) = 'J'
) )
ORDER BY 1, 2 DESC, 3, 4, 5
;
The result:
---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+------- QUERY PBLK QBLK PLNO OPSQ TYPE JOIN METHOD TYPE CREATOR TABLE NDXNAME ACCESS PREF XO MC GJOUGJOU ---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+------- 114 1 3 1 TABLEX First Table DSN8810 DEPT XDEPT2 I XO 114 1 3 2 TABLEX Innr NLjoin Table DSN8810 EMP XEMP1 I 1 114 1 3 3 A TABLEX Sort Table DSN8810 EMP U 114 1 1 SELECT First Table DSN8810 EMP XEMP2 I(2) LST 1 114 1 2 SELECT L/R NLjoin Table DSN8810 DEPT XDEPT1 I 1 114 1 3 SELECT L/R NLjoin Work TU00001 MANAGERS T/R J 114 1 3 A SELECT Sort Work TU00001 MANAGERS T/R J 114 1 4 A SELECT Sort Table DSN8810 EMP O 114 1 4 B SELECT Sort Table DSN8810 EMP O DSNE610I NUMBER OF ROWS DISPLAYED IS 9 DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
Concluding remark: I only had a very limited set of explain data at my disposal. Therefore I cannot guarantee these queries will show explain data for “real” SQL from your development or production environments reliably. Should you find any defect or shortcoming, I would gladly hear from you. Any other remarks or shortcomings are equally welcome.
Remarks? Questions? More information? e-mail us with your questions.Tags: DB2, db2 sql, explain, extended explain, performance monitoring, query optimization
Abe Kornelis
Abe Kornelis has been working with mainframes since 1983. In 2001 he started working with DB2 for z/OS, first as a systems programmer, later as Database Administrator, query author, and instructor. His experience in the area of making complex queries readable and tuning them motivated him to write this article. Abe is self-employed, his company Bixoft was founded in 1988. More information is available
on the web at www.bixoft.com If you
have any questions, please do not hesitate to mail Abe
at abe@bixoft.come-mail.
