RE: RPG programs as external stored procedures...

Won't the activation group just come from the setting on his program object?

-----Original Message-----
From: Nathan Andelin [mailto:nandelin@xxxxxxxxx]
Sent: Friday, February 16, 2018 4:36 PM
To: RPG programming on the IBM i (AS/400 and iSeries) <rpg400-l@xxxxxxxxxxxx>
Subject: 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.