How do I EXPLAIN SQL executing in an active thread?

Document ID : KB000021768
Last Modified Date : 14/02/2018
Show Technical Document Details

Question:

I have some SQL running that seems to be taking a long time. How can I run an EXPLAIN on it to see what access path its taking?

 

Answer:

Using CA Thread Term/Dynam DSNZPARM, go to menu item 2 "Active thread list"

Select your running thread with an "S", look for the STATUS column to be "INDB2". You have to be quick enough to catch it before it ends!!!!

Choose option "SQLCall" option "C".

If your SQL is still executing it will display that SQL.

On the "Thread Terminator SQL Call Display" displaying the SQL you have an "E" Explain option. CA Plan Analyzer for DB2 for Z/OS should be available.

Using "E" takes you to the CA Plan Analyzer for DB2 for Z/OS(PPA) , Quick Explain Data Editor screen where you can then carry out an explain with the EXPLAIN command having captured the SQL. The EXPLAIN command in PPA will display an "Explain Options" screen where you can select the options for the explain. Having done that and pressing enter will carry out the explain online with the MODE "O" online option or you can send the explain cards to a dataset in order to do the explain in batch using MODE "B" for batch with Batch Processor at some later time.

This will do an Enhanced Explain of the SQL that was executing.

Note that if the SQL is too fast to be caught by PTT another option is to use CA Detector for DB2 for Z/OS and capture the SQL using a collection. You can then find the PLANNAME that was executing within the Collection Interval and drill down to the SQL you wanted to explain and carry out an ENHANCED EXPLAIN again via PPA.