Nathan,
not sure where your information "SQL uses RLA" is from.
I don't think you are deeply enough involved in the database, SQL etc. development at IBM, for knowing that. It can only be a guess.
I don't know either.
... but it was Barbara Morris who told me that native I/O access uses the SQE (since Release 7.2) and Scott Forstie confirmed that all database access is performed by the SQE (except an QAQQINI option is set to go back to the CQE).
What "uses the SQE" exactly means, I don't know the details.
... but I believe these 2 guys know from what they are speaking.
RLA does not need any optimization, because it is the programmer who defines the physical and logical files to be read.
Each SQL Statement on the other hand has to be optimized, i.e. it has to run at least once through a FULL OPEN.
A FULL OPEN includes among others the opening the data path (ODP) which is the most time consuming part when executing an SQL Statement.
When translating 1:1. RLA into SQL, native I/O may win.
If you compare NO optimization with FULL OPENs, native I/O will win.
If you are setting additionally the compile option CLSSQLCSR to *ENDMOD or run the program in a *NEW activation group, native I/O will win.
Because the ODP is always deleted at the end of the program. With the next run again a FULL OPEN has to be performed.
Native I/O does not need any optimization, while SQL has to run at least twice through a FULL OPEN (after the first run the ODP gets closed but it is kept opened after the second run - if the ODP is reusable).
When comparing NO Optimization with FULL OPENs (over and over again), native I/O will win.
Let's assume you have to read 4 tables, Order Header, Address Table, Order Position and Item Master.
In your RPG program you'd Read the Order Header, Chain the address table, read the Order Position and Chain to the address master.
If you translate it 1:1 to SQL, you have at least 4 FULL Opens. If you join the 4 tables and select only what you really need and with the right indexes you'll be faster than native I/O (at least beginning with the 3 run!)
With RLA always 2 (complete) rows, the current and the next one are read.
With SQL a block of data can be read from the ODP.
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: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Nathan Andelin
Sent: Samstag, 16. September 2017 00:50
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: Re: Database I/O Modernization
On Fri, Sep 15, 2017 at 3:18 PM, Charles Wilt <charles.wilt@xxxxxxxxx>
wrote:
Not even close. Yes at some point a block of data is read, and part
of that data is extracted as a "record" or more likely a set of records.
That's RLA! Whether the read operation returns a pointer to one record or to a block of records is based on whether "blocking" was selected when the file was opened.
So the disk I/O it the bottom is basically the same, but that doesn't
mean SQL is using RLA.
You and I evidently have a different definition of RLA. To me, open, close, read, write, update, delete, position to, lock, unlock are RLA operations.
And yes, that is what the SQE is using under the covers.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-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
