IBM i General Design Standards › Design Standards for Database Files › The Database of iSeries › The iSeries Database as a Relational Database

The iSeries Database as a Relational Database

In essence, the database of iSeries is relational—data is kept in ‘flat’ tables and may be assembled into logical views. It has facilities for building access paths to assemble, order, and select data. The database has built-in facilities for recovery (via journaling and commit control), security, integrity, and concurrency control. It falls short however, of the full theoretical implementation of a relational database in a number of ways.

For instance:

• The relational terminology of tables, columns, and rows is not used.
• A metamodel is not available to describe the database handler’s own facilities with the same mechanisms as the database that it produces.

Much of the system information may, however, be materialized into an accessible format using the OUTFILE parameter on the various OS/400 display commands. From V2R1, there are also APIs to retrieve some of the information. The SQL/400 interface to the database has SQL catalog facilities that may be queried, using SQL.

• The join facilities of the OS/400 database are limited—they are read only, and limited to an equi-join. If fields from the secondary join file are used as keys (for instance with the OS/400 Open query file (OPNQRYF)) command, then true concurrency is not maintained.
• In the native interface, there are only limited facilities for manipulating sets of data within the database, and these are not presented explicitly in terms of the operations of the relational calculus (Union, Intersection, Subtraction, Addition, Select, Project, and Join) acting upon sets.

Selection can be specified in DDS (but is early binding).

A join can be specified in DDS (but is early binding). Fields from the secondary join file may not be used as key fields. The HLL read equal statement (for instance RPG III ‘READE’,) gives what is in essence access to a set of data.

Set level operations are of significance in that they provide a greater level of economy in specifying programs—in relational languages such as SQL, a single statement may often serve to specify what would be in most HLL’s be a ‘Do loop’ containing many lines of code.

The OPNQRYF command allows for dynamic joins and selections.

SQL/400 provides join specifications and a number of set level operations.

• There is not a full capability for field level security. It is possible however, to build logical views containing only a subset of the fields in the file and to restrict authority differently to different views.
• There are only limited capabilities for specifying rules for preserving the integrity of the database. Any further rules have to be incorporated explicitly in HLL code. For instance:
  • to test that foreign keys (that is, non-key fields on a file which are themselves the keys of other files) match prime key values
  • to test that instances of referenced keys cannot be deleted if they are used in dependent relations ("referential integrity")
• There is not proper support for a null value. This is significant because in a truly relational database, primary keys must not be null ("Entity integrity must be preserved").
• In device file DDS, a blank value cannot be distinguished from a null value.
• Many features of the database are "early binding"—facilities such as selection and key order are built into a compiled object. Although this gives a better performance, it also limits the flexibility of the database.
• Relations are implicit—from the presence of fields on files—rather than explicit.

Overall, the database of iSeries can be characterized as relational, but ‘early binding’—information about how to use the database information is incorporated at compile time rather than execution time.