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.
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.
None. This command establishes an exclusive database connection.
>>-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-+---|
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.
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.
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 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.
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.
Example 1
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
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
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
db2 "rollforward db sample to 1998-04-03-14.21.56 and stop
overflow log path (/logs)"
Example 5 (partitioned database environments)
db2 rollforward db sample to end of logs and stop
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)
db2 rollforward db sample to end of logs
db2 rollforward db sample to end of logs tablespace(TBS1)
db2 rollforward db sample to end of logs on dbpartitionnums (0, 2)
tablespace(TBS1)
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.
db2 rollforward db sample to 1998-04-03-14.21.56 and stop
tablespace(TBS1)
This completes successfully.
Example 7 (partitioned database environment)
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)
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)
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)
db2 rollforward db sample to end of backup and complete
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.
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.