You can use the DB2 EXPLAIN statement to determine the access paths for the SELECT parts of your statements. DB2 EXPLAIN helps you answer questions about Query Performance & Query Optimization; the answers give you the information that you need to make performance improvements. EXPLAIN indicates whether Db2 used an index to access data, whether sorts were performed, whether parallel processing was used, and so on. The information in the plan table can help you when you need to perform the following tasks:
- Determine the access path that Db2 chooses for a query.
- Design databases, indexes, and application programs.
- Determine when to rebind an application.
Three key tables for EXPLAIN are PLAN_TABLE, DSN_STATEMNT_TABLE & DSN_FUNCTION_TABLE.
Steps for DB2 EXPLAIN:
- Before you can use EXPLAIN, you must create a plan table to hold the results of EXPLAIN. It contains information about access paths for queries that were explained or hints.You can create it by using the below SQL.
CREATE TABLE [userid].PLAN_TABLE LIKE DEFAULT.PLAN_TABLE;
- Populate the plan table. You can populate the plan table by executing the SQL statement EXPLAIN. You can also populate a plan table when you bind or rebind a plan or package by specifying the option EXPLAIN(YES). EXPLAIN obtains information about the access paths for all explainable SQL statements in a package or in the DBRMs of a plan.
EXPLAIN PLAN SET QUERYNO = 1 FOR [your sql statement here]
- Select information from the plan table. Several processes can insert rows into the same plan table. To understand access paths, you must retrieve the rows for a particular query in an appropriate order. Execute this SQL command to view the Explain information:
SELECT * FROM PLAN_TABLE WHERE QUERYNO = 1 ORDER BY TIMESTAMP, QUERYNO, QBLOCKNO, PLANNO, MIXOPSEQ WITH UR;
Look at these fields for important information:
- PLANNO – Number of steps necessary to process the query indicated in QBLOCKNO. Indicates order in which the steps were executed.
- METHOD – Indicate joins method used for the step (PLANNO).
- 0 = First table accessed, continuation of previous table accessed, or not used
- 1 = Nested loop join. For each row of the present composite table, matching rows of a new table are found and joined
- 2 = Merge scan join. The present composite table and the new tables are scanned in the order of the join columns and matching rows are joined. 3 = Sorts neededby ORDER BY, GROUP BY, SELECT DISTINCT, UNION, a quantified predicate or an IN predicate. Does not access a new table.
- ACCESTYPE – Method used to access the table.
- DI = An intersection of multiple DOCID lists to return final DOCID list.
- DU = Union of multiple DOCID lists to return the final DOCID list.
- DX = An XML index scan of the index named in ACCESSNAME to return a DOCID list.
- E = Direct row using a row change timestamp column.
- H = Hash access. IF an overflow condition occurs, hash overflow index identified by ACCESSCREATOR and ACCESSNAME is used.
- HN = Hash access using an IN predicate, or an IN predicate that DB2 generates. If a hash overflow condition occurs, hash overflow index identified in ACCESSCREATOR AND ACCESSNAME is used.
- IN = Index scan when matching predicate contains an IN predicate and the IN-list is accessed through an in-memory table
- I = An index (identified in ACCESSCREATOR and ACCESSNAME).
- I1 = One-fetch index scan
- M = Multiple index scan (followed by MX, MI, MH, or MU).
- MH = Hash overflow index named in ACCESSNAME
- MX = Index scan on index named in ACCESSNAME. When the access method MX follows the access method DX, DI, or DU, the table is accessed by the DOCID index using the DOCID list returned by DX, DI, or DU
- MI = Intersection of multiple indexes
- MU = Union of multiple indexes
- N = Index scan when the matching predicate contains the IN keyword or by an index scan when DB2 rewrites a query using the IN keyword.
- NR = Range list access.
- O = Work file scan, as a result of a subquery.
- P = Dynamic pair-wise index scan
- R = Table space scan.
- RW = work file scan of materialized user-defined table function
- V = Buffers for an INSERT statement within a SELECT.
- Blank = Not applicable to the current row.
- MATCHCOLS – Number of index key used for index scan (when ACCESTYPE is I, I1, N, NR, MX, or DX, number of index keys used in an index scan).
- ACCESSNAME – Name of the index used for index scan (when ACCESTYPE is I, I1, H, MH, N, NR, MX, or DX, name of index).
- INDEXONLY – If access to an index alone is enough to carry out the step, or if the data, too, must be accessed. Y = Yes; N = No.
- PREFETCH – Indicates if data pages can be read in advance by prefetch.
- D = optimizer expects dynamic prefetch
- S = Pure sequential prefetch
- L = Prefetch through a page list
- U = List prefetch with an unsorted RID list
- Blank = unknown at bind time or no prefetch
- COLUMN_FN_EVAL – Indicates when aggregate functions are evaluated.
- R = while data is being read from the table or index
- S = While performing a sort to satisfy a GROUP BY clause
- X = While data is read from a table or index, for aggregate functions when an OFFSET clause is specified
- Y = While performing a sort, for aggregate functions when an OFFSET clause if specified.
- Blank = After data retrieval after any sorts
Sample EXPLAIN Queries
Query to find Total Cost: SELECT SUBSTR(PL.COLLID,1,10) AS COLLID, SUBSTR(PL.PROGNAME,1,10) AS PROGNAME, DATE(PL.EXPLAIN_TIME) AS DATE, TIME(PL.EXPLAIN_TIME) AS TIME, COUNT(PL.QUERYNO) AS "STMT COUNT", DEC(SUM(ST.TOTAL_COST),8,2) AS "TOTAL COST" FROM SJD.PLAN_TABLE PL, SJD.DSN_STATEMNT_TABLE ST WHERE PL.PROGNAME = ST.PROGNAME AND PL.COLLID = ST.COLLID AND PL.EXPLAIN_TIME = ST.EXPLAIN_TIME AND PL.QUERYNO = ST.QUERYNO GROUP BY PL.COLLID, PL.PROGNAME, PL.EXPLAIN_TIME ORDER BY PL.PROGNAME; Result: ————+---------+---------+---------+---------+------------- COLLID PROGNAME DATE TIME STMT COUNT TOTAL COST +---------+---------+---------+---------+----------------- MYCOLL MYPACK 05/08/2014 11.19.38 5 10.10 MYCOLL MYPACK 05/08/2014 17.36.17 8 19.11 Query to Compare Old & New Cost: SELECT ST1.QUERYNO AS QUERYNO, COALESCE(DEC(ST1.TOTAL_COST,8,2),0) AS "OLD COST", COALESCE(DEC(ST2.TOTAL_COST,8,2),0) AS "NEW COST" FROM SJD.DSN_STATEMNT_TABLE ST1 FULL JOIN SJD.DSN_STATEMNT_TABLE ST2 ON ST1.QUERYNO = ST2.QUERYNO WHERE ST1.COLLID = 'MYCOLL' AND ST2.COLLID = 'MYCOLL' AND ST1.PROGNAME = 'MYPACK' AND ST2.PROGNAME = 'MYPACK' AND DATE(ST1.EXPLAIN_TIME) = '2014-05-08' AND TIME(ST1.EXPLAIN_TIME) = '17.36.17' AND DATE(ST2.EXPLAIN_TIME) = '2014-05-15' AND TIME(ST2.EXPLAIN_TIME) = '13.05.14' AND ST1.TOTAL_COST <> ST2.TOTAL_COST ORDER BY QUERYNO; Result: ---------+---------+---------+---------+----- QUERYNO OLD COST NEW COST ---------+---------+---------+---------+----- 353 .15 .11 360 8.07 16.24 Query to find Total Cost and Explain Time: SELECT QUERYNO, TOTAL_COST, EXPLAIN_TIME FROM SJD.DSN_STATEMNT_TABLE WHERE PROGNAME = 'ID14SQP' AND COLLID = 'ID14SQL' ORDER BY TOTAL_COST DESC, EXPLAIN_TIME DESC; Result: -----+---------+---------+---------+---------+---------+---- QUERYNO TOTAL_COST EXPLAIN_TIME -----+---------+---------+---------+---------+---------+---- 88 +0.5616997729372477E+05 2014-11-02-18.03.04.993751 131 +0.1453335050780900E+01 2014-11-02-18.03.04.993751 131 +0.1453335050780900E+01 2014-10-27-16.55.51.641367 SELECT QUERYNO, COALESCE(DEC(TOTAL_COST,8,0),0) AS "COST", EXPLAIN_TIME FROM SJD.DSN_STATEMNT_TABLE WHERE COLLID = ‘ID14SQL' AND PROGNAME = ‘ID14SQP' ORDER BY EXPLAIN_TIME DESC; Result: ---------+---------+---------+---------+---------+ QUERYNO COST EXPLAIN_TIME ---------+---------+---------+---------+---------+ 88 434758 2014-11-02-18.03.04.993751 88 23184 2014-10-27-16.55.51.641367