SQL for eServer i5 and iSeries

by Kevin Forsythe

Overview

This examination of how SQL functions on the iSeries considers at all possible environments, including the traditional 5250 green screen applications, the SQL scripting tool within iSeries Navigator, and Visual Basic. Database management from creation to deletion is covered in detail. Programmers will learn how to implement referential constraints and primary and unique key indexes, check constraints, and journal physical files. How to extend a database with user-defined functions and procedures and how the new GUI development tools provide simplified methods for running, debugging, and tuning SQL statements are also explained.

Product Details

ISBN-13:	9781583470480
Publisher:	Mc Press
Publication date:	09/28/2004
Edition description:	REV
Pages:	350
Product dimensions:	7.00(w) x 9.00(h) x 0.73(d)

About the Author

Kevin Forsythe has over 19 years of experience on the iSeries and other platforms as programmer, analyst, consultant, and instructor. He blends a wide range of experiences with a passion for teaching and is the author of numerous articles. Kevin has developed AS/Credentials Courses for Interactive SQL and Embedded SQL. He is a frequent and award-winning speaker at COMMON conferences and other iSeries user conferences.

Read an Excerpt

SQL for eServer i5 and iSeries

---

By Kevin Forsythe

MC Press

Copyright © 2008 MC Press Online, LP
All rights reserved.
ISBN: 978-1-58347-680-2

---

Contents

Title Page,
Copyright Page,
Acknowledgments,
Preface,
1 - Interactive SQLs,
2 - SQL in iSeries Navigator,
3 - Embedded SQL,
4 - Advanced SQL Examples,
5 - Optimizing SQL,
6 - Creating Database Objects,
7 - Creating Functions,
8 - Creating Stored Procedures,
9 - Creating SQL-based Tools,
10 - Using SQL from within Microsoft Office,
A - SQL Basics for Beginners,


CHAPTER 1

Interactive SQLs

SQL is a common database language available on most if not all major computing platforms. However, it has only recently begun receiving widespread use on the iSeries platform. Legacy applications typically contain databases that were defined with Data Definition Specifications (DDS) rather than SQL. Using SQL to interact with these legacy databases on the iSeries involves some unique concerns.

First, those users new to SQL must familiarize themselves with the available tools,such as STRSQL and iSeries Navigator. Then, the language itself must be learned. The single most important command in SQL is the SELECT statement. This statement performs the majority of the data retrieval work, and learning its syntax makes learning other statements much easier.

Beyond the basic syntax of the SELECT statement, interacting with legacy databases often involves the unpleasant task of assembling a date from one or more columns that are simply stored as either numeric or character data. Or even worse, working with database designs that are older than DB2. Such databases often contain data in a packed format that must be manually unpacked to use.

Examples of Basic SELECTs

The SELECT statement is the backbone of SQL. It retrieves one or more columns of data from one or more files in the database. "File" is the traditional iSeries term for what is often called a "Table" on other systems. The DB2 UDB database is integrated into the OS/400 operating system of the iSeries. This integration offers tremendous advantages in stability, security, and performance over systems in which the database is simply an application that runs on the operating system. Learning to extract data from the database using SELECT will improve productivity; these same SELECT statements can be combined with other SQL statements to perform even more powerful functions. We review database updates later in this chapter. For now, let us focus on the SELECT statement.

Tool of Choice

Before we can run the SELECT statement, we need to discuss the tools that can be used to execute SQL statements on the iSeries. The STRSQL command is a popular choice for running interactive SQL statements on the iSeries, but it requires the installation of the DB2 Query Mgr and SQL DevKit. This licensed program also provides support for embedding SQL within programs written in RPGIV and other languages. In recent releases, IBM has updated the base operating system to include the capability to run SQL statements. For example, the RUNSQLSTM command can be used to execute SQL statements that have been prewritten in source members. This command has been around a while but, prior to V5R1, it was not in the base operating system. iSeries Navigator now includes a GUI SQL script processor, which provides a free interactive SQL tool with excellent features. We look at all these tools and more in this book, but for now, let's get started on the traditional "green screen" or 5250 data stream environment and execute the STRSQL command. (If you do not have the DB2 Query Mgr and SQL DevKit, use the Run SQL Script program in iSeries Navigator instead.)

Sample Database

The database is the foundation upon which all SQL statements are built. A poorly designed database will make all your SQL efforts more complicated. You can do things to overcome those complications. Some are discussed later in this section, and others are covered in the remaining chapters of this book. For the purposes of this book, the database is kept as simple as possible.

Before you can successfully use SQL, you must first understand the library and file structure of your applications. In this book, all the sample files exist in the same library. Production applications, however, often utilize files in multiple libraries. As long as all the files referenced exist once and only once within the library list being used when the SQL statements are executed, there is no need to qualify the file names with their associated libraries.

In Figure 1.1, CUSTFLAT illustrates a simple customer master file that is typical of applications developed in the SYS/36 environment on the iSeries or on older systems. It is not an externally described database file, but rather a program-described "flat file." On the iSeries, even program-described files have rudimentary field definitions.

The field definitions for CUSTFLAT are shown in Figure 1.2. Such field definitions can be used to extract data from the file with a little extra effort or — in some cases — a lot of extra effort!

The data in this type of file is stored in long strings of text that the programmer must know how to map or parse into the proper fields (see Figure 1.3). Because no system object contains information on how to map the fields, accurate documentation is critical for working with program described files.

Figure 1.4 shows the CUSTMAST file, which is an externally described file containing the same information as CUSTFLAT. The field layout of CUSTMAST can be found by running the command:

[ILLUSTRATION OMITTED]

The standardized field definitions for the file are displayed in Figure 1.5 and make using this file within SQL statements far easier than program-described files.

INVFILE is an externally described database file (Figure 1.6) that contains information about inventory items that are stored in a warehouse. Its field layout is illustrated in Figure 1.7 and can be found by running the command:

[ILLUSTRATION OMITTED]

These files are somewhat simplified compared to typical production database files, but for the purpose of illustrating the techniques and concepts involved in using SQL on the iSeries, they are well designed.

Getting Started with STRSQL

When STRSQL executes, it displays the SQL command interface shown in Figure 1.8

Use this interface to type in an SQL statement, press Enter, and the results will be displayed. To get started let's run a simple statement:

SELECT * FROM KPFSQL/CUSTMAST

This statement displays every column and every row in CUSTMAST, as shown in Figure 1.9. Use the "Position to line" and "Shift to column" fields on the display to change the rows and columns displayed. When finished, press Enter or F12 to return to the previous screen.

You may not wish to see every record in the table. The WHERE clause is used to select specific records. One example might be to filter out only the records for customers in Ohio:

SELECT * FROM KPFLIB/CUSTMAST WHERE CUSTST = 'OH'

Figure 1.10 shows the result of the select. All the columns in the table are included in the result, just as in the previous example, but this time only two records are included.

In the previous two examples, we used qualified file names. If the library KPFLIB exists in our library list, and the CUSTMAST file only exists in one library, we can safely omit the library name and write the statement as:

SELECT * FROM CUSTMAST WHERE CUSTST = 'OH'

SQL is not case sensitive, except for values inside quotes. So,

SELECT * from custmast where custst = 'OH'

will work fine, but

SELECT * from custmast where custst = 'oh'

will not. The second version fails because the data in the database is in uppercase and the quotes around the literal 'oh', requires the state to be in lowercase. To make this book more readable, example SQL statements will be in uppercase.

So far, in all of the selects we have used '*' to indicate that all fields in the file should be included in the output table. To select only a couple columns of data, we can write statements that specify exactly which fields, or columns to include.

The result table shown in Figure 1.11 includes only the customer number and name columns. (The SQL examples in this book will become increasingly complex, so if you feel you need more help, refer to Appendix A in the back of this book for a beginner's guide to SQL.)

Modifying the Columns Selected

Sometimes, the data you want to see is not the data in the file. An inventory transaction file might contain a price and a quantity, and we might want to return the extended price. Calculations can easily be used within a SELECT statement, as shown in Figure 1.12.

The extended price (QTY*PRICE) represents one column of data. The formula itself is used as the column heading, which is not terribly attractive. In some situations, you may want to assign a new column heading with the AS keyword.

SELECT PART, QTY * PRICE AS Extended_PRice from INVFILE

Figure 1.13 shows the literal 'EXTENDED_PRICE' as the heading for the column. If the column contains embedded blanks, the heading must be enclosed in double quotes. Unfortunately, this causes the quotes to appear in the heading as well. I suggest that you use an underscore (_) to separate different words in the headings.

Another example of modifying the data occurs when an inventory transaction file contains a unit of measure code. When running SELECT, we might want to translate that code into a more readable description. A good technique for accomplishing this uses a CASE statement.

The Entire CASE statement represents one column of data, as shown in Figure 1.14. For each record read from the INVFILE, the WHEN clauses is evaluated. The first WHEN condition that is true causes the value from its corresponding THEN clause to be returned for that record. If no WHEN condition is found to be true, the value associated with the ELSE clause is returned. The END keyword marks the end of the CASE statement.

Common Problems

When thinking of the challenges that iSeries professionals face when working with databases, the first two obstacles that come to mind are:

• Non-externally defined databases • Dates not stored as date data types

Old non-externally described databases, such as those used on the IBM SYS/36, did not contain field definitions within the database. It was up to each program accessing the data to parse the record into its various fields. Most databases in use today were probably designed and implemented before IBM provided us with date data types. Typically, dates are stored as either numeric or character fields broken down into the different portions of the date (Century, Year, Month, Day). These issues create road blocks for using SQL, so let's take a look at using SQL to extract data from those crusty but still functional legacy data files.

Non-externally Defined Database Files

A Customer Master file developed on the SYS/36 in RPGII would look something like the example shown in Figure 1.1. This type of file is sometimes called a "flat file" because it has no features or "terrain." It is just one long row of data. Typically, it is thought of as having no external definition, so running an SQL against it is impossible, right ...? Wrong! The iSeries provides an external definition for every file within the database. It's just that this one has very few fields! (See Figure 1.2.) Field names such as CUSTFLAT, F00001, or K00001 define the data and key portions of the record.

Files created in the SYS/36 environment often have F00001, F00002, K00001 as fields. F00001 and F00002 represent the data portions of the record, whereas K00001 represents the key portion of the record. To create a flat file in the native environment, simply run the command:

CRTPF FILE (CUSTFLAT) RCDLEN(113377)

The system creates a field name that defines the entire record and is given the same name as the file name. In this case, CUSTFLAT is both the file name and the field name.

Using the SUBSTR function, we can extract the data for each individual field from within the larger generic CUSTFLAT, F00001, or K00001 fields.

In Figure 1.15, this command displays all the customer names in the file. Including numeric data is a little trickier; if it was stored in zoned decimal format, then each digit occupies one byte of space in the record and it can be displayed using:

SELECT SUBSTR(INVFLAT, 1,7), SUBSTR(INVFLAT, 88,7) FROM INVFLAT

Looking at the resulting data in Figure 1.16, you probably noticed that the quantity field is not well formatted. To dress up the quantity field, let's convert it from a character field to a numeric field. This is accomplished with the DECIMAL function in this statement:

SELECT SUBSTR(INVFLAT,1,7), DECIMAL (SUBSTR(INVFLAT,8,7),7,0) FROM INVFLAT

In the example shown in Figure 1.17, we wrapped one function around another to format the quantity column appropriately.

Handling Negatives Numbers in Non-Database Files

In the previous examples, the quantities have all been positive. However, we may need to process negative numbers in some applications. This is made difficult by the way that IBM stores the sign in the zone portion of the last digit of the number. For positive values, the zone portion is loaded with a hex "F." For negative values, it's loaded with a hex "D." We can use the CASE statement in SQL to handle processing of the sign.

In this most recent example, we extracted the part number and the price. You can see in Figure 1.18 that the last record has a negative value for the price field, demonstrating one method for handling negative values.

Handling Packed Numbers in Non-Externally Defined Database Files

This gets even trickier when the data in the file is packed. Each digit of packed numeric data is compressed into a half of a byte. Use the HEX function to unpack this data, as in:

SELECT SUBSTAR(CUSTFLAT,4,30), HEX(SUBSTR(CUSTFLAT,1,3)) FROM CUSTFLAT

[ILLUSTRATION OMITTED]

This example does a good job of unpacking the data, but as shown in Figure 1.19, it includes the sign as an extra character at the end of the number. An 'F' indicates a positive value and 'D' indicates a negative value. We can remove the sign character by wrapping another SUBSTR around the whole expression as in:

[ILLUSTRATION OMITTED]

The technique illustrated in Figure 1.20 works well as long as the data is always positive. If the data can be negative, then more logic must be added to deal with the sign. An example of handling the sign with a CASE statement is in:

SELECT SUBSTR(CUSTFLAT,4,30), DECIMAL SUBSTR(HEX(SUBSTR(CUSTFLAT,1,3)),1,5),5,0) * CASE WHEN SUBSTR(HEX(SUBSTR(CUSTFLAT,3,1)),2,1) ='F' THEN 1 ELSE -1 END FROM CUSTFLAT

Wow! Is that complicated or what? As shown in Figure 1.21, the values are now numeric and would have a negative sign if needed. I said it was possible; I didn't say it was easy! The last example was a little silly because our customer number will probably never be negative, but the technique can be used on any packed number. This example might be difficult enough to scare you away from using SQL to process those legacy databases that contain packed data. But have no fear; later in this book I will show you how to make unpacking data much simpler.

The examples given above clearly define practical ways to manage data in non-database files with SQL. It makes using SQL more complicated, but frankly "flat files" make all programming work more complicated! Your best bet is to bite the bullet, and if possible, rebuild your files as an externally described database. But, even if you are forced to work with old "flat files," you can still use SQL.

Dates Not Stored as Date Data Types

Another problem that programmers everywhere face is date handling. Converting legacy database fields to and from date fields, and controlling the format of the date, is confusing and difficult. The next several examples show how to work with dates in real databases. Many legacy databases define a date as three or four separate fields: century, year, month, and day. These might be either numeric or character fields. The various date subfields must be merged together to form a complete date.

MMDDCCYY Dates

To assemble these fields into a real date, do the following:

select IHPART, IHMNTH '/' IHDAY '/' IHYEAR as DATE from INVHIST

The resulting column, shown in Figure 1.22, has a character data type. If it must be a true date data type, then another function such as DATE() can be used to convert the character field into a date field. The use of DATE() is reviewed later. If the date contains a two-digit century, it can be added with:

[ILLUSTRATION OMITTED]

The date in Figure 1.23 is displayed in MDYY format simply because that is the format that I find most appealing. If you wish to display the date in some other format, such as ISO or YMD, simply restructure the sequence in which the fields are concatenated, and alter the separator character if needed to achieve the desired format.

CYYMMDD Dates

The previous examples presume that the century is stored as a two-digit field in the file. What if it is a one-digit code, where '0' represents the twentieth century, and '1' represents the twenty-first century? A number of third-party vendors used this technique when preparing for Y2K. If you are "lucky" enough to need to work with dates in this format, modify the statement as follows:

[ILLUSTRATION OMITTED]

Figure 1.24 shows another date in USA format converted from a CYYMMDD format. Again, it is displayed in MDYY format.

Dates from Flat Files

If the data file is an old "flat file," then the methods described earlier can be used to pull the required data together, such as:

select substr(INVHFLAT,1,7) AS PART,
substr(INVHFLAT,14,2) '/'
substr(INVHFLAT,16,2) '/'
substr(INVHFLAT,20,2)
substr(INVHFLAT,18,2) AS DATE
FROM INVHFLAT

In Figure 1.25, you can see that a USA format date has again been created, this time from data stored in a flat file. Obviously, these examples have not covered every possible date format, but following these examples and applying them to your specific database will enable you to format your data as needed, no matter how the database may be designed.

Date Math with Date Fields

Once a date field has been assembled, the next challenge is performing date math. Adding a number of days, months, or even years to data is not too difficult, but more sophisticated options are available and are discussed in the next section. Date manipulation is easiest when the dates are stored in the file in date data type fields. For example, to select all the records from the customer master file when the last order date is within the last 90 days:

SELECT CUSTNBR, CUSTLOD FROM CUSTMAST WHERE CUSTLOD >= CURRENT DATE - 9900 DAYS

CURRENT DATE returns the current date; by subtracting 90 days from it, we set the lowest possible date that will still be included in the result table. In addition to using DAYS, we can also use MONTHS and YEARS.

(Continues...)

---

Excerpted from SQL for eServer i5 and iSeries by Kevin Forsythe. Copyright © 2008 MC Press Online, LP. Excerpted by permission of MC Press.
All rights reserved. No part of this excerpt may be reproduced or reprinted without permission in writing from the publisher.
Excerpts are provided by Dial-A-Book Inc. solely for the personal use of visitors to this web site.

---