Plan Analyzer : Explain with option FILTER results in message: No Explainable SQL statements to process. Check EXPLAIN object(s) for validity

Products

• Plan Analyzer for DB2 for z/OS

Issue/Introduction

If the FILTER option is specified like this example:

.CALL EXPLAIN                                                       
.DATA                                                               
   RULESSID   = (ssid)                                              
   ACM        = (N,DEMOACM)                                         
   STRATEGY   = (ssid,,DEMOADM)                                     
   PLANTAB    = (ROLLBACK)                                          
   EXPLTYPE   = (FUTURE)                                            
   TARGET     = (ssid(RBCRULE))                                     
   PERFTIE    = (N)                                                 
   REPORT     = (ACCESS/SHORT,SUMMARY)                              
   FILTER     = (SELECT)                                            
   SRCSQL     = (select * from sysibm.syscopy where ICTYPE = 'A' ;) 
.ENDDATA                                                            

 

This message will be seen:

 
No Explainable SQL statements to process. Check EXPLAIN object(s) for validity 
RETCODE = 0 Explain processing completed.

This is despite the SQL actually containing a select statement.

 

Environment

Release: R20
Component: PPADB2

Resolution

The FILTER card performs an exact text match (which honors case).

So this filter is used:

    FILTER = (SELECT)
 

and your SQL is coded this way:

    select * from sysibm.syscopy ...
 

there won't be a match.

This filter should be specified:

 
FILTER = (select)

Note these filters can be specified like this example that covers different versions of select: 
FILTER = (SELECT,Select, select)

Additional Information

Enhanced Explain Cards