by Roman B. Melnyk and Paul C. Zikopoulos
The technology preview of the next release of IBM DB2 Universal Database (DB2 UDB) for Linux , UNIX , and Windows is codenamed “Stinger”. As of the publication date of this article, it is available as an open beta for download at: http://www.ibm.com/data/stinger.
This article is another in a series that introduces some of the key features in DB2 UDB “Stinger,” and includes “work-through” examples that you can use with the open beta today. In this article, we will introduce the new RECOVER DATABASE command and significant changes to the history file that, together, make it easier to recover a database from a backup image. The RECOVER DATABASE command combines the functionality of the RESTORE DATABASE and the ROLLFORWARD DATABASE commands, and does not require you to specify which database backup image must be restored, or which log files are needed for point-in-time (PIT) recovery.
Recovering a database to a specific point in time, or restoring the database without rolling transactions forward creates a new log chain and causes transaction log files to be re-used. The current log chain (or sequence) is determined by which particular database backup image has been restored, and the log files that have been processed. Before “Stinger,” things could get complicated when re-used log files were archived, overwriting older archived log files. It would sometimes be difficult to determine when it was safe to delete archived logs, and there was no automated way to ensure that the appropriate chain of log files was made available to the DB2 rollforward utility after a database restore operation.
“Stinger” solves this problem by adding a new entry type to the history file. “Archived log” entries in the history file indicate precisely when log files have been archived, and exactly where they have been archived. With this information, and with the database backup and restore information already stored in the history file, the DB2 database recovery utilities can figure out which database backup image, and exactly which log files are required to restore to any point in time.
The new RECOVER DATABASE command allows you to simply specify a point in time to which to recover the database. You do not need to specify which database backup image is to be restored, and you do not need to know which log files on which log chain are required to reach the specified point in time.
Database Recovery before DB2 UDB “Stinger”
Let’s develop a straightforward recovery scenario to demonstrate database recovery before “Stinger”, and then compare that to simplified database recovery in “Stinger”. We will use the SAMPLE database that comes with DB2 UDB. We will start by configuring the database to be recoverable, and then create a full database backup image:
db2 update db cfg for sample using logretain on userexit yes
db2 backup db sample
The timestamp for this backup image is 20040618185309.
Next, we will insert some new data into the STAFF table, and then we will create another full database backup image:
db2 connect to sample
db2 insert into staff values (11,’Smith’,20,’Sales’,10,70000,15000)
db2 insert into staff values (12,’Cheney’,20,’Sales’,7,65000,14000)
db2 insert into staff values (13,’Stier’,20,’Sales’,9,72000,17000)
db2 connect reset
db2 backup db sample
The timestamp for this second backup image is 20040618185359.
Now we will insert some more data into the STAFF table:
db2 connect to sample
db2 insert into staff values (14,’Wolfrum’,20,’Sales’,6,62000,13000)
db2 insert into staff values (15,’Cerny’,20,’Sales’,8,63000,12000)
db2 insert into staff values (16,’Kit’,20,’Sales’,5,52000,10000)
db2 connect reset
A few minutes later, we will insert a final set of records into the STAFF table:
db2 connect to sample
db2 insert into staff values (17,’Chow’,20,’Sales’,12,80000,18000)
db2 insert into staff values (18,’Datta’,20,’Sales’,9,77000,16000)
db2 insert into staff values (19,’Masci’,20,’Sales’,11,95000,21000)
db2 connect reset
Next, we will restore the most recent backup image and then roll the transactions forward to a point in time that precedes the last three insert operations.
db2 restore db sample taken at 20040618185359
db2 rollforward db sample to 2004-06-18-19.00.000000
using local time and stop
Rollforward Status
Input database alias = sample
Number of nodes have returned status = 1
Node number = 0
Rollforward status = not pending
Next log file to be read =
Log files processed = S0000001.LOG – S0000002.LOG
Last committed transaction = 2004-06-18-18.54.34.000000
Querying the STAFF table confirms that the only new rows remaining are those with ID values ranging from 11 to 16. Rows with ID values ranging from 17 to 19 were inserted after the point in time to which we rolled the logged transactions forward.
Querying the history file (db2 list history backup all for sample) shows that it contains the following entry for the backup image whose timestamp is 20040618185359:
Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
— — —————— —- — ———— ———— ————–
B D 20040618185359001 F D S0000001.LOG S0000001.LOG
—————————————————————————-
Contains 2 tablespace(s):
00001 SYSCATSPACE
00002 USERSPACE1
—————————————————————————-
Comment: DB2 BACKUP SAMPLE OFFLINE
Start Time: 20040618185359
End Time: 20040618185431
—————————————————————————-
00002 Location: D:\WorkDir\SAMPLE.0\DB2\NODE0000\CATN0000\20040618
The history file contains no archived log entries (db2 list history archive log all for sample), because prior to “Stinger,” such entries were limited to those created by the ARCHIVE LOG command, and do not reflect other log archiving events (events that are used by the RECOVER DATABASE command in DB2 UDB “Stinger”).
The New RECOVER DATABASE Command and an Improved History File
Our recovery scenario in “Stinger” is identical to that described in the previous section up until the recovery phase. Now, instead of running a RESTORE DATABASE command (specifying which backup image we want to restore), followed by a ROLLFORWARD DATABASE command, we will use the new RECOVER DATABASE command to effect the recovery in one step.
The following diagram shows the principal clauses of the RECOVER DATABASE command syntax.
Querying the history file (db2 list history archive log all for sample) shows that it contains three archived log entries, including the following entry that precedes the recovery point in time that we will specify on the RECOVER DATABASE command (20040618224000). The archived log entry specifies the exact location of the log file (highlighted in yellow).
Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
— — —————— —- — ———— ———— ————–
X D 20040618223620 P D S0000001.LOG C0000000
—————————————————————————-
—————————————————————————-
Comment:
Start Time: 20040618223620
End Time:
Status: A
—————————————————————————-
EID: 4 Location: /home/melnyk/melnyk/NODE0000/SQL00001/SQLOGDIR/S0000001.LOG
The following command recovers the database to a point in time that precedes the last three insert operations:
db2 recover db sample to 2004-06-18-22.40.000000
Rollforward Status
Input database alias = sample
Number of nodes have returned status = 1
Node number = 0
Rollforward status = not pending
Next log file to be read =
Log files processed = S0000001.LOG – S0000002.LOG
Last committed transaction = 2004-06-18-22.36.20.000000
Querying the STAFF table confirms that in this case as well, the only new rows remaining are those with ID values ranging from 11 to 16. Rows with ID values ranging from 17 to 19 were inserted after the point in time to which we recovered the database.
Wrap-up
The new RECOVER DATABASE command in DB2 UDB “Stinger,” which combines the functionality of the RESTORE DATABASE and the ROLLFORWARD DATABASE commands, does not require that you specify a particular database backup image, or know which log files are needed for point-in-time recovery. The history file now stores the location of archived logs, so that the correct version of a log file can be retrieved automatically during a rollforward operation. These new DB2 UDB “Stinger” features make it easier than ever to recover a database from a backup image.
About the Authors
Roman B. Melnyk, PhD, is with IBM Canada Ltd., specializing in database administration, DB2 utilities, and SQL. Roman has written numerous DB2 books, articles, and other related materials. Roman co-authored DB2 Version 8: The Official Guide, DB2: The Complete Reference, DB2 Fundamentals Certification for Dummies, and DB2 for Dummies. You can reach him at roman_b_melnyk at hotmail.com.
Paul C. Zikopoulos, BA, MBA, is with IBM Canada Ltd. Paul has written numerous magazine articles and books about DB2. Paul has co-authored the books: DB2 Version 8: The Official Guide, DB2: The Complete Reference, DB2 Fundamentals Certification for Dummies, DB2 for Dummies, and A DBA’s Guide to Databases on Linux. Paul is a DB2 Certified Advanced Technical Expert (DRDA and Cluster/EEE) and a DB2 Certified Solutions Expert (Business Intelligence and Database Administration). You can reach him at: paulz_ibm at msn.com.
Disclaimers
The contents of this article represent those features that may or may not be available in the technology preview or beta. IBM reserves the right to include or exclude any functionality mentioned in this article for the “Stinger”, or a subsequent release. As well, any performance claims made in this article are not official communications by IBM; rather the result observed by the authors is un-audited testing. The views expressed in this article are those of the authors and not necessarily those of IBM Canada Ltd. or the IBM Corporation.
