Creating Dynamic Queries

May 26, 2004 Bruce Guetzkow

If you’ve ever created a “one time” query, you know it is extremely likely that you will end up running that query on a regular basis. Typically that means manually changing the selection criteria each time it runs. While Query/400 does allow you to dynamically change the selection criteria, it can only be done if the query is executed interactively, which is usually frowned upon for performance reasons. This article outlines a method for creating a query with dynamic selection criteria and still executing the query in batch.

A SOURCE FILE FOR YOUR QUERY

The key to making a query dynamic is converting it into a form that can accept variables. The easiest way to do that is to convert your query to a Query Management Query (QMQRY). A QMQRY is simply an SQL statement stored in a source member. Once the SQL statement has been placed into a source member, you can replace hard-coded values from your original query with variables that can be replaced at execution time. If you’ve never worked with SQL before, you needn’t worry; we will make use of an IBM command that will construct SQL statements for you.

We’ll begin by creating the source file used to store your QMQRYs: QQMQRYSRC. To create this source file, use command Create Source Physical File:

CRTSRCPF  FILE(library/QQMQRYSRC)
          RCDLEN(91)

Be sure to specify a record length of 91 (source length of 79, sequence number length of 6, date length of 6) instead of the command default of 92, as QMQRY requires a length of no more than 79 bytes for the source statement. If you specify a length of more than 91 on the CRTSRCPF command, you will not be able to run your SQL statements.

SAVING YOUR QMQRY

You can use any query, but I recommend starting with a simple query, with one or two files as input, using query join option 1 (matched records). Once you understand the process, you can easily branch out to more complicated queries. As indicated before, we will use an IBM command to convert your query to an SQL statement. That command is Retrieve Query Management Query (RTVQMQRY):

RTVQMQRY QMQRY(library/query)
         SRCFILE(library/QQMQRYSRC)
         SRCMBR(*QMQRY)
         ALWQRYDFN(*ONLY)

When this command completes, you will be able to view or edit the generated SQL statement as you would any other source file member, using Source Entry Utility (SEU) or WebSphere Development Studio client (WDSc) and the LPEX Editor or CODE/400. If you’ve never used SQL before this is a great way to learn how a statement is constructed.

In order to make this QMQRY dynamic, you need to identify the portions of the SQL statement that you’ll want to specify as variables. Typically this will be part, or all, of the Where clause, as this part of the SQL statement identifies the selection rules for your input files. Like CL, QMQRY specifies variables using the ampersand (&) character. If the Where clause of your converted query contains the following:

WHERE DEPT = 'NORTH'

And if you want to make department a variable, you can change the statement as follows:

WHERE DEPT = &DEPT

The variable name can be anything as long as it begins with an ampersand, is followed by an alphabetic character, and is no more than 30 characters long. Variables always should be specified in upper case.

CREATING THE QMQRY

Before you can execute your QMQRY, you must first create it as an object of type *QMQRY, using the Create Query Management Query command:

CRTQMQRY QMQRY(library/query)
         SRCFILE(library/QQMQRYSRC)
         SRCMBR(*QMQRY)

Unlike other IBM Create commands, there is no standard PDM option available to create this object. I suggest creating your own PDM option to submit this command to batch. In my experience, the CRTQMQRY command always completes normally (compiles cleanly). This means that a successful execution of the CRTQMQRY command only means that the object has been created. It does not necessarily mean that your resulting object can be executed. Since we are starting with a valid query and only adding a variable, we shouldn’t have any problems. However, if you create your own SQL statements, be aware that there is no syntax checking done by the CRTQMQRY command.

EXECUTING THE QMQRY

You can now construct a program to collect your dynamic selection rules, such as the department code from my earlier example. This can be any type of program (CL, RPG, COBOL, etc.) and should contain validation logic to make sure the values specified are reasonable. Once you have captured the needed values, your program can submit a job to batch to execute the QMQRY with the specified values. The command used to execute the QMQRY is Start Query Management Query (STRQMQRY). It can be executed directly from the Submit Job (SBMJOB) command or as part of a CL program. The syntax for this command is the following:

STRQMQRY QMQRY(library/query)
         OUTPUT(see below)
         SETVAR((variable1 value1) (variable2 value2)...)

You must specify a “variable value” pair for each variable you have placed into your QMQRY. The variable name on the command is specified without the leading ampersand (DEPT on the command, &DEPT in the QMQRY). Unlike other languages, QMQRY uses variables to replace parts of the actual SQL statement syntax before execution. The value must be specified as a character value and must contain any special characters necessary to make the SQL syntax valid.

I normally use Change Variable (CHGVAR) commands before the STRQMQRY to build each value, to ensure that it is the correct type and length and has all necessary components. For numeric values it is usually sufficient to do the following:

CHGVAR VAR(&ALPHA) VALUE(&NUMERIC)

Field &ALPHA is defined as a character variable and &NUMERIC is defined as a decimal value, both of the same size. For character values, you must surround the value with quotation marks. In my previous example, if the department field is 10 characters in length, I would create a CL variable with a length of 12, to contain the largest possible department value, plus the leading and trailing quotation marks. The command would be specified as follows:

STRQMQRY QMQRY(library/query)
         OUTPUT(see below)
         SETVAR(DEPT &DEPT)

There are some limitations regarding the VAR parameter: you are limited to a maximum of 50 variables that can be passed, and the maximum value length that can be passed per variable is 55 bytes.

QMQRY OUTPUT

The OUTPUT parameter on the STRQMQRY command allows you to produce the results of your query in three possible ways:

1. If executed interactively and you specify OUTPUT(*), the results are returned to your screen.

2. If executed in batch and you specify OUTPUT(*) or OUTPUT(*PRINT), the results are returned in a spool file (QPQXPRTF).

3. If you specify OUTPUT(*OUTFILE) and identify a file in the OUTFILE parameter, the results are returned in that file.

There are some slight differences in the layout of reports between Query/400 and QMQRY. The date, time, and page number are placed at the bottom of each page for QMQRYs. You can modify the layout of a printed QMQRY using a Query Management Form (QMFORM), but that is beyond the scope of this article. Also, the standard size of printer file QPQXPRTF has a record length of 80 bytes. If your report is more than 80 columns wide, you will receive multiple spool files: one for each 80 columns of the report. This can be overcome by using the Override Printer File (OVRPRTF) command and specifying the appropriate record length.

Because most users that I have worked with are familiar with reports generated using Query/400, I often specify OUTPUT(*OUTFILE) to send the results of my query to a work file (usually in library QTEMP), then create a simple Query/400 query with only the work file as input. This allows me to have the flexibility of a dynamic query but to retain the look and feel of a Query/400 report.

NOT ALL QUERIES ARE CREATED EQUAL

As I indicated above, it is best to try this procedure with simple queries first. The RTVQMQRY command makes the conversion of a Query/400 query to a QMQRY easy, but it does not handle all types of conversions well. It works best with simple file joins but does not create the proper SQL statement for outer joins or exclude joins. If you need to convert such queries, you will need to review the generated SQL statement and modify it accordingly. Always be sure to test your QMQRYs to make sure they work as intended.

Now that you have the blueprint, you can make almost any query dynamic. Impress your users. Impress your boss. You may even impress yourself!

Bruce Guetzkow has programmed on the AS/400 and iSeries since 1990 in manufacturing, distribution and other industries. He is currently the IS director at United Credit Service in Elkhorn, Wisconsin. E-mail: bguetzkow@itjungle.com