Dynamic Plan Stability - Capture statements from explain

If we want to capture a certain dynamic statement for dynamic plan stability, the statement has to reside in the dynamic statement cache. Than you have to search for the statement id and with the statement íd you're able to capture a certain statement.

It can be quite hard to find the statement in the dynamic statement cache (think of a batch job running only once a day) because the residency of the statement in the dynamic statement cache is very short.

There should be a possibility to do an explain of the statement which captures the statement (stabilizes it ) in dynamic plan stability tables.

Here is my current workaround:

//XCA10A1S  JOB 'SY832',PASCHKE,                                       
//         CLASS=A,MSGCLASS=H,MSGLEVEL=(1,1),NOTIFY=XCA10A1,SCHENV=DB27
//  SCHEDULE HOLDUNTL=('02:10','12/20/2018')                           
//JOBLIB  DD  DISP=SHR,DSN=SCD.DB27888.A.DSNLOAD                       
//**********************                                               
//** STEP0010 Löschen **                                               
//**********************                                               
//STEP0010 EXEC DB27TEP2                                               
//SYSIN    DD *                                                        
TRUNCATE TABLE XCA10A1.DSN_STATEMENT_CACHE_TABLE;                      
COMMIT;                                                                
//**********************                                               
//** STEP0020 Explain **                                               
//**********************                                               
//STEP0020 EXEC DB27TEP2                                               
//SYSIN    DD *                                                        
EXPLAIN STMTCACHE ALL;                                                 
COMMIT;                                                                
//*                                                                    
//**********************                                               
//** STEP0030 Select  **                                               
//**********************                                               
//STEP0030 EXEC DB27TEP2                                               
//SYSREC00 DD DISP=SHR,DSN=XCA10A1.DB27.SYSREC00.DATA                  
//SYSPUNCH DD SYSOUT=*                                                 
//SYSPRINT DD SYSOUT=*                                                 
//SYSTSPRT DD SYSOUT=*                                                 
//SYSTSIN   DD *                                                       
  DSN SYSTEM(DB27)                                                     
  RUN PROGRAM(DSNTIAUL) PARMS('SQL')                                   
//SYSIN    DD *                                                        
 SELECT                                                                
 CHAR('-STA DYNQUERYCAPTURE STBLGRP(SEPA) STMTID('),                   
 CHAR(STMT_ID),                                                        
 CHAR(')'),                                                            
 CHAR('                                    ')                          
   FROM XCA10A1.DSN_STATEMENT_CACHE_TABLE                              
   WHERE  STMT_TEXT LIKE ' SELECT DISTINCT swap.KEY_BOOKING_DATE,%'    
   AND SCHEMA = 'XCTC233'                                              
   AND LITERAL_REPL = '';                                              
//*                                                                    
//**********************                                               
//** STEP0040 -STA DYNQUERY                                            
//**********************                                               
//STEP0040 EXEC PGM=IKJEFT01,DYNAMNBR=20                               
//SYSTSPRT DD  SYSOUT=*                                                
//SYSPRINT DD  SYSOUT=*                                                
//SYSUDUMP DD  SYSOUT=*                                                
//SYSTSIN  DD  *                                                       
DSN SYSTEM(DB27)                                                       
//         DD DISP=SHR,DSN=XCA10A1.DB27.SYSREC00.DATA                  
//**********************                                               
//** STEP0050 -STO DYNQUERY                                            
//**********************                                               
//STEP0050 EXEC PGM=IKJEFT01,DYNAMNBR=20                              

The workaround has several restrictions:

1) You can't be sure that the statement is in DSC when the job is running

2) The access path of the statement found in DSC may not be the desired one.

The benefits:

We'ld have an excellent possibility to stabilize statements for the purpose of adhoc handling in case of  turning accesspathes.

Eventually we could avoid prio 1 PMRs as we have a workaround with stabilizing the statements.