AW: PRTSQLINF

["Birgitta Hauser" <Hauser@xxxxxxxxxxxxxxx>]

Did you recompile your program on V7?
Did you run your program after moving to V7 and before executing PRTSQLINF?

I assume the access plan was removed from the program object during the
internal conversion for V7 and the program was not yet run to get the
new/updated version of the access plan stored.

Access plans get stored within the program objects at compile time and will
be updated with the first execution (or as soon as the stored access plan
must be updated).
IMHO access plans stored within a program object are no longer important.
For SQL statements executed with the SQE access plans stored within the SQE
plan cache are validated. If there is no access plan within the SQE plan
cache the access plan stored within the program object will be valided. If
there is no access plan found within the program object, a new access plan
will be created.

BTW access plans for dynamic SQL are never stored within the program
objects. 

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"


-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Jon Paris
Gesendet: Monday, 16. May 2011 08:54
An: Midrange-L Midrange-l
Betreff: PRTSQLINF

I have a program with embedded SQL that when compiled on a V5R4 shows this
at the end of the PRTSQLINF report.

DECLARE PRODCSR CURSOR FOR SELECT * FROM PRODUCT1                   
  SQL4021  Access plan last saved on 05/08/11 at 21:46:39.   <<<<<  Plan
found       
  SQL4020  Estimated query run time is 1 seconds.                   
  SQL4010  Table scan access for table 1.                           
OPEN PRODCSR                                                        
FETCH NEXT FROM PRODCSR INTO : H ,: H ,: H ,: H ,: H ,: H ,: H ,: H 
FETCH NEXT FROM PRODCSR INTO : H ,: H ,: H ,: H ,: H ,: H ,: H ,: H 

Same program, same data on a V7 system and the access plan is reported as
not found.


 DECLARE PRODCSR CURSOR FOR SELECT * FROM PRODUCT1 ORDER BY PRODCD     
   SQL5065  Access plan not found.     <<<<< No plan !!!

 OPEN PRODCSR                                                          
 FETCH NEXT FROM PRODCSR INTO : H ,: H ,: H ,: H ,: H ,: H ,: H ,: H   
 FETCH NEXT FROM PRODCSR INTO : H ,: H ,: H ,: H ,: H ,: H ,: H ,: H   
 CLOSE PRODCSR    


I'm guessing this may be related to the differences in the Query engine etc.
over the releases - but I can't find anything that describes why this
occurs. Searching IBM for SQL5065 only shows up DB2 V9 on Linux - not
terribly useful. 

Anyone got any ideas?  
                                                   

Jon Paris
Partner400.com

Need the best in SQL education? Check out www.DB2foriDirections.com