January 16

5 comments

AS400 QMQRY – Query Management Queries

By NickLitten

January 16, 2007

management, Query

So, I was emailed the following hints and tips document discussing the ubiquitous Query Management Query object type on the IBM Power Server (the artist formerly known as AS400). It’s useful to refer to so, here it is:

QMQRY – QUery Management Query

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. 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.

  • I am facing an issue in Setvariable in QMQRY. My variable has trailing blanks, but while executing the query, those trailing blanks get suppressed. I dont want that to happen.
    Eg- STRQMQRY QMQRY(mylib/query1) OUTPUT(*OUTFILE) OUTFILE(Qtemp/t1) setvar(STRING1 &str1) (STRING2 &Str2)
    In this case
    &STR1 = ‘SELECT FIELD05 from mylib/myfile where field01 ‘
    &STR2 = ‘Like ”%efg%” ‘

    I face issue with &STR1 where STRQMQRY trim the trailing blanks(one blank after the field01 at the end) when running the actual query and so it results in error.

    Please help me here

    • Hi Nisha, You may have done this already, but have you try to place that space at the beginning of &STR2 instead?

      &STR1 = ‘SELECT FIELD05 from mylib/myfile where field01‘
      &STR2 = ‘ Like ”%efg%” ‘

      Not sure if this will work in STRQMQRY, but in my experience with other SQL tools it does work to concatenate the 2 variables as a single SQL command. Does this work for what you need done?

  • This is really good stuff, had a query with a unmatch file selection that had an extra ) in the code, followed these steps, updated the source and it fixed it… Thanks Nick.

  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

    Join the IBM i Community for FREE Presentations, Lessons, Hints and Tips

    >