Resetting the Associated Space of an SQL program

[Chris.2.Roberts@xxxxxx]

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