Re: PRTSQLINF

[Joe Pluta <joepluta@xxxxxxxxxxxxxxxxx>]

The first report says "ORDER BY PRODCD" while the second does not.  Is 
there a reason for this? Did you specify an ORDER BY in the program?

Joe

> 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