As a consultant, you run into a lot of interesting situations, and some days you walk away thinking, "How do these people get anything done?" In one shop where I worked, the backlog of custom reports and screens seemed to have been piling up since before the dawn of civilization. Yet, the basic tools needed to develop some quick custom queries were nonexistent. Maybe you've worked in a shop like that: one in which the AS/400 is so stripped down that it's a wonder that the machine can even IPL in the morning. I coined a name for these shops. I called them "OS/400 minimalists," and I soon learned to love them. Why? Because it's in these minimalist shops that you really discover the robust nature of OS/400 and your own creative resources to make them work. It was in these shops that I discovered the AS/400's Query Management/400 (QM/400) facility, a no-cost SQL compiler built right into the heart of DB2/400. With this built-in facility, you can knock out a lot of sophisticated custom reports to satisfy a minimalist customer's craving for real and immediate information. And it runs standard SQL without your having to buy other expensive reporting tools. QM/400 is an easy tool to use and takes just a moment to learn.

In this article, I'll demonstrate how to use QM/400 by building a simple program that passes a wild-card selection string and then builds a report. In the process, you'll learn how to build and store custom queries and how to run them from a command program. All you need to create and follow this tutorial is SEU.

QM/400 is a built-in facility that compiles SQL statements from source members and runs those statements against OS/400's DB2/400 relational database. It's not a program product you have to purchase; it's a built-in facility of the system. The process of using it is simple: you create a special source member containing an SQL statement, compile that source using the Create Query Management Query (CRTQMQRY) command, and then execute the query with the Start Query Management Query (STRQMQRY) command. If you compose the source member properly, you can pass selection and sorting criteria to the query from a CL program and command, making the user interface as native as possible. I'll walk you through these processes.

In this sample, I'll assume you have a CUSTOMER file composed of a NAME field and a CUST# field. I've designed the sample application so that, if you key in just a part of a customer's name, the program returns an entire list of names and their associated customer numbers.

The first thing you have to do is create a file to hold your QM/400 source program. This is a special source file that can exist in any library you choose. It's special because the SQL statement you put into it can't exceed a length of 79 characters. Since source files also require sequence numbers and other key information, the total length of a QM/400 source file should be 91 bytes. Create this source file by keying in this command:

 CRTSRCPF FILE(MYLIB/QQMQRYSRC) RCDLEN(91) 

This creates source file QQMQRYSRC in the MYLIB library. I've chosen the name QQMQRYSRC because it's the standard source file name that QM/400 expects, but you can call it anything you like.

Now what? Well, key in a sample SQL statement. Start SEU and create a new member in your QQMQRYSRC source file. Call this new member CUSTSEARCH. When you get a new blank work area, key in the following SQL statement:

 SELECT NAME, CUST# FROM MYLIB/CUSTOMER WHERE NAME LIKE &SEARCH 

The SQL code reads almost like an English paragraph. To paraphrase, the above statement reads "Select (the fields) NAME (and) CUST# from (the file) MYLIB/CUSTOMER where (the) NAME (field is) like (the) &SEARCH (field)." SQL syntax is pretty easy to read, and it's a snap to pick up.

There are some restrictions to source members compiled by QM/400. Most significantly, the SQL source member can only be a single SQL statement. This SQL statement, however, can be spread over multiple SEU lines and can be used to build sophisticated selection and sorting criteria utilizing multiple substitution parameters, each up to 55 characters in length.

Now, notice the third line of the code: WHERE NAME LIKE &SEARCH. The ampersand (&) identifies the field &SEARCH as a substitution parameter. Keep an eye on this parameter; it will go through many permutations throughout this article. This is the parameter I'll pass to this SQL program.

Also notice that I'm comparing the NAME field with a LIKE predicate, instead of the old standby equals (=) predicate. This is because I want my users to be able to search for a name by keying in a pattern of letters, instead of having to spell the name exactly as it is in the CUSTOMER file. This is sometimes called wild carding, and, in QM/400, the wild-card symbol is the percent character (%). I'll pass this wild-card character (%) to SQL and then let SQL do all the work of matching the NAME patterns.

Save this source member. It's now ready to be compiled. To compile this SQL statement into a QMQRY, you use this CRTQMQRY command:

 CRTQMQRY QMQRY(MYLIB/CUSTSEARCH) 

If the compile is successful, no error messages will be generated. Instead, a new object of the type QMQRY will be in your library.

You could theoretically execute this query interactively, but it's not much more work to create a CL program and a command for our new CUSTSEARCH QMQRY. So, crank up SEU again, this time in your CL source file, and key in the following CL statements:

 PGM (&SEARCH) DCL VAR(&SEARCH) TYPE(*CHAR) LEN(25) DCL VAR(&ARG) TYPE(*CHAR) LEN(27) CHGVAR VAR(&ARG) + VALUE('''' *TCAT &SEARCH + *TCAT '''') STRQMQRY QMQRY(CUSTSEARCH) + SETVAR((SEARCH &ARG)) ENDPGM 

The program is very simple: it receives a parameter called &SEARCH, and then it executes the STRQMQRY command. The query that it calls is the CUSTSEARCH QMQRY object.

There are only a couple of things to note in this program. Look at the CHGVAR line and notice that the &ARG parameter receives a concatenated version of the &SEARCH parameter. This line reformulates the &SEARCH string into something the CL syntax checker can accept. The newly reformulated variable &ARG is then passed on to the STRQMQRY command as the SEARCH parameter.

Save this CL program under the name CUSTSEARCH and compile it. Now, you have two objects called CUSTSEARCH: a QMQRY object and a PGM object. I'll show you how to make one more.

Crank up SEU again-this time in your QCMDSRC source file-and key the following code into a new member:

 CMD PROMPT('Search for Customer') PARM KWD(SEARCH) TYPE(*CHAR) + LEN(25) RSTD(*NO) MIN(1) + PROMPT('"Name" or + "%Partial Name%"') 

All this command does is call the CUSTSEARCH CL program, passing the SEARCH keyword. Notice that I used the MIN(1) parameter, telling the command that there must be at least one parameter passed. Doing this prevents a null string from being passed to the SQL.

Name this source member CUSTSEARCH, and then compile it. We now have the three CUSTSEARCH objects: the QMQRY object (which holds the compiled SQL statements), the PGM object (created from the CL source member that parses out the search string), and the CMD object (which is now the user interface). To see how this triad of program engineering works, key in CUSTSEARCH and press the F4 key. If you followed all the directions, you should see the screen shown in 1.

Name this source member CUSTSEARCH, and then compile it. We now have the three CUSTSEARCH objects: the QMQRY object (which holds the compiled SQL statements), the PGM object (created from the CL source member that parses out the search string), and the CMD object (which is now the user interface). To see how this triad of program engineering works, key in CUSTSEARCH and press the F4 key. If you followed all the directions, you should see the screen shown in Figure 1.

Now, if you key a name without wild-card characters, SQL attempts to use the LIKE statement to find exactly the same string in the NAME field of the CUSTOMER file. But if you key in a wild-card name, such as %Electrical%, SQL searches for any records that have the string Electrical embedded within them. SQL then returns a screen that looks like the one shown in 2.

Now, if you key a name without wild-card characters, SQL attempts to use the LIKE statement to find exactly the same string in the NAME field of the CUSTOMER file. But if you key in a wild-card name, such as %Electrical%, SQL searches for any records that have the string Electrical embedded within them. SQL then returns a screen that looks like the one shown in Figure 2.

So that's how QM/400 works. It takes a simple SQL statement recorded in a source member and compiles it into an object type of *QMQRY. You can pass parameters to the *QMQRY objects from any OS/400 program facility. The resulting output can be sent to the screen, to a printer, or to a file.

QM/400 brings an amazing level of SQL power to your system. Try it! If your shop is penny-pinching on SQL products or if you're just a simple old OS/400 minimalist like me, this might be just the ticket you need.

Thomas M. Stockwell is a senior technical editor for Midrange Computing. He can be reached by E-mail at This email address is being protected from spambots. You need JavaScript enabled to view it..

SQL Minimalism

Figure 1: CUSTSEARCH Command Prompt

SQL Minimalism

Figure 2: Query Management Search Results

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.

 

 

Also Read

SQL 101: DDL Recap—From Physical Files to SQL Tables, Starting the Journey

TechTip: Generate and Email a Spreadsheet with SQL

Practical SQL: Journal Forensics Using SQL