Can anybody explain what an SQL "access plan" is?
Jeff Berman
I have several jobs all running the same SQLRPG program at the same time.
Although everything works great, I noticed the following message in the
job log:
Message ID . . . . . . . . . : SQL7917
Message file . . . . . . . . : QSQLMSG
Library . . . . . . . . . : QSYS
Message text . . . . . . . . : Access plan not updated.
Message ID . . . . . . . . . : SQL7917
Message file . . . . . . . . : QSQLMSG
Library . . . . . . . . . : QSYS
Message . . . . : Access plan not updated.
Cause . . . . . : The query optimizer rebuilt the access plan for this
statement, but the program could not be updated. Another job may be
running the program. The program cannot be updated with the new access
plan until a job can obtain an exclusive lock on the program. The
exclusive lock cannot be obtained if another job is running the program,
if the job does not have proper authority to the program, or if the
program is currently being saved. The query will still run, but access
plan rebuilds will continue to occur until the program is updated.
Recovery . . . : See previous messages from the query optimizer to
determine why the access plan has been rebuilt. To ensure that the
program gets updated with the new access plan, run the program when no
other active jobs are using it.
- - - - -
Now, the program that generates this is called over and over again by each
of the jobs to perform the same function on the same files (the files, in
each job's QTEMP, are filled with fresh data in between calls). So it
sounds like all three jobs are sharing the same SQL "program".
I guess my questions are these: Is this message telling me that my query
is inefficient? If it *could* have rebuilt the access plan (whatever that
is!), would the query run faster or take less system resources? If so, is
there a way to force each job to have its own SQL program so that each job
could rebuild this access plan? And if not, then is there a way to tell
SQL not to even attempt rebuilding the access plan (surely it takes time
for it to rebuild this thing every time only to fail when it tries to
implement it)?
Any insight anyone would care to share would really help out a lot.
Thanks,
Jeff
Randy Egan
The SQL access plan is the information which is used by the database
runtime to access a specific instance of data for a program. For
pre-compiled programs, these access plans are stored in the program
object.
The QTEMP library is a separate library for each process on the
system. If your users are accessing files in there own QTEMP library,
they are all accessing different instances (copies) of data.
Therefore, the query optimizer must make a new plan. Without a new
plan, you would be accessing the wrong data. The same thing would
happen if you use OVRDBF or use the library list to allow users to see
different files.
If your users will be using the program multiple times with a current
session to access this temp data, you might consider doing a CRTDUPOBJ
of the program into their QTEMP. That way the access plan in their copy
of the program can be updated...
On the other hand, if they run the program once, and that's all for
their session, just continue letting SQL runtime create the temporary
access plans for each usage.
Randy Egan
Jeff Berman
> If your users are accessing files in their own QTEMP library,
> they are all accessing different instances (copies) of data.
> Therefore, the query optimizer must make a new plan. Without a new
> plan, you would be accessing the wrong data.
Thank you for taking the time to answer my question! I'd like to make
sure I'm understanding you, though. This is the message I'm getting:
<<<Cause . . . . . : The query optimizer rebuilt the access plan for this
statement, but the program could not be updated. Another job may be
running the program. The program cannot be updated with the new access
plan until a job can obtain an exclusive lock on the program. The
exclusive lock cannot be obtained if another job is running the program,
if the job does not have proper authority to the program, or if the
program is currently being saved. The query will still run, but access
plan rebuilds will continue to occur until the program is updated.>>>
The last sentence there is the one that's confusing me. From what you
said, it sounds like this SQL program could be run over the wrong data, is
that right? (Although, keep in mind that the program always runs over the
same two files which are in each job's QTEMP. The data in those two files
are changed before each call to the SQL program.)
Do you have any insight to what "The query will still run, but access plan
rebuilds will continue to occur until the program is updated" means? If
we've got three jobs, each with files in their own QTEMP, calling the same
SQLRPG at potentially the same time, is it possible that the queries will
run incorrectly? Or that performance will suffer? If this is the case,
then I like your idea of copying the program into QTEMP.
Thank you!!!
Jeff
Göran Boström
The user that runs SQL program needs to have *CHANGE authority to the
program to be able to update the access-plan.
Note that access-plan is not equal to an access-path. It is a plan that
suggest the best way to get you the data you asked for (perhaps read the
file sequential, build an index or some other method).
If the access-plan is saved in the program it wouldn't need tho make a new
access-plan each time the SQL statement runs.
Best regards
Randy Egan
Sorry to scare you. My comment about the wrong data was figurative in
that had the optimizer "NOT" rebuilt the access plan, the program would
be retrieving data from some other job's QTEMP library. But DB2 for
AS/400 wouldn't do that. Instead it probably is giving you a slight
performance degradation because subsequent usage of queries in the same
program and same job have to be recreated because they are not stored in
a permanent object.
Short answer: Data will be fine, performance might not....
Randy Egan, IBM Rochester
Igor A. Walter
> I'll probably end up doing some performance tests just for the heck of it;
> I'll post the results just in case anyone is interested.
Yes, I'm interested in your performance test as well as the solution you
took...
TIA, Igor
>
--
(opinions stated are not necessarily those of my employer)