IBM Data & AI

Welcome to the idea forum for IBM Data & AI (Formerly Analytics) Clients! 

 

IBM Employees:

The correct URL for entering your ideas is https://hybridcloudunit-internal.ideas.aha.io

 

Clients:

Our team welcomes any feedback  and suggestions you have for improving our offerings / products!  This forum allows us to connect your offering / product improvement ideas with IBM product and engineering teams.

 

If you have not registered on this portal please click on the following link and register.  To complete registration you will need to open the email you will receive from Aha to confirm your identity.  https://ibm.biz/AnalyticsIdeasPortalRegister

 

Add table function to "pretty print" explained statement

Db2 allows to install the EXPLAIN TABLES using a procedure (https://www.ibm.com/support/knowledgecenter/SSEPGG_11.1.0/com.ibm.db2.luw.admin.explain.doc/doc/t0059594.html)

 

There are different ways to generate an access plan, i.e., to explain a statement using SQL. One simple way is to use EXPLAIN PLAN FOR. However, it is not possible to print the generated access plan using SQL. Thus, add a table function to pretty print an access plan.

Some considerations:

  • if not specified, return the access plan for the last explained session user
  • else use the query tag or query number to identifiy the statements (see EXPLAIN PLAN FOR)
  • by default or if specified, print a compact representation of the query plan
  • if requested, print the plan with all details

The output should be in a table format (hence table function).

For some background information see this tweet:

https://twitter.com/data_henrik/status/1121318954407014400

 

The new functionality closes Db2 tooling gaps. It would allow to quickly obtain AND print access plan information in Jupyter notebooks and (IBM) Data Science tools. It would also enhance the SQL consoles in our aaS offerings.

  • Henrik Loeser
  • Apr 26 2019
  • Needs review
Why is it useful?

Users could directly obtain explain output using SQL without knowing details of the EXPLAIN tables. The formatted explain information could be shown in Jupyter Notebooks.

Who would benefit from this IDEA? Almost all Db2 users who need to look into SQL query access plans.
How should it work?

1) see above

2) functionality is missing, it would enhance the serviceability on the command line, in all interfaces that allow to use SQL and in popular development and presentation environments like Jupyter notebooks, SQL shells, IBM Data Science, etc.

3) There are few workarounds in the form of scripts but nothing directly available.

Idea Priority High
Priority Justification The feature is missing and other database systems already have this very useful feature
Customer Name
Submitting Organization
Submitter Tags
  • Attach files
  • Admin
    ROBERT INDRIGO commented
    08 May 20:03

    Table functions are named in EXPLAIN access plans since 11.1 fix pack 2

  • Ember Crooks commented
    08 May 20:47

    How does this already exist? The request is for a stored procedure that displays explain information

NOTICE TO EU RESIDENTS: per EU Data Protection Policy, if you wish to remove your personal information from the IBM ideas portal, please login to the ideas portal using your previously registered information then change your email to "anonymous@euprivacy.out" and first name to "anonymous" and last name to "anonymous". This will ensure that IBM will not send any emails to you about all idea submissions