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

Document ID : KB000049140
Last Modified Date : 14/02/2018
Show Technical Document Details

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)