RE: Resetting the Associated Space of an SQL program

Richard,

I'm on V4R4 which is the release the APAR is for. The programs are SQL RPG (+
some ILE) for which I do not have the source to all of them. The reason the
access plan/associated space grows is that due to implementation, the SQL tables
are not qualified and therefore the access plan is re0build each time the
library list changes. IBM made a fix early on in V4R4 that prevented locking of
the access plan - which broke the "extendibiliy" of the associated space - hence
the replacement APAR.

Again, the access plan/associated space size will differ also - IBM has put
numerous PTFs out to change this. I did think of saving off the first 32768
bytes of a "good" SQL program and copying them into and re-sizing to 32768 the
space of a "bad" SQL program but not too sure if this will break something else
in the program template. I'll have another go at this.

Thanks

Chris.







"Richard Jackson" <richardjackson@richardjackson.net> on 29/08/2000 23:17:44

Please respond to MI400@midrange.com

To:   MI400@midrange.com
cc:
Subject:  RE: Resetting the Associated Space of an SQL program




Which OS release are you on?  I'll bet you are on V4R2 or earlier.  If you
are on a release before V4R4, it might be better to upgrade rather than to
play with associated spaces.  At V4R4, there is a thing called the
system-wide statement cache that is way better than SQL packages.

I'll bet V4R2 or earlier because 16 megabytes was the old size limit for SQL
packages. At V4R3 when it became about 500 megabytes or about 15,000
statements.

Are the programs for which you want to reset the associated space SQL
packages?  If they are SQL packages, just delete them and let SQL recreate
them.  If they are regular programs containing embedded SQL, then why do
they grow?  If they are regular programs, it sounds like they are doing
something wrong with prepares that causes this growth.  In fact, it sounds
like they shouldn't be saving the rebuilt access plans at all but I don't
know how to stop that.

Now that you have decided that none of the comments above apply to your
situation, compile two of the programs and look at the size of the
associated space.  If I recall (and it has been a VERY long time) an RPG
program associated space is something like 200 bytes (or two pages - that
was really a long time ago) plus the size of the saved SQL statements.  Each
access plan requires 32k.  Work your way backwards until you figure out how
big it should be then find the length or offset value near offset zero in
the associated space - use dmpsysobj to view the associated space.  If you
can't find the value for the offset, compile the program and record the
size.  If you want to reset it later, change it to the size that you
recorded.

Richard Jackson
mailto:richardjackson@richardjackson.net
http://www.richardjacksonltd.com
Voice: 1 (303) 808-8058
Fax:   1 (303) 663-4325










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