Command Reference

ROLLFORWARD DATABASE

Recovers a database by applying transactions recorded in the database log files. Invoked after a database or a table space backup 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, either logretain, userexit, or both of these database configuration parameters must be set on) before the database can be recovered with roll-forward recovery.

Scope

In a multi-node environment, this command can only be issued from the catalog node. A database or table space rollforward command specifying a point-in-time affects all nodes that are listed in the db2nodes.cfg file. A database or table space rollforward command specifying end of logs affects the nodes that are specified. If no nodes are specified, it affects all nodes that are listed in the db2nodes.cfg file; if no roll forward is needed on a particular node, that node is ignored.

Authorization

One of the following:

sysadm
sysctrl
sysmaint

Required Connection

None. This command establishes a database connection.

Command Syntax

>>-ROLLFORWARD----+-DATABASE-+---database-alias----------------->
                  '-DB-------'
 
>-----+---------------------------------------+----------------->
      '-USER--username--+------------------+--'
                        '-USING--password--'
 
>-----+----------------------------------------------------------------+>
      +-TO--+-isotime--+--------------+-----------+---+--------------+-+
      |     |          '-ON ALL NODES-'           |   +-AND COMPLETE-+ |
      |     '-END OF LOGS--+--------------------+-'   '-AND STOP-----' |
      |                    '-| On Node clause |-'                      |
      '--+-COMPLETE------+---+--------------------+--------------------'
         +-STOP----------+   '-| On Node clause |-'
         +-CANCEL--------+
         '-QUERY STATUS--'
 
>----+---------------------------------------------------------------+>
     '-TABLESPACE----+-ONLINE-------------------------------------+--'
                     |    .-,------------------.                  |
                     |    V                    |                  |
                     '-(-----tablespace-name---+---)--+---------+-'
                                                      '-ONLINE--'
 
>-----+---------------------------------------------------------------------------+>
      '-OVERFLOW LOG PATH--(--log-directory--+-----------------------------+---)--'
                                             '-,--| Log Overflow clause |--'
 
>-----+-------------------------------------------------------------+>
      '-RECOVER DROPPED TABLE--drop-table-id--TO--export-directory--'
 
>--------------------------------------------------------------><
 
On Node clause
 
|---ON--+-| Node List clause |-------------------------+--------|
        '-ALL NODES--+-------------------------------+-'
                     '-EXCEPT--| Node List clause |--'
 
Node List clause
 
                     .-,-------------------------------------.
                     V                                       |
|---+-NODE---+--(-------node-number1--+-------------------+--+-->
    '-NODES--'                        '-TO--node-number2--'
 
>----)----------------------------------------------------------|
 
Log Overflow clause
 
    .-,---------------------------------------.
    V                                         |
|------log-directory--ON NODE--node-number1---+-----------------|

Command Parameters

DATABASE database-alias

The alias of the database to roll forward.

USER username

Identifies the user name under which the database is to be rolled forward.

USING password

The password used to authenticate the user name. If the password is omitted, the user is 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).

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.nnnnnn (year, month, day, hour, minutes, seconds, microseconds), expressed in Coordinated Universal Time (CUT).

The environment variable TZ indicates the difference between CUT and local time. For example, a TZ value of EST5EDT indicates that the local time zone is EST; that there is a 5-hour difference between this time zone and CUT; and that daylight savings time is observed. This observance reduces the difference from 5 to 4 hours when daylight savings time is in effect, and CUT = current time + 4.

In a partitioned database environment, if ON ALL NODES is specified, roll forward recovery is performed on all nodes.

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.

ALL NODES

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

EXCEPT

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

ON NODE / ON NODES

Roll forward the database on a set of nodes.

node-number1

Specifies a node number in the node list.

node-number2

Specifies the second node number, so that all nodes from node-number1 up to and including node-number2 are included in the node list.

COMPLETE / STOP

Stops the rolling forward of log records, and completes the roll-forward recovery process by rolling back any incomplete transactions and turning off the roll-forward 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.

Note:

When rolling table spaces forward to a point-in-time, the table spaces are placed in backup pending state.

CANCEL

Cancels the roll-forward recovery process. This leaves the database or table space(s) on all nodes on which forward recovery has been started in the restore-pending state. If the database roll-forward is not in progress (that is, the database is in rollforward-pending state), this option will change the database to restore-pending state. If a table space roll-forward 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 will be changed to restore-pending state. If a table space roll-forward is in progress (that is, at least one table space is in rollforward-in-progress state), all table spaces in rollforward-in-progress state will be changed to restore-pending state. If a table space list is specified, it must include all table spaces in rollforward-in-progress state. If rolling forward to a point in time, any table space passed in will be ignored, and all table spaces that are rollforward-in-progress state will be put in restore pending state. If rolling forward to the end of logs with a table space list, only those table spaces will be put in restore-pending state.

Note:

Use this option with caution.

QUERY STATUS

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

Node number
Rollforward status: Status may be database or table space rollforward pending, database or table space rollforward in progress, database or table space rollforward processing STOP, or no rollforward pending.
Next log file to be read: A string containing the name of the next required log file. In a multi-node environment, use this information if the rollforward utility fails with a return code indicating that a log file is missing or a log information mismatch has occurred.
Log files processed: A string containing the names of the processed log files that are no longer needed for recovery, and that can be removed from the directory.
Last committed transaction: A string containing a time stamp in ISO format (yyyy-mm-dd-hh.mm.ss). This time stamp marks the last transaction committed after the completion of roll-forward recovery. The time stamp applies to the database. For table space roll-forward, it is the time stamp of the last transaction committed to the database.

Note: QUERY STATUS is the default if the TO, STOP, COMPLETE, and CANCEL clauses are omitted.
If TO, STOP, or COMPLETE are specified, this information will be displayed if the command ran successfully.

TABLESPACE

This keyword is specified for table space level roll-forward.

tablespace-name

Mandatory for table space level roll-forward to a point in time. Also allows a subset of table spaces to be specified for a roll-forward to the end of logs. In a multi-node environment, each table space in the list does not have to exist at each node that is rolling forward. If it does exist at the node, it must be in the correct state.

ONLINE

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

OVERFLOW LOG PATH log-directory

Specifies an alternate log path to be searched for archived logs during recovery. In a multi-node environment, this is the default overflow log path for all nodes.

In a single-node environment, a relative overflow log path can be specified, but in a multi-node environment, the path must be fully qualified.

log-directory ON NODE

In a multi-node environment, allows a different log path to override the default overflow log path for a specific node.

RECOVER DROPPED TABLE drop-table-id

Recovers a dropped table during the rollforward operation. The table ID can be obtained using LIST HISTORY.

TO export-directory

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

Examples

Example 1

The ROLLFORWARD 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

Example 2

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

   db2 rollforward db sample to end of logs

Note:

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

Example 3

After three table spaces have been restored, roll forward one to the end of 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.245378 and stop
      tablespace(TBS2, TBS3) online

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.245378 and stop
      overflow log path (/logs)

Example 5 (MPP)

There are three nodes: 0, 1, and 2. Table space TBS1 is defined on all nodes, and table space TBS2 is defined on nodes 0 and 2. After restoring the database on node 1, and TBS1 on nodes 0 and 2, roll forward the database on node 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 node(s) 0 and 2.").

   db2 rollforward db sample to end of logs

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

Example 6 (MPP)

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

   db2 rollforward db sample to end of logs

Node 1 is ignored.

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

This fails because TBS1 is not ready for roll forward on node 1. Reports SQL4906N.

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

This completes successfully.

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

This fails because TBS1 is not ready for roll forward on node 1; all pieces must be rolled forward together.
Note: With table space roll forward to a point in time, the node clause is not accepted.

After restoring TBS1 on node 1:

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

This completes successfully.

Example 7 (MPP)

After restoring a table space on all nodes, roll forward to PIT2, but do not specify AND STOP. The ROLLFORWARD command is still in progress. Cancel and roll forward to PIT1:

   db2 rollforward db sample to pit2 tablespace(TBS1) 
   db2 rollforward db sample cancel tablespace(TBS1)
 
 ** restore TBS1 on all nodes **
 
   db2 rollforward db sample to pit1 tablespace(TBS1)
   db2 rollforward db sample stop tablespace(TBS1)

Example 8 (MPP)

Roll forward a table space that resides on eight nodes (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 nodes on which the table space resides do not have to be specified. The utility defaults to the db2nodes.cfg file.

Example 9 (MPP)

Roll forward six small table spaces that reside on a single node nodegroup (on node 6):

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

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

Usage Notes

The database manager uses the information stored in the archived and the active log files to reconstruct the transactions performed on the database since its last backup.

If the database is in roll-forward pending state when ROLLFORWARD DATABASE is invoked, the database will be rolled forward. Table spaces are returned to normal state after a successful database roll-forward, unless an abnormal state causes one or more table spaces to go offline.

If the database is not in roll-forward pending state and no point in time is specified, any table spaces that are in rollforward-in-progress state will be rolled forward to the end of logs. If no table spaces are in rollforward-in-progress state, any table spaces that are in rollforward pending state will be rolled forward to the end of logs.

The roll-forward operation can be performed on a subset of table spaces by specifying table space names.

If rolling forward table spaces to a point in time, a subset of table spaces must be specified. Only those table spaces specified will be rolled forward. Each table space must be in roll-forward pending state or, if continuing a table space roll-forward that is already in progress, in rollforward-in-progress state.

If enabling an existing database for roll-forward recovery, change the number of primary log files to the sum of the number of primary log files and secondary log files +1. More information will be logged for LONG VARCHAR fields and LOB data in a database enabled for roll-forward recovery.

Rolling databases forward may require a load recovery using tape devices. If prompted for another tape, the user 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. Terminate all devices.

Rolling databases forward may involve prerequisites and restrictions that are beyond the scope of this manual. For more detailed information, see the Administration Guide.

See Also

LIST HISTORY

LOAD

RESTORE DATABASE.

[ Top of Page | Previous Page | Next Page ]