RE: RPG programs as external stored procedures...

" Best practice is to let your user programms run in *CALLER (otherwise you would loose transaction safety!)"

Can you explain? That's the first time I've ever heard *CALLER being a best practice for programs. I can see it for service programs, but not programs.



-----Original Message-----
From: D*B [mailto:dieter.bender@xxxxxxxxxxxx]
Sent: Sunday, February 18, 2018 1:42 AM
To: RPG programming on the IBM i / System i <rpg400-l@xxxxxxxxxxxx>
Subject: Re: RPG programs as external stored procedures...

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

@high cpu percentage: Using SQL high CPU% is caused by building a missing index by the fly, or by full table scans and has to be solved by database
design: adding needed indexes or denormalisation (e.g. pre aggregation of summarized values). There might be cases to tolerate high cpu usage and long running queries (e.g.: in a BI environment)

@activation groups: here we are talking about sub microseconds and for remote access, you would have no chance to controll the ACTGRP of the Systemjob serving the remote connection. Best practice is to let your user programms run in *CALLER (otherwise you would loose transaction safety!)

@wrong place: for sure!!!

@open close: If your client apps are following best practices, they would use a connection pool and your stored procedures should free all ressources (using SQL the database engine would cache it anyway)

@stored procedures: if your stored procedures are returning result sets, it might be all working as designed. Returning Result Sets from stored procedures and UDTf was introduced to make IBM and Oracle & Co. happy.

D*B