14
Fri, Jun
4 New Articles

Beginning SQL

SQL
Typography
  • Smaller Small Medium Big Bigger
  • Default Helvetica Segoe Georgia Times

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

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.

BLOG COMMENTS POWERED BY DISQUS

LATEST COMMENTS

Support MC Press Online

$0.00 Raised:
$

Book Reviews

Resource Center

  • SB Profound WC 5536 Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application. You can find Part 1 here. In Part 2 of our free Node.js Webinar Series, Brian May teaches you the different tooling options available for writing code, debugging, and using Git for version control. Brian will briefly discuss the different tools available, and demonstrate his preferred setup for Node development on IBM i or any platform. Attend this webinar to learn:

  • SB Profound WP 5539More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.

  • SB HelpSystems ROBOT Generic IBM announced the E1080 servers using the latest Power10 processor in September 2021. The most powerful processor from IBM to date, Power10 is designed to handle the demands of doing business in today’s high-tech atmosphere, including running cloud applications, supporting big data, and managing AI workloads. But what does Power10 mean for your data center? In this recorded webinar, IBMers Dan Sundt and Dylan Boday join IBM Power Champion Tom Huntington for a discussion on why Power10 technology is the right strategic investment if you run IBM i, AIX, or Linux. In this action-packed hour, Tom will share trends from the IBM i and AIX user communities while Dan and Dylan dive into the tech specs for key hardware, including:

  • Magic MarkTRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now!  Request Now.

  • SB HelpSystems ROBOT GenericForms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.

  • SB HelpSystems ROBOT GenericIT security is a top priority for businesses around the world, but most IBM i pros don’t know where to begin—and most cybersecurity experts don’t know IBM i. In this session, Robin Tatam explores the business impact of lax IBM i security, the top vulnerabilities putting IBM i at risk, and the steps you can take to protect your organization. If you’re looking to avoid unexpected downtime or corrupted data, you don’t want to miss this session.

  • SB HelpSystems ROBOT GenericCan you trust all of your users all of the time? A typical end user receives 16 malicious emails each month, but only 17 percent of these phishing campaigns are reported to IT. Once an attack is underway, most organizations won’t discover the breach until six months later. A staggering amount of damage can occur in that time. Despite these risks, 93 percent of organizations are leaving their IBM i systems vulnerable to cybercrime. In this on-demand webinar, IBM i security experts Robin Tatam and Sandi Moore will reveal:

  • FORTRA Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:

  • FORTRAManaging messages on your IBM i can be more than a full-time job if you have to do it manually. Messages need a response and resources must be monitored—often over multiple systems and across platforms. How can you be sure you won’t miss important system events? Automate your message center with the Robot Message Management Solution. Key features include:

  • FORTRAThe thought of printing, distributing, and storing iSeries reports manually may reduce you to tears. Paper and labor costs associated with report generation can spiral out of control. Mountains of paper threaten to swamp your files. Robot automates report bursting, distribution, bundling, and archiving, and offers secure, selective online report viewing. Manage your reports with the Robot Report Management Solution. Key features include:

  • FORTRAFor over 30 years, Robot has been a leader in systems management for IBM i. With batch job creation and scheduling at its core, the Robot Job Scheduling Solution reduces the opportunity for human error and helps you maintain service levels, automating even the biggest, most complex runbooks. Manage your job schedule with the Robot Job Scheduling Solution. Key features include:

  • LANSA Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.

  • LANSAWhen it comes to creating your business applications, there are hundreds of coding platforms and programming languages to choose from. These options range from very complex traditional programming languages to Low-Code platforms where sometimes no traditional coding experience is needed. Download our whitepaper, The Power of Writing Code in a Low-Code Solution, and:

  • LANSASupply Chain is becoming increasingly complex and unpredictable. From raw materials for manufacturing to food supply chains, the journey from source to production to delivery to consumers is marred with inefficiencies, manual processes, shortages, recalls, counterfeits, and scandals. In this webinar, we discuss how:

  • The MC Resource Centers bring you the widest selection of white papers, trial software, and on-demand webcasts for you to choose from. >> Review the list of White Papers, Trial Software or On-Demand Webcast at the MC Press Resource Center. >> Add the items to yru Cart and complet he checkout process and submit

  • Profound Logic Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application.

  • SB Profound WC 5536Join us for this hour-long webcast that will explore:

  • Fortra IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn: