EXPLAIN for dynamic SQL.

[Raju Singh]

Hi,

Can we EXPLAIN the dynamic SQL in DB2 for zOS. If yes, can you please adivse on how to do that or direct me to a link. We are on DB2 9. I've searched enough but in vain.

[Anuj Dhawan]

The short answer for your version of DB2 is - yes, you can EXPLAIN dynamic DB2 queries. But they are NOT that straight forward.

For static SQL you can force developers to do explains with every pre-compile job. You can analyze and explain the statements in the packages easily. Using ODBC, JDBC, REXX or embedded dynamic SQL, the statements are prepared and executed dynamically and cannot be found in any package. As long as you know your SQL statement, you can explain it using the SQL EXPLAIN command. In some applications, like end user drivers, you even do not know exactly which statements will be executed dynamically. In this case the Resource Limit Facility (RLF) offers you the possibility to govern the resource usage.

Other possibilities to analyze a dynamic SQL statement are to explain it from the statement cache or to trace the access path information of its prepare by means of a DB2 trace. And as we talked about a 'trace', you'd need a monitoring tool to put a trace.

Suggest you've a look at Chapter 18 of this red-bbo: http://www.redbooks.ibm.com/redbooks/pdfs/sg246418.pdf

However, if your shop migrate to DB2 10, which they should - suggest you read about CURRENT EXPLAIN MODE. The CURRENT EXPLAIN MODE special register contains the values that control the EXPLAIN behavior in regards to eligible dynamic SQL statements. This facility generates and inserts EXPLAIN information into the EXPLAIN tables.

[Venkata Kumar]

We've used Mainview to put a trace and it has helped.

[Anuj Dhawan]

Yes - BMC MainView helps you to monitor system health and you can put traces on transactions etc. to analyze them.

[Raju Singh]

Thanks for the detailed explanation Anuj.

[Anuj Dhawan]

Thanks for the feedback and glad to see that it helped.

[Raju Singh]

yup, anytime!