Section 13.6. Database and Table Space Roll Forward | Understanding DB2: Learning Visually with Examples (2nd Edition)

13.6. Database and Table Space Roll Forward

If you have to restore your database or a table space in one of your databases, you will lose any changes made since the backup was taken unless you have log retain enabled and use the ROLLFORWARD command to replay the logs for your database.

13.6.1. Database Roll Forward

If a backup operation is performed online, then there are still users connecting to the database and they may be in the middle of a transaction. Therefore, an online backup contains the backup image of a database that is in an inconsistent state. After restoring the backup image into a database, the database is immediately placed in a roll forward pending state. You must run the ROLLFORWARD DATABASE command to bring the database back to a normal state.

If you performed an offline backup but your database is configured to use archival logging, then the database is also placed in a roll forward pending state following a restore. In this case, you do not need to use the ROLLFORWARD command because an offline backup implies that the database is already in a consistent state. To avoid this, use the WITHOUT ROLLING FORWARD option in the RESTORE DATABASE command. You need SYSADM, SYSCTRL, or SYSMAINT authority to perform the ROLLFORWARD command.

During a roll forward, the transactions in the log files are applied. You can apply all the changes in the log files, that is, roll forward to the end of logs, or you can roll forward to a Point In Time. This means DB2 will traverse the logs and redo or undo all database operations recorded in the logs up to the specified PIT. However, you must roll forward the database to at least the minimum recovery time. This is the earliest point in time to which a database must be rolled forward to ensure database consistency. If you attempt to roll forward but fail to do so, you will receive the following error message:

[View full width]

SQL1275N The stoptime passed to roll-forward must be greater than or equal to "timestamp",

because database "dbname" on node(s) "0" contains information later than the specified time.

The timestamp given in the error message is the minimum PIT to which you must roll forward the database.

Though we will not cover the QUIESCE command in this chapter, it is worth mentioning that you can use this command during regular database operations to create consistency points. You can always perform a point in time recovery to any of these points and be assured your database will be consistent. (See section 11.5.1 Lock Attributes, for more information about the QUIESCE command.)

During roll forward processing, DB2 does the following:

Looks for one log file at a time in the active log directory.
If found, reapplies transactions from the log file.
If the log file is not found in the active log directory, DB2 searches for the logs in the OVERFLOWLOGPATH, if specified in the ROLLFORWARD DATABASE command.

If DB2 does not find the log file in the active log directory, and you did not specify the OVERFLOWLOGPATH, then the logs have to be retrieved from their archive location. The method used is determined by the LOGARCHMETH1 database configuration parameter. If it is set to LOGRETAIN, then you have to retrieve the logs manually. If it is set to USEREXIT, then the user exit program db2uext2 is called to retrieve the log file. If it is set to DISK, TSM, or VENDOR, then DB2 automatically retrieves the log file from the respective archive locations.

Once the log is found in the active log directory or the OVERFLOWLOGPATH option, DB2 reapplies the transactions it contains and then goes to retrieve the next file it needs.

The syntax of the ROLLFORWARD command is:

 ROLLFORWARD DATABASE database_alias [USER username [USING password]] [TO {isotime [ON ALL DBPARTITIONNUMS] [USING LOCAL TIME] | END OF LOGS [On-DbPartitionNum-Clause]}] [AND {COMPLETE | STOP}] | {COMPLETE | STOP | CANCEL | QUERY STATUS [USING LOCAL TIME]} [On-DbPartitionNum-Clause] [TABLESPACE ONLINE | TABLESPACE (tblspace-name [ {,tblspace-name} ... ]) [ONLINE]] [OVERFLOW LOG PATH (log-directory [{,log-directory ON DBPARTITIONNUM db-partition-number} ... ])] [NORETRIEVE] [RECOVER DROPPED TABLE dropped-table-id TO export-directory]

You can use one or more of the following options for On_DbPartitionNum_Clause:

 ON {{DBPARTITIONNUM | DBPARTITIONNUMS} (db-partition-number [TO db-partition-number] , ... ) | ALL DBPARTITIONNUMS [EXCEPT {DBPARTITIONNUM | DBPARTITIONNUMS} (db-partition-number [TO db-partition-number] , ...)]}

To perform a roll forward of the sample database you can use any of the following statements:

ROLLFORWARD DATABASE sample TO END OF LOGS AND COMPLETE    (1)
ROLLFORWARD DATABASE sample TO timestamp AND COMPLETE      (2)
ROLLFORWARD DATABASE sample TO timestamp
USING LOCAL TIME AND COMPLETE                              (3)

Example (1) rolls forward to the end of the logs, which means that all archived and active logs are traversed. At the end DB2 completes the roll forward operation and brings the database from rollforward pending state to a usable state.

Example (2) rolls forward to the specified Point In Time. The timestamp used is in UTC (Universal Coordinated Time), which can be calculated as follows:

Local time value in the CURRENT_TIMEZONE special register

For example, to look at the value of the CURRENT_TIMEZONE special register, connect to the database and issue the following SQL statement:

 db2 "VALUES (CURRENT_TIMEZONE)"

If the local time is 2004-05-29-14.42.38.000000, and CURRENT_TIMEZONE is -5, then the corresponding UTC time is 2004-05-29-19.42.38.000000.

Example (3) is similar to example (2), but the timestamp can be expressed using local time.

Note that there is no keyword OFFLINE in the syntax, as this is the default mode. In fact, for the ROLLFORWARD command, this is the only mode allowed.

13.6.2. Table Space Roll Forward

You can perform table space roll forwards either online or offline, except for the system catalog table space (SYSCATSPACE) which can only be rolled forward offline. The following is an example of a table space ROLLFORWARD:

 ROLLFORWARD DATABASE sample TO END OF LOGS AND COMPLETE TABLESPACE ( userspace1 ) ONLINE

The options in this example have already been explained in section 13.6.1, Database Roll Forward. The only difference is the TABLESPACE option, which specifies the table space to be rolled forward.

13.6.3. Table Space Roll Forward Considerations

If the registry variable DB2_COLLECT_TS_REC_INFO is enabled, only the log files required to recover the table space are processed. The ROLLFORWARD command will skip over log files that are not required, which may speed recovery time.

You can use the QUERY STATUS option of the ROLLFORWARD command to list the log files that DB2 has rolled forward, the next archive log file required, and the timestamp of the last committed transaction since roll forward processing began. For example:

 ROLLFORWARD DATABASE sample QUERY STATUS USING LOCAL TIME

After a table space Point In Time roll forward operation completes, the table space is placed into backup pending state. A backup of the table space or database must be taken because all updates made to it between the PIT that the table space was recovered to and the current time have been lost.

13.6.4. Performing ROLLFORWARDs with the Control Center

You can use the Rollforward Wizard to perform a roll forward. In the Control Center, expand your database folder, right-click on the database name you want to roll forward, and select Rollforward from the menu. Alternatively, you can click on the database name and then select Rollforward from the Selected pull-down menu. The database must be in roll forward pending state to invoke the Rollforward Wizard. Figure 13.9 shows that you can choose to roll forward to the end of logs or to a PIT. The Rollforward Wizard guides you through the roll forward command options.

Figure 13.9. The Rollforward Wizard