Looking to evaluate changed access paths after a BIND? Find out how Plan Analyzer Enhanced Explain Processing can be integrated into your Development cycle.

Products

• Plan Analyzer for DB2 for z/OS

Issue/Introduction

Plan Analyzer for Db2 for z/OS (PPA) Enhanced Explain Processing provides the ability to create and explain strategies in batch.
Utilizing this feature, steps can be incorporated into current application BIND jobs to automate a comparison of SQL, host variables, and access paths.
This process ensures that explain processing is run consistently (without intervention) and that historical comparisons are available for future reference.

 

Resolution

The JCL described below creates an Explain Strategy if it does not already exist or, optionally, adds a Strategy Version to the existing strategy. The first execution of this JCL creates a strategy and generates explain output. Subsequent executions will create a new strategy version and compare the explain output to the previous execution. You will need to insert this step after your current application's BIND maintenance cycle and add symbolic JCL to ensure unique strategy names are used. The process requires the strategy name and DBRM name have a one-to-one relationship, i.e. a unique strategy will be created for each DBRM. This can be achieved by making the strategy name (defined in the STRATEGY parameter) equal to the DBRM name (defined in the SRCDBRML parameter).

//STEP1    EXEC PGM=PTLDRIVM,REGION=4M,PARM='SUFFIX=00,EP=BPLBCTL' 
//STEPLIB  DD DISP=SHR,DSN=xxxxxxxx.LOADLIB 
//               DD DISP=SHR,DSN=ssid.PRIVATE.SDSNEXIT 
//PTILIB     DD DISP=SHR,DSN=xxxxxxxx.LOADLIB
//              DD DISP=SHR,DSN=ssid.PRIVATE.SDSNEXIT 
//CTRANS DD DSN=xxxxxxxx.CTRANS,DISP=SHR     
//PTIPARM  DD DISP=SHR,DSN=xxxxxxxx.PARMLIB   
//PTIXMSG  DD DISP=SHR,DSN=xxxxxxxx.XMESSAGE 
//SYSOUT   DD SYSOUT=*    
//PTIIMSG  DD SYSOUT=*   
//UTPRINT  DD SYSOUT=*         
//ABNLIGNR DD DUMMY                 SUPPRESS ABENDAID DUMPS 
//SYSUT1   DD UNIT=SYSDA,SPACE=(CYL,(30,30))   
//SYSREC   DD UNIT=SYSDA,SPACE=(TRK,(1,1))    
//BPIIPT   DD *            
.CALL EXPLAIN               
.DATA                  
   RULESSID   = (ssid)       
   ACM        = (N,TESTACM)   
   STRATEGY   = (ssid,DBRMNAME,xxxxxxx,@AUTO) 
   PLANTAB    = (ROLLBACK)     
   SQLQUAL    = (SYSIBM)     
   EXPLTYPE   = (FUTURE)     
   ISOLATE    = (CS)      
   SAVERPTS   = (Y)  
   VSAM       = (N)  
   TARGET     = (ssid)    
   DATABASE   = (AUTO,HVERNAME,DSNA)      
   REPORT     = (COMPARE,ACCESS,STATISTICS,PHYSRULE) 
   COMPOPTS   = (BBNBEE NN ,,N)       
   SRCDBRML   = (xxxxxxxx.DBRMLIB(dbrmname),) 
.ENDDATA                          
//BPIOPT   DD   *                       
.CONTROL BPID(BPIDNAME) +    
 LOGID(ssid) UNIT(SYSDA)          
.LIST SYSOUT(A)                      
.OPTION NOERRORS   SQLERRORS RETRY(88) NOBINDERRORS  
.CONNECT ssid                                           

Required Explain Parameters:
STRATEGY - this parameter identifies the subsystem, strategy creator and strategy name.
   To ensure consistency, make the strategy name equal to the DBRM name.
   In this example, both the strategy and DBRM name is "DBRMNAME".
   The strategy creator is xxxxxxx.
   The subsystem containing the strategy is ssid.
SRCDBRML - this parameter must define the DBRM library and member.
DATABASE - this parameter ensures that the explain reports are saved to the historical database (for subsequent analysis).
REPORT - use the Compare Report to compare the newest DBRM version to the next most recent one.

Results:

* Compare Analysis Report
The resulting Compare Analysis Report can optionally expound on changed/unchanged SQL, changed/unchanged Access Paths, and paired/unpaired SQL statements. These options are controlled via the COMPOPTS parameter.

* Viewing/Generating Reports
Through the use of the DATABASE option, vital explain information such as SQL, access path, rules violations, and statement information is saved to the historical database and can later be used to build online or batch reports. Access to the historical database is attained through the use of one of the following options: the Query Explain Database (#3), Compare Explain Versions (#4) or Identify Problem SQL (#12), displayed on the Plan Analyzer Main Menu.

Query Explain Database (#3) offers a multitude of reporting options. Explain, Cost, Stmt Type, Object and Information Summary reports can be generated on each item (or group of items) saved to the historical database; these reports can be limited in various ways including by the cost value (timeron value). Numerous other online reports such as Access Path, Predict Analysis and Object Summary can also be accessed for any item in the database.

Compare Explain Versions (#4) also offers a multitude of reporting options including the Compare Explain Versions Report. Any 2 items within the entire database can be chosen for input into the Compare Explain Versions Report. For example, if a historical version of a an explain existed via different strategies/collections/plans, these items could easily be selected as input into the compare report by using the CEVO (old) and CEVN (new) line commands on the PPA CEV Info. Summary screen.

Identify Problem SQL (#12) offers two powerful reporting options; the Identify Problem SQL Report and the Identify Rule Problems report. The Identify Problem SQL Report is based upon 1 or more search elements (joined by an AND or OR conjunction); these search elements are comprised of over 50 explain elements such as Sequential Prefetch, List Prefetch, Index Scan, Tablespace Scan, as well as Join, Locking and Sorting variations. Similarly the Identify Rule Problem Report is based upon 1 or more search elements (joined by an AND or OR conjunction); these elements contain over 100 rules available within Expert System Rules. Both the Identify Problem SQL and Identify Rule Problems Report can be run against 1 or more items in the historical database.

Using both the REPORTS and SAVERPTS parameters will direct Plan Analyzer to store reports with each Strategy Version, allowing you to examine the timeline of changes. As an example, suppose the Compare Analysis Report reported a change in the access path and you needed to find out why this change occurred, (suspecting that it was data related). If the explain has been executed with the STATISTICS keyword within the REPORT parameter, then DB2 catalog statistics for the referenced objects are permanently saved with each explain. To access these reports, enter an 'R' on the CMD line to the left of the Strategy Version on the PPA Explain Strategy Services screen and then select the desired report. This provides a huge advantage of being able to find the cause of the access path change when it's due to changed catalog statistics such as CLUSTERRATIO, NLEVELS, CARDF etc.