I was catching up on my reading this weekend when I found an IBM white paper discussing the advantages of moving from using the Open Query File command (OPNQRYF) to SQL.
When I first started programming on the AS400 I did use the OPNQRYF command frequently, but I do not use it any more. What I was doing with OPNQRYF I feel I can do so much better with SQL, embedded in RPGLE and CL. For how to embed SQL statements in your CL see Run SQL statements in your CL. There is also a performance advantage to using SQL. OPNQRYF uses the Classic Query Engine, which is slower than the SQL Query Engine used by SQL, see Query Engines: CQE versus SQE.
This white paper discusses the major advantages that SQL has in comparison to the IBM i5/OS Open Query File (OPNQRYF) command. It also provides a methodology for converting OPNQRYF applications to an SQL-based model, as well as some key points to consider during the conversion process.
Even though the white paper was written in 2008, the reasons it makes to move from OPNQRYF to SQL is still valid, perhaps more so with the enhancements added to SQL in subsequent releases of IBM i.
You can open the PDF file directly by this link: IBM white paper: Moving from OPNQRYF to SQL
As always very nice post. One little comment since IBM i 7.2 there is no performance difference between Opnqryf and SQL, because opnqryf was ported to SQE.But sure, it is still recommended to eliminate old querys and opnqryf.
ReplyDeleteWe recently went up to V7R2 and have noticed some open query file commands taking a substantial more length of time to process. It's from an old PKMS release. Does anyone know a reason for this if it now uses the SQE engine?
ReplyDeleteYeah. The ibm letter to v7r2m0 users says if you specify a set of key fields in an opnqryf on a physical file that has a different set of keys, the outcome is unpredictable on the rpg using that access path.
ReplyDeleteAnd i have a fresh experience of optimizing a report program previously using opnqryf, when i optimized it removing opnqryf and implementing the right series of recursive compounded sql commands. Its sped up from 30+ minutes down to just around less than 5 minutes. Amazing what sql can do if you just learn how to think the sql way.
ReplyDelete