Re: PRTSQLINF

[Jon Paris <jon.paris@xxxxxxxxxxxxxx>]

Answers in-line Birgitta


Jon Paris
Partner400.com

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

On May 16, 2011, at 7:19 AM, midrange-l-request@xxxxxxxxxxxx wrote:

> Did you recompile your program on V7?

Yes

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

Yes - several times.

> 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.

No conversion. Program was compiled from source on V7

> 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).

Yup - that's the theory.

> 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.

Good point - but I'd still like to know why it happens.

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

Yep - but this is not dynamic - it is static.