Thread Termination\Dynamic DSNZPARM: How do I EXPLAIN SQL executing in an active thread?

Products

• Thread Terminator for DB2 for z/OS

Issue/Introduction

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?

 

 

Environment

Release: R20
Component: PTT

Resolution

Using 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. Plan Analyzer for DB2 for Z/OS should be available.

Using "E" goes to the Plan Analyzer for DB2 for Z/OS(PPA) , Quick Explain Data Editor screen where an explain can be carried out with the EXPLAIN command having captured the SQL. The EXPLAIN command in PPA will display an "Explain Options" screen where select options for the explain can be chosen. Then pressing enter goes to the online explain with the MODE "O" online option. Alternatively, 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 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 required to explain and carry out an ENHANCED EXPLAIN again via PPA.