What Makes Up a DB2 UDB Database?

From a user 's perspective, a database is a collection of tables (preferably related in some way) that are used to store data. However, from a database administrator's viewpoint, a DB2 UDB database is much more; a database is an entity that is comprised of many physical and logical components. Some of these components help determine how data is organized, while others determine how and where data is physically stored. So how do you create a database and allocate storage space for it? Before you can answer that question, you must have a basic understanding of the different types of objects that are used by DB2 UDB.

DB2 UDB Objects

DB2 UDB uses both a logical and a physical storage model comprised of several different, yet related, objects. Four types of objects exist. They are:

• System objects

• Recovery objects

• Storage objects

• Database (or data) objects

System objects

System objects consist of registry variables, instance configuration files, and individual database configuration files. Registry variables are set at the system level and affect every instance that resides on a particular server. Instance configuration files (also known as DB2 Database Manager configuration files) are created and assigned to individual instances during the instance creation process. Values in an instance's configuration file control how resources are allocated for that particular instance, and changes to them affect every database that falls under that instance's control. (Values for many of the parameters in an instance configuration file can be modified to improve overall performance or increase concurrency.) Database configuration files are created and assigned to individual databases during the database creation process. Values in a database's configuration file control how resources are allocated for that particular database, and changes to them can improve performance or increase capacity, depending upon the type of activity the database encounters.

Recovery objects

Recovery objects consist of transaction log files and recovery history files. By default, one recovery history file and three transaction log files are automatically created when a database is created. Recovery history files are used, together with database backup images and transaction files, to coordinate database recovery operations. The recovery history file contains information about every backup operation executed, while transaction log files contain records of recent database operations performed. In the event a database has to be recovered from an application, user, or system error, events stored in the transaction log files can be replayed to return the database to a consistent (database consistency is described in detail in Chapter 7) and stable state, or to return a database to the state it was in up to the point in time that the error occurred, if roll-forward recovery is enabled. You cannot modify transaction log files or recovery history files directly; however, you will find that their contents are important should you need to repair a database that has been destroyed or damaged.

Storage objects

Storage objects control where data is physically stored and how data is moved between storage and memory during normal operation. Three types of storage objects are used. They are:

• Buffer pools

• Containers

• Tablespaces

Buffer pools

A buffer pool is a section of memory that has been reserved for the sole purpose of caching data pages as they are read from physical storage. Whenever data is needed to resolve a query, the page that the data is stored on (data is stored in sections called pages ) is located in physical storage and transferred to a buffer pool, where it is then read and/or modified. If the page is modified, it is copied back to physical storage; however, all pages read stay in memory until the space they occupy is needed or until all connections to the database are terminated . Furthermore, whenever a page of data is retrieved, the DB2 Database Manager uses a set of heuristic algorithms to try to determine which pages will be needed next ”those pages are retrieved as well (this is referred to as prefetching ). Retaining all pages loaded and prefetching are done to improve overall performance; data can be accessed much faster when it is stored in memory than when it is stored on disk.

Containers

A container is some form of physical storage that the DB2 Database manager has reserved access to. A container can be a directory that may or may not already exist, a fixed-size , preallocated file that may or may not already exist, or a physical (raw) device that is recognized by the operating system. (On Linux and UNIX operating systems, a physical device can be any logical volume that uses a character special interface; on Windows operating systems, a physical device is any unformatted partition or any physical disk.)

Tablespaces

Tablespaces are used to control where data is physically stored and to provide a layer of indirection between a table and one or more containers in which the table's data actually resides. A single tablespace can span many containers, but each container can only belong to one tablespace. When a tablespace spans multiple containers, data is written in a round- robin fashion (in groups of pages called extents ) to each container assigned to that tablespace; this helps balance data across all containers that belong to a given tablespace.

Two types of tablespaces can exist: system managed space (SMS) tablespaces and database managed space (DMS) tablespaces. With SMS tablespaces, only directory containers can be used for storage, and the operating system's file manager is responsible for controlling how that space is used. With DMS tablespaces, only file and/or device containers can be used for storage, and the DB2 Database Manager is responsible for controlling how the space is used.

If you look closely at how DB2 UDB manages data, you will discover that, depending upon how a table has been defined, each corresponding record can be stored as three distinct values: as a regular data value, which is how values produced by an application, user, or trigger are stored; as an index value, which is how all related index data values are stored; and as one or more long data values, which is how each long data and large object (LOB) value is stored. Consequently, three types of tablespaces can exist: regular, long, and temporary. As you might imagine, regular data and index data can reside in regular tablespaces, while long field data and large object data can reside in long tablespaces. Temporary tablespaces, on the other hand, are used for a much different purpose. Temporary tablespaces are classified as either system or user ”system temporary tablespaces are used to store internal temporary data generated when some types of operations are performed (for example, sorting data, reorganizing tables, creating indexes, and joining tables), while user temporary tablespaces are used to store declared global temporary tables, which in turn , are used to store application specific data for a brief period of time.

Database (or data) objects

Database objects ” otherwise known as data objects ”are used to logically store and manipulate data, as well as to control how all user data (and some system data) is organized. Data objects include tables, indexes, views, aliases, schemas, triggers, user-defined data types, user-defined functions, and sequences. We will examine each of these objects in more detail a little later.