RE: Resetting the Associated Space of an SQL program

["Richard Jackson" <richardjackson@xxxxxxxxxxxxxxxxxx>]

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

-----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 12:45 PM
To: MI400@midrange.com
Subject: Resetting the Associated Space of an SQL program




I'm currently experiencing problems on my systems that have been identified
and
fixed by APAR 89097:

   After putting on SF99104 version 8, BPICS applications slowed
   down.  STRDBG UPDPROD(*YES) showed msgCPI4323 RC1 subtype x/800C
   F/QQQVAP PROC/QQQSNDPMSG T/QSQOPEN PROC/FULL_OPEN. For every
   CPI4323 there was a msgSQL7917 F/QSQOPEN PROC/FIXPGM T/QSQOPEN
   PROC/OPQRYDBM.  Using DSPOBJD on bpics_program_names to find
   program size showed that many programs were over 16 million
   bytes.  There was no room to store the rebuilt access plan.
   Recompiling the programs reduced the program size and
   performance returned to acceptable levels.  Unfortunately, the
   programs filled back up with hours.  SF60758 (in Database Group
   PTF SF99104 version 8) added a code path that changed how SQ
   component handled access plan sizes.  This change caused the
   program to reach it's limit.  This caused the performance
   degradation due to the system having to handle SQL7917 for every
   CPI4323.  Once the fixing PTF is applied, users will need to
   recompile their programs to reduce their size.  Removing SF60758
   is not recommended.


For the majority of the programs, I have been able to re-compile the code no
problem. For the other programs, I do not have the source and was wondering
if
there was a way via MI instructions (MODS, etc) to reset the associated
space
size of a program so that the template and associated space (foregoing valid
access plans) would be the same as if I had re-compiled the program?

I have tried the following approaches:

CHGPGM FRCCRT(*YES)

and the folling MI program:

#include <QSYSINC/MIH/RSLVSP>
#include <QSYSINC/MIH/MODS>
#include <STRING.H>

void main(int argc, char **argv) {

  _SYSPTR           pgm_ptr;
  _SPCPTR           sp;
  _SPC_MOD_T        mod_t;

  pgm_ptr = rslvsp(_Program,  argv^2], argv^1], _AUTH_OBJ_MGMT);
  memset(&mod_t, 0, sizeof(_SPC_MOD_T)); /* Clear out the template */

  mod_t.Size = (argv^3])?atoi(argv^3]):0;
  mod_t.Modify_Size = 1;
  mod_t.Modify_Auto_X = 1;
  mod_t.Auto_Extend = 1;
  mods2(pgm_ptr,&mod_t); /* Set space attributes */

}


With the MI/C program I get some success when I pass a large enough new
space
size (less than the existing space size though). Is there also a way to
re-initialise the access plan for an SQL object via MI or is that type of
question more appropriate for one of the other midrange lists?! If someone
has
another approach, I'd be grateful to hear it.

Thanks

Chris.



--
Chris Roberts (mailto:Chris.2.Roberts@sb.com).
Information Resources Standard Applications, SmithKline Beecham PLC,
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
+---

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