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.

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.

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.

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.

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.

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.

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.

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 usingpassword

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.

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 FilesIBMSQLLIB<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.

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 tobackup_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 tobackup_path'

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

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 fortablespace_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:DB2NODE0000SQL00002SQLT0002.0
 Type                                 = Path

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

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.

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.

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:

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 sqllibin 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 running the backup command, you can opt for a full, incremental, or delta backup. A full backup, as its name implies, contains all of the data of the database or tablespace being backed up. An incremental backup (also called a cumulative backup) makes a copy of all database data that has changed since the most recent successful full backup. On the other hand, a delta backup copies all database data that has changed since the most recent successful backup (full or other). Incremental and delta backups are therefore reliant on previous backup images and cannot be used by themselves to restore a database. This reliance on other, previously created, backup images makes it very important to save all the backup images needed to perform a database recovery.

DB2 automatically determines the pathname and filename for backup files. The generation of this name is far from random; it follows a strict naming convention. Dissecting the name of a database backup path and file can let you know a lot about the DB2 backup that created the backup file.

The following information is contained in the path and/or name of a backup file:

In Windows, this naming convention is represented by a pathname and a filename. In Unix, the naming convention is represented only by the filename. On Windows, a four-level subdirectory tree houses the backup file:

DB_alias.TypeInst_nameNODEnnnnCATNnnnnyyyymmddhhmmss.Seq_num

A backup filename in Windows might be:

SAMPLE.0DB2INSTNODE0000CATN000020060227145655.001

For Linux and Unix, rather than dealing with a four-level subdirectory tree, all the information is built into the filename itself:

DB_alias.Type.Inst_name.NODEnnnn.CATNnnnn.timestamp.Seq_num

Using the same parameters as the Windows example, our Linux or Unix filename example would be:

SAMPLE.0.DB2INST.NODE0000.CATN0000.20060227.145655.001

Backup, restore, and rollforward operations performed on a database are logged in a recovery history file named db2rhist.asc. Each database has its own recovery history file, located in the same directory as the database.

The list history command shows the backup, restore, and rollforward operations performed on a database. For example, to learn which backup and restore operations have been performed on the sample database, you can use this command:

C:> db2 list history backup all for db sample

The report generated by issuing this command contains a symbol indicating the operation performed (B for backup, R for restore). Here is a sample of the output from the db2 list history command:

                    List History File for sample

Number of matching file entries = 1


 Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID
 -- --- ------------------ ---- --- ------------ ------------ --------------
  B  D  20060409190953001   F    D  S0000000.LOG S0000000.LOG
 ----------------------------------------------------------------------------

If the operation was a backup, a symbol indicates the type of backup:

F – Offline Backup
N – Online Backup
I - Incremental Offline Backup
O - Incremental Online Backup
D - Delta Offline Backup
E - Delta Online Backup

The backup report also tells you which tablespaces were backed up as well as the location of the backup image. This information can come in quite handy when trying to recover your database.

To learn which rollforward operations have been performed on the sample database, use this command:

C:> db2 list history rollforward all for db sample

The report generated by issuing this command contains an F, indicating that a rollforward operation was performed. The type column of the report indicates whether the rollforward was performed to the end of the logs (indicated by an E) or to a point in time (indicated by a P).

DB2 has components with autonomic features and automated backup utilities. These components include database configuration parameters, the health monitor and its indicators, and a number of GUI interfaces (including the Health Center, the Web Health Center, the Task Center, and the Configure Automatic Maintenance wizard). If you are a power user, you can skip the GUIs and configure automatic backup using the appropriate command-line processor (CLP) commands. See the IBM DB2 Universal Database System Monitor Guide and Reference for detailed instructions. This section provides a high-level overview.

The DB2 health monitor runs on the database server. The health monitor provides the functionality required for automatic database backup and maintenance. The health monitor gathers information about the health of the system using health indicators. Using these indicators does not impose a performance penalty. They are not the same thing as snapshot monitor switches.

Health indicators exist at the instance, database, tablespace, and tablespace container levels. The DBA configures health indicators using the Health Center, the Web Health Center, the CLP, or APIs. DB2 monitors these indicators and can take actions identified by the DBA. These actions can include:

Health indicators fall into four categories:

The db.db_backup_req health indicator is a state-based, database-level indicator. This indicator determines when a database backup is required based on either the time elapsed or the amount of data changed since the last backup. The auto_db_backup database configuration parameter must be set to on for automatic backups to occur. Automatic database backups can be either offline (cold) or online (hot).

The db.tb_reorg_req and db.tb_runstats_req indicators are set after a database is restored. Here is the recommended procedure for restoring a DB2 database:

Following this procedure helps enhance the performance of your applications. Reorg and runstats can be run at any time in offline (cold) or online (hot) mode. They do not require a database restore operation. However, you should include these steps in your database restore or recovery operating procedures.

A backup policy specifies automated maintenance behavior to ensure that the database is backed up regularly. The backup policy for a database is created automatically when the DB2 Health Monitor first runs. You can still perform manual backup operations when automatic maintenance is configured. DB2 performs automatic backup operations only if they are required.

You define the time periods, or windows, for online and offline maintenance with the DB2 Health Center. DB2 then determines the need to perform a backup operation based on one or more of the following criteria:

You configure the requested time or number of log pages between backups, the backup media, and the backup type (online or offline) using the Configure Automatic Maintenance wizard in the Control Center or Health Center. Automatic database backup can be enabled for either online (hot) or offline (cold) backup if the database is enabled for rollforward recovery (archive logging); otherwise, only offline backup is available. Automatic database backup supports disk, tape, Tivoli® Storage Manager (TSM), and vendor DLL media types.

If you select Backup to Disk, the automatic backup feature regularly deletes backup images from the directory specified in the Configure Automatic Maintenance wizard. Only the most recent backup image is guaranteed to be available at any given time. This directory should be kept exclusively for the automatic backup feature and not be used to store other backup images.

Offline backup, restore, reorg, and runstats restrict access to the database, as does online reorg and, to a certain extent, online restore. Offline database backups and table and index reorganization are run in the offline maintenance time period that you define. These features run to completion even if they go beyond the time period specified. DB2’s internal scheduling mechanism learns over time and estimates job completion times. If the offline time period you define is too small for a particular database backup or reorganization activity, the scheduler does not start the job the next time around. Instead, the health monitor notifies you if you need to increase the offline-maintenance time period.

You can set up automated backup, reorg, and runstats using the DB2 Health Center:

The Health Center now shows two health indicators: Database backup required and Update statistics required. You should verify that automatic maintenance parameter settings for the database are enabled, substituting db_name with the name of the database for which you have created the automatic maintenance policy:

If the database parameters are set to off, you can enable them with the following commands, which work on either platform (shown here on Windows):

C:> db2 connect to db_name
C:> db2 update db cfg using auto_maint on
C:> db2 update db cfg using auto_db_backup on
C:> db2 update db cfg using auto_tbl_maint on
C:> db2 update db cfg using auto_runstats on
C:> db2 update db cfg using auto_reorg on
C:>db2 connect reset

To reset the Database Backup Required Indicator, use this command:

C:> db2 backup dbdb_name

You have now automated the backup and maintenance of your database. Refer to the IBM DB2 Universal Database System Monitor Guide and Reference for information on setting other health indicators.

In addition to performing a backup, it’s a good idea to run the db2look command against your database and save that output. db2look will display the statements necessary to reproduce the database objects of a database. The command below would create such an output for the sample database, and send it to path/file.

db2look -d sample -a -m -l -x -xd -f -o path/file

The output stored in path/file can come in very handy when recovering a DB2 database. It’s a good idea to run this as soon as you create a database, or any time you make any structural changes to the database. Then place the output of this command someplace where you can easily retrieve it during a recovery operation.

Crash recovery ensures that the database is brought to a consistent state after a software failure or power outage. Fortunately, crash recovery in DB2 is quite simple, and nothing special has to be done to a database to prepare it for crash recovery.

If you would rather have the database wait for you after a crash, you can turn off crash recovery by setting the autorestart database configuration parameter to off. If you change this setting, you must run the restart command to start the database after a crash:

% db2 restart db sample

The restart command effectively establishes a database connection. The database logs are then used to restore the database to a transaction-consistent state. Consequently, database changes made by committed transactions before the failure are made effective. On the other hand, rolled back transactions are rolled back in the database as are transactions that were in flight during the failure. At the end of the crash recovery, the DB2 database is restored to a transaction-consistent state.

If a database is damaged beyond the point where crash recovery can repair it (for example, the loss of a container), it must be restored from backup. Since this restores the database to a previous version, this is referred to as version recovery.

A version recovery restores the database to the state it was in at the time the database backup was performed. Changes made to the database after a backup was taken are lost unless rollforward recovery is enabled prior to the failure and a rollforward command is issued after the restore (see the section “Performing a Rollforward Recovery” later in this chapter). Accordingly, in practice it’s common to see a restore command used in tandem with a rollforward command.

DB2 8.2 introduced the recover command, which automatically performs a version recovery followed by a rollforward recovery.

In order to restore a database or one of its tablespaces, we need to have access to our backups. This may sound obvious, but it is not so trivial when dealing with incremental and delta backups (see the section “Backup levels” earlier in this chapter). In short, we need to have all of the database backups necessary to restore our database/tablespace(s). If the files were moved to a different machine or network, gather the backup files in preparation for the restore operation. If you can’t find all your backup files, you might want to consider digging into the recovery history file of your database (see the section “Discovering the history of your backup operations” earlier in this chapter). The recovery history file can tell you where the database backups were created and the names of their files if they were stored on disk. If possible, put the backup files needed for your restore into a single directory. For our example, we’ll use the directory C:ackups.

If you do not intend to perform a redirected restore of your database and want the database backup to be restored into the database it came from, it is important that the containers that existed at the time when the backup image was made are still around. This is commonly not the case if you have to restore your database to a new machine after the original machine failed. If the tablespace containers that existed when your database backup was performed don’t exist in the location you are trying to restore, you get an error during the recovery operation. To avoid such a problem, you can use a redirected restore (as described later in this chapter).

We are finally ready to issue the restore database command. If you’re running a version prior to 8.2, use the following syntax to restore a database:

C:> restore DB database_name_or_alias from backup_location taken at yyyymmddhhmmss

For example, the following command specifies that we want to restore our database named sample from a backup identified by the timestamp 20060227145655:

C:> db2 restore db sample from c:ackups taken at 20060227145655 replace existing

As mentioned earlier, the timestamp of a backup is part of its filename. The replace existing clause states that existing data (if any) is deleted and replaced by the content of our backup.

You can also restore just a tablespace. Tablespaces can be restored online or offline (except for a tablespace containing the system catalog tables, which must be restored offline). Tablespaces can be restored from full-fledged database backups or from backups that were performed at the tablespace granularity. You can use the following syntax to restore tablespace(s) of a database:

restore database database_name_or_alias tablespace 
(tablespace_name, other_table_space_name,...) online from backup_location taken at yyyymmddhhmmss replace existing

For example, the following command performs an online restore of the tablespace named userspace1 of the sample database from a backup identified by the timestamp 20060227145655 and located in C:ackups.

C:> db2 restore db sample tablespace(userspace1) online from c:ackups taken at 20060227145655 replace existing

If you’re running 8.2 or later, and you want to recover the entire database, you have the option of using the recover command instead:

C:> db2 recover db sample to isotime

This command automatically selects a backup that is prior to isotime, restores it, then rolls forward to the most recent transaction that it can find prior to isotime.

If you did not include the without rolling forward clause when issuing your restore command, the database is left in a rollforward pending state after the restore command completes. Tablespace restoration always places the restored tablespace(s) in the rollforward pending state. The database cannot be used until it is brought out of this state with the rollforward command. See the section “Performing a Rollforward Recovery” later in this chapter.

This step is covered in detail in the section “Reorganizing Data and Collecting Statistics” later in this chapter.

Assume that you have a valid backup that you want to recover. Let’s use our earlier example from the restore command procedure, but this time perform a redirected restore:

C:> db2 restore db sample from c:ackups taken at 20060227145655 redirect

By specifying the redirect option, DB2 actually pauses so that you can define the appropriate tablespace containers for your target database.

At this point, we need to define the appropriate tablespace containers for our target database. The question is, how do we know what tablespace containers we need? This is where we can use the list tablespaces show detail command or the list tablespace containers for tablespace_num command, where tablespace_num is an integer representing one of the entries returned from issuing the list tablespaces command.

Of course, you cannot learn about tablespace container information from the source database if the source database has failed. Accordingly, you should get information about the tablespaces in the source database being backed up and put it away for safekeeping when needed for a redirected restore operation. You can use the output of the db2look command to provide this information (see the section “Using db2look” earlier in this chapter).

Next, define tablespace containers for the tablespaces that are associated with the database backup being restored. For example, the following commands create new tablespace containers for the syscatspace, tempspace1, and userspace1 tablespaces. In this case, we use directory names relative to the database directory, not absolute pathnames.

C:> db2 "set tablespace containers for 0 using (path 'tbsp0cont1')" 
C:> db2 "set tablespace containers for 1 using (path 'tbsp1cont1')" 
C:>db2 "set tablespace containers for 2 using (path 'tbsp2cont1')"

In Step 1, we were able to pause the restore db operation by specifying the redirect option. Now that we have defined the needed containers in Step 2, we can proceed with restoring the database:

C:> db2 restore db sample continue

As was the case with our in-place restore, if you are interested in accounting for transactions that occurred after our backup and after archive logging was enabled, you need to perform a rollforward recovery by issuing the rollforward command described in the following section “Performing a Rollforward Recovery.”

This step is covered in detail in the section “Reorganizing Data and Collecting Statistics” later in this chapter.

In order for rollforward recovery to work, you need to have all your logfiles. The rollforward command assumes that the logfiles are in the log directory specified in the logpath configuration parameter; alternatively, you can specify the rollforward command with an overflow log path log directory. The overflow log path directory might be where a user exit saves archived logs or where the logarchmeth1 parameter, available in 8.2 and later, copies them. If you are recuperating from a disk failure, move the archived logs to the overflow log path directory so the rollforward command can see the archived logs.

If you plan to roll forward tablespaces to a PIT prior to the end of your logs, this PIT must be equal to or greater than the minimum PIT for your tablespaces. As mentioned earlier, you can examine the minimum PIT with this command:

C:> db2 list tablespaces show detail

Let’s cover a few examples of the rollforward command to exemplify how you might use it.

C:> db2 rollforward db sample user db2admin using password to 2006-03-01-13.23.19.232911 and stop

This command rolls the sample database forward. In this example, the rollforward operation is carried out under the username db2admin with a password of password. The timestamp, 2006-03-01-13.23.19.232911, specifies the point that the rollforward should stop. As you can imagine, this flexibility of PIT recovery can help if a database is corrupted by some rogue or even trusted application. Simply recover your database and then rollforward to before things went bad. The and stop clause in the example command tells DB2 to take the database out of the rollforward pending state so you can start using it.

If you want to roll forward all the way to the end of our logs, use this command:

C:> db2 rollforward db sample user db2admin using password to end of logs and stop

You may have noticed that we did not try to perform the rollforward operations using the online keyword. This is because you cannot perform rollforward operations online at the database level. You can do this at the tablespace level, though, as shown in the following example:

C:> db2 rollforward db sample user db2admin using password to end of logs and stop tablespace(userspace1) online

This command rolls forward the sample database’s tablespace named userspace1. The command is issued following the restore command for the userspace1 tablespace. The rollforward operation is carried out under the username db2admin with a password of password. An online rollforward operation cannot be performed on the system catalog tablespace (syscatspace). As done earlier, the and stop clause of our example command tells DB2 to take our database out of the rollforward pending state so that we can start using the database.

Special attention also needs to be paid when rolling forward tablespaces with tables that have referential integrity relationships with tables housed in other tablespaces. When you roll forward such a tablespace, DB2 leaves the table in the check pending state. This state prevents the table from being used until you check its constraints. You can restore a table from the check pending state to a normal state by executing a set constraints statement.

When issuing the set constraints command, it is a good idea to specify an exception table into which rows violating the defined constraints are placed. The following command removes the table test from the check pending state. But before doing so, the contents of the table are checked against defined constraints for the table. Violators of the constraints are placed in the badtest table. To finish things up, the table is returned to the normal state. Future updates to the table are performed only if the updates do not violate the constraints of the table.

C:> db2 set constraints for test immediate checked for exception in test use badtest

If you performed a rollforward operation at the tablespace level in Step 3, your tablespaces are in a backup pending state. You must perform a database backup before using the tablespaces.

This step is covered in detail in the next section.