ROLLFORWARD DATABASE command

Scope

In a partitioned database environment, this command can be invoked only from the catalog partition but each partition participates in the rollforward operation. 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.

In a DB2® pureScale® environment, this command can be issued from any member, and online table space-level rollforward operation can be performed while other members are online. Unlike in partitioned database environments in which users can choose to rollforward through a subset of the database partitions, in a DB2 pureScale environment the logs from all members are automatically applied. If a rollforward operation is in progress on a member when it fails, the ROLLFORWARD command can be reissued from any member. The rollforward resumes from where it was left off when the original member failed.

For partitioned tables, you are also required to roll forward related table spaces to the same point in time. This requirement applies to table spaces that contain 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 release version. This restriction is an important consideration when upgrading to a new DB2 database release version.

Authorization

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 saving 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:

1. 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.
2. 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.
3. 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.
4. 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.
5. 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.
6. 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.
7. 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, or all members in the DB2 pureScale environment, should be rolled forward to the minimum recovery time. See here 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 processed, 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 and a DB2 pureScale environment, this status information is returned for each database partition or member. The information returned contains the following fields:

Member number

The member number or database partition number. In a single-partition environment, this is always 0.

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. In a DB2 pureScale environment, this string lists all log files that have been processed by the ROLLFORWARD command on all DB2 members. 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. To support concurrency with other applications in a DB2 pureScale environment, the table space-level rollforward operation might need to test for certain internal locks. A rollforward operation in this environment might fail with lock timeout even if locktimeout is set to -1.

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

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 are 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. In a DB2 pureScale environment, the LIST TABLESPACES command is deprecated; use the following monitoring UDF: SELECT * FROM TABLE( SYSPROC.MON_GET_TABLESPACE('TBSPACE_1,0) )

In a DB2 pureScale environment, ensure that there is adequate free disk space in the retrieval path before starting a rollforward operation. This allows the operation to retrieve the larger number of files from the archive, as required in a DB2 pureScale environment, without affecting performance. Use the following formula to calculate how much space you need to retrieve the value of the active log space for all members: (logprimary + logsecond) * number of members.

If you want to roll forward a table space that contains a system-period temporal table or bitemporal table to a point in time, the ROLLFORWARD DATABASE command must include the name of the table space that contains the associated history table. You can roll forward the table space for the temporal table or the table space for the history table individually, but only to the end of logs.

If the ROLLFORWARD DATABASE command 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 parameter to terminate rollforward recovery. Incomplete transactions are rolled back to ensure that the database or table space is left in a consistent state.

If a database rollforward detects a log record for a table space schema transport, the corresponding transported table space is taken offline and moved into drop pending state. This occurs because of the absence of the 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 the transport is complete so that a subsequent rollforward operation does not pass the point of schema transport in the log stream.

If you extracted log files from a database backup, and you plan to run the ROLLFORWARD command with the TO END OF BACKUP clause for that database, use the NORETRIEVE option. The NORETRIEVE option is needed because all the required log files are available and no files need to be retrieved from the archive location. Without using the NORETRIEVE option, some archived log files can be retrieved which are not needed for this rollforward. If the archive locations contain log files from other log chains, the ROLLFORWARD results in SQLCODE -1265.