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

book

Article ID: 49140

calendar_today

Updated On:

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