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 using directly dynamic sql statements (SYSIN) using the DSNTIAUL DB2 program.
Can Plan Analyzer to explain these jobs?
Release : 20.0
Component : CA Plan Analyzer for DB2 for z/OS
Plan Analyzer supports dynamic SQL through a couple of different approaches:
1) Using CA Detector, the dynamic SQL statements can be passed into Plan Analyzer to be explained.
2) A File source can be specified as input to PPA explain containing dynamic SQL statements separated by semicolon. The dataset can be either sequential or a PDS.
3) A SQL source can be specified as input to PPA explain. This would be specifying freeform SQL and just like file multiple SQL statements require separation by semicolon.
Here is an example on using a File to pass in dynamic SQL to be explained.
//STEP1 EXEC PGM=PTLDRIVM,REGION=0M,PARM='EP=BPLBCTL'
//STEPLIB DD DISP=SHR,DSN=PP.RD200.PRD.CDBALOAD
//PTILIB DD DISP=SHR,DSN=PP.RD200.PRD.CDBALOAD
//PTIPARM DD DISP=SHR,DSN=PP.RD200.PRD.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
RULESSID = (DB22A)
ACM = (N,CARDOA9)
STRATEGY = (DB22A,TESTEX2,CARDOA9,@AUTO,REPLACE)
PLANTAB = (ROLLBACK)
LINES = (55)
PROCDDF = (N)
FLOATFMT = (SCI)
SAVEHOST = (Y)
PROCVIEW = (Y)
EXPLTYPE = (FUTURE)
ISOLATE = (CS)
TARGET = (DB22A(@DEFAULT))
DATABASE = (AUTO,@FUTRDBA,DB22A)
REPORT = (ACCESS/SHORT,COST,DEPENDENCY/SHORT,PHYSRULE/SHORT,
PLANRULE/SHORT,PREDICATE,PREDRULE/SHORT,SQLRULE/SHORT,
SUMMARY,SUPPRPTS)
SRCFILE = (CARDO09.SQL.CNTL)
.ENDDATA
/*
//BPIOPT DD *
.CONTROL BPID(BP-CARDOA9-105240-20200916) +
LOGID(DB22A) UNIT(SYSDA)
.LIST SYSOUT(A,,)
.OPTION NOERRORS
.RESTART OVERRIDE
.CONNECT DB22A
/*
//