Plan Analyzer : Explaining dynamic sql statements in a file

Products

• Plan Analyzer for DB2 for z/OS

Issue/Introduction

Running plan analyzer to explain the performance of batch/online programs based on their DBRMs.

There is a need to explain the performance of some jobs that are directly using dynamic sql statements  (SYSIN) using the DSNTIAUL DB2 sample program.

Can Plan Analyzer for DB2 for Z/OS(PPA) explain these jobs?

Environment

Release : 20.0

Component : Plan Analyzer for DB2 for z/OS

Resolution

Plan Analyzer supports dynamic SQL through a couple of different approaches:


1) Using Detector for Db2 for Z/os(PDT), the dynamic SQL statements can be passed into PPA to be explained.

2) A FILE source can be specified as input to the PPA explain containing dynamic SQL statements separated by semicolon. The dataset can be either sequential or a PDS.

3) An SQL source can be specified as input to PPA explain. This would be freeform SQL and just like FILE, multiple SQL statements require separation by semicolon.

Below is an example of using a dataset to pass dynamic SQL to be explained.

//STEP1 EXEC PGM=PTLDRIVM,REGION=0M,PARM='EP=BPLBCTL' 
//STEPLIB DD DISP=SHR,DSN=hlq.CDBALOAD 
//PTILIB DD DISP=SHR,DSN=hlq.CDBALOAD 
//PTIPARM DD DISP=SHR,DSN=hlq.CDBAPARM 
//* 
//SYSOUT DD SYSOUT=* 
//UTPRINT DD SYSOUT=* 
//ABNLIGNR DD DUMMY SUPPRESS ABENDAID DUMPS
//SYSUT1 DD UNIT=SYSDA,SPACE=(TRK,(1,1)) 
//SYSREC DD UNIT=SYSDA,SPACE=(TRK,(1,1)) 
//SORTOUT DD UNIT=SYSDA,SPACE=(TRK,(1,1)) 
//BPIIPT DD *
.CALL EXPLAIN 
.DATA <=========================================the explain cards being input to the EXPLAIN after this card
RULESSID = (ssid) 
ACM = (N,authid1) 
STRATEGY = (ssid,TESTEX2,authid1,@AUTO,REPLACE) 
PLANTAB = (ROLLBACK) <===============================we just want an explain report so we will ROLLBACK any updates to the PLAN_TABLE
LINES = (55) 
PROCDDF = (N) 
FLOATFMT = (SCI) 
SAVEHOST = (Y) 
PROCVIEW = (Y) 
EXPLTYPE = (FUTURE) <============ as the SQL is in a dataset and there won't be any explain data stored on a PLAN_TABLE we do a FUTURE explain. 
ISOLATE = (CS) 
TARGET = (ssid(@DEFAULT)) 
REPORT = (ACCESS/SHORT,COST,DEPENDENCY/SHORT,PHYSRULE/SHORT, 
PLANRULE/SHORT,PREDICATE,PREDRULE/SHORT,SQLRULE/SHORT,
SUMMARY,SUPPRPTS) 
SRCFILE = (authid1.SQL.SQL)               <====================================this dataset contains the SQL.
.ENDDATA                                  <====================================the last Explain card is the SRCFILE input to batch processor input DD. 
/* 
//BPIOPT DD * 
.CONTROL BPID(BP-authid1-FILEEX-SQL) +    <========== This BPID (Batch processor ID) can be anything that makes sense as a name...53 characters maximum! 
LOGID(ssid) UNIT(SYSDA) 
.LIST SYSOUT(A,,) 
.OPTION NOERRORS 
.RESTART OVERRIDE <=========================having read in the batch processor options to BPIOPT the BPIIPT cards are executed from the start.
.CONNECT ssid
/* 
//

Additional Information

Explaining SQL

Ensure that your authid.PLAN_TABLE is current of format relating to the release of DB2 being used.

See also:

How does Plan Analyzer assign creator of the PLAN_TABLE used in Explain?

Auto Creation of the PLAN_TABLE, PLAN_TABLE_HINT_IX and DSN_STATEMNT_TABLE during an EXPLAIN within Plan Analyzer for Db2 for z/OS and SQL-EASE for Db2 for z/OS