× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



Hi Justin

I think I can see this recommendation as valid when you have a program that works on some output from the caller - that is, that the called program behaves kind of like a service program.

Maybe?

Vern

On 2/19/2018 8:04 AM, Justin Taylor wrote:
" 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




As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.