Many of us still struggle with "flat" files on the iSeries because many shops still run some legacy code. These venerable yet functional pieces of code often require either a process too unique to be handled by a packaged software application or an interface for the iSeries DB2/UDB database or some other database.

As iSeries professionals become more familiar with SQL, they find situations where they would like to use SQL against these flat files. You might think that the lack of field definitions makes this impossible, but that's not the case. Even flat files have rudimentary field definitions that can be used in SQL statements.

My new book, SQL for eServer i5 and iSeries, outlines the process for dealing with data in flat files. This is just one of many uniquely iSeries issues that the book covers, including topics as old as flat files and as new as Java, Visual Explain, and encoded vector indexes. The following excerpt illustrates the handling of flat files.

A customer master file developed on the S/36 in RPG II would look something like the example shown in Figure 1.

Figure 1: This Customer Master flat file came from RPG II. (Click images to enlarge.)

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 2.)

Figure 2: These are the field definitions for CUSTFLAT.

Field names such as CUSTFLAT, F00001, or K00001 define the data and key portions of the record. Files created in the S/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 this command:

The system creates a field name that defines the entire record and has the same name as the file name--in this case, CUSTFLAT. Using the SUBSTR function, you can extract the data for each individual field from within the larger, generic CUSTFLAT, F00001, or K00001 fields.

Figure 3: Use SELECT with the substring function.

The command shown above 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 this command:

Figure 4: Select numeric data from a flat file.

Looking at the resulting data in Figure 4, you probably noticed that the quantity field is not formatted well. To dress up the quantity field, use the DECIMAL function to convert it from a character field to a numeric field:

Figure 5: Format data with multiple functions.

In Figure 5, one function is wrapped around another to format the quantity column appropriately.

Handling Negative Numbers in Non-Database Files

In the previous examples, the quantities have been positive. However, you may need to process negative numbers in some applications. This is difficult because 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." You can use the CASE statement in SQL to handle sign processing.

In this example, you extract the part number and the price. You can see in Figure 6 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:

Figure 6: Handle negative values in flat files.

Figure 7: Handle packed values in flat files.

This example code unpacks the data, but as shown in Figure 7, 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:

Figure 8: Eliminate the packed sign position.

The technique illustrated in Figure 8 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. Here's an example of handling the sign with a CASE statement:

Figure 9: This is the best solution for packed numbers in flat files.

Wow! Is that complicated or what? As shown in Figure 9, 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.

These examples clearly define practical ways to manage data in non-database files with SQL. It's a little complicated, but flat files make all programming work more complicated! Your best bet is to rebuild your files as an externally described database. But if you are forced to work with old flat files, you can still use SQL.

You've now learned more than how to handle flat files. You've learned how to use multiple functions together to achieve a desired result and how to use the CASE statement within SQL.

Kevin Forsythe is the author of the new book SQL for eServer i5 and iSeries. He has over 18 years of experience working with the iSeries platform and its predecessors. He has been a member of the DMC team for the past nine years. Kevin's primary responsibility is providing iSeries education, but he also provides customers with project management, system design, analysis, and technical construction. In addition to his technical skills (RPG IV, CL, OS/400, SQL, FTP, Query, VB, Net.Data), Kevin possesses the ability to communicate new and complex concepts to his students. He has been the primary instructor for DMC's iSeries-based AS/Credentials training courses since 1997 and has authored courses such as Advanced ILE, SQL, Embedded SQL, Operations Navigator, and Intro to WebSphere Studio. An award-winning speaker, he has spoken at every COMMON Conference since the spring of 2000.

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