Rollforward sessions - CLP 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 are:

   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 you stop it, so that you do not miss any logs.

If the rollforward command encounters an error, the rollforward operation will not complete. The error will be returned, and you will then be able to fix the error and reissue the command. If, however, you are unable to fix the error, you can force the rollforward to complete by issuing the following:

   db2 rollforward db sample complete

This command brings the database online at the point in the logs before the failure.

Example 2

Roll the database 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

Note that 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

In the following example, there is a database called sample. The database is backed up and the recovery logs are included in the backup image; the database is restored; and the database is rolled forward to the end of backup timestamp.

Back up the database, including the recovery logs in the backup image:

   db2 backup db sample online include logs

Restore the database using that backup image:

   db2 restore db sample

Roll forward the database to the end of backup timestamp:

   db2 rollforward db sample to end of backup

Example 6 (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 offline on database partitions 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 7 (partitioned database environments)

In the following example, there is a partitioned database called sample. All the database partitions are backed up with a single system view backup; the database is restored on all database partitions; and the database is rolled forward to the end of backup timestamp.

Perform a single system view (SSV) backup:

   db2 backup db sample on all nodes online include logs

Restore the database on all database partitions:

   db2_all "db2 restore db sample taken at 1998-04-03-14.21.56"

Roll forward the database to the end of backup timestamp:

   db2 rollforward db sample to end of backup on all nodes

Example 8 (partitioned database environments)

In the following example, there is a partitioned database called sample. All the database partitions are backed up with one command using db2_all; the database is restored on all database partitions; and the database is rolled forward to the end of backup timestamp.

Back up all the database partitions with one command using db2_all:

   db2_all "db2 backup db sample include logs to /shared/dir/"

Restore the database on all database partitions:

   db2_all "db2 restore db sample from /shared/dir/"

Roll forward the database to the end of backup timestamp:

   db2 rollforward db sample to end of backup on all nodes

Example 9 (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.

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 10 (partitioned database environments)

After restoring a table space on all database partitions, roll forward to PIT2, but do not specify AND STOP. The rollforward operation 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 dbpartitionnums **

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

Example 11 (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 12 (partitioned database environments)

Rollforward recover six small table spaces that reside on a single database 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 13 (Partitioned tables - Rollforward to end of log on all data partitions)

A partitioned table is created using table spaces tbsp1, tbsp2, tbsp3 with an index in tbsp0. Later on, a user adds data partitions to the table in tbsp4, and attaches data partitions from the table in tbsp5. All table spaces can be rolled forward to END OF LOGS.

   db2 rollforward db PBARDB to END OF LOGS and stop 
      tablespace(tbsp0, tbsp1, tbsp2, tbsp3, tbsp4, tbsp5)

This completes successfully.

Example 14 (Partitioned tables - Rollforward to end of logs on one table space)

A partitioned table is created initially using table spaces tbsp1, tbsp2, tbsp3 with an index in tbsp0. Later on, a user adds data partitions to the table in tbsp4, and attaches data partitions from the table in tbsp5. Table space tbsp4 becomes corrupt and requires a restore and rollforward to end of logs.

   db2 rollforward db PBARDB to END OF LOGS and stop tablespace(tbsp4)

This completes successfully.

Example 15 (Partitioned tables - Rollforward to PIT of all data partitions including those added, attached, detached or with indexes)

A partitioned table is created using table spaces tbsp1, tbsp2, tbsp3 with an index in tbsp0. Later on, a user adds data partitions to the table in tbsp4, attaches data partitions from the table in tbsp5, and detaches data partitions from tbsp1. The user performs a rollforward to PIT with all the table spaces used by the partitioned table including those table spaces specified in the INDEX IN clause.

   db2 rollforward db PBARDB to 2005-08-05-05.58.53 and stop 
      tablespace(tbsp0, tbsp1, tbsp2, tbsp3, tbsp4, tbsp5)

This completes successfully.

Example 16 (Partitioned tables - Rollforward to PIT on a subset of the table spaces)

A partitioned table is created using three table spaces (tbsp1, tbsp2, tbsp3). Later, the user detaches all data partitions from tbsp3. The rollforward to PIT is only permitted on tbsp1 and tbsp2.

   db2 rollforward db PBARDB to 2005-08-05-06.02.42 and stop 
      tablespace( tbsp1, tbsp2)

This completes successfully.