Does this scenario sound familiar? Your company has a few super-users who have created oodles of custom reports using Query/400. Now they want these reports made available to other users or run as part of your nightly processing. Did I mention that they want to be able to enter selections for date range or other criteria each time the report is run? If you’ve come across such a situation, you know your options. You write an RPG program to replace the query; put the query itself on a menu, using RUNQRY with the RCDSLT(*YES) option so that the record-selections screen is displayed before running the query; or lock the person who wrote the query in a closet and throw away the key.

Each one of these options has drawbacks. If you write an RPG program, it may take days to replace a query that took someone only minutes to create. Using RCDSLT(*YES) is less time consuming; however, the record selection screen can often be awkward and doesn’t allow for any type of validation. In addition, the query runs interactively, which can be a major drain on your system’s resources. As for the last option, that could involve jail time and would waste valuable closet space.

There is another option. Query Management gives you the flexibility of an RPG program and, with the addition of DB2 UDB for AS/400 Query Manager, is as easy to use as Query/400. Query Management is included as a component of OS/400 and gives you a set of commands that can be used to run, create, or retrieve source for Query Management objects. DB2 UDB for AS/400 Query Manager, on the other hand, is a separately licensed program product that provides a more user-friendly interface to Query Management objects. It allows you to customize Query Management queries using standard SQL commands. You can also pass parameters to a Query Management query, so you can create a front-end to prompt for selection criteria, then submit the report to batch with that selection criteria.

However, there are drawbacks to using Query Management queries. For example, you can’t do outer joins—that is, a query where you want all records from your primary file, regardless of whether or not matching records exist in your secondary files. But the pros far outweigh the cons, and after reading this article, you’ll see why.

Query Manager Basics

Before creating a report using Query Manager, take a look at the components of a Query Manager report. The two basic parts of a report in Query Manager are the Query Management query, which describes your data, and the Query Management form, which

formats the report. The Query Management query itself is basically an SQL statement that defines what data appears on the report and the file, or files, containing this data. The Query Management form defines page formatting, such as page headings, column headings, column formatting, subtotals, groupings, and level-break text. Think of the Query Management query as the right side of the brain and the Query Management form as the left side. Now that you have a good picture of what makes up Query Manager, you can start building reports with Query Manager.

Morphing Query/400 Queries

One of the great things about Query Management is that you can easily convert a Query/400 query definition into a Query Management query and/or a Query Management report form. To follow along with the examples in this article, you should first create two source physical files: QQMQRYSRC and QQMFORMSRC. Be sure to replace the default source file record length of 92 with a record length of 91. This will prevent the Query Management commands from returning a warning message when they are used. These files will be used to store the source members for your Query Manager objects. You will also want to select a Query/400 definition to work with.

Converting a Query/400 object is a four-step process. The first step is to retrieve the source for the Query Management query by using the Retrieve Query Management Query (RTVQMQRY) command. This command extracts SQL source for a Query Management query from your Query/400 query definition into a source file. The key is to specify *YES for the Allow Information from QRYDFN (ALWQRYDFN) command. Type the following command:

RTVQMQRY +

QMQRY(yourlib/qryname) +

SRCFILE(yourlib/QQMQRYSRC) +

ALWQRYDFN(*YES)

The second step is to retrieve the form for this query into a source file by using the Retrieve Query Management Form (RTVQMFORM) command. Enter the command like this:

RTVQMFORM +

QMFORM(yourlib/qryname) +

SRCFILE(yourlib/QQMFORMSRC) +

ALWQRYDFN(*YES)

The third step is to create the Query Manager query by using the Create Query Management Query (CRTQMQRY) command, which should look like this:

CRTQMQRY +

QMQRY(yourlib/qryname) +

SRCFILE(yourlib/QQMQRYSRC)

The fourth and final step is to create the Query Management form by using the Create Query Management Form command(CRTQMFORM). That command should look like this:

CRTQMFORM +

QMFORM(yourlib/qryname) +

SRCFILE(yourlib/QQMFORMSRC)

That is all that’s required to convert a Query/400 query definition into a Query Manager report. Now I’ll explain how to work with these objects in Query Manager.

Working with Query Manager Queries

As I mentioned, you could create Query Manager objects by creating the source code, but an easier way is with the Start Query Manager (STRQM) command. Figure 1 (page 79) shows the main menu for DB2 for AS/400 Query Manager. Option 1 allows you to display and modify a Query Manager query. (Remember that the Query Manager query defines what appears on the report, while the Query Manager report form defines formatting.) The screen displayed in Figure 1 shows all Query Manager query objects in the current library. The TYPE field shows either SQL or PROMPT. The difference between these two is that a PROMPTED QM query uses prompts to guide query definitions where SQL statements define a QM query.

When creating new Query Manager queries from this screen, you are probably better off creating them in PROMPT mode. It’s simpler to create a query this way, and you can convert a prompted Query Manager query to SQL by taking option 10. For now, look for the Query Manager query you created earlier from your converted Query/400 query, and take option 2. The screen displayed will look similar to the SEU editing panel, and contain the converted SQL code from your original query definition. Pressing F15 from this screen will check the syntax of your SQL code. Pressing F5 will display a prompt where you specify a Query Manager report form and whether to run the query interactively or in batch.

You can also pass parameters to a Query Manager query. In the example shown in Figure 2, at line 10, the WHERE clause contains the parameters &INVOICE and &CONM. Exit the Edit Query screen by pressing F3. Then press F12 to go back out to the Query Manager menu screen.

Query Manager Report Forms

To Work with Query Manager report forms, take option 2 from the main screen of DB2 Query Manager for AS/400. Now locate the form you created earlier, and type option 2 to edit. The resulting screen is similar to that of Query/400 but has a few more bells and whistles. Type a 1 next to Edit column formatting. Here you define column attributes, including field size, edit codes, and column headings. Press F12 to go back to the previous screen, and type a 1 next to Edit page heading. This screen allows you to define headings for your report. This is one of the areas where Query Manager is more flexible than Query/400. On this screen, you can not only define static text and page number or date and time information, but you can also grab data from your query and display it in the page heading. This is done by referencing the column (field) from the Edit column formatting screen in the format &col, where col represents a field name. You can also define multiple lines of page-heading text to appear on the report. (I’ll cover this later.)

For now, press F12 to go pack to the previous screen. Edit page footing works the same way as the previous option, except that the information entered is printed at the bottom of each page. Edit final text allows you to define what text to print at the end of the report. This feature is similar to Edit page heading/footing but supports page-number or date/time information.

The Edit break text option can only be used if you have one or more break levels defined on your report. Break levels are defined on the Edit column formatting screen by entering a value from BREAK1 (highest level) through BREAK6 in the usage column. Once you have defined break levels, you can specify heading and footing text to appear before or after each level break on the report. The break heading/footing options allow flexibility in printing column data, with the added bonus of being able to skip to a new page before the heading or after the footing, as well as optionally reprinting the column headings. These options allow you to customize the look of your Query Manager report.

Finally, formatting options allow you to specify line spacing, separators, column headings, and break text.

Separators appear on the report as a series of equal sign (=) characters. Now that you have examined each section of the Query Manager report, you can put it all to use.

Putting It All Together

To follow the example in this article, go to the Midrange Computing Web site (www.midrangecomputing.com/mc) and download the code associated with this article. In this example, you will use Query Manager to create a customer invoice form. To do this, you need to create three files: a customer master (CUSTMAST) file, an invoice header (INVHDR) file, and an invoice detail (INVDTL) file. To create these files, put the SQL code from the member called CRTFILES into a source file named QSQLSRC. Now compile the CL program CTF001CL using the Create CL Program (CRTCLPGM) command:

CRTCLPGM +

PGM(libname/CTF001CL) +

SRCFILE(libname/QCLSRC)

Next, type CALL CTF001CL PARM(yourlib), where yourlib is the library that contains the QSQLSRC file you created.

Now copy the source member INVOICEQMQ to your QQMQRYSRC file and copy INVOICEQMF to your QQMFORMSRC file. Create the Query Manager query INVOICEQMQ and the INVOICEQMF form by typing the following commands:

CRTQMQRY +

QMQRY(yourlib/INVOICEQMQ) +

SRCFILE(yourlib/QQMQRYSRC)

CRTQMFORM QMFORM(yourlib/INVOICEQMF) +

SRCFILE(yourlib/QQMFORMSRC)

Next, look at the Query Manager query and form. Type STRQM to bring up the Query Manager/400 main menu. Enter option 1 to display the Work with Query Manager Queries screen. Use option 2 on this screen to modify the design of the INVOICEQMQ query. You will be presented with an editor screen similar to that of SEU. The SQL code displayed joins the invoice-header, detail, and customer-master files.

Notice that the final field selected is &CONM AS COMPNAME. This creates a parameter that allows you to pass a company name to the query that displays on the top of the report form. You’ll also notice that the WHERE clause contains the condition WHERE (INV# = &INVOICE). This creates a parameter called INVOICE, which allows us to specify the invoice number that is printed.

Pressing F5 prompts for any parameters used and displays the results of the SQL statements. You must enclose text fields like the company name in single quote (‘) characters when prompted or when supplying the values through the SETVAR parameter on the STRQMQRY command. If not, Query Manager will mistake the value as a field name and an error will be returned. In my example, when prompted for INVOICE, enter a number from 99 to 102 to display sample invoice data created with by the CTF001CL program.

Press F3 to exit this display, then press F12 to return to the main menu of Query Manager/400. Take option 2 to show the Work with Query Manager Report Forms display. Place a 2 in the option field next to the form named INVOICEQMF to modify the design of the invoice form.

Now take option 1 to edit the column formatting. You will notice that the customer name and address fields have the usage keyword OMIT. This prevents these fields from printing on every line of the printed output but allows access to these fields for printing on the page heading. The invoice quantity (INVQTY) and invoice amount (INVAMT) fields have their usage keywords set to SUM, which causes summaries to be displayed for these fields at the end of each invoice. Press F12 to return to the Select Report Format prompt.

Place a 1 next to the Edit Page Headings option. You’ll notice that you are able to define multiple lines of text that display the customer name and address in addition to the invoice number, invoice date, and other header information. These can be left-justified, right-justified, or centered, which really allows you to customize the page headings. The number of text lines used for the page headings is limited only by the size of the printed page. Refer to the columns from a Query Manager query using an ampersand (&) followed by the column number. The column number should match the sequence shown on the column formatting display. Press F12 to exit this screen.

Next, place a 1 next to the Edit final text option. Blank lines before is set to BOTTOM. This causes the total values to print at the bottom of the page. Press F12 to exit, and continue to press F3 until you return to a command prompt.

Now that you have examined the objects, try printing the invoice. Use the Start Query Management Query (STRQMQRY) command:

STRQMQRY +

QMQRY(INVOICEQMQ) +

OUTPUT(*PRINT)+

QMFORM(*LIBL/INVOICEQMF) +

DATETIME(*NO) +

PAGNBR(*NO) +

SETVAR((INVOICE &INVNA) +

(CONM ‘’’WIDGET WAREHOUSE’’’))

The CL program PIN001CL, on the Midrange Computing Web site, prints a sample invoice. To run this program, type CALL PIN001CL PARM(101). The parameter supplied is the invoice number. Your printed output should resemble the invoice shown in Figure 3 (page 81). That is all that’s required to generate an invoice from Query Manager/400.

More Powerful Queries

It’s easy to convert a Query/400 query definition into Query Manager. Query Manager’s flexibility makes it almost as capable as an RPG program for reports. The more you explore Query Manager, the more you will be amazed at what you can do.

The next time you must generate a custom report, consider using Query Manager instead of an RPG program or Query/400. Query Manager will help you to better manage your queries—and your time.

REFERENCES AND RELATED MATERIALS

• DB2 for AS/400 Query Manager Use (SC41-5212-01)
• DB2 UDB for AS/400 Query Management Programming (SC41-5703-01)

Figure 1: Type STRQM to access the main menu of DB2 for AS/400 Query Manager.

H QM4 05 Q 01 E V W E R 01 03 01/03/12 12:59
V 1001 050 Sample Invoice QM Query

SELECT

A.CUSTNO, A.CUSNAM, A.CUSAD1, A.CUSAD2, A.CUSCTY, A.CUSSTT, A.CUSZIP,

A.CUSPHN, B.INV#, B.INVDTE, B.INVCUS, C.IND#, C.INDLIN, C.INVDES,

C.INVQTY, C.INVPRC, INVQTY * INVPRC AS INVAMT, &CONM AS COMPNAME

FROM CUSTMAST A,

INVHDR B,

INVDTL C

WHERE (INV# = &INVOICE)

AND (CUSTNO = INVCUS)

AND (INV# = IND#)

Figure 2: This is sample source for a Query Management query.

3

BILL TO: W-s Widget World INVOICE WIDGET WAREHOUSE

1600 Pennsylvania Avenue INVOICE #: 101

Washington, DC 11111 INVOICE DATE: 3,172,000

PHONE: (800)555-1212

Line # Item Description Qty. Price/EA Line Amount

-

1 WIDGETS-8 INCH 12.00 2.00 24.0000

2 FREIGHT 1.00 5.75 5.7500

====== =============

13.00 29.7500

Figure 3: The Query Manager report form INVOICEQMF creates an invoice.