Db2: SQL-based explain and printed acccess plan

Vote for this Db2 idea

This Friday in Munich, I gave a talk about the new native JSON functions in Db2 at the German Db2 user group (DeDUG) meeting. To speed up queries and to enforce uniqueness or some structural rules, Db2 functional indexes can be used with the new JSON_VALUE. As usual for some prototyping, I utilized a Jupyter Notebook for my presentation. Now, how to demonstrate that indexes are really used? Show the access plan! But how in a notebook?

Db2 access plans

Interacting with Db2 from a Jupyter Notebook is entirely based on plan SQL. To generate and print an access plan, some steps are needed.

First, I create the EXPLAIN tables. There are two options and one of them is to call a procedure:

CALL SYSPROC.SYSINSTALLOBJECTS('EXPLAIN', 'C', CAST (NULL AS VARCHAR(128)), CAST (NULL AS VARCHAR(128))) 

Once the EXPLAIN tables are available, I can explain a statement and fill the tables:

EXPLAIN PLAN FOR SELECT id FROM myjson
  WHERE JSON_VALUE(doc, 'strict $.name.last' returning varchar(60))='Loeser'
  AND JSON_VALUE(doc, 'strict $.id' returning integer)=701

Now, with the access plan information available, I need to turn this into something readable. This step, however, is not really user-friendly when working on the command line or only based on pure SQL. Here is a small SQL statement to render the explain information for the last explained (see "max(explain_time") into a table:


SELECT distinct O.Operator_ID, S2.Target_ID, O.Operator_Type,
S.Object_Name, CAST(O.Total_Cost AS INTEGER) Cost
FROM SYSTOOLS.EXPLAIN_OPERATOR O
     LEFT OUTER JOIN SYSTOOLS.EXPLAIN_STREAM S2
                    ON O.Operator_ID=S2.Source_ID
     LEFT OUTER JOIN SYSTOOLS.EXPLAIN_STREAM S
                    ON O.Operator_ID = S.Target_ID
                    AND O.Explain_Time = S.Explain_Time
                AND S.Object_Name IS NOT NULL
WHERE O.explain_time=(select max(explain_time) from SYSTOOLS.EXPLAIN_OPERATOR)
ORDER BY Operator_ID ASC;

The above gives the following output for my statement:

operator_id	target_id	operator_type	object_name	cost
1	None	RETURN	None	6
2	1	FETCH	MYJSON	6
3	2	IXSCAN	JIX2	0

As you can see, the IXSCAN operator signals an index scan on my index JIX2.

Improving the output - pretty printing the access plan

The above output is barebones, so I quickly searched for other scripts and found Markus Winand's last_explained view. That works and gives nicer output, but I was looking for something built into Db2. Because it is missing and I know how to open a Db2 feature request, I turned it into the "Add table function to pretty print explained statement". Click on the link and vote or comment on it.

If you have feedback, suggestions, or questions about this post, please reach out to me on Twitter (@data_henrik) or LinkedIn.