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:

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.