02
Sat, Nov
2 New Articles

OPNQRYF Demystified

Typography
  • Smaller Small Medium Big Bigger
  • Default Helvetica Segoe Georgia Times

One of the strengths of OS/400 is its integrated relational database. Although the database has been given a name in V3R1 (DB2/400 or DB2 for OS/400), it has been part of the operating system since the days of the S/38. Just as the database is part of OS/400, support for SQL is an integrated part of the database. OS/400 and IBM's licensed program products offer many different ways to access the SQL engine, all with the same general goal?to select and sort database information.

This article explores one of the most widely used and most misunderstood methods?the Open Query File (OPNQRYF) CL command. OPNQRYF is part of the base operating system (unlike some other SQL access tools, such as SQL/400). This makes it a very cost-effective way to sort and select data. In theory, OPNQRYF should be easy for AS/400 programmers to master because it takes advantage of the familiar CL interface. Unfortunately, in the translation from SQL syntax to CL syntax, OPNQRYF became one of the least intuitive commands ever designed. An understanding of what OPNQRYF is designed to do and which parameters are really relevant is essential to your ability to take full advantage of this powerful command.

OPNQRYF is an extension of CL, which allows us to build Open Data Paths (ODPs) using OS/400's Query Optimizer. Properly used, OPNQRYF lets you simplify high-level language (HLL) programs, such as RPG or COBOL. OPNQRYF performs the sorting and record selection while the HLL program performs calculations and file updates.

Where OPNQRYF Came From

OPNQRYF originated on the S/38 as a way to dynamically sort, select, and join data. When OS/400 was invented as the successor to the S/38's operating system, the underlying OS code of OPNQRYF was redesigned to use OS/400's integrated Query Optimizer. The Query Optimizer is a facility that finds and builds fast access paths across the database. So transparent was this implementation using the Query Optimizer that many S/38 programmers assumed that OPNQRYF was innately different from other Query facilities. Was one method faster than another? Which one used more AS/400 resources?

In actuality, as 1 shows, OPNQRYF uses the same routines to get access to the DB2/400 database. In other words, OPNQRYF is like all the other Query implementations on the AS/400: it's a front-end to the services and resources of the AS/400's Query Optimizer.

In actuality, as Figure 1 shows, OPNQRYF uses the same routines to get access to the DB2/400 database. In other words, OPNQRYF is like all the other Query implementations on the AS/400: it's a front-end to the services and resources of the AS/400's Query Optimizer.

So, why use OPNQRYF instead of a different Query implementation? Well, first of all, it's a robust extension of Query into the CL syntax. This provides the power to select records, sort data, and join files within CL programs. Second, OPNQRYF provides you with complete prompting support throughout OS/400.

This article focuses on the elements of OPNQRYF and how this facility builds upon the underlying strengths of the Query Optimizer. In it, we'll walk through the steps of building a simple OPNQRYF and give some pointers on ways to streamline your program development.

In our simple example, we'll focus on building an OPNQRYF that takes a Vendor History (VNDRHIST) database file, selects a single Vendor Number (VNDRNO), and accesses those history records in Purchase Date (PDTE) order.

Open Data Path?The Virtual Relational Database

Records in a relational database are physically stored in arrival sequence. Unless the database has a keyed sequence, grouping records in a logical manner, programmers must constantly create new sorted instances of the database. This problem is resolved in OS/400 with ODPs, and ODPs are used extensively by OPNQRYF.

An ODP is a virtual pathway between individual records within a relational database, as well as between individual database files. It's a map of record pointers, maintained within memory, to access requested files, fields, and records. It is exactly this pathway that the OPNQRYF command creates.

The first step, then, in executing an OPNQRYF command is to prepare the database files for the creation of an ODP by telling DB2/400 to share the resources of the associated files. This is usually accomplished by the Override Database File (OVRDBF) command. For instance, this is the command for sharing the VNDRHIST file.

 OVRDBF FILE(VNDRHIST) + SHARE(*YES) 

Once this command has been issued, from the command prompt or from within a CL program, the VNDRHIST file is ready for the construction of an ODP.

Constructing the ODP with OPNQRYF

Now, you're ready to actually build the virtual database using OPNQRYF. Typing in OPNQRY and pressing the F4 prompt key will give you a list of parameters that?nine times out of ten?will totally intimidate even the most hardened of programmers. There are six full screens of prompts, each with their own confusing sets of parameters (see 2). Fortunately, there are only a few parameters required to successfully build the ODP, but spotting them through the pages and pages of prompts is difficult.

Now, you're ready to actually build the virtual database using OPNQRYF. Typing in OPNQRY and pressing the F4 prompt key will give you a list of parameters that?nine times out of ten?will totally intimidate even the most hardened of programmers. There are six full screens of prompts, each with their own confusing sets of parameters (see Figure 2). Fortunately, there are only a few parameters required to successfully build the ODP, but spotting them through the pages and pages of prompts is difficult.

Despite the plethora of potential parameters, there are essentially just three basic ones required to construct the ODP: FILE, QRYSLT, and KEYFLD.

FILE obviously identifies the file or files that compose the ODP. QRYSLT identifies the selection criteria of the individual records needed. KEYFLD identifies the order in which the ODP will read the records of the database files.

Here is an example of constructing the ODP with an OPNQRYF statement.

 OPNQRYF FILE(VNDRHIST) + QRYSLT('VNDRNO=30') + KEYFLD((PDTE)) 

In this example, OPNQRYF builds an ODP to the VNDRHIST file, selecting VNDRNO 30 and accessing the history records in PDTE order.

Since OPNQRYF is integrated with OS/400's underlying SQL, if an ODP that matches the OPNQRYF criteria already exists, OS/400 will find it and use it instead of building a new one. Also, since no new instances of VNDRHIST data have been created, there's no possibility that the data you will be using will not be "current." As long as the ODP is active?until you either close it or end the job?you can be sure that the data being referenced will always be up-to-the-minute.

Connecting the HLL Program to the Virtual Database Highway

So now that an ODP has been constructed, what's next? It's time to climb on that virtual database highway and step on the gas pedal. You simply call an HLL program to process the records, like this:

 CALL PGM(MYPGM) 

The only requirement within the HLL is that it must reference the ODP appropriately. In RPG, this means the filename used in the F-spec must reference a file that has the same database format that the ODP is referencing.

Within the HLL program, only those records selected by OPNQRYF will be processed (in our example, those records with a VNDRNO of "30"), and those records will arrive in the order defined by the KEYFLD parameter (PDTE order). There's no other required code within the HLL; it's as if you were accessing a completely separate database of selected records. However, if the file is updated, your HLL program will have immediate access to the new information.

Closing Down the Highway with CLOF

After your HLL program has finished processing the records accessed by the ODP, you will probably want to deconstruct the virtual database highway. Why? Because, if you subsequently run another program that uses the same database file, OS/400 will assume you want to run along that same virtual database highway. Obviously, this could cause you some problems, presenting you with only the records you'd selected and sorted in the OPNQRYF.

To close down the highway, you simply need to close the file. This is accomplished with the Close File (CLOF) command. In our example, the command would look like this:

 CLOF OPENID(VNDRHIST) 

Of course, you don't have to worry about closing the ODP if you sign off the system or end a batch job; OS/400 keeps your unique ODP open only as long as the job that opened it is running. However, if you don't close the ODP after you're finished, OS/400 will expend its resources to maintain it, so it's common practice to always use a CLOF after the call to the HLL.

Restoring DB2/400

The final step in deconstructing the ODP is to inform DB2/400 that it no longer needs to share the resources of the particular database file. This is accomplished by telling OS/400 to delete the initial OVRDBF. The command to execute this is Delete Override (DLTOVR), and the last statement in our sample CL program looks like this:

 DLTOVR FILE(VNDRHIST) 

The complete step-by-step construction and deconstruction of this ODP is shown in 3.

The complete step-by-step construction and deconstruction of this ODP is shown in Figure 3.

Expanding the Basics

If you think the example we've been using is deceptively simple, you're right! As I said before, the OPNQRYF statement is a CL front-end for the underlying SQL engine of OS/400. Consequently, CL programs that use OPNQRYF require a substantial amount of code to define the OPNQRYF parameters. For the CL programmer, this requires both ingenuity and a firm understanding of CL string variables.

For instance, in our example, we hard-coded the QRYSLT statement to always choose VNDRNO 30. In real life, the program would be much more robust if we could use a CL variable. You might assume that we could use the following statements to accomplish this:

 PGM PARM(&VNDRVAR) DCL VAR(&VNDRVAR) + TYPE(*CHAR) LEN(5) OPNQRYF FILE(VNDRHIST) + QRYSLT('VNDRNO=&VNDRVAR') + KEYFLD((PDTE)) 

Unfortunately, this won't work. Why? Because, when the CL program parses the QRYSLT statement, it will not substitute the value of the variable &VNDRVAR because it considers everything within quotes as a literal.

The solution to this problem?and the technique almost always used by skilled CL programmers?is to build up a string variable of selection criteria within CL and then to pass that entire string along to the SQL compiler as the QRYSLT field.

Consequently, the solution to our above requirement would look like this:

 PGM PARM(&VNDRVAR) DCL VAR(&VNDRVAR) + TYPE(*CHAR) LEN(5) DCL VAR(&QRYSLT) + TYPE(*CHAR) LEN(256) CHGVAR VAR(&QRYSLT) + VALUE('VNDRNO=' *CAT &VNDRVAR) OPNQRYF FILE(VNDRHIST) + QRYSLT(&QRYSLT) KEYFLD((PDTE)) 

As your OPNQRYF skills expand, you'll often find yourself spending a tremendous amount of time fine-tuning QRYSLT statements and KEYFLD statements using this technique. Unfortunately, this effort is not well supported by the prompting capabilities of OS/400. When you press F4 to prompt the QRYSLT parameter, you will see that IBM has not provided any means to interactively build the QRYSLT field as a string. Perhaps some day it will occur to Rochester that this is an area in need of improvement.

Finally, don't neglect to consider all of the other integrated capabilities that OS/400 provides to the OPNQRYF command. Using OPNQRYF functions such as %WLDCRD, %NULL, %MAX, %XLAT, and, of course, the powerful CLfunction %SST adds a tremendous capability to OPNQRYF. Building your ODP using OPNQRYF can be as powerful as any HLL method of accessing data.

OPNQRYF's Unique Contribution to SQL/400

So, should you be using OPNQRYF? Absolutely! It's the CL equivalent of the Swiss army knife for building powerful ODPs to DB2/400. No other SQL tool syntactically integrates as transparently into CL as OPNQRYF, and, since it uses the same underlying SQL resources of OS/400, you can be certain that its power is as consistent and robust as DB2/400 itself.

Thomas M. Stockwell is a senior technical editor for Midrange Computing.

OPNQRYF Demystified

Figure 1: Finding OPNQRYF in the Plethora of AS/400 Query Tools


OPNQRYF Demystified

Figure 2: The Six Prompting Screens of the OPNQRYF Command


OPNQRYF Demystified

Figure 3: The Five Steps to OPNQRYF


Thomas Stockwell

Thomas M. Stockwell is an independent IT analyst and writer. He is the former Editor in Chief of MC Press Online and Midrange Computing magazine and has over 20 years of experience as a programmer, systems engineer, IT director, industry analyst, author, speaker, consultant, and editor.  

 

Tom works from his home in the Napa Valley in California. He can be reached at ITincendiary.com.

 

 

BLOG COMMENTS POWERED BY DISQUS

LATEST COMMENTS

Support MC Press Online

$

Book Reviews

Resource Center

  • SB Profound WC 5536 Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application. You can find Part 1 here. In Part 2 of our free Node.js Webinar Series, Brian May teaches you the different tooling options available for writing code, debugging, and using Git for version control. Brian will briefly discuss the different tools available, and demonstrate his preferred setup for Node development on IBM i or any platform. Attend this webinar to learn:

  • SB Profound WP 5539More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.

  • SB HelpSystems ROBOT Generic IBM announced the E1080 servers using the latest Power10 processor in September 2021. The most powerful processor from IBM to date, Power10 is designed to handle the demands of doing business in today’s high-tech atmosphere, including running cloud applications, supporting big data, and managing AI workloads. But what does Power10 mean for your data center? In this recorded webinar, IBMers Dan Sundt and Dylan Boday join IBM Power Champion Tom Huntington for a discussion on why Power10 technology is the right strategic investment if you run IBM i, AIX, or Linux. In this action-packed hour, Tom will share trends from the IBM i and AIX user communities while Dan and Dylan dive into the tech specs for key hardware, including:

  • Magic MarkTRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now!  Request Now.

  • SB HelpSystems ROBOT GenericForms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.

  • SB HelpSystems ROBOT GenericIT security is a top priority for businesses around the world, but most IBM i pros don’t know where to begin—and most cybersecurity experts don’t know IBM i. In this session, Robin Tatam explores the business impact of lax IBM i security, the top vulnerabilities putting IBM i at risk, and the steps you can take to protect your organization. If you’re looking to avoid unexpected downtime or corrupted data, you don’t want to miss this session.

  • SB HelpSystems ROBOT GenericCan you trust all of your users all of the time? A typical end user receives 16 malicious emails each month, but only 17 percent of these phishing campaigns are reported to IT. Once an attack is underway, most organizations won’t discover the breach until six months later. A staggering amount of damage can occur in that time. Despite these risks, 93 percent of organizations are leaving their IBM i systems vulnerable to cybercrime. In this on-demand webinar, IBM i security experts Robin Tatam and Sandi Moore will reveal:

  • FORTRA Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:

  • FORTRAManaging messages on your IBM i can be more than a full-time job if you have to do it manually. Messages need a response and resources must be monitored—often over multiple systems and across platforms. How can you be sure you won’t miss important system events? Automate your message center with the Robot Message Management Solution. Key features include:

  • FORTRAThe thought of printing, distributing, and storing iSeries reports manually may reduce you to tears. Paper and labor costs associated with report generation can spiral out of control. Mountains of paper threaten to swamp your files. Robot automates report bursting, distribution, bundling, and archiving, and offers secure, selective online report viewing. Manage your reports with the Robot Report Management Solution. Key features include:

  • FORTRAFor over 30 years, Robot has been a leader in systems management for IBM i. With batch job creation and scheduling at its core, the Robot Job Scheduling Solution reduces the opportunity for human error and helps you maintain service levels, automating even the biggest, most complex runbooks. Manage your job schedule with the Robot Job Scheduling Solution. Key features include:

  • LANSA Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.

  • LANSAWhen it comes to creating your business applications, there are hundreds of coding platforms and programming languages to choose from. These options range from very complex traditional programming languages to Low-Code platforms where sometimes no traditional coding experience is needed. Download our whitepaper, The Power of Writing Code in a Low-Code Solution, and:

  • LANSASupply Chain is becoming increasingly complex and unpredictable. From raw materials for manufacturing to food supply chains, the journey from source to production to delivery to consumers is marred with inefficiencies, manual processes, shortages, recalls, counterfeits, and scandals. In this webinar, we discuss how:

  • The MC Resource Centers bring you the widest selection of white papers, trial software, and on-demand webcasts for you to choose from. >> Review the list of White Papers, Trial Software or On-Demand Webcast at the MC Press Resource Center. >> Add the items to yru Cart and complet he checkout process and submit

  • Profound Logic Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application.

  • SB Profound WC 5536Join us for this hour-long webcast that will explore:

  • Fortra IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn: