It sounds like you have could have into an issue where the program object has run out of space to store the Access plan.
Are the SQL statements static?
The command PRTSQLINF will allow you to see the SQL information stored in the program object.
If it is an issue of program size, recompiling the program will clear out all the static access plans.
Converting the SQL to dynamic SQL will potentially solve the issue, since the Access plan is not stored with the program object.
It may be better to ask why the program needs to constantly rebuild the access plan, and if a redesign of the SQL is in order.
Using parameterized SQL may increase performance since the system can look up the access plan independently of the values in a where clause.
i.e. (SELECT FIELD LIST FROM FILE WHERE Field1 = ? and Field = ?)
FYI,
I had a SQL-RPGLE program with a static SQL statement that was accessing an alias. The alias was being recreated at least times per program call.
The alias was being recreated in QTEMP to point to a file in different library and each time the statement was executed over the new alias.
Over the course of 12 months the Access Plan for the static SQL exceeded the max for the program object and the SQL statements began failing.
I ended up converting the program to a Dynamic SQL-RPGLE program.
Chris Hiebert
Programmer/Analyst
Disclaimer: Any views or opinions presented are solely those of the author and do not necessarily represent those of the company.
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Dave Shaw
Sent: Monday, December 09, 2013 10:23 AM
To: RPG programming on the IBM i ( AS/400 and i Series)
Subject: SQLRPGLE performance problem after update from 6.1 to 7.1
We recently (finally!) upgraded our production partition from 6.1 to 7.1.
We have a service program written in SQLRPGLE that handles all the I/O access to our manufacturing Bill of Materials application. It took some effort to get it right, but it was working quite well on 6.1. At any given time, it's servicing dozens of jobs on the system.
One of the procedures is called repeatedly from a particular set of reports. Since we did the upgrade, these reports have gone from running in under 10 minutes time to running for hours on end. When we look in the job log, we see information message SQL7917, Access plan not updated. The second level text says:
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.
Does anyone know if there is any alternative to trying to find a time when one of the reports using this procedure can get exclusive access to this service program? This is a very large production system with hundreds of user jobs running at any given time, and as I said earlier, this service program is normally being used by dozens of them. We have one very small window on Sundays when we do dedicated promotions from development to production. Would re-promoting this service program also update the access plan? We use Implementer for change management.
Thanks for any advice.
Dave Shaw
Mohawk Industries
--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L) mailing list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
http://archive.midrange.com/rpg400-l.
midrange.com
