RE: Resetting the Associated Space of an SQL program

So the 16 meg limit still applies to program associated spaces ... and the
fix in V4R3 to make SQL packages extend of to 15,000 statements or 500
megabytes applies to SQL packages but not to program associated spaces.
That's too bad.  Therefore, SQL packages have stopped using program
associated spaces to store access plans or SQL packages use some form or
feature of associated space different from that available to a regular
program.  That is interesting.

I would be complaining to IBM about the way that this works.  Something
like, "please give me a switch to tell database, if you have to rebuild the
plan for a statement, just replace the old plan, don't make the space grow".
My argument would go something like, "SQL packages, especially in program
associated spaces, are an IBM thing, we cannot turn them on or off.  You do
not insist that all my SQL statements use qualified names so the use of
unqualified names and library lists is a normal behavior.  The ability to
ship programs without source has been happening since there were program
products - I think that IBM started the practice in the 50's - another very
normal behavior.  It is IBM code that decides to rebuild access plans and
extend the space.  It is IBM code that doesn't deal well with running out of
space.  Documented or not, there isn't anything that I can do to prevent
this from happening expect to recompile the program when it breaks and I
cannot recompile programs for which I have no source.  The other symptoms
that I experience are completely ordinary.  This is a design bug."  The
operative word here is "bug".  If it is an enhancement, the pressure is off.
I would insist that it is a bug.  It probably won't get you anywhere but it
is worth a try.  Is Kent Milligan hanging out around here somewhere?  What
do you think Kent?

Can you explain what is meant by, "that prevented locking of the access
plan - which broke the "extendibility" of the associated space".  In your
code, you were marking the associated space as extendable and you reported
that the associated spaces were growing - up to 16 meg.  That tells me that
the extendibility is not broken.  What locking are you referring to and what
extendibility is broken?

One final suggestion.  Recreate the programs that you can and save copies.
When the production versions grow too large, use create duplicate object to
replace them.  Go to your install tapes for the product and restore pristine
copies of the programs for which you have no source.  When those blow the
space size, use create duplicate object to reset the space.  This is like
killing flies with a sledgehammer but it will work.

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

-----Original Message-----
From: owner-mi400@midrange.com [mailto:owner-mi400@midrange.com]On
Behalf Of Chris.2.Roberts@sb.com
Sent: Tuesday, August 29, 2000 11:42 PM
To: MI400@midrange.com
Subject: 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
+---

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