DB2 Version 9.7 for Linux, UNIX, and Windows

ROLLFORWARD DATABASE command

The ROLLFORWARD DATABASE command recovers a database by applying transactions recorded in the database log files. This command can be run after a database or a table space backup image has been restored, or if any table spaces have been taken offline by the database due to a media error.

The database must be recoverable (that is, the logarchmeth1 or logarchmeth2 database configuration parameters must be set to a value other than OFF) before the database can be recovered with rollforward recovery.

Scope

In a partitioned database environment, this command can only be invoked from the catalog partition. A database or table space rollforward operation to a specified point in time affects all database partitions that are listed in the db2nodes.cfg file. A database or table space rollforward operation to the end of logs affects the database partitions that are specified. If no database partitions are specified, it affects all database partitions that are listed in the db2nodes.cfg file; if rollforward recovery is not needed on a particular partition, that partition is ignored.

For partitioned tables, you are also required to roll forward related table spaces to the same point in time. This applies to table spaces containing data partitions of a table. If a single table space contains a portion of a partitioned table, rolling forward to the end of the logs is still allowed.

It is not possible to roll forward through log files created on a previous DB2® database release version. This is an important consideration when upgrading to a new DB2 database release version.

Authorization

One of the following:

SYSADM
SYSCTRL
SYSMAINT

Required connection

None. This command establishes an exclusive database connection.

Command syntax


>>-ROLLFORWARD--+-DATABASE-+--database-alias-------------------->
                '-DB-------'                   

>--+-------------------------------------+---------------------->
   '-USER--username--+-----------------+-'   
                     '-USING--password-'     

>--+-------------------------------------------------------------------------------------+-->
   |                .-ON ALL DBPARTITIONNUMS-.  .-USING UTC TIME---.                     |   
   +-TO--+-isotime--+------------------------+--+------------------+-+--+--------------+-+   
   |     |                                      '-USING LOCAL TIME-' |  +-AND COMPLETE-+ |   
   |     |                .-ON ALL DBPARTITIONNUMS-.                 |  '-AND STOP-----' |   
   |     +-END OF BACKUP--+------------------------+-----------------+                   |   
   |     '-END OF LOGS--+----------------------------------+---------'                   |   
   |                    '-| On Database Partition clause |-'                             |   
   '-+-COMPLETE---------------------------+--+----------------------------------+--------'   
     +-STOP-------------------------------+  '-| On Database Partition clause |-'            
     +-CANCEL-----------------------------+                                                  
     |               .-USING UTC TIME---. |                                                  
     '-QUERY STATUS--+------------------+-'                                                  
                     '-USING LOCAL TIME-'                                                    

>--+-------------------------------------------------------+---->
   '-TABLESPACE--+-ONLINE--------------------------------+-'   
                 |    .-,---------------.                |     
                 |    V                 |                |     
                 '-(----tablespace-name-+--)--+--------+-'     
                                              '-ONLINE-'       

>--+------------------------------------------------------------------------+-->
   '-OVERFLOW LOG PATH--(--log-directory--+----------------------------+--)-'   
                                          '-,--| Log Overflow clause |-'        

>--+------------+----------------------------------------------->
   '-NORETRIEVE-'   

>--+------------------------------------------------------------+-><
   '-RECOVER DROPPED TABLE--drop-table-id--TO--export-directory-'   

On Database Partition clause

       .-ALL DBPARTITIONNUMS--+--------------------------------------------+-.   
       |                      '-EXCEPT--| Database Partition List clause |-' |   
|--ON--+-| Database Partition List clause |----------------------------------+--|

Database Partition List clause

|--+-DBPARTITIONNUM--+------------------------------------------>
   '-DBPARTITIONNUMS-'   

     .-,--------------------------------------------------.     
     V                                                    |     
>--(---db-partition-number1--+--------------------------+-+-)---|
                             '-TO--db-partition-number2-'       

Log Overflow clause

   .-,------------------------------------------------------.   
   V                                                        |   
|----log-directory--ON DBPARTITIONNUM--db-partition-number1-+---|

Command parameters

DATABASE database-alias

The alias of the database that is to be rollforward recovered.

USER username

The user name under which the database is to be rollforward recovered.

USING password

The password used to authenticate the user name. If the password is omitted, you will be prompted to enter it.

TO

isotime

The point in time to which all committed transactions are to be rolled forward (including the transaction committed precisely at that time, as well as all transactions committed previously). A rollforward operation to a point in time returns a success message only if there is a transaction with a larger timestamp value in the log files. Even if there is no transaction with a larger timestamp, you can still issue a rollforward operation with the COMPLETE option.

This value is specified as a time stamp, a 7-part character string that identifies a combined date and time. The format is yyyy-mm-dd-hh.mm.ss (year, month, day, hour, minutes, seconds), expressed in Coordinated Universal Time (UTC, formerly known as GMT). UTC helps to avoid having the same time stamp associated with different logs (because of a change in time associated with daylight savings time, for example). The time stamp in a backup image is based on the local time at which the backup operation started. The CURRENT TIMEZONE special register specifies the difference between UTC and local time at the application server. The difference is represented by a time duration (a decimal number in which the first two digits represent the number of hours, the next two digits represent the number of minutes, and the last two digits represent the number of seconds). Subtracting CURRENT TIMEZONE from a local time converts that local time to UTC.

USING UTC TIME

Allows you to rollforward to a point in time that is specified as UTC time. This is the default option.

USING LOCAL TIME

Allows you to rollforward to a point in time that is the server's local time rather than UTC time.

Note:

If you specify a local time for rollforward, all messages returned to you will also be in local time. All times are converted on the server, and in partitioned database environments, on the catalog database partition.
The timestamp string is converted to UTC on the server, so the time is local to the server's time zone, not the client's. If the client is in one time zone and the server in another, the server's local time should be used. This is different from the local time option from the Control Center, which is local to the client.
If the timestamp string is close to the time change of the clock due to daylight savings, it is important to know if the stop time is before or after the clock change, and specify it correctly.
Subsequent ROLLFORWARD commands that cannot specify the USING LOCAL TIME clause will have all messages returned to you in local time if this option is specified.
It is important to choose the USING LOCAL TIME or the USING UTC TIME (formerly known as GMT time) correctly. If not specified, the default is USING UTC TIME. Any mistake in the selection may cause rollforward to reach a different point in time than expected and truncate the logs after that point in time. Mistaking a local timestamp as a UTC timestamp may cause the required logs to be truncated undesirably and prevent further rollforwards to a point later than the mistaken time.
Specify a valid timestamp when recovering a database. A valid timestamp would be the time that the last backup in the partitioned database system was completed.
When issuing multiple ROLLFORWARD DATABASE commands, the timestamp you specify for each subsequent command must be greater than the timestamp you specified in the previous command.

END OF LOGS

Specifies that all committed transactions from all online archive log files listed in the database configuration parameter logpath are to be applied.

If an END OF LOGS rollforward is attempted, you cannot switch to a point-in-time (PIT) rollforward. To rollforward to a PIT when a previous END OF LOGS rollforward is in progress, you must redo the restore and then run the rollforward command.

END OF BACKUP

Specifies that all partitions in the partitioned database should be rolled forward to the minimum recovery time. See Examples section below for an example.

ALL DBPARTITIONNUMS | ON ALL DBPARTITIONNUMS

Specifies that transactions are to be rolled forward on all database partitions specified in the db2nodes.cfg file. This is the default if a database partition clause is not specified.

EXCEPT

Specifies that transactions are to be rolled forward on all database partitions specified in the db2nodes.cfg file, except those specified in the database partition list.

ON DBPARTITIONNUM | ON DBPARTITIONNUMS

Roll the database forward on a set of database partitions.

db-partition-number1: Specifies a database partition number in the database partition list.
db-partition-number2: Specifies the second database partition number, so that all database partitions from db-partition-number1 up to and including db-partition-number2 are included in the database partition list.

COMPLETE | STOP

Stops the rolling forward of log records, and completes the rollforward recovery process by rolling back any incomplete transactions and turning off the rollforward pending state of the database. This allows access to the database or table spaces that are being rolled forward. These keywords are equivalent; specify one or the other, but not both. The keyword AND permits specification of multiple operations at once; for example, db2 rollforward db sample to end of logs and complete. When rolling table spaces forward to a point in time, the table spaces are placed in backup pending state.

CANCEL

Cancels the rollforward recovery operation. This puts the database or one or more table spaces on all database partitions on which forward recovery has been started in restore pending state:

If a database rollforward operation is not in progress (that is, the database is in rollforward pending state), this option puts the database in restore pending state.
If a table space rollforward operation is not in progress (that is, the table spaces are in rollforward pending state), a table space list must be specified. All table spaces in the list are put in restore pending state.
If a table space rollforward operation is in progress (that is, at least one table space is in rollforward in progress state), all table spaces that are in rollforward in progress state are put in restore pending state. If a table space list is specified, it must include all table spaces that are in rollforward in progress state. All table spaces on the list are put in restore pending state.
If rolling forward to a point in time, any table space name that is passed in is ignored, and all table spaces that are in rollforward in progress state are put in restore pending state.
If rolling forward to the end of the logs with a table space list, only the table spaces listed are put in restore pending state.

This option cannot be used to cancel a rollforward operation that is actually running. It can only be used to cancel a rollforward operation that is in progress but not actually running at the time. A rollforward operation can be in progress but not running if:

It terminated abnormally.
The STOP option was not specified.
An error caused it to fail. Some errors, such as rolling forward through a non-recoverable load operation, can put a table space into restore pending state.

Use this option with caution, and only if the rollforward operation that is in progress cannot be completed because some of the table spaces have been put in rollforward pending state or in restore pending state. When in doubt, use the LIST TABLESPACES command to identify the table spaces that are in rollforward in progress state, or in rollforward pending state.

QUERY STATUS

Lists the log files that the database manager has rolled forward, the next archive file required, and the time stamp (in UTC) of the last committed transaction since rollforward processing began. In a partitioned database environment, this status information is returned for each database partition. The information returned contains the following fields:

Database partition number
Rollforward status: Status can be: database or table space rollforward pending, database or table space rollforward in progress, database or table space rollforward processing STOP, or not pending.
Next log file to be read: A string containing the name of the next required log file. In a partitioned database environment, use this information if the rollforward utility fails with a return code indicating that a log file is missing or that a log information mismatch has occurred.
Log files processed: A string containing the names of processed log files that are no longer needed for recovery, and that can be removed from the directory. If, for example, the oldest uncommitted transaction starts in log file x, the range of obsolete log files will not include x; the range ends at x - 1. This field is not updated in case of a table space rollforward recovery operation.
Last committed transaction: A string containing a time stamp in ISO format (yyyy-mm-dd-hh.mm.ss) suffixed by either "UTC" or "Local" (see USING LOCAL TIME). This time stamp marks the last transaction committed after the completion of rollforward recovery. The time stamp applies to the database. For table space rollforward recovery, it is the time stamp of the last transaction committed to the database.

QUERY STATUS is the default value if the TO, STOP, COMPLETE, or CANCEL clauses are omitted. If TO, STOP, or COMPLETE was specified, status information is displayed if the command has completed successfully. If individual table spaces are specified, they are ignored; the status request does not apply only to specified table spaces.

TABLESPACE

This keyword is specified for table space-level rollforward recovery.

tablespace-name

Mandatory for table space-level rollforward recovery to a point in time. Allows a subset of table spaces to be specified for rollforward recovery to the end of the logs. In a partitioned database environment, each table space in the list does not have to exist at each database partition that is rolling forward. If it does exist, it must be in the correct state.

For partitioned tables, point in time roll-forward of a table space containing any piece of a partitioned table must also roll-forward all of the other table spaces in which that table resides to the same point in time. The table spaces containing the index partitions are included in the list of pieces of a partitioned table. Roll-forward to the end of the logs for a single table space containing a piece of a partitioned table is still allowed.

If a partitioned table has any attached or detached data partitions, then PIT rollforward must include all table spaces for these data partitions as well. To determine if a partitioned table has any attached, detached, or dropped data partitions, query the Status field of the SYSDATAPARTITIONS catalog table.

Because a partitioned table can reside in multiple table spaces, it will generally be necessary to roll forward multiple table spaces. Data that is recovered via dropped table recovery is written to the export directory specified in the ROLLFORWARD DATABASE command. It is possible to roll forward all table spaces in one command, or do repeated roll forward operations for subsets of the table spaces involved. If the ROLLFORWARD DATABASE command is done for one or a few table spaces, then all data from the table that resided in those table spaces will be recovered. A warning will be written to the notify log if the ROLLFORWARD DATABASE command did not specify the full set of the table spaces necessary to recover all the data for the table. Allowing rollforward of a subset of the table spaces makes it easier to deal with cases where there is more data to be recovered than can fit into a single export directory.

ONLINE

This keyword is specified to allow table space-level rollforward recovery to be done online. This means that other agents are allowed to connect while rollforward recovery is in progress.

OVERFLOW LOG PATH log-directory

Specifies an alternate log path to be searched for archived logs during recovery. Use this parameter if log files were moved to a location other than that specified by the logpath database configuration parameter. In a partitioned database environment, this is the (fully qualified) default overflow log path for all database partitions. A relative overflow log path can be specified for single-partition databases. The OVERFLOW LOG PATH command parameter will overwrite the value (if any) of the database configuration parameter overflowlogpath.

log-directory ON DBPARTITIONNUM

In a partitioned database environment, allows a different log path to override the default overflow log path for a specific database partition.

NORETRIEVE

Allows you to control which log files are to be rolled forward by disabling the retrieval of archived logs.

RECOVER DROPPED TABLE drop-table-id

Recovers a dropped table during the rollforward operation. The table ID can be obtained using the LIST HISTORY command, in the Backup ID column of the output listing. For partitioned tables, the drop-table-id identifies the table as a whole, so that all data partitions of the table can be recovered in a single roll-forward command.

TO export-directory

Specifies a directory to which files containing the table data are to be written. The directory must be accessible to all database partitions.

Examples

Example 1

The ROLLFORWARD DATABASE command permits specification of multiple operations at once, each being separated with the keyword AND. For example, to roll forward to the end of logs, and complete, the separate commands:

db2 rollforward db sample to end of logs
db2 rollforward db sample complete

can be combined as follows:

db2 rollforward db sample to end of logs and complete

Although the two are equivalent, it is recommended that such operations be done in two steps. It is important to verify that the rollforward operation has progressed as expected, before stopping it and possibly missing logs. This is especially important if a bad log is found during rollforward recovery, and the bad log is interpreted to mean the "end of logs". In such cases, an undamaged backup copy of that log could be used to continue the rollforward operation through more logs. However if the rollforward AND STOP option is used, and the rollforward encounters an error, the error will be returned to you. In this case, the only way to force the rollforward to stop and come online despite the error (that is, to come online at that point in the logs before the error) is to issue the ROLLFORWARD STOP command.

Example 2

Roll forward to the end of the logs (two table spaces have been restored):

db2 rollforward db sample to end of logs
db2 rollforward db sample to end of logs and stop

These two statements are equivalent. Neither AND STOP or AND COMPLETE is needed for table space rollforward recovery to the end of the logs. Table space names are not required. If not specified, all table spaces requiring rollforward recovery will be included. If only a subset of these table spaces is to be rolled forward, their names must be specified.

Example 3

After three table spaces have been restored, roll one forward to the end of the logs, and the other two to a point in time, both to be done online:

db2 rollforward db sample to end of logs tablespace(TBS1) online

db2 rollforward db sample to 1998-04-03-14.21.56 and stop
   tablespace(TBS2, TBS3) online

Two rollforward operations cannot be run concurrently. The second command can only be invoked after the first rollforward operation completes successfully.

Example 4

After restoring the database, roll forward to a point in time, using OVERFLOW LOG PATH to specify the directory where the user exit saves archived logs:

db2 "rollforward db sample to 1998-04-03-14.21.56 and stop
   overflow log path (/logs)"

Example 5 (partitioned database environments)

There are three database partitions: 0, 1, and 2. Table space TBS1 is defined on all database partitions, and table space TBS2 is defined on database partitions 0 and 2. After restoring the database on database partition 1, and TBS1 on database partitions 0 and 2, roll the database forward on database partition 1:

db2 rollforward db sample to end of logs and stop

This returns warning SQL1271 ("Database is recovered but one or more table spaces are off-line on database partition(s) 0 and 2.").

db2 rollforward db sample to end of logs

This rolls TBS1 forward on database partitions 0 and 2. The clause TABLESPACE(TBS1) is optional in this case.

Example 6 (partitioned database environments)

After restoring table space TBS1 on database partitions 0 and 2 only, roll TBS1 forward on database partitions 0 and 2:

db2 rollforward db sample to end of logs

Database partition 1 is ignored.

db2 rollforward db sample to end of logs tablespace(TBS1)

This fails, because TBS1 is not ready for rollforward recovery on database partition 1. Reports SQL4906N.

db2 rollforward db sample to end of logs on dbpartitionnums (0, 2) 
   tablespace(TBS1)

This completes successfully.

db2 rollforward db sample to 1998-04-03-14.21.56 and stop 
   tablespace(TBS1)

This fails, because TBS1 is not ready for rollforward recovery on database partition 1; all pieces must be rolled forward together. With table space rollforward to a point in time, the database partition clause is not accepted. The rollforward operation must take place on all the database partitions on which the table space resides.

After restoring TBS1 on database partition 1:

db2 rollforward db sample to 1998-04-03-14.21.56 and stop 
   tablespace(TBS1)

This completes successfully.

Example 7 (partitioned database environment)

After restoring a table space on all database partitions, roll forward to point in time 2, but do not specify AND STOP. The rollforward operation is still in progress. Cancel and roll forward to point in time 1:

db2 rollforward db sample to pit2 tablespace(TBS1) 
db2 rollforward db sample cancel tablespace(TBS1)

** restore TBS1 on all database partitions **

db2 rollforward db sample to pit1 tablespace(TBS1)
db2 rollforward db sample stop tablespace(TBS1)

Example 8 (partitioned database environments)

Rollforward recover a table space that resides on eight database partitions (3 to 10) listed in the db2nodes.cfg file:

db2 rollforward database dwtest to end of logs tablespace (tssprodt)

This operation to the end of logs (not point in time) completes successfully. The database partitions on which the table space resides do not have to be specified. The utility defaults to the db2nodes.cfg file.

Example 9 (partitioned database environment)

Rollforward recover six small table spaces that reside on a single-partition database partition group (on database partition 6):

db2 rollforward database dwtest to end of logs on dbpartitionnum (6)
   tablespace(tsstore, tssbuyer, tsstime, tsswhse, tsslscat, tssvendor)

This operation to the end of logs (not point in time) completes successfully.

Example 10 (partitioned database environment)

You can use the TO END OF BACKUP clause with the ROLLFORWARD command to roll forward all partitions in a partitioned database to the minimum recovery time. The minimum recovery time is the earliest point in time during a rollforward when a database is consistent (when the objects listed in the database catalogs match the objects that physically exist on disk). Manually determining the correct point in time to which to roll forward a database is difficult, particularly for a partitioned database. The END OF BACKUP option makes it easy.

db2 rollforward db sample to end of backup and complete

Usage notes

If restoring from an image that was created during an online backup operation, the specified point in time for the rollforward operation must be later than the time at which the online backup operation completed. If the rollforward operation is stopped before it passes this point, the database is left in rollforward pending state. If a table space is in the process of being rolled forward, it is left in rollforward in progress state.

If one or more table spaces is being rolled forward to a point in time, the rollforward operation must continue at least to the minimum recovery time, which is the last update to the system catalogs for this table space or its tables. The minimum recovery time (in Coordinated Universal Time, or UTC) for a table space can be retrieved using the LIST TABLESPACES SHOW DETAIL command.

Rolling databases forward might require a load recovery using tape devices. If prompted for another tape, you can respond with one of the following:

c: Continue. Continue using the device that generated the warning message (for example, when a new tape has been mounted)
d: Device terminate. Stop using the device that generated the warning message (for example, when there are no more tapes)
t: Terminate. Take all affected table spaces offline, but continue rollforward processing.

If the rollforward utility cannot find the next log that it needs, the log name is returned in the SQLCA, and rollforward recovery stops. If no more logs are available, use the STOP option to terminate rollforward recovery. Incomplete transactions are rolled back to ensure that the database or table space is left in a consistent state.

If database rollforward detects a table space schema transport log record, the corresponding transported table space will be taken offline and moved into drop pending state. This is because database does not have complete logs of transported table spaces to rebuild transported table spaces and their contents. You can take a full backup of the target database after transport completes, so subsequent rollforward does not pass the point of schema transport in the log stream.

Note: Rolling forward through a redistribute operation cannot restore the database content since log records are not recorded for data redistribution. See the "REDISTRIBUTE DATABASE PARTITION GROUP command".

Compatibilities

For compatibility with versions earlier than Version 8:

The keyword NODE can be substituted for DBPARTITIONNUM.
The keyword NODES can be substituted for DBPARTITIONNUMS.
Point in time rollforward is not supported with pre-V9.1 clients due to V9.1 support for partitioned tables.