Craig S. Mullins
An Introduction to the Architecture of DB2 for OS/2
By Craig S. Mullins
The generic name for the DB2 workstation products is now DB2 for common servers. This change occurred with the general availability of Version 2 of the DB2 for common servers product. Version 2 of both DB2 for OS/2 and DB2 for AIX is available, with V2 code for Windows NT, Sun Solaris, Siemens Nixdorf SINIX, and HP-UX, soon to follow. This article will focus on one of these products-DB2 for OS/2-although most of the information covered in this article will apply to the other platforms as well.
To effectively administer DB2 for OS/2 one must first understand the difference between the DB2 for OS/2 Database Manager and a DB2 for OS/2 database. The Database Manager is the actual DB2 for OS/2 product in which one or more databases can be created. An installation of the Database Manager is also referred to as an instance.
Each database is composed of multiple files. First, every database has its own set of system catalog tables containing the meta data description of the objects created in that database. Table spaces, new to Version 2 of DB2 for OS/2, can be used to partition data storage based upon the type of data. Up to three table spaces can be utilized per table (one each for normal data, index, and large object fields). Table spaces can span multiple volumes and can be extended without stopping the database. However, it is crucial to note that the table space concept implemented by DB2 for OS/2 is not the same as that used by DB2 for MVS.
Additionally, each database has an associated set of log files used to record database changes. Additionally, log files will be archived by DB2 for OS/2 when transaction activity ceases and the log file is closed or when the log is full. Recovery can then be requested from the active and archived logs using the RESTORE and ROLLFORWARD utilities.
DB2 for OS/2 utilizes a set of directories for establishing an environment for enabling data access. The directories used by DB2 for OS/2 are as follows:
RDBMS products love memory - DB2 for OS/2 is no different. DB2 employs several different types of memory structures to more efficiently process data.
Heaps are groupings of memory segments utilized for specific types of processing. Some of the more important heaps used by DB2 for OS/2 are:
Every DB2 for OS/2 database also has a buffer pool associated with it. The buffer pool is a memory block set aside as a staging area for database reads and writes. It contains the most recently used pages of data read from the database. Additionally, a DB2 for OS/2 employs a directory cache for staging reads and writes from the directories covered in the previous section.
The actual size of the heaps, lock list, caches, and buffers may be modified by changing database configuration parameters.
System and database configuration
Many parameters for tuning and setting up your DBMS are available to the user of DB2 for OS/2. These are known as configuration parameters; they are changed using a GUI-based configuration tool. Two levels of configuration are provided: system and database. System-level configuration, usually referred to as database manager configuration, applies to the entire DB2 for OS/2 installation. This is where basic database manager specifications such as defaults, maximums, and minimums are made. For example, this is where the following parameters can be set:
Version 2 of DB2 for OS/2 runs in a multithreaded environment. It is designed to use OS/2 operating system threads for execution instead of processes. The DB2 database server process creates threads for all application agents. By using threads, substantially less memory is required to support multiple users, thereby freeing additional memory for more suitable uses (such as larger buffer pool specifications).
User profile management
Access to DB2 for OS/2 is validated outside of Database Manager by the User Profile Management (UPM) feature: every user must provide a valid userid to UPM before accessing data. This feature is not necessary on the other platforms (including DB2 for AIX). UPM provides userid validation, user and group management, and log in/log out facilities. User log in is performed using alternative methods by DB2 on other platforms.
To access data across a network from the server version of DB2 for OS/2, IBM provides Client Application Enabler (CAE) software. CAEs enable client workstations running disparate operating systems to access the DB2 for OS/2 server. The CAE also requires a communication protocol such as APPC, NetBIOS, TCP/IP, or NetWare IPX/SPX.
By contrast, applications running on the workstation that require access to a DRDA server other than DB2 for OS/2, will require the Distributed Database Connection Services (DDCS) product to enable the distributed access. DB2 for common servers can also be accessed as a DRDA Application Server as of Version 2.
Version 2 highlights
Version 2 of DB2 for OS/2 is a significant new release that propels DB2 into the ranks of the leading RDBMS products. Support for user-defined functions and data types, triggers, check constraints, recursive SQL, compound SQL, large objects, table spaces, and more efficient utility processing merely scrapes the surface of the new features in this release.
IBM is the undisputed leader in terms of SQL optimization; the DB2 for OS/2 optimizer was completely rewritten to utilize the Starburst optimization technique created at IBM's Almaden research lab. The new optimizer uses advanced query rewrite rules to automatically transform a complex query into a simpler query that is easier to optimize. It also examines a greater number of alternative access paths as it searches for the best query execution plan. These advances will make it less important for users to understand the mechanics of writing efficient SQL queries, enabling them to concentrate on formulating accurate queries instead of accurate and efficient ones.
The new Visual Explain tool enables users to more easily decipher the access path chosen by the optimizer. It depicts the access path in a graphical format instead of an encoded or tabular format used by other RDBMS products.
Additional performance techniques have been incorporated into DB2 for OS/2 making applications and data access much more efficient:
Fig. 2 - Distributed Connections with DB2 for OS/2
Version 2 of DB2 for OS/2 is a robust database server that compares favorably to any other popular database server. In terms of performance, availability, technical features, and support, DB2 V2 provides a most effective and useful relational database management system.
EDGE Magazine Online, published by PLATINUM technology, inc.
© 1999 Craig S. Mullins &
Associates, Inc. All rights reserved.