Explaining dynamic sql statements of a job

Products

• Bind Analyzer for DB2 for z/OS
• SQL-Ease for DB2 for z/OS
• SYSVIEW Performance Management Option for DB2 for z/OS
• Plan Analyzer for DB2 for z/OS
• Subsystem Analyzer for DB2 for z/OS
• Database Management for DB2 for z/OS - Performance Suite
• Database Management for DB2 for z/OS - SQL Performance Suite
• Insight Performance Monitor for DB2 UDB 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 using directly dynamic sql statements  (SYSIN) using the DSNTIAUL DB2 program.

Can Plan Analyzer to explain these jobs?

Environment

Release : 20.0

Component : CA Plan Analyzer for DB2 for z/OS

Resolution

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

/*

//