Re: RPG programs as external stored procedures...

On Fri, Feb 16, 2018 at 2:31 PM, Robert Rogerson <rogersonra@xxxxxxxxx>
wrote:

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?).

I'm not aware of any way to change the activation group of IBM programs.

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?

Yes, my understanding is that the files would be opened and closed with
program call, given that your stored "procedure" maps to a "program".

My understanding is that opening and closing files is expensive.

Relatively expensive. Subsequent opens seem to be less expensive. We're
talking like a millisecond or so.

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.

That's my understanding too. The buffers pertaining to files remain
allocated across calls too.

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.

You haven't mentioned whether your stored procedures run in the *caller
activation group, or *new, or *named. *new activation is relatively
expensive.

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?

I'm not aware of any best practice that fits for every application. If you
leave your files open between invocations, then that can lead to each
QZDASOINIT Job having hundreds of open files, which may not be good.

Thanks for your insight and recommendations.