Getting an execution plan from DB2 LUW is a three step procedure where the first step is a one-time setup.
Create required tables (once)
The recommended way to create the required explain tables is to call this procedure:
CALL SYSPROC.SYSINSTALLOBJECTS('EXPLAIN', 'C',
CAST (NULL AS VARCHAR(128)), CAST (NULL AS VARCHAR(128)))This will install the required tables in the SYSTOOLS schema (e.g. SYSTOOLS.EXPLAIN_STREAM).
Alternative installation procedures and customizations are explained in the documentation.
Explain the statement
Prefix any SQL statement with explain plan for to store the execution plan details in the explain tables.
This step does not yet show the execution plan, it just stores it in the database.
EXPLAIN PLAN FOR SELECT 1 FROM sysibm.sysdummy1Display the stored execution plan
IBM provides some tools to display the data stored in the explain tables. However, the output format is not as useful as it could be, so I’m using my own SQL query to display the information I’m usually interested in. The DB2 documentation actually recommends doing this.
Below you find the definition of the view last_explained which returns a formatted explain plan for the last statement that was explained for the current user in this database. Please note that it’s scope is not limited to the current session. The view can be used as simple as that:
SELECT * FROM last_explainedThe result might look like this. The next section has the details how to interpret it.
Explain Plan
----------------------------------------------
ID | Operation | Rows | Cost
1 | RETURN | | 0
2 | TBSCAN GENROW | 1 of 1 (100.00%) | 0
Predicate Information
Explain plan by Markus Winand - NO WARRANTY
http://use-the-index-luke.com/s/last_explained Warning
This view is highly experimental. It is provided as is without any warranty. It was extended by Ember Crooks to include ActualRows if available. Read her article to see how to collect them.
The view assumes the explain tables in the SYSTOOLS schema. If you are using a different schema, you need to adjust the below view manually.
Minimum requirement: DB2 LUW 9.7 FixPack 4 (Note: Express-C was only available with FixPack 2)
Tip
The view is available on GitHub (direct download).
-- Copyright (c) 2014-2017, Markus Winand - NO WARRANTY
-- Modifications by Ember Crooks - NO WARRANTY
-- Info & license: http://use-the-index-luke.com/s/last_explained
--
--#SET TERMINATOR ;
CREATE OR REPLACE VIEW last_explained AS
WITH tree(operator_ID, level, path, explain_time, cycle)
AS
(
SELECT 1 operator_id
, 0 level
, CAST('001' AS VARCHAR(1000)) path
, max(explain_time) explain_time
, 0
FROM SYSTOOLS.EXPLAIN_OPERATOR O
WHERE O.EXPLAIN_REQUESTER = SESSION_USER
UNION ALL
SELECT s.source_id
, level + 1
, tree.path || '/' || LPAD(CAST(s.source_id AS VARCHAR(3)), 3, '0') path
, tree.explain_time
, POSITION('/' || LPAD(CAST(s.source_id AS VARCHAR(3)), 3, '0') || '/' IN path USING OCTETS)
FROM tree
, SYSTOOLS.EXPLAIN_STREAM S
WHERE s.target_id = tree.operator_id
AND s.explain_time = tree.explain_time
AND S.Object_Name IS NULL
AND S.explain_requester = SESSION_USER
AND tree.cycle = 0
AND level < 100
)
SELECT *
FROM (
SELECT "Explain Plan"
FROM (
SELECT CAST( LPAD(id, MAX(LENGTH(id)) OVER(), ' ')
|| ' | '
|| RPAD(operation, MAX(LENGTH(operation)) OVER(), ' ')
|| ' | '
|| LPAD(rows, MAX(LENGTH(rows)) OVER(), ' ')
|| ' | '
-- Don't show ActualRows columns if there are no actuals available at all
|| CASE WHEN COUNT(ActualRows) OVER () > 1 -- the heading 'ActualRows' is always present, so "1" means no OTHER values
THEN LPAD(ActualRows, MAX(LENGTH(ActualRows)) OVER(), ' ') || ' | '
ELSE ''
END
|| LPAD(cost, MAX(LENGTH(cost)) OVER(), ' ')
AS VARCHAR(100)) "Explain Plan"
, path
FROM (
SELECT 'ID' ID
, 'Operation' Operation
, 'Rows' Rows
, 'ActualRows' ActualRows
, 'Cost' Cost
, '0' Path
FROM SYSIBM.SYSDUMMY1
-- TODO: UNION ALL yields duplicate. where do they come from?
UNION
SELECT CAST(tree.operator_id as VARCHAR(254)) ID
, CAST(LPAD(' ', tree.level, ' ')
|| CASE WHEN tree.cycle = 1
THEN '(cycle) '
ELSE ''
END
|| COALESCE (
TRIM(O.Operator_Type)
|| COALESCE(' (' || argument || ')', '')
|| ' '
|| COALESCE(S.Object_Name,'')
, ''
)
AS VARCHAR(254)) AS OPERATION
, COALESCE(CAST(rows AS VARCHAR(254)), '') Rows
, CAST(ActualRows as VARCHAR(254)) ActualRows -- note: no coalesce
, COALESCE(CAST(CAST(O.Total_Cost AS BIGINT) AS VARCHAR(254)), '') Cost
, path
FROM tree
LEFT JOIN ( SELECT i.source_id
, i.target_id
, CAST(CAST(ROUND(o.stream_count) AS BIGINT) AS VARCHAR(12))
|| ' of '
|| CAST (total_rows AS VARCHAR(12))
|| CASE WHEN total_rows > 0
AND ROUND(o.stream_count) <= total_rows THEN
' ('
|| LPAD(CAST (ROUND(ROUND(o.stream_count)/total_rows*100,2)
AS NUMERIC(5,2)), 6, ' ')
|| '%)'
ELSE ''
END rows
, CASE WHEN act.actual_value is not null then
CAST(CAST(ROUND(act.actual_value) AS BIGINT) AS VARCHAR(12))
|| ' of '
|| CAST (total_rows AS VARCHAR(12))
|| CASE WHEN total_rows > 0 THEN
' ('
|| LPAD(CAST (ROUND(ROUND(act.actual_value)/total_rows*100,2)
AS NUMERIC(5,2)), 6, ' ')
|| '%)'
ELSE NULL
END END ActualRows
, i.object_name
, i.explain_time
FROM (SELECT MAX(source_id) source_id
, target_id
, MIN(CAST(ROUND(stream_count,0) AS BIGINT)) total_rows
, CAST(LISTAGG(object_name) AS VARCHAR(50)) object_name
, explain_time
FROM SYSTOOLS.EXPLAIN_STREAM
WHERE explain_time = (SELECT MAX(explain_time)
FROM SYSTOOLS.EXPLAIN_OPERATOR
WHERE EXPLAIN_REQUESTER = SESSION_USER
)
GROUP BY target_id, explain_time
) I
LEFT JOIN SYSTOOLS.EXPLAIN_STREAM O
ON ( I.target_id=o.source_id
AND I.explain_time = o.explain_time
AND O.EXPLAIN_REQUESTER = SESSION_USER
)
LEFT JOIN SYSTOOLS.EXPLAIN_ACTUALS act
ON ( act.operator_id = i.target_id
AND act.explain_time = i.explain_time
AND act.explain_requester = SESSION_USER
AND act.ACTUAL_TYPE like 'CARDINALITY%'
)
) s
ON ( s.target_id = tree.operator_id
AND s.explain_time = tree.explain_time
)
LEFT JOIN SYSTOOLS.EXPLAIN_OPERATOR O
ON ( o.operator_id = tree.operator_id
AND o.explain_time = tree.explain_time
AND o.explain_requester = SESSION_USER
)
LEFT JOIN (SELECT LISTAGG (CASE argument_type
WHEN 'UNIQUE' THEN
CASE WHEN argument_value = 'TRUE'
THEN 'UNIQUE'
ELSE NULL
END
WHEN 'TRUNCSRT' THEN
CASE WHEN argument_value = 'TRUE'
THEN 'TOP-N'
ELSE NULL
END
WHEN 'SCANDIR' THEN
CASE WHEN argument_value != 'FORWARD'
THEN argument_value
ELSE NULL
END
ELSE argument_value
END
, ' ') argument
, operator_id
, explain_time
FROM SYSTOOLS.EXPLAIN_ARGUMENT EA
WHERE argument_type IN ('AGGMODE' -- GRPBY
, 'UNIQUE', 'TRUNCSRT' -- SORT
, 'SCANDIR' -- IXSCAN, TBSCAN
, 'OUTERJN' -- JOINs
)
AND explain_requester = SESSION_USER
GROUP BY explain_time, operator_id
) A
ON ( a.operator_id = tree.operator_id
AND a.explain_time = tree.explain_time
)
) O
UNION ALL
VALUES ('Explain plan (c) 2014-2017 by Markus Winand - NO WARRANTY - V20171102','Z0')
, ('Modifications by Ember Crooks - NO WARRANTY','Z1')
, ('http://use-the-index-luke.com/s/last_explained','Z2')
, ('', 'A')
, ('', 'Y')
, ('Predicate Information', 'AA')
UNION ALL
SELECT CAST (LPAD(CASE WHEN operator_id = LAG (operator_id)
OVER (PARTITION BY operator_id
ORDER BY pred_order
)
THEN ''
ELSE operator_id || ' - '
END
, MAX(LENGTH(operator_id )+4) OVER()
, ' ')
|| how_applied
|| ' '
|| predicate_text
AS VARCHAR(100)) "Predicate Information"
, 'P' || LPAD(id_order, 5, '0') || pred_order path
FROM (SELECT CAST(operator_id AS VARCHAR(254)) operator_id
, LPAD(trim(how_applied)
, MAX (LENGTH(TRIM(how_applied)))
OVER (PARTITION BY operator_id)
, ' '
) how_applied
-- next: capped to length 80 to avoid
-- SQL0445W Value "..." has been truncated. SQLSTATE=01004
-- error when long literal values may appear (space padded!)
, CAST(substr(predicate_text, 1, 80) AS VARCHAR(80)) predicate_text
, CASE how_applied WHEN 'START' THEN '1'
WHEN 'STOP' THEN '2'
WHEN 'SARG' THEN '3'
ELSE '9'
END pred_order
, operator_id id_order
FROM systools.explain_predicate p
WHERE explain_time = (SELECT MAX(explain_time)
FROM systools.explain_operator)
)
)
ORDER BY path
);
TODO:
Operator ID is currently giving the physical operator ID as stored in the explain tables. It might be more appropriate to display a line number like the Oracle DBMS_XPLAN does so that it is more easy to find a specific ID.
Indicate the presences of predicates with a asterisk next to the operation ID like Oracle’s DBMS_PLAN does it.
It might also be good to indicate the presences of filter predicates by using another character (e.g. !).
Even more sophisticated features would be possible:
- *
START and STOP predicates are present
- >
A START but no STOP predicate is present
- <
A STOP bu no START predicate is present
- !
A SARG predicate is present (additionally, !).
A heuristic to indicate Top-N clauses: if the number of rows drops for TBSCAN or IXSCAN but there are no predicates, it must be a Top-N limit. This logic is incomplete, of course.
