Section 18.1. DB2 Architecture

18.1. DB2 Architecture

DB2 UDB is a distributed database system implemented in a client/server architecture. The DB2 UDB runtime client can be installed on a separate physical system from the server. Client code is installed by default with the server. Client and server code at the physical server is separated into different address spaces; that is, they do not share physical memory. Application code runs in the client process while server code runs in separate processes. Separate memory units are allocated for database managers (instances), databases, and applications.

18.1.1. The Power User's View

Before launching into this chapter, here are some key terms that should be familiar to power users.

18.1.1.1. Instance

The set of processes that manage data is called an instance in DB2. Each instance is a complete, independent environment. Each instance has separate security from other instances on the same machine (system); has its own databases and partitions, which other instances cannot access directly; controls what can be done to the data and manages system resources assigned to it; and contains all the database partitions defined for a given parallel database system. The DB2 instance process is started with the db2start command.

The DB2 instance process runs on the DB2 server and is responsible for enabling access to the specified database. Several processes are created when the instance process is started. These processes interact with each other, maintaining the database and connected applications. Of these processes, several background processes are prestarted; others start on an as-needed basis.

18.1.1.2. Databases

Each DB2 database is a collection of interrelated data, and each database includes a set of system catalog tables that describe the logical and physical structure of the objects in the database. Other aspects of the database structure are maintained in a database configuration parameter file and the recovery log.

18.1.1.3. Schemas

A schema is an identifier that qualifies tables and other database objects. A schema name is used as the first part of a two-part object name. For example, a schema named Smith might qualify a table named smith.payroll.

18.1.1.4. Tables

A relational database presents data as a collection of tables. Data in a table is arranged in columns and rows. The data in the table is logically related. Relationships can be defined between tables.

18.1.1.5. Views

A view provides a different way of looking at data in one or more tables; it is a named specification of a result table. A view has columns and rows just like a base table (a table created with the create table command). All views can be used just like base tables for data retrieval.

18.1.1.6. Indexes

An index is a set of keys, each pointing to rows in a table. An index allows efficient access when selecting a subset of rows in a table by creating a direct path to the data through pointers. The DB2 SQL Optimizer uses indexes to determine the most efficient (fastest) way to access data. The DB2 SQL Optimizer is a component of DB2's SQL compiler. It reviews statistics created with the runstats command, then chooses an access plan for a data manipulation language statement by selecting the one with the minimal estimated cost. The runstats command should be run after a database reorg, restore, or recover command.

18.1.1.7. DB2 engine dispatch units

Different operating systems dispatch tasks, threads, or processes. DB2 UDB server operations are performed by engine dispatch units (EDUs), implemented as processes or threads. DB2 uses the term EDU for consistency's sake across its varied platforms. Some DB2 background processes are started with the instance; others are initialized when the database is activated by a connection. DB2 EDUs can be easily identified because they start with the string db2. For example, db2gds, db2sysc, and db2wdog are all DB2 EDUs.

18.1.2. The DBA's View

A DBA is typically concerned with the following architectural elements, as most pertain to the physical elements of the database.

18.1.2.1. Connecting to a DB2 database

To work with a DB2 database, the database manager instance's processes must be started, and your application or session must be connected to the database. To issue the following command, you must have sysadm or sysctrl authority:

% db2 connect to dbname user username using password

18.1.2.2. System catalog tables

The system catalog tables describe the logical and physical structure of the data and contain security information for database object access privileges. Catalog tables are created when the database is created and are updated during the course of normal operation. They cannot be explicitly created or dropped, but they can be queried and viewed.

18.1.2.3. Database partition

A database partition is part of a database that consists of its own data, indexes, configuration files, and transaction logs. A partitioned database is a database with two or more partitions. Tables can then be placed in one or more database partitions using the partitioning feature.

Database partitions can reside on a single physical server, in which case the partitions are referred to as logical partitions. Alternatively, database partitions can also span multiple physical machines. (This is why DB2 database partitions are also known as nodes or database nodes.)

A database partition group is a set of one or more database partitions. You can create your own database partition group or use the default group.

A single-partition database, not surprisingly, has only one database partition. The partition still resides in a database partition group, but all data in the database is stored in the single partition. A partitioned database (or cluster) has two or more database partitions. Tables can be located in one or more database partitions. When a table is in a database partition group consisting of multiple partitions, some of its rows are stored in one partition, and other rows are stored in other partitions. You can create one or more database partitions on a physical system. The number of processors (CPUs) and amount of memory (RAM) installed in the system should be taken into consideration to meet your performance requirements. A database with multiple partitions is also known as a DB2 cluster.

In a multipartition database, the partitioning information is housed in the database node configuration file, db2nodes.cfg. The default location for this file is the database instance owner's home directory, specifically the /home/<instance_name>/sqllib directory on Linux and Unix or \Program Files\IBM\SQLLIB<INSTANCE_NAME> on Windows. Each instance of DB2 has its own db2nodes.cfg file. Whenever a database is created under the instance, the database is partitioned based on the contents of the db2nodes.cfg file.

When a database is created, three partition groups are automatically created by default: the ibmcatgroup, ibmtempgroup, and ibmdefaultgroup partition groups. The ibmcatgroup partition group houses the DB2 catalog tablespace (such as syscatspace). This partition group consists of only one partition. The ibmtempgroup contains the system temporary tablespace (such as tempspace1), and the ibmdefaultgroup contains the user tablespace (such as userspace1). The ibmtempgroup and ibmdefaultgroup partition groups span all of the partitions of a database.

To execute a command or SQL statement against all database partitions, you can use the db2_all command.

Since the backup and restore commands (described later in this chapter) operate against a single partition at a time, a multipartitioned database requires special attention during backup. DB2 requires that the catalog partition (that is, the partition that contains the catalog tables, which would be the partition that the create database command was executed on) be backed up or restored before any of the other partitions.

To do this, you can use the command:

C:> db2_all '<<+0< db2 backup db sample to backup_path'

The <<+0< specifies that only partition 0 is backed up.

After processing the catalog partition, other partitions can be backed up and restored in parallel. This parallel processing can be achieved using the command:

C:> db2_all '||<<-0< db2 backup db sample to backup_path'

where ||<<-0< specifies the desire to run the backup of all partitions except for 0 in parallel.

18.1.2.4. Containers

A container is a physical storage device. It is identified by a directory name, a device name, or a filename. Each container can belong to only one tablespace. (Tablespaces are covered in the next section.) To find which containers are associated with a tablespace, run this command:

C:> db2 list tablespace containers for tablespace_num

In this example, tablespace_num is an integer representing one of the tablespace IDs returned from the list tablespaces command. For example, to see the containers for the userspace1 tablespace (which has a tablespace ID of 2), run this command:

C:> db2 list tablespace containers for 2             Tablespace Containers for Tablespace 2  Container ID                         = 0  Name                                 = C:\DB2\NODE0000\SQL00002\SQLT0002.0  Type                                 = Path

18.1.2.5. Tablespaces

A database is subdivided into tablespaces that are stored on one or more physical storage devices by defining containers on the different devices. Each DB2 database table is assigned to a tablespace, and multiple tables can reside in the same tablespace. Depending on the design and housing tablespace type chosen by the DBA who created the table, the DBA can have indexes associated to the table as well as large objects of the table (character large objects and binary large objects, for example) living in tablespaces other than that used to house the primary table data.

If a tablespace has multiple containers, DB2 spreads the data for a table housed in the tablespace across the containers in a uniform fashion. It is a common practice to use the fastest storage containers for a database's most frequently used tables and slower containers for less frequently used tables.

In DB2, tablespaces take on two different flavors: system managed spaces (SMS) and database managed spaces (DMS). A database can contain a combination of SMS and DMS tablespaces. Each container of an SMS tablespace is a directory in the file space of the operating system running DB2, whereas each container of a DMS tablespace can either be a fixed-size, preallocated file, or a physical device such as a disk. In practice, SMS tablespaces are typically used for small- to moderate-sized databases. DMS tablespaces are more difficult to set up but provide more flexibility. For example, with a DMS tablespace, a container can be added to a tablespace on the fly; you can't do that with an SMS tablespace. With a DMS tablespace, you can also split primary data, tables, indexes, and large objects into different tablespaces. With an SMS tablespace, all data for a table must be stored in the SMS tablespace.

When you first create a database, three tablespaces are created by default: syscatspace, tempspace1, and userspace1. The syscatspace tablespace contains system information about the objects that make up a database. The information is housed in DB2 system catalog tables and views. tempspace1 is the tablespace used by DB2 when temporary tables must be dynamically created to handle such things as join operations. By default, when you create a table and do not explicitly specify a housing tablespace name, the table is created under userspace1.

It is important to know what tablespaces your database is made up of. As you will see later, with archive logging enabled, you can back up at the tablespace granularity. To list the tablespaces of a given database, use this command (shown here for Windows):

C:> db2 list tablespaces            Tablespaces for Current Database  Tablespace ID                        = 0  Name                                 = SYSCATSPACE  Type                                 = System managed space  Contents                             = Any data  State                                = 0x0000    Detailed explanation:      Normal  Tablespace ID                        = 1  Name                                 = TEMPSPACE1  Type                                 = System managed space  Contents                             = System Temporary data  State                                = 0x0000    Detailed explanation:      Normal  Tablespace ID                        = 2  Name                                 = USERSPACE1  Type                                 = System managed space  Contents                             = Any data  State                                = 0x0000    Detailed explanation:      Normal

18.1.2.6. Large objects (LOBs)

DB2 also provides a specialized DMS tablespace called a large tablespace, also referred to as a long tablespace, which is specialized for the storage of large objects, whether binary, character, or graphic. LOB data will be included in regular backups.

18.1.2.7. Transaction logs

In DB2, transaction logs keep track of changes to a database, and they record how sets of changes are grouped as transactions. Think of transactions as a set of SQL statements that are executed as a single (that is, atomic) operation. The transaction logs keep track of whether a transaction is committed or rolled back. Transaction logs play a critical role in both crash recovery and rollforward recovery, both of which are discussed in the section "Recovery Types" later in this chapter. DB2 uses a technique known as write ahead logging in which transactions are logged while they occur, before any data is written to the database. Transaction logs are kept either in files or in raw devices.

You can classify transaction logfiles as either primary or secondary logfiles. Primary logfiles are allocated when the database is first connected to, or during database activation time (using the activate database command). The number of primary logfiles is defined by the logprimary parameter value of the database configuration file. Primary logfiles are created immediately; secondary logfiles are created dynamically on an as-needed basis by the database. Secondary logfiles are created when there is a need for more transaction log space (because all the primary logfiles are filled up, and there is no way an older primary logfile can be overwritten because it contains data from an active transaction). In such cases, the temporary need for more log space is fulfilled by creating secondary logfiles.

The logsecond parameter value of the database configuration file controls the maximum number of secondary logfiles that can be allocated. The size of logfiles is defined by the logfilsiz database configuration parameter; the unit for the value is 4 KB pages. Accordingly, if your database has a logprimary parameter value of 2 and a logfilsiz parameter value of 300, your database has 2 primary logfiles with 300 4 KB pages.

If a log contains information about transactions that have not been committed or rolled back, or if a log contains information that has been committed but has not been externalized to the database disk, the log is considered active. On the other hand, if a log contains information about committed and subsequently externalized transactions (that is, transactions that have been persisted to disk), and the logs are located in the same disk as the active logs, these logs are online archive logs. Archive logs that have been moved from the active log directory to another directory or media are known as offline archive logs. You can move archive logs from the active log directory to another location either manually or automatically with the userexit parameter (in DB2 UDB V8.1 and previous) or the logarchmeth1 and logarchmeth2 parameters (in DB2 UDB V8.2 and later). The usage of these parameters is covered in the section "Managing archive logs," later in this chapter.

You can learn the path of DB2's files by looking at the values of the database configuration parameters. The database configuration also can tell you more information about your logging, such as the number of primary logfiles allowed (specified by the logprimary database configuration parameter) as well as the size of the logfiles (specified by logfilsiz). To see the value of these database configuration parameters, run this command:

C:> db2 get db cfg for sample | find /I "log"

In the Unix world, you can use grep in the same way to look for a particular database configuration parameter. For example, the following command helps pinpoint database configuration parameters related to the location of logfiles (the Windows find command would give similar output):

% db2 get db cfg for sample | grep -i log  Number of primary logfiles      (LOGPRIMARY) = 3 Number of secondary logfiles     (LOGSECOND) = 2 Changed path to logfiles        (NEWLOGPATH) = Path to logfiles                             = /home/db2inst1/NODE0000/SQL00002/SQLOGDIR/ Overflow log path           (OVERFLOWLOGPATH) = Mirror log path               (MIRRORLOGPATH) =

To protect against media failure, keep the database logs on a different physical device from the database itself.

18.1.2.8. Managing archive logs

By default, when you create a DB2 database, it uses circular logging. In circular logging, when a log reaches its maximum size, the log wraps around (in a circle, hence the name circular logging) and overwrites earlier entries unless the logfile or files are still needed for crash recovery. In this case, you encounter a log-full condition. Therefore, the number of primary logfiles must be sufficient to allow at least one inactive logfile at all times.

Circular logging does not allow for rollforward recovery (explained in the section "Recovery Types" later in this chapter). For example, let's say we back up a database at time T0, and the database fails at time T1. To recover from the failure, we restore the database from the T0 backup. If circular logging is enabled (which means that rollforward recovery is not enabled), we would not be able to recover our delta changes from T0 to T1. In DB2 terms, circular logging therefore supports only crash and version recovery (also explained in "Recovery Types").

If a database is using circular logging, the database can be backed up only when no applications are connected to the database. This form of a backup is known as an offline backup. With circular logging, a backup operation also must be performed on the entire database; you cannot perform tablespace-level backup.

If you also want to perform rollforward recovery, you must switch the database from circular logging to archive logging. When a database uses archive logging, it can be backed up when the database is online. (You can also perform offline backups if you choose.) In addition to being able to back up at the database level, archive logging allows you to back up at the tablespace level. This ability to pick and choose which tablespaces are backed up (and subsequently restored) during a backup operation allows you to create a more appropriate database backup plan in which more active (frequently changing) tablespaces can be backed up more often than less active (not so frequently changing) tablespaces.

In addition to allowing you to perform hot backups, archive logging also allows you to recover the database to the point of failure by applying transactions that were successfully committed since the database backup. Going back to our earlier example, let's say we back up a database at time T0, and the database fails at time T1. To recover from the failure, we restore our database from our backup at T0. If archive logging was enabled, we can recover our delta changes from T0 to T1. As you'll see later in this chapter, we are also not restricted to recovering our data all the way to T1 (the end of our logs). We can recover our data up until any point in time between T0 to T1.

There are two methods of enabling archive logging: keeping your archive logs in their original location as long as they are needed for recovery, or copying them to an alternate location to use during recovery.

If you want to keep the logs in their original location, you can set the value of the logretain parameter to recovery. This can be done with the using logretain option to the db2 update db command. The following sequence of commands shows how to enable this parameter. A basic backup command is included because the database is immediately placed into a backup pending state when you enable and activate archive logging.

% db2 update db cfg for sample using logretain on % db2 force applications all % db2 terminate % db2stop % db2 backup db sample % db2start

Setting logretain to recovery adds the complexity of having to worry about your disk filling up with logfiles as applications interact with your database and perform operations that cause your logfiles to grow. The more active your database is, the larger and quicker these logs grow. It is critical that you move old logfiles to an alternate location (ideally not the same location as your database) to prevent the disk from filling up. As mentioned earlier, in order to protect against media failure, you should keep database logs on a different physical device than the database itself. Also, for more frequently changing databases, online backups of the most active tablespaces should be performed more frequently. This way the database can be restored instead of rolled forward using archive logs, which results in longer downtime.

An alternative to leaving the archive logs in the database directory is to copy them to another location. The traditional method of doing this (prior to 8.2) was to set the userexit parameter to on. In 8.2, this parameter has been replaced with the logarchmeth1 and logarchmeth2 parameters.

If you are running DB2 8.2 or later, you should use the logarchmeth1 and logarchmeth2 parameters. These parameters are much easier to use than the userexit parameter; the parameter name is short for log archive method. You can set these parameters in a few different ways:

Set logarchmeth1 to userexit

Only logarchmeth1 can be set to this value, and it is the equivalent to setting userexit to on. userexit is automatically updated for you.

Set logarchmeth1 to DISK

If you set logarchmeth1 to DISK:/ path / directory, archive logs are automatically copied to the directory you specify. This method is much easier than creating a userexit script that accomplishes the same thing.

Set logarchmeth1 to TSM or VENDOR

These arguments are meant for sending archive logs to a commercial backup product. TSM is short for Tivoli Storage Manager.

Optionally set logarchmeth2

If you've set logarchmeth1, you can also set logarchmeth2. If you specify a value for both parameters, both values are used, and archive logs are archived twice. You can specify to archive to a second directory or to archive to TSM or another vendor.

If you are running 8.1 or would prefer to continue using userexit scripts in 8.2, you can also use the userexit parameter. When using this parameter, a user-supplied program (userexit) can automatically take a filled logfile and (if programmed to do so) copy the logfile to an offline archive. User exits also come into play when a database is rebuilt from a backup image. To account for the changes after the database backup, userexit is also responsible for retrieving the offline archive files that it stored externally.

In Unix systems, the userexit can be any executable program, such as a shell or Perl script, or a compiled program. It must be named db2uext2 (with no extension) and must be stored in the sqllib/bin directory of a DB2 installation. In Windows, the user exit must be a compiled program named db2uext2.exe and needs to be stored in the sqllib\bin directory of the DB2 installation. DB2 provides sample userexit programs in the sqllib/samples/c directory.

If you want to use the userexit parameter, the following command sets this configuration parameter to on in an 8.1 (or prior) database:

C:> db2 update db cfg for sample using userexit on

If you want to use the userexit parameter in an 8.2 (or later) database, use this command. It automatically sets userexit to on.

C:> db2 update db cfg for sample using logarchmeth1 userexit

When you change the values of logretain, userexit, logarchmeth1, or logarchmeth2, the database is put into a state known as backup pending. At this point, you must perform a backup (covered later in this chapter) of the database before you can use your database.