Which files are used by a Query?

This is a question I am asked enough for me to want to write this so I can direct the askers here. The question is:

How can I discover the files used by all the Query/400 objects in a library?

The Query/400 object is called a Query Definition, and is the following object type: *QRYDFN

To generate a list of the files they use is a two-step process.

The first process is to create an output file that contains the data. I use the Display Program Reference command, DSPPGMREF, to generate the output file. If I wanted to list all the files used by all of the Query definitions in the library MYLIB I would use the following:

Changing column headings in output file

I am sure there are some who are going to criticize me for saying this, but I do use Query. While I do not use it for "production" reports, I do use it is a quick and easy way to check data, or even to generate a quick "one off" output file. Yes, I know I could use SQL to do the same, but sometimes it is just, in my opinion, quicker and easier to use Query.

One of the drawbacks of using Query are the Column Headings it produces for outfiles. If for example I join a file to itself and/or "define a result field" the Column Heading produced is unclear or outright confusing. Even if I go into the option to "Specify report column formatting" and change the Column Headings, I still get the original column headings in the outfile.

I could email myself/download the file and change the column headings in Microsoft Excel, but there must be an easy way to do it? Fortunately there is using SQL.

Soft coding parameters used by Query

In the environment I work there are many programs that produce "download reports". There are not paper reports, but extracts of file(s) that are sent, via email, to the requestor. Most of these use a Query to gather information together.

Many are coded like this:

RUNQRY QRY(QUERY1)

Which means that when the Query is run it will use the files that are hard coded in the Query’s definition. Therefore, if I want to perform a test and use files in a testing library I have to change the Query.

Or if I want to change which file or library the output file is in I have to change the Query.

Fortunately, there is an easy way to overcome these issues by using the parameters in the RUNQRY command.

Changing name of Query/400 print file

I was asked a question about how to change the name of the spool file created by running Query/400.

The default print file for Query/400 output is QPQUPRFIL. The inquisitor wanted to change the name of the spool file to another which would allow the users to be able to identify it.

This can be achieved by using the OVRPRTF command.

Getting the SQL statement out of Query/400

I first learned about embedding SQL into RPG programs at a user group meeting many years ago. I could see it as a tool that would increase my productivity, but all I had was a paper copy of the slideshow that had been presented. The company I was working with had SQL loaded on their AS400 (IBM i), but none of their programmers used it. This was the time before Google, and I struggled to find resources about SQL especially about the different types of joins, etc.

Fortunately I stumbled across a way to retrieve the SQL statement from a Query/400 into a source member. Now I could build a Query close to what I want, then view the SQL to get an idea of how to make my own statement.

Query Engines: CQE versus SQE

I received an interesting message regarding the Passing parms to a Query post that I consider worth sharing.

Before I share the message I need to explain that there are two query engines on the IBM i (AS400): the Classic Query Engine (CQE) and the SQL Query Engine Engine (SQE). What runs through each engine is shown below:

Passing parms to a Query

Query/400 has always provides a quick and easy way to generate a report or create an output file. I have found that once they are built they never go away, as a programmer never has time, with all the demands on their time, to replace it with a RPG program.

If a Query joins several large files it can become a resource hog, taking away CPU power from normal daily interactive processing. The solution has always been to run Queries in batch.

Most people find that if they need to change the Query’s run parameters they use the Record Select parameter, RCDSLT(*YES), in the Run Query command, RUNQRY.

RUNQRY QRY(QUERY_NAME) RCDSLT(*YES)

Of course this cannot be run in batch. Therefore, this Query, with its large joins, runs interactively, and the other users complain that the system is running slow.

But there is another way using the Start Query Management Query, STRQMQRY, command.