RE: RPG programs as external stored procedures...

Running ILE programs in *DFTACTGRP is not recommended. If you specify a named activation group for your programs, that should move them out of *DFTACTGRP.

If your programs end with *inLR, they'll re-open files and other initialization upon activation. Leaving *inLR will probably give you some performance gain, but could cause issues since variables will carry over between runs (*inLR will cause all variables to be re-initialized).

Are your QZDASOINIT jobs running in *BASE memory pool?




-----Original Message-----
From: Robert Rogerson [mailto:rogersonra@xxxxxxxxx]
Sent: Friday, February 16, 2018 3:31 PM
To: RPG programming on the IBM i / System i <rpg400-l@xxxxxxxxxxxx>
Cc: Steve Kilner <stevekilner@xxxxxxxxx>
Subject: RPG programs as external stored procedures...

Hi All,

We have multiple stored procedures called from dotNet applications. Periodically the CPU percentage spikes and when I do a WRKACTJOB and sort by CPU % QZDASOINIT jobs are at the top of the list.

These jobs run in the *DFTACTGRP (do the have to or should they?).

Here is where I want to pick the group's brain.

A program does not specify any keywords on the F-specs and sets on *INLR at the end of the program.  So does this mean that each time the stored procedure runs it must open and close the files? My understanding is that opening and closing files is expensive.

My understanding is the if a file is specified as static in a service program it is opened when the service program is first called and remains open until the activation group of the service program ends.

I use this approach in many of my service programs so the file is opened only once and closed when the activation group ends.  But I'm not sure this is the right approach for QZDASOINIT jobs as they run in the *DFTACTGRP which means the file won't be closed until the job ends and *DFTACTGRP jobs remain for a long time.

Am I looking in the wrong place for potential performance gains? Is there a best practice for handling files in external programs called by stored procedures?

Thanks for your insight and recommendations.

Rob