SQE_NATIVE_ACCESS QAQQINI Deprecated in 7.4

[c20200325]

Hi,

We are preparing to upgrade from 7.3 to 7.4. It was brought to our attention that the past upgrade several years ago from 7.1 to 7.2 caused some issues with OPNQRYF statements (exact issues were not recalled) attributed to the use of the SQE instead of the CQE. To resolve those issues at that time, the SQE_NATIVE_ACCESS value was set to *No. Has anyone run into any issues related to the deprecation of this option in 7.4 that they can share to help us determine what may need to be tested and mitigated?

Thank you,
Bobby

[B.Hauser]

If you did not do anything except setting the SQE_NATIVE_ACCESS, then you will now get the same problems with the QUERY/400 or OPNQRYF as you had when going to 7.2 without SQE_NATIVE_ACCESS.
Beginning with Release 7.2 everything (even QUERY/400, OPNQRYF, native I/O and even UPDDTA) are performed by the SQE (SQL Query Engine).
Query/400 and OPNQRYF are no SQL and are optimized differently. This caused problems in Release 7.2 in companies where OPNQRYF and QUERY/400 are used heavily.
That's why the QAQQINI Option SQE_NATIVE_ACCESS was introduced, to get the queries rerooted to the old optimization, and give the companies enough time to create the right indexes, so OPNQRYF (btw. programs still using OPNQRYF should be rewritten with embedded SQL) and QUERY/400 can be executed performant with the SQE.
At Release 7.4 IBM decided, to just ignore SQE_NATIVE_ACCESS, because it was enough time to rework the queries.

[john.sev99]

The issue I'm guessing you encountered is that records may no longer be retrieved in RRN order if no sort sequence is specified. IBM have never really addressed this, there is no RRN sort order option in queries etc which seems a little short sighted.

[B.Hauser]

No I mean the complete optimization, i.e. whether an index/access path is used or not or which index/access path is use.
Quite often it makes a big difference if you select a few rows on whether an access path is used or a table scan is performed.
Specifying a logical will not help, because the query optimizer rewrites the (SQL) statement based on the physical file and then starts optimization. At this point it does not know anymore that you specified an index or not.
As soon as an index is used the rows are not returned in the RRN sequence.
Also QUERY/400 and OPNQRYF are always optimized, but the original Query Optimizer worked differently from the SQE-Optimizer.

RRN is an IBM i specific (old) technique. In a modern normalized database you would have a (numeric) unique artificial column (for example identity column) which then can be sorted.
BTW there is also a way in SQL sorting the records in the RRN Sequence (ORDER BY RRN(yourTable).
But as said OPNQRYF and QUERY/400 are no longer strategical products and are consequently no longer updated.

[john.sev99]

Sorry, was replying to the OP and guessing the order records were returned may have been the issue previously encountered.
Although query etc may not be strategical products, I do believe the default sorting should be RRN to maintain compatibility.

[c20200325]

Thank you both for responding. I managed to find a couple of snippets of the "issues" encountered with the SQE when 7.2 was installed:

1) "In the past with operating system upgrades programs using OPNQRYF record selection was slow or not all records were selected. To resolve the issue looks like QUSRSYS.QAAAINI file was changed from *DEFAULT to *NO for SQE_NATIVE_ACCESS."
2) "upgrade to V7 R2 which adversely affected OPNQRYF commands, causing them to only select the first 100 or so records."

While I agree that the OPNQRYF usage should be converted, unfortunately it is where we are right now.

I have run a test with an existing legacy program that uses OPNQRYF on the 7.4 machine and compared it to what ran on 7.3 with SQE_NATIVE_ACCESS set to *No, and it was identical. It was not limited to 100 records.

My questions are:

1) Is OPNQRYF not reliably supported by the SQE?
2) Did IBM render all programs using OPNQRYF effectively useless with 7.4?

Thank you again for your willingness to share your experience and expertise to help us understand our situation.

[B.Hauser]

Everything data access how it happens (SQL, Native I/O, OPNQRYF, QUERY/400, UPDDTA ...) is executed with the SQE. No way back anymore!
If access paths that were used with the old query engine (CQE) will not be used by the SQE and if there is no other access path is available that can be used, a table scan is performed.
... and as said the CQE Optimizer workes/works differently from the SQE optimizer, so access paths may no longer be used.