Extended explain in DB2 z/OS

Written by Abe Kornelis on . Posted in DB2 z/OS, Featured

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
  2. Explain with Range Information
  3. Explain with Range and Sort Information.

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 executing
   EXPLAIN ALL SET QUERYNO=114 FOR
with 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.
The explain Query:
--
-- 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.
The Explain Query:
--
-- 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: , , , , ,

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.
x
Sign up for our Newsletter

Enter your email and stay on top of things,