× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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

As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.