Use SQL Without Buying SQL

BY DON DENONCOURT

Since the installation of OS/400 Release 3.0, you may have noticed the change in the Query Utilities (GO QUERY) menu. Now under the new subtitle "Query management" are several menu options. What is this Query Management stuff anyway? You may feel, like I did, that you had been managing your AS/400 queries just fine all along and that these new options weren't needed!

Looking into this further, I discovered that Query Management (QM) is an SQL-based environment for creating queries. This environment allows entry of SQL statements into a source file and then compiles them. The raw power of SQL makes the creation of complex, multifile reports an intuitive process for anyone who understands relational databases. Powerful SQL SELECT statements, using constructs such as NOT, NOT IN, LIKE, NOT LIKE, BETWEEN, GROUP BY, HAVING, UNION, are all available in the QM/SQL source statement. Even AS/400 sites like mine, that have Query rather than SQL, can now indirectly use SQL through Query Management. For more information on SQL and SQL statements see the article "Introduction to SQL" in this issue.

WHAT WILL QM DO FOR ME?

QM offers many capabilities that the standard AS/400 Query product does not. Variables, for instance, can be passed to a QM Query before execution. These variables can be used to specify selection criteria in the WHERE clause, or to select different fields for the SELECT clause. Also, parentheses can be used in the SQL WHERE clause. This capability of grouping logical statements simplifies complex selection criteria when using OR relationships, and resolves ambiguity. In Query, if there are several AND relationships with several ORs needed, the AND statements have to be respecified for each OR.

Unlike AS/400 Query, QM supports OPNQRYF. The variety of selection methods available in OPNQRYF, and many available in QM/SQL as well, immediately makes a QM Query a more dynamic tool than an AS/400 Query.

Forms control is another area where QM excels -- upstaging AS/400 Query. QM supports: field variable insertion in page and final text; other than break field data in break text; SAA character edits and numeric edits; and explicit placement of page, break, summary, and final text. You specify Forms control in a source file separate from the QM/SQL statement source member.

QM/Forms are compiled similarly to QM/Queries. The QM/Form object can then be tied to a QM/Query at run time, without having to be set up for a QM/Query to run. A QM/Query will run without a form specified, and would then default to the SAA forms control standards. Also, almost magically, both QM/Query and QM/Form source can be retrieved and translated from existing AS/400 queries.

Query Management is available in three environments -- Callable Interface, CL commands, and QM Procedures. The Callable Interface, or CI, is for use in the HLLs -- C, COBOL and RPG. QM procedures are for true SAA programming and are available across SAA platforms. CL commands, which are most comfortable to us, are what I will be using for the following examples. 1 shows the SAA QM Procedure commands and their CL counterparts.

Query Management is available in three environments -- Callable Interface, CL commands, and QM Procedures. The Callable Interface, or CI, is for use in the HLLs -- C, COBOL and RPG. QM procedures are for true SAA programming and are available across SAA platforms. CL commands, which are most comfortable to us, are what I will be using for the following examples. Figure 1 shows the SAA QM Procedure commands and their CL counterparts.

SIMPLE QM EXAMPLE:

To create a QM/Query you must first key an SQL statement into a source file. Or, simply, let the system retrieve the source statements from an AS/400 Query. More on this later. The source file should use the default name QQMQRYSRC and be 91 characters long, not 92. The actual source line is expected to be 79 characters long, because QM was developed for the SAA platform. Add the AS/400's 12 characters for line numbers and dates and you have 91. This strange source file length will stop warning messages from popping up every time you run the Create QM/Query command. I use QMQUERY as the member type since the compiled object will have a type of *QMQUERY.

Let's do an actual QM/Query. Key the following QM/SQL statement into a QM/Query source member named TESTQM. This SQL statement will list item master information when the product line is equal to 'Z'.

 SELECT ITEMNO, ITMDSC, QTY FROM ITMSTR WHERE PRODLINE = 'Z' 

The following Create Query Management Query (CRTQMQRY) command will create the QM/Query:

 CRTQMQRY QMQRY(YOURLIB/TESTQM) + SRCFILE(YOURLIB/QQMQRYSRC) 

Since the CRTQMQRY command is often slow, it is advisable to submit it to batch. After creating the QM/Query object, run the following Start Query Management Query (STRQMQRY) command to start the TESTQM QM/Query:

 STRQMQRY QMQRY(YOURLIB/TESTQM) + QMFORM(*SYSDFT) + OUTPUT(*PRINT) 

The QM FORM option defaults to SAA form standards. Later we will cover creation of a QM/Form for use with a QM/Query. QM uses the printer file QPQXPRTF for printed output. If a QM/Query exceeds the default width of this printer file, it prints the overflow to a second spool file. This overflow default, with some tape, can provide an easy method for creating extra wide reports. QPQXPRTF's default width, though, is 80 characters to conform to SAA guidelines. I changed the QPQXPRTF printer file from 80 characters to a more familiar standard of 132 characters. This is OK since I really don't expect to port my QM/Queries to other SAA platforms.

There is one problem with the creation (CRTQMQRY) and running (STRQMQRY) of a QM/Query. The create will, most often, terminate normally without flagging invalid SQL statements. This is especially true for incorrectly spelled file and field names. The QM/Query waits until runtime to abort and flag SQL errors. This does consume time so be careful when you code an SQL statement.

EXTERNAL VARIABLES

Adding variable field names and record selection to my QM/Query item master list was easy. I simply changed the QM/SQL statement in the QM/Query source member to the following:

 SELECT ITEMNO, ITMDSC, QTY, &field1, &field2 FROM ITMSTR WHERE PRODCD = &select 

The variables &field1, &field2, and &select relate to fields from the file or literals that will be indicated in the STRQMQRY command.

The QM/Query then had to be recreated with the following command:

 CRTQMQRY QMQUERY(YOURLIB/TESTQM) + SRCFILE(YOURLIB/QQMQRYSRC) 

The following STRQMQRY command will run the TESTQM QM/Query using SETVAR to relate the variables with fields or literals. As you can see, &field1 is replaced with TYPE, &field2 with COST, and &select with 'ABC'. The triple single quotes on the select are required, otherwise QM will think that ABC is a field name. The resulting QM/Query will list all products with a PRODCD equal to 'ABC' and include fields QTY, TYPE and COST.

 STRQMQRY QMQRY(YOURLIB/TESTQM) + QMFORM(*SYSDFT) + OUTPUT(*PRINT) + SETVAR((field1 MAKBUY) + (field2 COST) + (select '''ABC''')) 

Any variables not set by the variable option will be prompted for by OS/400 when running the QM/Query interactively. This can be handy for dynamic online inquiries. Designing a command to prompt for dynamic variables, as an alternative, could also provide a more friendly interface by giving a range of field names and/or selection logic. For example, if (select 'ABC') were not part of SETVAR, the user would be prompted for the value.

THE EASY WAY

Perhaps the easiest way to become familiar with QM SQL and Forms specifications is to let the AS/400 translate your current AS/400 queries to QM/Queries and QM/Forms source members. The RTVQMQRY command will create the SQL source member and RTVQMFORM will translate the Query format into source. Before translating your first AS/400 Query to QM form source, create a source file -- QQMFORMSRC with the IBM suggested length of 162. The created form source provides for six break levels, whether they are used or not. Unused break levels can be removed from the form source. A minimal form source member would have one Header and Table record and one Report record for each field in the QM/Query followed by one End record (explained later).

Once the QM/Form and QM/Query source members are created, use the CRTQMFORM and CRTQMQRY commands to create the *QMFORM and *QMQUERY objects. Remember to specify the name of your QM/Form object in the QMFORM parameter of the STRQMQRY command.

Some AS/400 Query functionality can be lost or altered in the converted QM/Query and QM/Form. IBM provided for this, though, with the Analyze Query (ANZQRY) command. It will show you many of the lost or altered functions. You can also use the STRQMQRY command and take the option to use an AS/400 Query to generate standard QM output. You can then inspect the resulting report for consideration of an AS/400 Query as a candidate for QM conversion. For instance, you will lose the unmatched record join capability of AS/400 Query.

FORMAT CONTROL

For ad hoc reports, a QM/Query list using the SAA report format control defaults is adequate. But, for regular use, you should design a QM/Form to explicitly control your QM/Query report format. This will present an overview of the QM/Form and is not intended to provide complete instruction. You can acquire more information by referring to the following manuals: Programming: OS/400 Query Management Programmer's Guide (SC21-8192-0), and Programming: OS/400 Query Management Reference (SC21-8193-0).

A QM/Query without a specified form has no header, footer, page or summary text. Calculated fields go wild on length -- taking up valuable report width. 2 is an actual QM/Form source listing. I have added comments with an asterisk in position one. Any comments with the asterisk starting in other than position one have been added for clarity and should not be used in an actual QM/Form source member.

A QM/Query without a specified form has no header, footer, page or summary text. Calculated fields go wild on length -- taking up valuable report width. Figure 2 is an actual QM/Form source listing. I have added comments with an asterisk in position one. Any comments with the asterisk starting in other than position one have been added for clarity and should not be used in an actual QM/Form source member.

As you can easily see, form source is cryptic. This is definitely one area where the Query Management Programmer's Guide is helpful. A series of single-digit alpha codes specifies a record type in position one; a series of four-digit numeric codes are used to define a report format option. The alpha record codes are:

H -- Header Record

T -- Table Description Record

R -- Report Row Record

V -- Value Record

E -- End Record

The Header record details object level information such as the operating system level, and creation date and time. There is one Table Description record before each group of Report Row records. Report records are one or more records that specify header, detail, footer, break, summary, and final total report lines. Value records are used to set distinct nontabular report options, such as if the report should start a new page after a break total printed. Value records consist of the V, the distinct four-digit format option number, the length of the option selection, and the selected option word or number. The End record consists only of the E character. All information following the E record is ignored.

The Table Description records, at first, seem to consist of an array of random three- and four-digit numbers that mean nothing. A closer inspection will show that the four-digit number's first two digits specify the Table type and the second two digits specify the option. 3 identifies most of the options that can be specified in a QM/Form source Table Record. These four- digit options allow you to code a dynamic columnar coding format for the subsequent Report Row Record. The first four digit code is the Table Record type. For instance, 1110 defines the Field Table Record. The second number, three digits long, defines the number of Report Records that the Table Description record's columnar format is being designed for. The third number, also three digits, defines the number of four digit/three digit option sets in this Table Record. The three-digit numbers that follow the four-digit numbers further define each four-digit number.

The Table Description records, at first, seem to consist of an array of random three- and four-digit numbers that mean nothing. A closer inspection will show that the four-digit number's first two digits specify the Table type and the second two digits specify the option. Figure 3 identifies most of the options that can be specified in a QM/Form source Table Record. These four- digit options allow you to code a dynamic columnar coding format for the subsequent Report Row Record. The first four digit code is the Table Record type. For instance, 1110 defines the Field Table Record. The second number, three digits long, defines the number of Report Records that the Table Description record's columnar format is being designed for. The third number, also three digits, defines the number of four digit/three digit option sets in this Table Record. The three-digit numbers that follow the four-digit numbers further define each four-digit number.

As you review the QM/Form in 2 remember that all of a Table's option need not be defined. But then, unselected options could not be used in the following Report Row Record like the C-Spec's dropped AN/ORs. Also, options that were not defined in the T-record or options that were not indicated in the Report Row record will default to the SAA standard. For instance, a field's width would default to the database's field size.

As you review the QM/Form in Figure 2 remember that all of a Table's option need not be defined. But then, unselected options could not be used in the following Report Row Record like the C-Spec's dropped AN/ORs. Also, options that were not defined in the T-record or options that were not indicated in the Report Row record will default to the SAA standard. For instance, a field's width would default to the database's field size.

The Report Row Record's Usage option allows you to define a wide spectrum of field usage definitions. The usage codes -- AVG, COUNT, FIRST, LAST, MAX, MIN, SUM can be used to specify reporting on break and final lines. Usage codes BREAK1 through BREAK6 specify fields on which to report subtotals. Finally, the OMIT usage code can be used to disregard completely a field on the report. The OMIT usage is handy when several QM/Forms are created for use with one QM/Query.

Field Report records also allow several other control options. Column headings are the most obvious. Field headings can be up to 62 characters in length and use embedded underscores '_' to define multi-line headings. Width can be set, usually to override calculated field lengths. The sequence of field placement on the report can be overridden to be other than the QM/Query SQL SELECT field placement. Numeric edit codes can also be set if the QM default does not suit you. These strange codes -- E, D, I, J, K, L, P -- are the new set of SAA edit codes (see figure 4). A number immediately following these numeric edits overrides the default decimal precision for the Field Record. Unlike the C edit default which would simply truncate character data, the CW character edit will put any characters in a field that exceed the column width in the next report line. The CT edit may be use for mixed DBCS and single-byte character edit. Finally, the indentation between output fields can be set.

CALLABLE INTERFACE

The Callable Interface (CI) is a method of using Query Management with C, COBOL, and RPG programs. This interface allows application programs to execute QM commands. Don't get too excited here -- the term interface in CI means only that there is communication between the QM commands and the calling program. Data pulled from a QM/Query is not made available to the application program. The communications area was set up to pass return codes and completion messages back to the HLL from QM.

The RPG interface is through a CALL to the API DSQCIR which has three parameters. The first parameter is a data structure name which describes the CI communications area. The second variable is a four-byte binary value that indicates the length of the third parameter, a CI command string. This command string is used to run a query, print a query, or run any of the other commands available in QM. For more information please refer to the Query Management Programmer's Guide.

Interactive HLL programs using QM's CI run comparatively slow when executing a QM/Query. However, a QM/Query, once run, remains in a quiescent state basically like an OPNQRYF does, and lists are much faster the second time a request is run.

HAPPY SQLING

Overall, I would have to say that QM allows greater flexibility in report formatting over AS/400 query. A QM user would require an inherent knowledge of their relational database. Run-time QM is slow, but no slower than AS/400 query. A QM/SQL statement can be keyed in a minute versus the time-consuming process of the AS/400 Query menu-driven interface -- especially when several files are joined. Before QM I would write AS/400 query specifications on paper for my operator to create later. Now, using QM, I can enter the QM/SQL source statement in a couple minutes myself. Also, QM would be excellent for use as a prototyping tool for a proposed database system's screen inquiries and reports.

SQL is here to stay, and for those of us that do not have the standard SQL system, we can still become proficient in SQL using Query Management while providing a valuable service for our current employers.

Query Management

Figure 1 SAA QM Procedure commands and CL equivalents

FIGURE 1: SAA QM Procedure commands and CL equivalents QM/Proc CL QM Command Description ANZQRY List AS/400 query portability ERASE DLTQMQRY/DLTQMFORM/DLTF Delete QM/Query QM/Form or File EXIT ENDPGM End open of QM/Query instance 
EXPORT RTVQMQRY/RTVQMFORM Get QM source from QM Object GET DCL CHGVAR Get the value of a variable IMPORT CRTQMQRY/CRTQMFORM Create QM/Query or QM/Form PRINT STRQMQRY Print QM/Query data or desc. RUN STRQMPRC Process a QM/Query 
or QM/Proc SAVE STRQMQRY OUTPUT(*FILE) Save QM/Query data to a file SET STRQMQRY SETVAR() Set the value of a variable START Open a QM/Query instance in HLL WRKQMFORM Work with QM/Form functions WRKQMQRY Work with QM/Query functions

 Query Management
 Figure 2 QM/Form source
FIGURE 2: QM/Form source * Header information H QM4 F 01 E V W E R 01 03 91/01/18 09:11 * Text for 6 fields with 6 parameters * / +-----------/ * Col / ? ----1--- ----2--- ----3--- ----4--- ----5--- ----6------- * Flds ? ? Col Use Col Indt Col Wdth Col Edit Col 
Seq Head Text T 1110 006 006 1114 007 1115 006 1116 005 1117 005 1118 003 1113 062 R 1 1 Item_Number * '_' is for next line R BREAK1 1 2 Aisle-Bin R 1 3 Qty_On_Hand R OMIT 1 4 Qty_Allo R 1 5 Std_Cost R SUM 1 9 K02 6 Extended_Cost V 1201 001 0 
* Blank lines before page V 1202 001 2 * Blank lines after page * H E A D E R S P E C I F I C A T I O N S * 2 Txt Lines /----1--- ----2--- ----3------- * ? / Line No. Alignmnt Head Txt V 1210 002 003 1212 004 1213 006 1214 062 R 1 CENTER My Company Name 
&DATE &TIME * Text line 1, default Date/Time R 2 CENTER Inventory Valuation &PAGE * Text line 2, default Page V 1301 001 2 * Blank lines before footer V 1302 001 0 * Blank lines after footer * F O O T E R S P E C I F I C A T I O N S V 1310 001 003 1312 004 
1313 006 1314 062 R 1 CENTER TESTQM * Report name footer text V 1401 002 NO * New Page for Final Text ? V 1402 004 1 * Put final sum at line V 1501 001 1 * Detail line spacing V 1502 003 YES * Outline for break columns ? V 1503 003 YES * Default 
break text asterics ? V 1505 003 YES * Column wrap line kept on a page ? V 1507 003 YES * Column head separators '-' ? V 1508 003 YES * Break summary separators '-' ? V 1510 003 YES * Final summary separators '=' ? * B R E A K L E V E L 1 V 3080 001 1 
* Break level number 1 V 3101 002 NO * New Page V 3102 002 NO * Repeat Column Headings V 3103 001 0 * Blank lines before break text V 3104 001 0 * Blank lines after break text * B R E A K 1 H E A D E R H E A D I N G T 3110 001 003 3112 004 3113 
006 3114 055 R 1 CENTER ** BREAK 1 HEADER TEXT V 3201 002 NO * New page for break footer V 3202 004 1 * Put break at summary line V 3203 006 0 * Blank lines before break footer V 3204 001 1 * Blank lines after break footer 
* B R E A K 1 F O O T E R T 3210 002 003 3212 004 3213 006 3214 055 R 1 CENTER ** BREAK 1 FOOTER TEXT LINE 1 R 2 CENTER ** BREAK 1 FOOTER TEXT LINE 2 E * end of QM/Form source

 Query Management
 Figure 3 Options in QM/Form Source Table Record
FIGURE 3: Options in QM/Form Source Table Record Table Description Type (digits 1&2) Tabular Column Option (digits 3&4) Field 11 10 Specify Table (always first) 12 Field Data Type 13 Field Header 14 Field Usage 15 Indent between fields 16 Field List width 17 Field 
Edit 18 Sequence of Fields on List Page Header 12 Page Footer 13 Final Text 14 Break Header 31 Break Footer 32 10 Specify Table (always first) 12 Line Number 13 Alignment 14 Text

 Query Management
 Figure 4 SAA Edit Codes
 FIGURE 4: SAA Edit Codes Edit Lead Neg. Thous. Curr. Prcnt -99999.99 Code Zeros Sign Sep. Symbol Sign Example E N Y N N N -9.99999999E+06 D2 N Y Y Y N $-99999.99 I3 Y Y N N N -00000099999.990 J2 Y N N N N 000000099999.99 K1 N Y Y N N -99,999.9 L3 
N Y N N N -99999.990 P2 N Y Y N Y -99,999.99% Note: the numeric digit following the edit code only specifies the decimal precision. 

Don Denoncourt is a freelance consultant. He can be reached at This email address is being protected from spambots. You need JavaScript enabled to view it..

---

MC Press books written by Don Denoncourt available now on the MC Press Bookstore.

		Java Application Strategies for iSeries and AS/400 Explore the realities of using Java to develop real-world OS/400 applications. List Price $89.00 Now On Sale

Also Read

TechTip: Prevent Unintentional Deletion of Objects with RESTRICT ON DROP

SQL 101: DDL Recap—Improving Our Basic SQL Tables, Part 2

TechTip: Automate Complex Tasks Using SQL and User-Defined Table Functions