Re: PRTSQLINF

[Luis Rodriguez <luisro58@xxxxxxxxx>]

Jon,

Besides Rob's comment about the ORDER BY clause, I seem to remember that in
7.1 the query optimizer can make changes in real-time to the execution plan,
while the SQL is running. IIRC, this is called Adaptive Query Processing
(ADP). I wonder if this makes less valid the storing of the access plan
along with the program object?

Regards,

Luis Rodriguez
IBM Certified Systems Expert — eServer i5 iSeries
--



On Mon, May 16, 2011 at 9:20 AM, Jon Paris <jon.paris@xxxxxxxxxxxxxx> wrote:

> 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.
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.