In this excerpt series, you learn how IBM i handles database files. You also discover the differences between tables and views and between physical and logical files.

By Brian Meyers and Jim Buck

Editor's Note: This article is excerpted from chapter 3 of Programming in ILE RPG, Fifth Edition.

This series examines field reference files and introduces you to the IBM i data types and the storage implications of numeric, character, and date data types. It also covers how to use SQL or DDS to define database files and how to access these files from within RPG programs. In addition, it compares externally described files with program-described files, and it explains externally described device files and how to use DDS to define them.

IBM i Database Concepts

The IBM i operating system is unique in the way it handles data. Unlike other systems, which require additional, costly software to provide them with database capabilities, IBM i was designed with database applications in mind, and the database is tightly integrated into the operating system. The operating system automatically treats all data files as part of a large relational database system. One consequence of this approach is that all data files must be defined to the system independently of application programs. Even those applications that on the surface seem to be creating files are actually creating records and storing them in a file that must have been defined to the operating system before program execution.

You can define these files to the system at a record level (i.e., not divided into individual fields) or at a field level. If you define a file only at the record level, RPG programs that use that file must subdivide that record into the appropriate fields by using fixed-format Input or Output specifications. This type of file is called a program-described file because you explicitly code the field descriptions in the RPG programs that are to use the file. However, if you break down the file to the field level—outside the programs that use it—you do not need to code those field definitions within every program that uses the file, as the compiler automatically retrieves the definitions into the program. This type of file is called an externally described file because the field definitions are external to the programs that use the file (the field definitions are a part of the file object itself). ILE RPG programmers almost universally prefer externally described files over program-described files.

Externally described files can reduce the need for duplication of data (redundancy) across files, following well-established database design principles. Externally described files impose standardization among programmers and across applications, because all programs using a given file use the same field definitions and names. And externally described files increase programmer efficiency and reduce errors, as programmers don’t need to duplicate the file definition effort each time they need to use a file within a program.

Externally described files also simplify system maintenance. For example, if it is necessary to add a field to a record layout or to change a field’s definition (e.g., expand postal code to 10 characters), you make these changes in only one place (in the external file definition), rather than in every program that uses that file. Simply recompiling the programs that use the file lets them use the new layout and, in many cases, without changes to their coding—under certain conditions, even recompiling is unnecessary.

Physical and Logical Files

IBM i lets you define two kinds of database files: physical files and logical files. Physical files store data records in arrival sequence (i.e., the order in which they are written to the file). If you define a physical file at a field level and one or more of those fields are designated as a key field, you can subsequently access records stored in that file in either key sequence or arrival sequence (first in, first out). If you do not define a key field, access is limited to arrival sequence.

Logical files describe how data appears to be stored in the database. Logical files do not actually contain data records. Instead, they store access paths, or pointers, to records in physical files. A logical file is always based on one or more physical files. The three most common uses for a logical file are to provide these capabilities:

• Sorting the records in a physical file
• Selecting certain records from a physical file
• Selecting certain fields from a physical file’s record layout

RPG does not distinguish between physical files and logical files. It uses the same coding and techniques to process either one.

A third type of file, the device file, associates a layout with a hardware device, such as a printer or a workstation. For example, a printer file contains a description of a printed report’s appearance, and a workstation file describes the layout of one or more display screens. RPG processes device files by using techniques similar to those it uses to process database files.

Next time: SQL Database Concepts.  Can't wait? Want to learn more about Programming in ILE RPG?  Pick up the book in the MC Bookstore today!

Jim Buck's career in IT has spanned more than 35 years, primarily in the college education, manufacturing, and healthcare industries. Past president (13 years) of the Wisconsin Midrange Computer Professional Association, he has served on several teams developing IBM and COMMON certification tests. Jim has co-authored several IBM i textbooks with Bryan Meyers that are used by many companies and in colleges worldwide. Other accomplishments include: recipient of the 2007 IBM System i Innovation - Education Excellence Award, 2014 COMMON President's Award, and 2013/2016/2017 IBM Champion - Power Systems.

Jim is the president and founder of imPower Technologies, where he provides professional IBM i training and consulting services. He is active in the IBM i community, working to help companies train their employees in the latest IBM technologies and develop the next generation of IBM i professionals.

---

MC Press books written by Jim Buck available now on the MC Press Bookstore.

		Control Language Programming for IBM i Master the A-Z of CL, including features such as structured programming, file processing enhancements, and ILE. List Price $79.95 Now On Sale
		Mastering IBM i Get the must-have guide to the tools and concepts needed to work with today's IBM i. List Price $85.95 Now On Sale
		Programming in ILE RPG Get the definitive guide to the RPG programming language. List Price $95.95 Now On Sale
		Programming in RPG IV Understand the essentials of business programming using RPG IV. List Price $79.95 Now On Sale