Midrange News for the IBM i Community

Posted by: Ken Lauder
Fort Worth, TX
Shared Service Programs & Activation Groups
has no ratings.
Published: 13 Sep 2012
Revised: 23 Jan 2013 - 4055 days ago
Last viewed on: 01 Mar 2024 (5147 views) 

Using IBM i? Need to create Excel, CSV, HTML, JSON, PDF, SPOOL reports? Learn more about the fastest and least expensive tool for the job: SQL iQuery.

Shared Service Programs & Activation Groups Published by: Ken Lauder on 13 Sep 2012 view comments(3)

We are currently developing a custom ERP system on V5R4M5 (soon to be 7.1).  Our business model dictates packaged products require too much customization.  


Our first test of the ERP system was a few weeks ago.  With the write-once, share-many type architecture our programmers have written many SQLRPGLE GET programs that are called by other programs.  These GET programs are also using SQL mostly in the form of SELECT * for getting the data.  I am trying to get them to be column selective so indexing will help but we still have a memory issue.


As users drill through menu options from order entry, to parts lookup, etc. their jobs can exceed 120MB.  The further they drill down the more activation groups they acquire since they are all *NEW.  They are using work files instead of dynamic memory.


What is the best approach for better memory utilization?  Because the activation groups are using so many modules and we cannot share the activation groups I fear the more users we put on the box the worse it will get.  I believe more direct SQL coding in the programs without trying to share service programs is the answer but I'm hitting a wall with the CIO and programmers.



Return to midrangenews.com home page.
Sort Ascend | Descend


(Sign in to Post a Comment)
Posted by: bobcozzi
Site Admin ****
Comment on: Shared Service Programs & Activation Groups
Posted: 11 years 5 months 17 days 16 hours 55 minutes ago

You already identified your problem. This notion of "information hiding" doesn't work well when performance is an issue. Embedded SQL is a good enough performance feature to use vs native database I/O (assuming you're retrieving multiple records). There is little reason to move the I/O not only into SQL but into subprocedures in service programs that are effectively eating up your system.

So the 2 things you're doing poorly are:

  1. ACTGRP(*NEW) on a *SRVPGM is almost NEVER recommended.
  2. SELECT * FROM... is NEVER recommended, and is in fact contrary to why you would hide the data I/O; isn't it?

For activation groups, I recommend either Named or *CALLER. For programs either Named or *NEW--rarely would I use *CALLER for a program, but if it works, that's fine too.

Remember, SQL is not native, so it does a call to another program that calls another program that calls another program etc... native I/O would be faster for single-record I/O in 99.9% of the cases.

But to be blunt, I do find that the number one issue with poor performing routine, recently written are due to Programmer Pride of Ownership/Authorship. In most cases they don't want to look at how many variables they've declared or how much data is being returned by a subprocedure (as a return value, not a parameter) and think just because it compiles its "good enough".

Other Things to Consider:

Do your subprocedures that access SQL declare lots of variables or large (huge) data structures to store the data in? just because RPG lets you declare 32k fields doesn't mean it will create/destroy them in a time that is favorable to performance. In most cases, when several variables are declared, they should NOT be declared in the Subprocedure but at the global level--or better yet, redesign the routine so it doesn't need so many variables, or consider using User Spaces.

Are you returning large data structures on Subprocedures? I mean the actual Return Value (using the RETURN option to send the data back to the caller--as a "Factor 2" of the RETURN opcode)? Anything larger than 10 bytes or so (16 is maximum) is crappy performance and shouldn't done until V7r1 is installed and you include the RTNPARM keyword. Otherwise, only do that on one-off subprocedure. For example, in cozTools we have an EDITDATE procedure that returns a character string. That string is more than 16 bytes on its declaration, but is often less when it is returned. However, if you are returning 200-byte data structures, or worse, an array, your performance will suck.

That last thing is recursion. While subprocedures are cool in that that they can be called recursively, as are service programs, this is huge performance hit. Should only be used when formulas call for it, and when they do, try to rewrite them so they do not.

Posted by: neilrh
Premium member *
Jackson, MI
Comment on: Shared Service Programs & Activation Groups
Posted: 11 years 5 months 16 days 22 hours 50 minutes ago
Edited: Fri, 14 Sep, 2012 at 12:30:29 (4186 days ago)

A thing I tend to do is, group procedures into families, declare the files (or fields globally), and each business function will begin with something like: 

p GetPatternSize  b                   export                   
d GetPatternSize  pi                  extproc('GetPatternSize')
d  PartNo                        8p 0 const 
  // Return parms

     if PartNo <> File.PartNo;

  // Business logic to load pattern size data to return parms

p GetPatternSize  e

 There is only one place I get the data from file, and that is generally private to the family module source. Now the LoadInventoryData procedure may be a chain or sql fetch (most likely a chain in the above case). This style allows me to call many related "get" procedures in sequence, where the first called will trigger a data load, and each subsequent will use the already loaded data. I'm also not batting around large data areas of data between programs - the business logic is all contained in the procedure, the calling program does not need to receive the file data, and selectively pass it off to procedures to make decisions for it (because when the rules change, you have to add more parms in addition to the coding changes - when the data is self contained in the procedure and it's relatives, the program merely asks a question using a key field).

Posted by: Ringer
Premium member *
Comment on: Shared Service Programs & Activation Groups
Posted: 11 years 5 months 16 days 21 hours 13 minutes ago

ACTGRP *NEW is killing you, all that activation/destroying on every round trip call, all those expensive file opens/closes. We just use a named AG with our company name for *SRVPGM's. *PGM are *CALLER or a named AG. And we keep files open until no longer needed.

And did you code your SQL so the ODP's can be reused on the 3rd subsequent SQL OPEN? If you're not seeing "ODP Reused." in the jog log, you want to investigate. That's a time saver too.

Chris R