2. Database Concepts – ILE RPG in Easy Bytes

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.

Data Definition Language (DDL) statements Data Manipulation Language (DML) statements Embedded SQL Host Language statements SQL Procedural Language (SPL) statements You use DDL statements to create new objects, such as tables, and to alter the structure or properties of existing database objects (this chapter primarily discusses DDL statements). Some common DDL statements are as follows: CREATE TABLE CREATE VIEW CREATE INDEX ALTER TABLE DML statements retrieve and manipulate the contents of existing database objects (i.

When SQL creates a new table, the database creates a single-member physical file object in a library. Tables are arranged into rows (records) and columns (fields). Appropriately enough, SQL uses the Create Table statement to produce a table:

The column’s data type determines how its values are stored, how much storage the column occupies, and what kinds of operations can be performed on the column when it is used within a program. Commonly used data types fall into three general categories: Character Numeric Date To understand the various data types, let’s first examine how the computer stores data.

Although a program can retrieve and process virtual rows from a view, the view does not actually contain data. Instead, it stores an access path, a sequence of pointers to the actual data in one or more tables. When SQL creates a new view, the database constructs an unkeyed logical file based on a physical file.

The procedure for creating database file definitions with DDS is similar to that of creating an RPG program. The first step is to use an editor, such as the RDi LPEX editor, to create a source member of definition statements. Most installations use a file named QDDSSRC to store members representing externally described files.

In fact, SQL tables are implemented as physical files. When you use DDS to describe the file, the physical file’s source statements define the record format of the file. Physical files can contain only one record format.

The concepts behind logical files correspond to those of SQL views and indexes. Recall from an earlier lesson that logical files define access paths to data stored in physical files. You can use a logical file:

As previously mentioned, it is standard practice to use source file QDDSSRC to store database source members. Also recall that the source type is PF for physical file and LF for logical file. You can use RDi’s LPEX editor to edit source code for DDS source members (e.

Externally describing printer files offers many of the same benefits as externally describing database files. In particular, this method lets you change a report format without changing the source code of the program that produces the report—a wise approach to application maintenance. You use DDS to define printer files in a source file, the same as you do for database files.

RPG and DDS allow you to edit numeric fields (but not character fields). Editing is used in part because of the way numbers are stored in the computer. For example, if Amount, a six-byte field with two decimal positions, is assigned the value 31.

In the previous examples, we provided the length in positions 30–34, and for numeric fields, we specified the number of decimal positions in positions 36–37. The REF (Reference) feature of DDS gives it the capability to copy field definitions from one file to another, thus eliminating the need to duplicate field definitions in every externally described file that has the same fields as another one. In essence, fields in one externally described file can inherit the data attributes of the fields in another externally described file, called the field reference file.

Externally described files discussed in earlier lessons were first introduced to the language with the RPG III syntax, which preceded today’s RPG. Before then, programs were written using program described files. As the name suggests, program described files are described entirely inside the RPG program; that is, the record layout must be detailed in the program source itself.