DB2 rollforward db

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 (UTC). 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 LOCAL TIME Allows the user to rollforward to a point in time that is the user's local time rather than GMT time. This makes it easier for users to rollforward to a specific point in time on their local machines, and eliminates potential user errors due to the translation of local to GMT time.

Notes:

1. If the user specifies a local time for rollforward, all messages returned to the user will also be in local time. Note that all times are converted on the server, and in partitioned database environments, on the catalog database partition.
2. The timestamp string is converted to GMT 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.
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.

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.

 

 

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.

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.245378 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.245378 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 partitions, and table space TBS2 is defined on 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.245378 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.

Note:

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.245378 and stop tablespace(TBS1)

This completes successfully.

Example 7 (partitioned database environment)

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