RE: Resetting the Associated Space of an SQL program

Richard,

This is the original thinking that I obtained from as400service.ibm.com:


IBM released an APAR sa86910 (sf60758) which would not lock an access plan if it
is invalid:

   If the query optimizer has identified an access plan as not
   valid, then do not lock it on the next use.  This will allow
   the access plan to be updated in the program, even when there
   are multiple users of the program.

   Problem Conclusion
   If the access plan is no longer valid, make a copy and do not
   lock the original.  This will allow it to be updated when there
   are multiple users of the SQL program running simultaneously.

Whilst this apar was loaded, SQL programs continued to run and when the access
plan was updated, it was added to the end of the existing associated space
instead of rewriting the SQL-portion of the associated space from it's base. So,
over time, the 16Mb associated space limit was reached. When the limit was
reached, the access plan could not be updated. As we were using unqualified
tables, the access plan needed to be updated or it would be rebuild each program
call.

This was noticed by an IBMer and logged as a problem and fixed in sf62992 (see
my original posting which detailed this PTF).  This recommended re-compiling
programs to reduce the associated space size. Which is where I am now. Many
programs, full associated space, not much program source...

It's not quite as easy for me to re-install the original installation source.
Working in a Pharmaceutical environment, we need to ensure that our
installations are validated. It's easier to make a technical modification to the
program that doesn't  affect the program template or involve changing too many
of the original program attributes - hence my inclination to use MI :-)

I've had a little success with the approach you mentioned earlier - I got hold
of the associated space size for the original objects from our main development
system and modified the space size of the "larger" associated space objects to
be the same. So far there are no reported problems. It's a quick and dirty
"hack" but without knowing how the optimizer re-builds the plan and forcing it
to do so on an object without actually running it, there may be problems lurking
round the corner. It would be good to know what the SQL pre-processor
initialises the space to when the program is compiled. I guess I can test it
but, if someone else knows better, I'd prefer not to spend half the day with
DMPSYSOBJ.

Thanks

Chris.

PS. It was Kent Milligan/Mike Cain who brought this PTF to my attention when I
attended the SQL Performance Tuning and Query Optimization course here in the
UK.

--
Chris Roberts (mailto:Chris.2.Roberts@sb.com).
IRSA, SmithKline Beecham,
c/6 SB House, Great West Rd., Brentford, Middlesex, TW8 9BD, UK.
Tel: (+44)(0)20 8975-3146, Fax: (+44)(0)20 8975-3188.

DISCLAIMER: The views contained herein are those of the author
            and not of my employer, its subsidiaries or affiliates.


+---
| This is the MI Programmers Mailing List!
| To submit a new message, send your mail to MI400@midrange.com.
| To subscribe to this list send email to MI400-SUB@midrange.com.
| To unsubscribe from this list send email to MI400-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: dr2@cssas400.com
+---