Programming in ILE RPG - Creating and Using Files: IBM Database Concepts

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

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

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 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 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 Programming in ILE RPG
Get the definitive guide to the RPG programming language.
List Price $95.95

Now On Sale

Programming in RPG IV Programming in RPG IV
Understand the essentials of business programming using RPG IV.
List Price $79.95

Now On Sale

BLOG COMMENTS POWERED BY DISQUS

LATEST COMMENTS

Support MC Press Online

$

Book Reviews

Resource Center

  •  

  • 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.

  • 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

  • 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: