SQL is the strategic interface to DB2/400. See how it handles sorting, dates, date arithmetic, changing data, and grouping data.

In “Beginning SQL, Part 1” (MC, November 1998), I introduced you to the SELECT clause and walked you through a number of examples based on file DSPOBJD, the output of the Display Object Description (DSPOBJD) command over all objects in a library. I’m using this file because everyone should be able to create it and because some of the examples presented here could be expanded into a disk-space management/reporting system.

I’ll be working from the same input file, DSPOBJD. If you no longer have the file, create it as was outlined in last month’s article. Figure 1 shows the fields that will be used in the following examples. I encourage you to key in the examples and try them as you go along. And don’t be afraid to experiment with variations on what is presented here.

Sorting the Results

Part 1 of this series outlined the process of selecting the desired records and fields from the DSPOBJD file. The article illustrated how to create and select a derived field that gave the object size in MB. But suppose you wanted to see the results sorted by object owner and size (perhaps with the idea of talking to those people who have a number of large objects in the library)? Sorting in SQL is done with an ORDER BY clause. Figure 2 shows you how to sort the results by owner, then by size.

Go ahead and give it a try. If the objects in the library have more than one owner, keep paging down and you’ll see the other owners appear.

The field list that follows ORDER BY can contain any of the fields in the select list. You can’t sort by a field that isn’t selected. You should have noticed that I’ve managed to use the assigned name of a derived field here, but I wasn’t able to do this in the WHERE clause last month. I can use the name here because the ORDER BY clause takes place after the fields have been selected. The WHERE clause happens early in the process because one of the first things that the SQL engine attempts is to eliminate records from consideration (for performance reasons). The ORDER BY clause happens much later in the process.

Sort Descending

The results look good, but wouldn’t it be easier to talk to the owners of large objects if the biggest objects came first? No sweat! Try the code in Figure 3, where I’ve added the DESC keyword.

The DESC keyword can follow any field name in the ORDER BY clause and causes the sort on that field to be in descending sequence.

The DSPOBJD file also allows you to see when objects were created. Every object has a creation date, and it could be interesting to see just how old a given object is. Field ODCDAT in DSPOBJD contains the creation date, and Figure 4 might be what you want.

When you run Figure 4, many of you will notice that the dates are being sorted by month. That’s because ODCDAT is a character field and it is created based on your system default date format, which in the United States is generally *MDY. If your machine has another date format, you may have sorted by day or you may have sorted correctly by year. If the latter is your situation, don’t look smug and go away—this topic is still of value to you.

For the rest of this discussion, I’m going to assume that the dates in DSPOBJD are in MDY format. If yours are different, adjust the columns in the example accordingly. One way to get the dates to sort correctly is to derive separate year, month, and day fields from ODCDAT and sort on them. This can be done using the SUBSTR scalar function. SUBSTR takes three parameters: a character field, the starting column number, and, optionally, the number of columns to substring. If the third parameter is omitted, it defaults to the remainder of the field. The code in Figure 5 demonstrates this approach.

Dates split up in this manner make for difficult presentation and manipulation. The dates can be put back into one field using the concatenation function. Figure 6 demonstrates how the concatenation operator (||) works.

Concatenation works on character fields, but what if you wanted to concatenate numbers? One option is to convert the number to a string using the DIGITS scalar function and concatenate the result.

I’ll leave you to check out the specification of DIGITS in the DB2 for AS/400 SQL Reference. Chapter 3 contains descriptions of 80 scalar and seven column functions.

Dates, Arithmetic, and Y2K

Neither of the two previous date approaches is Y2K compliant. Fortunately, SQL has the scalar DATE function, which will convert a character date representation into a true date value, and which will “window” the value if the century isn’t supplied. A six-digit date with a year of 40 or greater is considered to be in the 1900s, and any date with a year of 39 or lower is considered to be in the 2000s.

The trick in getting DATE to work a on 6-byte date is remembering that it must determine whether its argument is in MDY, DMY, or YMD format. DATE assumes the argument is in the format specified by the default job date format and date separator when the query is created. In this example, I’ll create a DATE function argument in MM/DD/YY format using a combination of SUBSTR and ||. The code in Figure 7 works when ODCDAT is in MDY format.

Not only do true date fields sort correctly, but you can also add and subtract from them. If disk space is becoming tight, it might be instructive to see which objects have been created in the last week or the last month. You can get today’s date by coding CURDATE() or CURRENT DATE or CURRENT_DATE. You then subtract from it to get a starting date for the selection. To list objects created in the last month, try the code in Figure 8.

If you wanted objects created yesterday, you could code the following:

= CURDATE() - 1 DAY

Dates

You specify date arithmetic increments or decrements in DAY or DAYS, MONTH or MONTHS, and YEAR or YEARS. This is powerful and useful stuff and might be a quick fix for some low-volume Y2K reports or displays.

Updating Data

Object creation dates are useful, but equally useful is checking when the object was last used. If you have a large file that hasn’t been used for over a year, maybe it should be deleted. (Or, as the more cautious among us do, copy it to tape, delete it, then forget where you put the tape.) The DSPOBJD file provides the last-used date in ODUDAT. SQL can’t tell you if you should get rid of unused objects, but it can easily pick them out for you.

Try running the code in Figure 9 to list objects by their last-used date. Chances are you will crash with the error message, “SQL0181, Value in date, time, or timestamp string not valid.” Because not all objects have been used, some may have blanks in ODUDAT and the DATE function gives an error because it has received the character representation of an invalid date.

One answer is to preprocess the file and change all the blank fields to a low, but valid, date—an opportunity to introduce the UPDATE statement!

In an UPDATE statement, you specify which file to update, the field to update, and its new value in a SET clause, and, optionally, you can specify which records to update using a WHERE clause. I’m using windowing, so the lowest valid date is January 1,
1940. In Figure 10, I am updating DSPOBJD and setting ODUDAT to a constant character value of 010140 wherever ODUDAT is blank. (This changes data in DSPOBJD, so you might want to make a copy of it before going any further.) Try running Figure 10, then run Figure 9 again—if it crashed before, it should work now.

If you want to update more than one field in the record, simply separate field information in the SET clause by commas, for example:

SET ODOBSZ = ODOBSZ/1024, ODUDAT = ODCDAT

This code converts the size in bytes to the size in KB, demonstrating that the new value can be an expression. It also shows that one field can be set to the value of another by setting the last used date to the created date. (Considering the creation date of unused objects as their last-used dates makes more sense to me than introducing January 1, 1940.)

Deleting Data

If you decided that you didn’t want records with blank last-used dates, you could just delete them with a DELETE statement. The DELETE statement needs a file name from which the records are to be deleted and, optionally, a WHERE clause. If you use the code in Figure 11, you’ll delete all records with a blank last-used date. If you omit the WHERE clause, all records in the file are deleted. (Since it is so easy to delete all records, I tread very cautiously when I’m deleting records. I first do a SELECT * FROM file WHERE condition to assure myself that I’m getting the correct records. Then, I replace the SELECT * with DELETE FROM to actually remove the records.)

Totals, Averages, Etc.

You might be interested in seeing how big the biggest object in the library is. That’s where column functions come in. SQL has a MAX column function that does just what you want. The scalar functions that have been used up until now (e.g., DATE) operate on a constant, field, or expression in the current record. A column function (e.g., MAX) operates on a field or expression in all the records in the file.

While you’re at it, why don’t you get the biggest size, the smallest size, and the average size of objects? Try out the code in Figure 12. It should return a single line of output.

Column functions can also work on the records in a set of records. SQL divides records into sets using the GROUP BY clause. A GROUP BY clause is followed by a field or list of fields, each of which must also be in the select list.

Suppose you are curious about what kinds of objects are in the library and how many there are of each type. You can easily determine this information by grouping on field ODOBTP and using the COUNT column function. When you code COUNT(*), you get the number of records. Try the code in Figure 13.

When you use a GROUP BY clause, you can also pick out groups that possess a specific characteristic by using the HAVING clause. If you wanted to find out all owners who have more than 50 programs in the library, you could run the code in Figure 14.

Figure 14 also demonstrates the sequence in which clauses must be coded on a SELECT statement.

A practical way to find duplicates is to code the following statement:

HAVING COUNT(*) > 1

Where Next?

This two-article series was intended to provide you with a jump-start into the power of SQL. I’ve tried to use familiar terms rather than the SQL terms, e.g., file instead of table, field instead of column, and record instead of row. If you want to go further, and I think you should, read the full version of this article on the MC Web site at www.midrangecomputing.com/mc/99/01. (This covers coding parameters in queries!) Then, you’ll at least want to look at the IBM manuals. You may also want to invest in some third-party books, videos, or classes. Check out MC’s discussion forums (including one specific to SQL) at www.midrangecomputing. com/forums. And remember, since the SQL engine is a part of DB2/400, experimentation doesn’t cost anything. So by all means, experiment!

References DB2 for AS/400 Query Manager Use V4R1 (SC41-5212-00, CD-ROM QB3AGF00)

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

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
ODCTRU Char 10 Created by user
ODUDAT Char 6 Last Used date--mmddyy ODOBTP Char 8 Object type

Figure 1: Field names from DSPOBJD used in the examples

SELECT ODOBOW, ODOBNM, ODOBTP,

DECIMAL(ODOBSZ/(1024*1024),7,2) AS SIZE_MB
FROM DSPOBJD
ORDER BY ODOBOW, SIZE_MB

Figure 2: Sorting the data

SELECT ODOBOW, ODOBNM, ODOBTP,

DECIMAL(ODOBSZ/(1024*1024),7,2) AS SIZE_MB
FROM DSPOBJD
ORDER BY ODOBOW, SIZE_MB DESC

Figure 3: Sorting in descending sequence

SELECT ODCDAT, ODOBOW, ODOBNM, ODOBTP
FROM DSPOBJD
ORDER BY ODCDAT

Figure 4: Sorting by date created

SELECT SUBSTR(ODCDAT,5,2) AS CRT_YY,

SUBSTR(ODCDAT,1,2) AS CRT_MM,

SUBSTR(ODCDAT,3,2) AS CRT_DD,

ODCDAT, ODOBOW, ODOBNM, ODOBTP
FROM DSPOBJD
ORDER BY CRT_YY, CRT_MM, CRT_DD

Figure 5: Breaking up “date created” using SUBSTR

SELECT SUBSTR(ODCDAT,5,2) ||

SUBSTR(ODCDAT,1,2) ||

SUBSTR(ODCDAT,3,2) AS CRT_DATE,

ODCDAT, ODOBOW, ODOBNM, ODOBTP
FROM DSPOBJD
ORDER BY CRT_DATE

Figure 6: Using concatenation to rebuild creation date

SELECT DATE( SUBSTR(ODCDAT,1,2) || '/' ||

SUBSTR(ODCDAT,3,2) || '/' ||

SUBSTR(ODCDAT,5,2) ) AS CRT_DATE,

ODCDAT, ODOBOW, ODOBNM, ODOBTP
FROM DSPOBJD
ORDER BY CRT_DATE

Figure 7: True date field using windowing from a 6-digit date

SELECT DATE( SUBSTR(ODCDAT,1,2) || '/' ||

SUBSTR(ODCDAT,3,2) || '/' ||

SUBSTR(ODCDAT,5,2) ) AS CRT_DATE,

ODCDAT, ODOBOW, ODOBNM, ODOBTP
FROM DSPOBJD
WHERE DATE( SUBSTR(ODCDAT,1,2) || '/' ||

SUBSTR(ODCDAT,3,2) || '/' ||

SUBSTR(ODCDAT,5,2) )

>= CURRENT DATE - 1 MONTH
ORDER BY CRT_DATE

Figure 8: Using date arithmetic

SELECT DATE( SUBSTR(ODUDAT,1,2) || '/' ||

SUBSTR(ODUDAT,3,2) || '/' ||

SUBSTR(ODUDAT,5,2) ) AS LAST_USED,

ODOBOW, ODOBNM, ODOBTP
FROM DSPOBJD
ORDER BY LAST_USED

Figure 9: Last used date may cause “invalid date” errors

UPDATE DSPOBJD
SET ODUDAT = '010140'
WHERE ODUDAT = ' '

Figure 10: Updating with a constant

DELETE FROM DSPOBJD
WHERE ODUDAT = ' '

Figure 11: A DELETE statement

SELECT MAX(ODOBSZ), MIN(ODOBSZ), AVG(ODOBSZ)
FROM DSPOBJD

Figure 12: Column functions

SELECT ODOBTP, COUNT(*) AS NUMBER
FROM DSPOBJD
GROUP BY ODOBTP
ORDER BY NUMBER DESC

Figure 13: Column functions on a set

SELECT ODOBOW, COUNT(*) AS NUMBER
FROM DSPOBJD
WHERE ODOBTP = '*PGM'
GROUP BY ODOBOW
HAVING COUNT(*) > 50
ORDER BY NUMBER DESC

Figure 14: The HAVING clause

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