Chapter 25: SQL on i5 and iSeries

SQL plays an increasingly important role within i5 and iSeries applications. IBM has clearly stated that SQL is the direction of the future for the DB2 database. SQL's position as an open industry standard makes it a valuable asset in developing cross-platform applications. All i5 programmers and developers should make the effort to familiarize themselves with SQL syntax and its implementation on the i5. This chapter introduces SQL and some of the standard SQL tools included with i5/OS. Much of this chapter is taken from the book SQL for eServer i5 and iSeries, published by MC Press.

SQL Basics for Beginners

For those who are new to SQL, this section reviews the basic syntax of SQL, giving beginners the opportunity to develop enough skill and comfort with the language to better understand the material presented later in this chapter. The material covered in this section includes:

• SELECT statement

• Field List

• FROM clause

• WHERE clause

• ORDER BY clause

• Column Functions

• Summary Functions

• GROUP BY clause

• HAVING clause

• INSERT statement

• UPDATE statement

• DELETE statement

All the sample data for this section comes from a Customer Master File, CUSTMAST, shown in Figure 25.1, or an Inventory File, INVFILE, shown in Figure 25.2. Depending on which statement is executed, some or all data may be displayed. Later the INSERT, UPDATE, and DELETE statements modify the contents of this file.

Figure 25.1: Data from the CUSTMAST file.

Figure 25.2: Data from the INVFILE file.

SELECT Statement

The SELECT statement is the single most important statement in SQL. Once you master that statement, learning other SQL statements is simple. The SELECT statement has many parts, called clauses. Each clause performs a specific task within the statement. Many of these clauses are optional; only two clauses are required—the SELECT and FROM clauses. These two clauses identify what data to show and where to get it from. Although the SELECT statement is generally performed as a stand-alone command, it is often included within other, more complex statements.

Field List

Each field, sometimes called a data item, is listed immediately after the word SELECT. When all the available fields in the selected file(s) should be included, use a wildcard (*) to include all columns. Typically, each column in the SELECT statement derives from a field in the file referenced in the FROM clause, but a column can be created by an expression (formula) or constant. A series of simple SELECT statements and their results are listed here and illustrated in Figures 25.3, 25.4, 25.5, and 25.6:

      SELECT PART, QTY, PRICE FROM INVFILE 

Figure 25.3: Results of a SELECT containing a constant.

Figure 25.4: Results of a SELECT against INVFILE.

Figure 25.5: Using a formula in a SELECT.

Figure 25.6: Using AS to rename a column.

      SELECT * FROM CUSTMAST      SELECT CUSTNBR, CUSTNAM, CUSTCTY, CUSTST FROM CUSTMAST      SELECT CUSTNAM, 100 FROM CUSTMAST   SELECT PART, QTY, PRICE FROM INVFILE 

      SELECT PART, QTY PRICE FROM INVFILE 

      SELECT PART, QTY * PRICE AS TOTAL_PRICE FROM INVFILE 

FROM Clause

The FROM clause was used in all the above statements to indicate which file to retrieve data from. Multiple files can be included within the FROM clause, separated by commas. When multiple files are included in the FROM clause, the data from the files must be joined together on one or more columns. It's possible to join data from multiple files using the following statement:

      SELECT CUSTNBR, ORDERNBR FROM CUSTMAST, ORDERMAST      WHERE CUSTMAST.CUSTNBR = ORDERMAST.CUSTNBR 

Although this method is functional, the JOIN keyword allows for both a simpler and more flexible syntax for joining files:

      SELECT CUSTNAME, ORDERNBR FROM CUSTMAST      JOIN ORDERMAST ON CUSTNBR = CUSTNO 

WHERE Clause

The WHERE clause is optional. If it is included, it follows the FROM clause and is used to include only the desired records in the result set. The WHERE clause may use simple expressions such as:

      SELECT * FROM CUSTMAST WHERE CUSTST = 'OH' 

Additional restrictions on the data in the result set can be added by using AND and OR to link additional tests, such as:

      SELECT * FROM CUSTMAST WHERE CUSTST = 'OH' AND      CUSTCTY = 'TOLEDO'      SELECT * FROM CUSTMAST WHERE CUSTST = 'OH' OR CUSTST = 'MI' 

These are the simplest examples of the WHERE clause. Other more sophisticated examples appear throughout this chapter and in IBMs online reference manuals.

ORDER BY Clause

The ORDER BY clause sequences the records in the result set. One or more column names can be used:

      SELECT * FROM CUSTMAST WHERE CUSTST = 'OH'      ORDER BY CUSTZIP      SELECT * FROM CUSTMAST ORDER BY CUSTST, CUSTZIP 

The result set created includes all the fields and records from the customer master file, sorted by state and then zip code within state.

Column Functions

Two types of column functions exist in SQL: scalar and summary. Summary functions are discussed later in this section. Scalar functions typically perform some sort of conversion on a column. For example, scalar functions could translate data to upper or lowercase, as illustrated here and in Figures 25.7 and 25.8:

      SELECT UPPER(CUSTNAM) FROM CUSTMAST 

Figure 25.7: Using a function in a SELECT.

Figure 25.8: Using a function to translate to lowercase.

      SELECT LOWER(CUSTNAM) FROM CUSTMAST 

Scalar functions could also perform other useful tasks, such as converting a numeric amount to an integer (Figure 25.9):

      SELECT PART, INT(QTY) FROM INVFILE 

Figure 25.9: Using INT to convert data to integer.

A complete list of the available scalar functions can be found in IBMs SQL reference manual.

Summary Functions

The summary functions are quite different from scalar functions. The column functions produce a single row in the result set (Figures 25.10, 25.11, 25.12):

      SELECT SUM(QTY) FROM INVFILE 

Figure 25.10: Total of the Quantity Field from INVFILE.

Figure 25.11: Average of the Quantity Field from INVFILE.

Figure 25.12: The Minimum and Maximum Quantities from INVFILE.

      SELECT AVG(QTY) FROM INVFILE 

      SELECT MIN(QTY), MAX(QTY) FROM INVFILE 

GROUP BY Clause

The summary functions provide a single result for the entire table. That is not always enough; sometimes you need subtotals or averages by part, customer, order, date, or other values (Figures 25.13 and 25.14). To create subtotals with summary functions, add the GROUP BY clause:

      SELECT SUM(QTY) FROM INVFILE GROUP BY PART 

Figure 25.13: Subtotals of Quantity for each part.

Figure 25.14: Average for each part.

      SELECT AVG(QTY) FROM INVFILE GROUP BY PART 

      SELECT MIN(QTY), MAX(QTY) FROM INVFILE      GROUP BY PART, LOCATION 

The last example (Figure 25.15) summarizes the data by location within part. Any number of columns may be used; simply include them in the list separated by commas. The first field listed is the highest order in the sort sequence. The last field listed is the lowest-order sort field. No other columns may be included with the summary functions, except those used in the GROUP BY clause. In these examples, the part number is used as the GROUP BY field and can be included in the list of selected columns (Figures 25.16, 25.17, and 25.18):

      SELECT PART, SUM(QTY) FROM INVFILE GROUP BY PART 

Figure 25.15: Minimum and Maximum for each part.

Figure 25.16: Part numbers and their total quantity.

Figure 25.17: Part numbers and their average quantity.

Figure 25.18: The Min and Max quantities for each part in each location.

      SELECT AVG(QTY), PART FROM INVFILE GROUP BY PART 

      SELECT PART, LOCATION, MIN(QTY), MAX(QTY) FROM INVFILE      GROUP BY PART, LOCATION 

HAVING Clause

The HAVING clause performs a function similar to that of the WHERE clause. As the WHERE clause is used to select which record to include in the result set, the HAVING clause is used to select which subtotals to include in the result table (Figures 25.19, 25.20, and 25.21). Therefore, it is only used with a statement that has summary functions. For example:

      SELECT PART, SUM(QTY) FROM INVFILE      GROUP BY PART HAVING SUM(QTY) > 0 

Figure 25.19: Including only subtotals having a certain value.

Figure 25.20: Show parts and their average quantity if over 50.

Figure 25.21: Min and Max quantities for parts in specific locations.

      SELECT AVG(QTY), PART FROM INVFILE      GROUP BY PART HAVING AVG(QTY) > 50 

      SELECT PART, LOCATION, MIN(QTY), MAX(QTY) FROM INVFILE      GROUP BY PART, LOCATION      HAVING LOCATION = 'R1-001'      OR LOCATION = 'R2-203' 

INSERT Statement

If you need to add new records to a database, two common syntaxes are used to accomplish that. Individual records may be added one at a time, or whole sets of records from one file may be added to another. The basic syntax to insert one record at a time uses the VALUE clause:

      INSERT INTO INVFILE VALUES('PART1', 100,19.99)      INSERT INTO INVFILE VALUES('PART2', 150,14.99)      INSERT INTO INVFILE VALUES('PART1', 200,10.00) 

To add more than one record at a time using the VALUES keyword, simply add another set of values after the initial set, as shown here:

      INSERT INTO INVFILE VALUES("PART1", 100,19.99),      ("PART2", 150,14.99),      ("PART1", 200,10.00) 

More often however, a sub-SELECT is a better choice than the VALUE clause for adding multiple rows. The sub-SELECT extracts some number of records from one or more files and inserts all the selected data into the specified file. Examples of the INSERT statement using sub-SELECTs are listed here:

      INSERT INTO INVWORK (SELECT * FROM INVFILE)      INSERT INTO INVWORK (SELECT PART, QTY, PRICE      FROM INVFILE)      INSERT INTO INVFILE (SELECT PART, QTY, PRICE      FROM INVFILE WHERE PRICE < 20) 

UPDATE Statement

If you need to change data in existing database records, rather than add new ones, use the UPDATE statement. UPDATE changes the contents of one or more fields within the file, updating all the selected records. If a WHERE clause is included with the statement, only selected records are changed. If no WHERE clause is included, all the records in the file are updated. Examples of both syntaxes are shown here:

      UPDATE INVFILE SET QTY = 0 WHERE PART = "PART1" AND        LOCATION = "R1-001"      UPDATE INVFILE SET QTY = 100, PRICE = 19.99        WHERE PART = "PART1"      UPDATE INVFILE SET PRICE = PRICE * 1.1 

DELETE Statement

You can also remove records from a file using the DELETE statement. If a WHERE clause is included in the statement, only the selected records are removed. If no WHERE clause is included, all records in the file are removed:

      DELETE FROM INVWORK      DELETE FROM INVWORK        WHERE LOCATION = 'R1-001'      DELETE FROM INWORK        WHERE LOCATION = 'R1-001' AND        PART = "PART2" 

All the examples provided within this chapter are designed as simple and (hopefully) clear examples of how to write basic SQL statements. If you are new to SQL, these examples should help you to make sense of the more complex statements used elsewhere in this chapter.