Detector : Explain cards produced by Detector Batch Reporting.
search cancel

Detector : Explain cards produced by Detector Batch Reporting.

book

Article ID: 9394

calendar_today

Updated On:

Products

Plan Analyzer for DB2 for z/OS Detector for DB2 for z/OS

Issue/Introduction

Detector Batch Reporting can generate Plan Analyzer EXPLAIN control cards for reported SQL and can write them to a sequential file. These cards can then be processed using Plan Analyzer.

The EXPLAIN control cards are generated for reported SQL and are written to a sequential file normally with a data set using the PPAEXPL DD card.

A license for Plan Analyzer is required to use this option.

To generate control cards for use by Plan Analyzer to perform EXPLAINs on your SQL, a sample JCL job is provided, located in high-level.CDBAJCL(PDTBATCH).

PDTBATCH is the same job that is used to execute the batch reporting facility.

The EXPLAIN parameter controls whether control cards are generated and written to a sequential file for the SQL that matches the filtering and selection criteria you have specified.

To generate the cards, include EXPLAIN=Y in your SYSIN section and specify a target data set where the data will be written in the PPAEXPL DD.

The above information comes from the Detector User Guide however the control cards generated by PDTBATCH to the PPAEXPL DD are NOT complete and ready for execution!!

The explain cards produced by PDTBATCH must be enveloped with the full card deck for a Plan Analyzer Explain. PDTBATCH can't do that since that is not it's function and also the variety of options and settings available in a Plan Analyzer Explain mean that the end user should prepare them. It would still be a manual process to add the PDTBATCH explain cards to a JCL deck ready for the explain.

There is another way!!

Environment

Release : R20

DB2 for Z/os.

Resolution

The solution to this manual process is to have a JCL to execute the Explain Control Cards produced by PDTBATCH in one job stream.

The four steps of this JCL include:

1. Create a work dataset for the explain deck and input the "header" explain control cards.

2. Run PDTBATCH to generate the Explain Control cards for the SQL picked up by Detector.

3. Add a final control card to finish off the complete explain control card deck.

4. Submit the built explain control card deck with Batch Processor and delete the work dataset.

 

//USERIDA JOB CARD
//*----------------------------------------------------------
//*   STEP 1
//*   ALLOCATE DATASET PPAEXPL FOR INPUT TO PDTBATCH.
//*   INPUT TO SYSUT1 THE FIRST GROUP OF EXPLAIN CARDS
//*   AND OUTPUT TO SYSUT2 FOR THE NEXT STEP TO USE.
//*   DATASET USERID.PPAEXPL WILL BE PASSED TO THE NEXT STEP.
//*   A DATASET CONTAINING THE CARDS COULD ALSO BE READ INTO
//*   SYSUT1 INSTEAD OF THE INSTEAM CARDS BELOW.
//*   ALTER THESE CARDS AS APPROPRIATE FOR YOUR REQUIREMENTS. 
//*
//*----------------------------------------------------------
//STEP1    EXEC PGM=IEBGENER
//SYSIN    DD  DUMMY
//SYSPRINT DD  SYSOUT=*
//SYSUT2   DD  DSN=USERID.PPAEXPL,DISP=(NEW,PASS),
//             UNIT=SYSDA,
//             DCB=(LRECL=80,BLKSIZE=800,RECFM=FB),
//             SPACE=(TRK,(15,10))
//SYSUT1   DD  *,DCB=(LRECL=80,BLKSIZE=800)
.CALL EXPLAIN
.DATA
  RULESSID = (SSID)
  ACM      = (N,USERIDB)
  VERSION  = (LATEST)
  STRATEGY = (SSID,,USERID)
  PLANTAB  = (ROLLBACK)
  SQLQUAL  = (USERID,OVERRIDE)
  VIEWQUAL = (USERID)
  CASE     = (UPPER)
  LINES    = (60)
  PROCDDF  = (N)
  FLOATFMT = (SCI)
  PROCVIEW = (Y)
  EXPLTYPE = (CURRENT)
  ISOLATE  = (CS)
  VSAM     = (N)
  TARGET   = (SSID(@DEFAULT))
  PERFTIE  = (Y)
  REPORT   = (SUMMARY,COST,SUPPRPTS,ACCESS/SHORT,PREDICATE,
              DEPENDENCY/SHORT,RI,SQLRULE,PREDRULE/SHORT,PERF/LA,
              PLANRULE)
//*----------------------------------------------------------
/* ALTERNATIVELY A DATASET COULD BE USED TO INPUT THE EXPLAIN
/* CARDS.
/*
/* //SYSUT1   DD  DSN=USERID.INPUT.EXPLAIN.CARDS,DISP=(OLD)
/*
//*----------------------------------------------------------
//*   STEP 2
//*   RUN PDTBATCH WITH EXPLAIN=Y TO OUTPUT TO DD PPAEXPL
//*   WITH MOD SO THAT IT APPENDS THE EXPLAIN CONTROL CARDS
//*   TO THE END OF DD PPAEXPL
//*   ALTER THE SEARCH CRITERIA INPUT TO SYSIN BELOW AS REQUIRED.
//*   THE CARDS COULD ALSO BE READ IN FROM A DATASET TO SYSIN
//*
//*----------------------------------------------------------
//STEP2    EXEC PGM=PTLDRIVM,PARM='EP=PDTBATCC'
//STEPLIB  DD DISP=SHR,DSN=HLQ.CDBALOAD
//         DD DISP=SHR,DSN=SSID.PRIVATE.SDSNEXIT
//         DD DISP=SHR,DSN=HLQ.SDSNLOAD
//PTILIB   DD DISP=SHR,DSN=HLQ.CDBALOAD
//         DD DISP=SHR,DSN=SSID.PRIVATE.SDSNEXIT
//         DD DISP=SHR,DSN=HLQ.SDSNLOAD
//PTIPARM  DD DISP=SHR,DSN=HLQ.CDBAPARM
//*
//PPAEXPL  DD  DSN=USERID.PPAEXPL,DISP=(MOD,PASS)
//*
//SYSOUT   DD  SYSOUT=*
//SYSPRINT DD  SYSOUT=*
//SYSUDUMP DD  SYSOUT=*
//SYSIN    DD  *
 SSID=SSID
 DATASTORE=DATASTOR
 VCAT=PDT.DATASTOR.VCAT
 PRINT=Y
 FORMAT=DETAIL
 REPORT=PROG
 SORTMSG=Y
 EXPLAIN=Y
 STARTDATE=YYYY/MM/DD
 STARTTIME=(HH:MM)
 ENDTIME=(HH:MM)
 ENDDATE=YYYY/MM/DD
//*----------------------------------------------------------
/* ALTERNATIVELY A DATASET COULD BE USED TO INPUT THE PDTBATCH
/* CARDS.
/*
/* //SYSIN    DD  DSN=USERID.INPUT.PDTBATCH.CARDS,DISP=(OLD)
/*
//*---------------------------------------------------
//*
//*   RUN IEBGENER TO APPEND A BATCH PROCESSOR REQUIRED
//*   .ENDDATA LINE TO DATASET USERID.PPAEXPL
//*   TO FINISH OFF THE EXPLAIN CARD DECK
//*
//*---------------------------------------------------
//STEP3   EXEC  PGM=IEBGENER
//SYSIN    DD   DUMMY
//SYSPRINT DD   SYSOUT=*
//SYSUT2   DD   DSN=USERID.PPAEXPL,DISP=(MOD,PASS)
//SYSUT1   DD   *
.ENDDATA
/*
//*
//*---------------------------------------------------
//*
//*   EXECUTE BATCH PROCESSOR AND INPUT THE EXPLAIN CARDS
//*   THAT HAVE BEEN BUILT IN DATASET USERID.PPAEXPL
//*   CHECK THE BATCH PROCESSOR .OPTION AND .LIST OPTIONS 
//*   THAT WILL BE USED AT YOUR SITE. THE GENERATED DATASET IS
//*   DELETED AT THE END OF THE JOB TO CLEAN IT UP. 
//*
//*---------------------------------------------------
//STEP4   EXEC PGM=PTLDRIVM,PARM='EP=BPLBCTL'
//STEPLIB  DD DISP=SHR,DSN=HLQ.CDBALOAD
//         DD DISP=SHR,DSN=HLQ.SDSNEXIT
//         DD DISP=SHR,DSN=HLQ.SDSNLOAD
//PTILIB   DD DISP=SHR,DSN=HLQ.CDBALOAD
//         DD DISP=SHR,DSN=HLQ.SDSNEXIT
//         DD DISP=SHR,DSN=HLQ.SDSNLOAD
//PTIPARM  DD DISP=SHR,DSN=HLQ.CDBAPARM
//PTIXMSG  DD DISP=SHR,DSN=HLQ.CDBAXMSG
//SYSOUT   DD SYSOUT=*
//PTIIMSG  DD SYSOUT=*
//UTPRINT  DD SYSOUT=*
//ABNLIGNR DD DUMMY
//BPIIPT   DD   DSN=USERID.PPAEXPL,DISP=(OLD,DELETE)
//BPIOPT   DD   *
.LIST      SYSOUT(X)
.OPTION NOERRORS NOSQLERRORS RETRY(01) NOBINDERRORS    +
  WRAPLINE
/*
//
 
------------------------------------------------------------
The full explain card deck generated by the above JCL would look like this:
 
.CALL EXPLAIN                            <<<< Step 1 : First section of explain cards
.DATA
  RULESSID = (SSID)
  ACM      = (N,AUTHIDB)
  VERSION  = (LATEST)
  STRATEGY = (SSID,,AUTHID)
  PLANTAB  = (ROLLBACK)
  SQLQUAL  = (AUTHID,OVERRIDE)
  VIEWQUAL = (AUTHID)
  CASE     = (UPPER)
  LINES    = (60)
  PROCDDF  = (N)
  FLOATFMT = (SCI)
  PROCVIEW = (Y)
  EXPLTYPE = (CURRENT)
  ISOLATE  = (CS)
  VSAM     = (N)
  TARGET   = (SSID(@DEFAULT))
  PERFTIE  = (Y)
  REPORT   = (SUMMARY,COST,SUPPRPTS,ACCESS/SHORT,PREDICATE,
              DEPENDENCY/SHORT,RI,SQLRULE,PREDRULE/SHORT,PERF/LA,
              PLANRULE)
  SRCPACK  = (SSID,LOCAL,colln,pkgname)     <<<< Step 2: from here cards are appended by PDTBATCH
.
.
.
.
  SRCPACK  = (SSID,LOCAL,colln,pkgname)
.ENDDATA                                     <<<< Step 3: End of card deck appended at end ready for submission

Additional Information

Unload Collection Data from a Data Store

Powered by Wolken Software