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

Products

• CA RC Compare for DB2 for z/OS
• CA Bind Analyzer for DB2 for z/OS
• CA SQL-Ease for DB2 for z/OS
• CA Sysview Performance Management Option for DB2 for z/OS
• CA Plan Analyzer for DB2 for z/OS
• CA Subsystem Analyzer for DB2 for z/OS
• CA Database Analyzer for DB2 for z/OS
• CA Fast Unload for DB2 for z/OS
• CA Fast Check for DB2 for z/OS
• CA Fast Index for DB2 for z/OS
• CA Fast Load for DB2 for z/OS
• CA Rapid Reorg for DB2 for z/OS

Issue/Introduction

Description:

If you specify a FILTER option like this example:

 

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

 

You may receive message:
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.

Solution:

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

So if you have this filter:

	FILTER = (SELECT)

 

and your SQL is coded this way:

	select * from sysibm.syscopy ...

 

there won't be a match.

You will have to specify this filter
FILTER = (select)

Note that you can specify more filters like this example that covers different versions of select:
FILTER = (SELECT,Select, select)

Environment

Release:
Component: PPADB2