Distributed relational database processing

A relational database is a set of data stored in one or more tables in a computer.

A table is a two-dimensional arrangement of data consisting of horizontal rows and vertical columns as shown in the following table. Each row contains a sequence of values, one for each column of the table. A column has a name and contains a particular data type (for example, character, decimal, or integer).

Table 1. A typical relational table
Item Name Supplier Quantity
78476 Baseball ACME 650
78477 Football Imperial 228
78478 Basketball ACME 105
78479 Soccer ball ACME 307

Tables can be defined and accessed in several ways on the system. One way to describe and access tables on the system is to use a language like Structured Query Language (SQL). SQL is the standard IBM database language and provides the necessary consistency to enable distributed data processing across different systems.

Another way to describe and access tables on the system is to describe physical and logical files using data description specifications (DDS) and access tables using file interfaces (for example, read and write high-level language statements).

SQL uses different terminology from that used on the IBM i operating system. For most SQL objects, there is a corresponding IBM i system object. The following table shows the relationship between SQL relational database terms and system terms.

Table 2. Relationship of SQL terms to system terms
SQL term System term
relational database. A database that can be perceived as a set of tables and can be manipulated in accordance with the relational model of data. There are three types of relational databases a user can access from a IBM i environment, as listed under the System term column. For more information, see the Relational database topic.

system relational database or system database. All the database objects that exist on disk attached to the system that are not stored on independent auxiliary storage pools.

user relational database or user database. All the database objects that exist in a single independent auxiliary storage pool group along with those database objects that are not stored in independent auxiliary storage pools.
Notes:
  • The IBM i operating system can be host to multiple relational databases if independent auxiliary storage pools are configured on the system. There is always one system relational database, and there can be one or more user relational databases. Each user database includes all the objects in the system database.
  • The user should be aware that from a commitment control point of view, the system database is treated as a separate database, even when from an SQL point of view, it is viewed as being included within a user database. For more information, see the Troubleshooting transactions and commitment control topic.

remote relational database, or remote database. A database that resides on IBM i or another system that can be accessed remotely.

schema. Consists of a library, a journal, a journal receiver, an SQL catalog, and an optional data dictionary. A schema groups related objects and allows you to find the objects by name.
Note: A schema is also commonly referred to as a collection.
library. Groups related objects and allows you to find the objects by name.
table. A set of columns and rows. physical file. A set of records.
row. The horizontal part of a table containing a serial set of columns. record. A set of fields.
column. The vertical part of a table of one data type. field. One or more bytes of related information of one data type.
view. A subset of columns and rows of one or more tables. logical file. A subset of fields, records or both of up to 32 physical files.
index. A collection of data in the columns of a table, logically arranged in ascending or descending order. A type of logical file.
package. An object that contains control structures for SQL statements to be used by an application server. SQL package. Has the same meaning as the SQL term.
catalog. A set of tables and views that contains information about tables, packages, views, indexes, and constraints. The catalog views in QSYS2 contain information about all tables, packages, views, indexes, and constraints on the IBM i operating system. Additionally, an SQL schema contains a set of these views that only contains information about tables, packages, views, indexes, and constraints in the schema. No similar object. However, the Display File Description (DSPFD) command and the Display File Field Description (DSPFFD) command provide some of the same information that querying an SQL catalog provides.

A distributed relational database consists of a set of tables and other objects that are spread across different but interconnected databases. The database management system may use DRDA protocols to access any of these interconnected databases. An application connection to a database in such an environment is one of two types: local or DRDA. There is, at most, only one local optimized database connection per activation group. Any additional database connections must use DRDA.

In DRDA terminology, an application requester (AR) is the code that handles the application end of a distributed connection. The AR is the application that is requesting data.

An application server (AS) is the code that handles the database end of the connection.

A simple distributed relational database is shown in the following figure where the application runs on one system, and the database is located on another system.

Figure 1. A simple distributed relational database
A Simple Distributed Relational Database

DRDA also supports multi-tier connections between an application requester and a server. In this topology, the server that an application requester connects to is an application server, but any other server further downstream is called a database server (DS) as it does not interact directly with the application requester. In addition, to highlight its role as neither the system where a database request originates nor the system that performs the database function for the request, each application server or database server between an application requester and the final database server is also called an intermediate server.

A more complex distributed relational database is shown in the following figure where the application runs on one system, and the database management system running on a second system routes a request to a database server located on a third system.

Figure 2. A more complex distributed relational database
A More Complex Distributed Relational Database

The term client is often used interchangeably with AR, and server with AS or DS.

A unit of work is one or more database requests and the associated processing that make up a completed piece of work as shown in the following figure. A simple example is taking a part from stock in an inventory control application program. An inventory program can tentatively remove an item from a shop inventory account table and then add that item to a parts reorder table at the same location. The term transaction is another expression used to describe the unit of work concept.

In the preceding example, the unit of work is not complete until the part is both removed from the shop inventory account table and added to a reorder table. When the requests are complete, the application program can commit the unit of work. This means that any database changes associated with the unit of work are made permanent.

With unit of work support, the application program can also roll back changes to a unit of work. If a unit of work is rolled back, the changes made since the last commit or rollback operation are not applied. Thus, the application program treats the set of requests to a database as a unit.

Figure 3. Unit of work in a local relational database
Unit of Work in a Local Relational Database