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 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
|
More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.
TRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now! Request Now.
Forms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.
Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:
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.
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:
LATEST COMMENTS
MC Press Online