The stored procedure include only native I/O or do they include SQL, too?
With native I/O only, you'll never have pics, because the files specified within the F-Specs are taken and read, no if or then or anything else. There is no optimization at all!
Opening files located within the F-Specs is very fast (we are talking about nano seconds).
So I assume you problem is not the native I/O, but (embedded) SQL!)
If the (external) programs/procedure include SQL. Each of these SQL statements have to be optimized, i.e. an access plan must be built or at least validated (which is also very fast). But the next step, opening the data path (ODP), is the most time consuming part of executing an SQL-Statement. The (temporary) objects described in the access plan must be created, linked together and finally populated with data.
Opening the Data Path can be very time consuming especially if a temporary index must be built. Also if there are no (optimal) indexes available and a table scan must be performed ... it takes time.
If the ODP ist deleted after the execution, i.e. for example the job or the activation group in which the program ran ends, the next time a FULL Optimization must be performed. If the ODP stays opened, only the data within the ODP must be updated with the next run (PSEUDO Open).
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them and keeping them!"
„Train people well enough so they can leave, treat them well enough so they don't want to.“ (Richard Branson)
-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Robert Rogerson
Sent: Freitag, 16. Februar 2018 22:31
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
--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L) mailing list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/rpg400-l.
Please contact support@xxxxxxxxxxxx for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
http://amzn.to/2dEadiD
midrange.com
