The data part of a physical file can have one or more members. Members allow a file to be subdivided. All the records in all the members have exactly the same format. Members provide a convenient way to partition these records. We might put this month’s information in one member and last month’s in another. Each member has its own unique name, and we can access the records in a member by using the member name. We should note that SQL tables can have only one member. SQL’s single-member restriction conforms to the relational model’s insistence that all data be stored in two-dimensional tables. Multimember files are three-dimensional.

To create a physical file, we use the CRTPF system command. This command uses the DDS statements in the source physical file as a template to create the physical file. The physical file created with this command contains no data records. We must use a separate program or utility to add the actual data records to the file.

As we saw earlier, the SQL statement to define the data also creates the table. We can execute the CREATE TABLE statement using Query Manager, Interactive SQL, or by embedding it in an HLL program. The table that is created with this statement is a physical file, and it is identical to the one created through the native interface.

Creating Logical Files and Views

Logical files allow a user to access data in a format that is different from the way it is stored in one or more physical files. Logical files provide the data and program independence we discuss in the next section. The logical file contains no data records; instead, it contains the relative record number of the data record in the physical file. The logical file contains the index to the physical file. We often say the logical file provides the access path to the data.

A logical file’s structure can range from very simple to extremely complex. The four categories of logical files and views are

•  Simple logical files or views that map data from a single physical file or table to some other logical record definition.

•  Multiple-format logical files that allow access to several physical files, each with its own record-format definition. This type of logical file can be created only through the native interface; it cannot be created through the SQL interface.

•  Join logical files that define a single record definition built from fields in any combination of two or more physical files, tables, logical files, or views, as long as the total number of physical files and tables does not exceed 32.

•  SQL views, which are similar to join logical files and provide the same result, but are implemented quite differently. Join files maintain or share an access path for each join. SQL views find the access paths they need at run time, guided by a Query Definition Template stored with the file.

Like the physical file, the logical file has two parts. The first part looks just like the first part of the physical file. It contains the file attributes and the field descriptions. The second part contains the relative record number of the data record in the physical file. A program using a logical file sees only the data from the physical file presented in the format described in the field descriptions of the logical file.

Not surprisingly, to create a logical file, we use the CRTLF system command. This command uses the DDS statements in a source physical file as a template to create the logical file. DDS statements in this file also identify the names of one or more physical files on which the logical file is based. Once created, the logical file contains the relative record numbers of the data records in the one or more physical files on which it is based.

The CREATE VIEW statement in SQL identifies the table the view represents, along with the column descriptions for the view. Again, the view created is a logical file, which is identical to one that would be created if the native interface were used. Logical files perform three database operations: formatting (includes projection, joining, and field derivation), record selection, and ordering. A DDS-created file can do all three. An SQL-created file can do either formatting (an SQL View) or ordering (an SQL index), but not both. SQL cannot create views that select a subset of physical file records. An SQL view could be created by DDS, but DDS is not typically used to create files that look only like SQL views.

Data Dictionary and Catalogs

A single place in every AS/400 system contains the descriptions of all components in all the physical and logical files. In the native interface, this entity is called the data dictionary. The data dictionary is a special OS/400 object that the database manager maintains and that users can query to find file structure and where-used information. The database manager automatically logs information contained in the data dictionary whenever a new database object is created.

The data dictionary’s purpose is to allow users and application developers to see what the database looks like on any system. What record formats are used? What are their attributes? Where is a particular name used in the system? You can find the answers to all these, and other, questions by using the data dictionary. In the SQL interface, the data dictionary is called the system-wide catalog. SQL also allows developers to create other catalogs. Each SQL collection (the SQL name for a library in the native interface) can optionally have its own catalog.

Data and Program Independence

The combination of the physical and logical files on the AS/400 provides the independence between the programs and the data those programs use. Separating the description of the data from the program allows application programs to look at the data differently than just the way the data is physically stored. In keeping with the technology independence of the architecture, this concept of separating programs and data was fundamental to the original design of the System/38 and the AS/400.

Let’s look at the structure of the physical file, which contains the description of the data along with the data itself. This description is often called the external file description, and both the System/38 and the AS/400 are said to have externally described data. The advantage of having externally described data is that the program does not have to contain the data descriptions. This means the program does not dictate how the data is to be physically stored in the system. Also, a single application program can operate on files that contain data in different formats.