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 i5/OS® operating system. For most SQL objects, there is a corresponding i5/OS 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 System 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 i5/OS 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 i5/OS 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.

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 i5/OS 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 exists when the application programs that use the data and the data itself are located on different machines, or when the programs use data that is located on multiple databases on the same system. In the latter case, the database is distributed in the sense that DRDA® protocols are used to access one or more of the databases within the single system. The connection to a database in such an environment is one of two types: local or DRDA. There is, at most, only one local database connection at one time. One simple form of a distributed relational database is shown in the following figure where the application program runs on one machine, and the data is located on a remote system.

When using a distributed relational database, the system on which the application program is run is called the application requester (AR), and the system on which the remote data resides is called the application server (AS). The term client is often used interchangeably with AR, and server with AS. Figure 1. A distributed relational database

A Distributed Relational Database

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 2. Unit of work in a local relational database

Unit of Work in a Local Relational Database

 

Parent topic:

Introduction to distributed database programming

 

Related concepts


Relational database
Troubleshooting transactions and commitment control
XA transaction support for commitment control

 

Related reference


Display File Description (DSPFD) command
Display File Field Description (DSPFFD) command