| Previous | Table of Contents | Next |
If we look more closely at DB2/400, we find it has two separate parts. The Database Manager and the DDS language both come with OS/400. The Query Manager and SQL Development Kit is a licensed product that must be purchased separately. As its name suggests, this optional product contains Query Manager, the end-user query product for the AS/400 that uses SQL. The kit also contains an interactive user interface to SQL called Interactive SQL, and the language precompilers that are used when SQL statements are embedded in an HLL. An unfortunate situation that has occurred is IBM’s requirement that you pay for SQL, while DDS comes with OS/400. This situation is a key reason why SQL has never been popular in many AS/400 shops, because it sends the message that SQL is a foreign product, but it’s not. Most database developers in Rochester these days think primarily in terms of SQL, not DDS. The DDS interface will continue to be supported, but new function will most likely come on the SQL side.
Although there are two distinct database interfaces on an AS/400, it is very important to understand that there is only one database. You can access, define, and manipulate data on the AS/400 through either the DDS interface or the SQL interface. Because the SQL interface was designed to use the same MI instructions that the DDS interface uses, either interface can manipulate data objects that were created through the other interface. This capability to mix and match the two interfaces provides another level of power and flexibility for the AS/400 database.
The biggest problem with having two interfaces is the confusion different terminology causes. As with the naming differences between OS/400 objects and the MI system objects, different groups of people created the names used in the two database interfaces. For example, the native DDS interface has physical files, which contain the actual data in the database. As we saw previously, a physical file is a two-dimensional table. The same physical file in the SQL interface is called a table, which is a more descriptive name for this data structure. A logical file in the native DDS interface does not contain data, but instead points to the actual data; logical files give a program a view of the data. For the SQL interface, a logical file is called a view. Likewise, record and field names in the DDS interface are called row and column names in the SQL interface, to reflect the concept of a table.
This section presents an overview of various database components found in the AS/400. The purpose here is not to show how to use the AS/400 database. Several good books and articles have already been written to describe the externals of the database and to show how a programmer can use the two database interfaces.3 Instead, the purpose of this section is to give you an overview of the database characteristics and of the fundamental operations the DBMS performs.
3My favorite book on this subject, and the one I highly recommend, is Paul Conte’s Database Design and Programming for DB2/400, published by Duke Press in 1997.
This section is divided into two parts. In the first part, I describe the fundamental functions any DBMS must provide and show how the AS/400 accomplishes these functions. The second section comprises an overview of other database features, some of which have to do with database performance and some of which provide support for the AS/400 as a database server. Following this overview, I explain the internal implementation of some of the fundamental database functions.
There are many ways to implement a relational database; but in general, any database management system is expected to provide seven functions. They are
We can now look at how the AS/400 accomplishes these functions.
You can use the AS/400 database’s native DDS language to describe the physical and logical database files. DDS has statements that use keywords and parameters to describe both the attributes of the file itself and the fields in the database records. You also can use DDS to describe the device files used in the AS/400. The format and type of data used with the physical devices attached to the system are contained in these device files.
DDS can define several attributes for the fields in the database records. Some of the attributes include the field’s name, its length, and the kind of data (alpha or numeric) the field contains. Depending on the type of data the field contains, you can describe several other specific attributes. For example, if a field contains decimal data, you can define the total number of decimal digits and the number of digits to the right of the decimal point.
DDS statements are stored in source file members, which are then compiled into file objects by the OS/400 commands CRTPF (Create Physical File) and CRTLF (Create Logical File). Likewise, you can use SQL to describe the attributes of the database files. Unlike DDS, which is only a data description language, a single SQL statement both describes and creates the tables and views. In SQL, the file definition is not separate from the create instruction. SQL’s CREATE TABLE statement, for example, defines the name of the table, the name of the columns (fields), and the attributes of the columns. This statement, when executed, also creates the table.
The physical files, called tables in SQL, contain the actual data. A physical file record has a fixed set of fields. Each field may be (although is not usually) of variable length. In SQL terminology, a table has fixed-length rows with variable-length columns. To avoid total confusion, I use the terminology for the native interface whenever possible because it is more familiar to most AS/400 users, unless I need to specifically describe an SQL implementation.
A physical file has two parts. The first part contains the file attributes and the field descriptions. The file attributes include the file’s name, its owner, its size, the number of records in the file, the file’s key fields, and some other attributes. The field descriptions contain the attributes for each field in the records. The second part of a physical file contains the data.
| Previous | Table of Contents | Next |