Re: SQL7917 Access plan not updated

I opened PMR 24113,211,788 SQL7917 Access plan not updated.

Thanks,
-Arco

2016-12-20 13:31 GMT+01:00 Rob Berendt <rob@xxxxxxxxx>:

There are some strange things going on with access plans stored in the
programs on IBM i 7.3.
I hope you open a pmr.

Too often lately IBM is giving me fixes or work arounds with zero effort
to find out why the behavior started and needed the fix lately.

PMR 53903,082,000: MCH3203 pointing to access plan issue stored in
program. Workaround: Change QAQQINI to force a rebuild of the access
plan. IBM is still taking a look at this one. However "requests for
update" are not being answered. Holy Days start early for the development
staff? I know that my kids are on break, which in their case means the
coach can schedule two-a-days! Oh goody we get to wake up earlier!

PMR 44475,082,000: Performance problem just started with similar data
load, system load, etc. IBM: Build the suggested indexes. Me: Why did
it just start? IBM: I'll send you a link to a document which says the
reason can be blamed on everything short of George Bush but just build the
indexes.

Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1
Group Dekko
Dept 1600
Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





From: Arco Simonse <arco400@xxxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Date: 12/20/2016 06:37 AM
Subject: SQL7917 Access plan not updated
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>



Hi,

From time to time (and it's going to happen more frequently 2 or 3 times a
day) we are getting the message below in our webserver CGI joblogs. Ending
the CGI jobs or restarting the webserver instance helps releasing the
locks, and then it is ok for a while.

We certainly have some handler programs running in the CGI jobs which run
in named activation groups and keep their files open for performance
reasons. I guess these must have to do with the issue. But we were doing
so
for years and never had these issues before. It seems to have started
since
7.3... We are on the latest cume.

The message description says: "See previous messages from the query
optimizer to determine why the access plan has been rebuilt."
But I cannot find that information, where should I look for these query
optimizer messages?

Thanks,
-Arco

Message ID . . . . . . : SQL7917 Severity . . . . . . . :
00
Message type . . . . . :
Information
Date sent . . . . . . : 20/12/16 Time sent . . . . . . :
08:25:49


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.

Display Message Details

Message ID . . . . . . : SQL7917 Severity . . . . . . . : 00
Date sent . . . . . . : 20/12/16 Time sent . . . . . . : 08:25:49
Message type . . . . . : Information
From . . . . . . . . . : QTMHHTP1 CCSID . . . . . . . . : 65535

From program . . . . . . . . . : QSQRUN3
From library . . . . . . . . : QSYS
From module . . . . . . . . : QSQOPEN
From procedure . . . . . . . : SNDINFOMSG
From statement . . . . . . . : 41952

To program . . . . . . . . . . : QSQRUN3
To library . . . . . . . . . : QSYS
To module . . . . . . . . . : QSQOPEN
To procedure . . . . . . . . : STORE_ACCPLN
To statement . . . . . . . . :
24841
--
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.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD


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

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD