If you aren’t using SQL or have only a nodding acquaintance with it, this article is for you. It explains how SQL coexists with DDS, how it can replace procedural code, and how it can make your life easier. This article describes basic syntax and shows some practical examples that you can run on your own machine. Experimentation with SQL is an easy and inexpensive (free!) way to learn. Even if you are already using SQL, you may find some new techniques in the examples.

The AS/400 comes with a native database management system, DB2/400. Files in this system are traditionally described with DDS and accessed a record at a time from a third-generation language (3GL) such as RPG or COBOL. SQL can work concurrently with native access to allow you to manipulate these files.

“SQL is Rochester’s strategic interface” to DB2/400. So wrote Kent Milligan of the DB2/400 Solutions Team in a recent Internet discussion (find it with Deja News by searching on “SQL is Rochester’s strategic interface”). SQL has been around on the AS/400 since at least V2R1, and with each new release of the operating system, IBM has improved both the functionality and performance of the product. It is no longer possible to write off SQL as a passing fad.

IBM defines SQL in DB2 for AS/400 SQL Programming V4R2 this way: “SQL consists of statements and clauses that describe what you want to do with the data in a database and under what conditions you want to do it.” This is an excellent definition, but it needs a little fleshing out, since I could describe an RPG batch program the same way.

SQL vs. DDS

SQL eliminates many of the procedural issues you must deal with when you write applications with a language like RPG. Rather than access and update data a record at a time using controlling procedural logic in a 3GL, with one SQL statement, you can operate on a set of data. (Think of a set as a group of records with some common characteristic,
e.g., all records with the same customer number.) And, with SQL, you generally don’t have to worry about how the data is keyed or sorted.

In a single SQL statement, you specify what set (group) of records you want, how the set should be sorted, and what you want done with each record in it. You don’t have to

worry about coding logic to position into the file, to read records in a loop, or to select records for display and/or update.

You can use IBM’s implementation of SQL in DB2/400 by coding SQL statements in a 3GL. You can also execute ad hoc SQL statements, either interactively or in a CL program by using the Start Query Management Query (STRQMQRY) command. One such statement can often replace a small to moderate 3GL program that uses native access.

Consider a few other advantages of SQL over native OS/400 data access:
• SQL queries can accept parameters. How often have you wanted to have something like this in a CL program?

RUNQRY QRY(LARGEOBJ) +

OWNER(&OWNER) +

MINSIZEMB(&MB) +

UNUSED(&DAYS)

This task would be pretty difficult to do in Query/400 because Query/400 doesn’t take parameters. But it’s remarkably simple with SQL because SQL queries do allow parameters.

• SQL is like Open Query File (OPNQRYF) when it comes to filtering, sorting, and manipulating data. But since SQL runs inside the program, rather than in a separate CL, you have a lot more control. (I’ve seen some OPNQRYF/3GL combinations that will run without the OPNQRYF and will cause significant damage in the process.) I find the SQL syntax easier than OPNQRYF, and SQL has more functions for data manipulation.

• SQL is largely immune to file changes. Add a field to a file, and programs that use the file will get a level check, unless you recompile them over the new file. (It’s best not to use the dangerous LVLCHK(*NO) practice.) Query/400 will also object. SQL is much more tolerant and won’t object, though it can’t handle all changes. (For example, it is going to get upset if you remove a field that it was using. And if you lengthen a numeric field that is being read into a program, you may also want to check that the receiving variable is big enough to hold the new larger field without truncating it.)

• SQL isn’t just an IBM standard. It is the de facto relational database access language, and college graduates will know more about it than they will about DDS. (Something to consider in a tight job market.)

• SQL is where IBM is putting its development efforts and has been for several years. Remember the “strategic interface” comment at the beginning of the article. Some of the enhancements that go into SQL find their way into DDS, but in recent releases, DDS has been the poor relative.

These arguments are not meant to imply that SQL is going to totally replace DDS and native I/O now or in the near future. However, SQL is powerful enough and efficient enough now that systems can be written without DDS and native I/O. SQL is such a powerful tool in the AS/400 developer’s toolkit that is worth getting acquainted with and shouldn’t be ignored.

SQL Syntax Overview

The basic statements used to manipulate data are SELECT, UPDATE, DELETE, and INSERT. Each statement has one or more clauses; for example, FROM and WHERE. Clauses in a statement must come in the correct sequence; for example, FROM always comes before WHERE. A typical SELECT statement might look like this:

SELECT ODLBNM, ODOBNM,

ODOBSZ, ODOBTX
FROM DSPOBJD
WHERE ODOBTP = ‘*FILE’

SQL syntax is free-form, and there is no punctuation between clauses. Lists, like the “ODLBNM, ODOBNM, ODOBSZ, ODOBTX” text shown, are separated by commas. Parentheses are used for grouping and prioritizing.

The following statement is syntactically correct and identical in function to the previous statement but much less readable:

SELECT ODLBNM,
ODOBNM,ODOBSZ,
ODOBTX FROM
DSPOBJD WHERE
ODOBTP=’*FILE’

In this article, I’ll be using the structured layout for readability.

Examples in This Article

All the examples use a common input file, DSPOBJD, the output of the Display Object Description (DSPOBJD) command. To build such a file, issue a command something like this:

DSPOBJD OBJ(inlib/*ALL) +

OBJTYPE(*ALL) +

OUTPUT(*OUTFILE) +

OUTFILE(outlib/DSPOBJD)

Here, inlib is a moderately sized library containing several different object types with a variety of creation and last used dates; outlib is a library in your library list. It could be QTEMP.

The records in the file created from the DSPOBJD command contain many fields that tell you all kinds of useful things about the objects in your library.

Are you interested in what else is in the file? The CL manual says: “The database format (QLIDOBJD) of the output file is the same as that used in the IBM-supplied file database QADSPOBJ.” You’ll find this file in QSYS.

In these examples, I’ll use the fields listed in Figure 1.

Simple SELECT Statements

The SELECT statement in Figure 2 is almost as simple as you can get with SQL: The first line starts the statement with the SQL reserved word SELECT. It is followed by at least one blank and then a list of field names to be selected, each separated by commas and, optionally, one or more blanks. At least one blank must follow the last field selected. In this example, I’ve selected the object name, the object type, the size of the object, and the object description.

The second line is the FROM clause; it specifies the file from which the data is to be selected.

Try running this SQL interactively. (See the “Use SQL Free!” sidebar if you need instructions.) You should see a report that is formatted like Figure 3.

Obviously, your data will be different, but you’ll notice that the DDS column heading text has been used to identify the columns.

Here is the simplest useful SELECT statement you can run:

SELECT *
FROM DSPOBJD

The asterisk character (*) is shorthand for “all fields in the same order they appear in the record.” Run this statement, and you should see a report that runs off the right of your screen, formatted like the one in Figure 4.

If you keep scrolling your display to the right, you’ll eventually come to the end of the data. (For Query/400 users, this is functionally equivalent to RUNQRY *NONE DSPOBDJ). A maximum of 8,000 fields can be retrieved this way.

The WHERE Clause in the SELECT Statement

Suppose you want to list just the files in your library; you would include a WHERE clause, as shown in Figure 5.

Run this, and you’ll see that, indeed, only files are listed. (Presuming, of course, that you ran DSPOBJD over a library that included some files.)

This WHERE clause introduces the equal to (=) relational operator. The other operators are greater than (>), greater than or equal to (>=), less than (<), less than or equal to (<=), and not equal to (<>). On either side of a relational operator, you can have a field

name or an expression. For example, to select objects in which the owner and creator are different, you’d code WHERE ODOBOW <> ODCRTU.

A More Complex WHERE Clause

Suppose that, for one particular object owner, you want to list files that are bigger than 2 MB or programs that are bigger than 70 K. The query in Figure 6 demonstrates this using AND, OR, and parentheses.

This is standard Boolean logic with parentheses used to group conditions. Run this, and you should see something like Figure 7.

(Your data will be different, so play around with the object size selection numbers as necessary.)

Figure 6 cheated a bit, because it assumed that a KB was 1,000 bytes. As most programmers know, a KB is 1,024 bytes, and a MB is 1,048,576 bytes. (You knew that last one exactly, didn’t you? I had to get my calculator out.) Rather than use old technology (my calculator), the WHERE clause gives me the opportunity to introduce expressions. Figure 8 is an example that includes an expression in both the SELECT list and the WHERE clause.

I’ve decided that I want to see file sizes in MB instead of bytes, so I’ve done two things in the select list. First, the expression ODOBSZ/(1024*1024) reduces bytes to MB (I don’t have to remember that the conversion factor is 1,048,576). Expressions can be a combination of fields, constants, and functions, grouped and prioritized as necessary with parentheses. Second, I’ve renamed the result of the expression with AS SIZE_MB. The AS clause is a good way to give meaningful names to derived fields.

Note that an identical expression appears in the WHERE clause. You’d think you could code SIZE_MB in its place, but you can’t. Unfortunately, SQL clauses are executed in a fixed sequence, and the WHERE clause always comes before the SELECT clause, so during the WHERE execution, SIZE_MB has not yet been materialized. Run this query, and you’ll see something that looks like Figure 9.

SQL has used the new name for the column heading and has made sure SIZE_MB is very precise; but it’s also ugly, hard to read, and a lot wider than what I wanted. So now I can introduce functions.

I’ll produce something more readable if I code the select list this way:

DECIMAL(OBSZ/(1024*1024),7,2) AS SIZE_MB

DECIMAL is a scalar function that accepts three parameters: a numeric value, a precision (the number of digits in the result), and a scale (the number of digits after the decimal place.) Try running Figure 10.

You should see results that look like Figure 11, which is much more readable.

To Be Continued...

You have the basis of some useful queries here—if you could just somehow make the size in megabytes a parameter or make the user name a parameter (or maybe both), or if you could somehow pass in a creation date or pick only objects created in the last week. The good news is that you can do all this easily with SQL queries. The bad news is that I don’t have space to cover it in this article.

This article has only scratched the surface of SQL. There is a wealth of easy-to-use functionality still to be revealed. In an upcoming issue of MC, look for an article that digs a bit deeper. (Or if you can’t wait, take a look at Harness the Power of AS/400 SQL, a video by Brian Singleton, available from MC Publishing.)

If you have never tried running interactive SQL, go try it now. The only expense is a little of your time.

References

DB2 for AS/400 SQL Programming V4R2 (GC41-5058-01,CD-ROM QB3AQA01/QB3AQ801)

DB2 for AS/400 SQL Reference V4R2 (SC41-5612-01, CD-ROM QB3AQA01/QB3AQ901)

Harness the Power of AS/400 SQL. Video. Singleton, Brian. Carlsbad, California: MC Publishing Co., 1996.

OS/400 DATA through RPLxxx Commands V3R2 (SC41-3275-01)

The cost of getting started with SQL is zero. IBM has built SQL runtime support into the AS/400. It is hard to tell by looking at current documentation exactly which older releases of OS/400 don’t have SQL support, but, if you are on V2 or later, you probably don’t have to worry. However, there is an easy way to check: If you have the Create Query Management Query (CRTQMQRY) command on your machine, you can run ad hoc interactive SQL statements and experiment with the examples in this article. If you don’t, you need to think about upgrading to a new release of OS/400 before you get into SQL.

Here’s a summary of various methods that let you run SQL on your machine:

Free SQL with CRTQMQRY and Start Query Management Query (STRQMQRY)

1. Create a source physical file with a record length of 91. (You can use longer record lengths, but if you key anything beyond column 79, it is ignored, which may make debugging difficult.) Use source something like this:

CRTSRCPF FILE(yourlib/QSQL) +

RCDLEN(91) +

TEXT(‘SQL Query Source’)

2. Using SEU, create a member called PROOF that contains this text:

SELECT * FROM QSQL

Make the type SQL for documentation purposes, though TXT works just as well.

3. “Compile” PROOF using the CRTQMQRY command from the command line. Running this command will create an object named PROOF of type *QMQRY.

4. Run PROOF using the STRQMQRY command from the command line.

Running this command should display the SQL command you entered in member PROOF. (Note that you can also code the STRQMQRY command in CL programs.)

With this technique, you can create additional SQL source members, “compile” them with CRTQMQRY, and run them with STRQMQRY.

For convenience, you might want to create a couple of PDM user commands, like those in Figure A.

Free SQL with EXCSQLSTM

In December 1994, Midrange Computing published the Execute SQL Statement (EXCSQLSTM) utility, which is based on CRTQMQRY and STRQMQRY and executes an SQL statement directly from the command line. The code can be downloaded from the Midrange Computing Web site at http://www.midrangecomputing.com/ftp/prog/94/B941207
.

Embedded SQL

If you want to embed SQL statements in a 3GL, there is additional cost because you will need to purchase the DB2 Query Manager and SQL Development Kit (licensed product 5769ST1). This product includes precompilers that allow you to embed SQL statements in C, COBOL, FORTRAN, PL/I, RPG, and REXX programs. It also gives you the Start SQL Interactive Session (STRSQL) and the Start DB2 Query Manager AS/400 (STRQM) commands.

• STRSQL allows you to build SQL statements interactively. You can display and select field names as you are prompted to build the various clauses of the statement. You can then run the statement, but you can’t save it, though STRSQL has a command history similar to QCMD.

• STRQM provides all the facilities of SQL in building SQL statements, but it also allows you to save them as *QMQRY objects that can be run later by STRQMQRY. It also provides report writer features like those of Query/400.

CQ /* Create Query */ CRTQMQRY QMQRY(&L/&N) SRCFILE(&L/&F)
SQ /* Start a QM Query */ STRQMQRY QMQRY(&L/&N)

Figure A: These user-defined PDM options make working with Query Management easier

Field Type Length Description

ODLBNM Char 10 Library where object resides ODOBNM Char 10 Object name
ODOBSZ Packed 10, 0 Object size in bytes ODOBTX Char 50 Object text, i.e., description ODCDAT Char 6 Created date—mmddyy ODOBOW Char 10 Object owner
ODOBTP Char 8 Object type
ODCTRU Char 10 Created by user
ODUDAT Char 6 Last Used date—mmddyy

Figure 1: DSPOBJD produces a file that contains these fields, among others

SELECT ODOBNM, ODOBTP, ODOBSZ, ODOBTX
FROM DSPOBJD

Figure 2: A simple SELECT statement

Object Object Object Type Size Text description ACCT *PGM 32,768 Print list of Inv Adjustment

Accounts BIGOBJC *PGM 45,056 List big old objects in a library BIGOPGMR *PGM 49,152 List BIG unused object for PGMRS BKUP *PGM 28,672 Save LENNON$S Library
CCCSQLC *PGM 36,864 Compile RPG ILE SQL program

Figure 3: Results of a simple SELECT statement

Display Display Display Object Object Storage Century Date Time Library Object Type Attribute Freed

0 082498 125834 LENNON$S ACCT *PGM CLP 0

0 082498 125834 LENNON$S BIGOBJC *PGM CLP 0

0 082498 125834 LENNON$S BIGOPGMR *PGM CLP 0

0 082498 125834 LENNON$S BKUP *PGM CLP 0

0 082498 125834 LENNON$S CCCSQLC *PGM CLP 0

Figure 4: Results of the simplest SELECT statement Figure 5: A simple WHERE clause

SELECT ODOBNM, ODOBTP, ODOBSZ, ODOBOW
FROM DSPOBJD
WHERE ODOBOW = ‘LENNON$S’ AND

( ODOBTP = ‘*FILE’ AND ODOBSZ > 200000000

OR ODOBTP = ‘*PGM’ AND ODOBSZ > 70000

)

Figure 6: A complex WHERE clause

SELECT ODOBNM, ODOBTP, ODOBSZ, ODOBTX
FROM DSPOBJD
WHERE ODOBTP = ‘*FILE’

Object Object Object Object Type Size Owner CMPC *PGM 77,824 LENNON$S CMPFSC *PGM 73,728 LENNON$S MUSICT1D *FILE 238,305,280 LENNON$S

Figure 7: Results of a complex WHERE clause

SELECT ODOBNM, ODOBTP, ODOBOW,

ODOBSZ/(1024*1024) AS SIZE_MB
FROM DSPOBJD
WHERE ODOBOW = 'LENNON$S' AND ODOBTP = '*FILE'

AND ODOBSZ/(1024*1024) > 200

Figure 8: Using an expression

Object Object Object Type Owner SIZE_MB MUSICT1D *FILE LENNON$S 227.265625000000000000000

Figure 9: Results of using an expression

SELECT ODOBNM, ODOBTP, ODOBOW,

DECIMAL(ODOBSZ/(1024*1024),7,2) AS SIZE_MB
FROM DSPOBJD
WHERE ODOBOW = 'LENNON$S' AND ODOBTP = '*FILE'

AND ODOBSZ/(1024*1024) > 200

Figure 10: Using a function

Object Object Object Type Owner SIZE_MB MUSICT1D *FILE LENNON$S 227.26

Figure 11: Results of using a function

Sam Lennon is an analyst, developer, consultant and IBM i geek. He started his programming career in 360 assembly language on IBM mainframes, but moved to the AS400 platform in 1991 and has been an AS400/iSeries/i5/IBM i advocate ever since.

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