DB2 Backup and Recovery for LiveCycle ES

Ratings: 0

This article outlines one method for performing a backup and recovery on a IBM DB2 database. This article is not intended as a comprehensive technical guide of specific database backup and recovery features, but is meant to be an outline of basic DB2 commands needed for a database administrator to create a reliable database backup strategy for LiveCycle ES.

Caution: As with any other aspect of your LiveCycle ES implementation, your backup and recovery strategy needs to be developed and tested in a development or staging environment before being used in production in order to ensure that the entire solution is working as expected with no data loss.

DB2 database documentation

The information provided in this article is a summary of the steps required to perform the tasks described. For detailed information, consult the manufacturers documentation provided with your database.

DB2 backup

To maintain synchronization between the database backup and the GDS backup, it is important that you do not deploy any long-lived process during a backup or run DDL activities that would create, alter, or delete tables or objects in the database. DB2 will force you to roll forward to the completion time of these DDL activities, which can be later than the time you exit out of LiveCycle backup mode. There is a very small chance that this can occur, but it is better to avoid the problem.

Configuring the LiveCycle ES database for backup

Prior to backing up the database, you must set specific database parameters such as:

LOGARCHMETH1

: This parameter specifies the media type of the primary destination for archived logs.

LOGARCHMETH2

: This parameter specifies the media type of the secondary destination for archived logs. If this path is specified, log files will be archived to both this destination and the destination specified by the logarchmeth1 database configuration parameter.

LOGRETAIN

: (Deprecated) This parameter needs to be set to ON for Archival Logging strategy.

LOGPRIMARY

: This parameter indicates the number of primary log files to be allocated. The total log file size default limit on active log space is 256 GB.

LOGSECOND

: This parameter indicates the maximum number of secondary log files that can be allocated.

LOGFILSIZ

: This parameter is used to specify the size of a log file (in number of 4 KB pages). The size of the log file has an impact on performance because there is a cost for switching from one log to another. This value also indicates the log file size for archiving. Even though the larger log file size improves the performance of the database, it also increases the chance of failure. When considering the active log space, it may be better to have a larger number of smaller log files.

TRACKMOD

: This parameter enables Incremental and Delta backup. If you dont turn this configuration to on, you will get SQL2426N message error when you try to perform an incremental backup.

To update the number of primary log files, run:

update database configuration for <database name> using LOGPRIMARY <num of primary log files>
To update the number of secondary log files, run:

update database configuration for <database name> using LOGSECOND <num of secondary log files>
To change the physical location of the log files, run:

update database configuration for <database name> using NEWLOGPATH <new log path>
To turn on the track modification pages, run:

update database configuration for <database name> using TRACKMOD ON
To enable archival logging mode for LiveCycle DB2 database, run:

update database configuration for EDC using LOGRETAIN RECOVERY

Note: You must take a full offline database backup right after configuration, for example:

db2 => backup db adobe online to c:/lcbackup/db2 
 SQL2413N Online backup is not allowed because the database is not recoverable or a backup pending condition is in effect. 
 db2 => backup db adobe to c:/lcbackup/db2 
 Backup successful. The timestamp for this backup image is: 20080102171058 
 db2 =>

To query your current settings, run: get database configuration for <database name>

For detailed discussion of DB2 logging, see the article at the following location:

www.ibm.com/developerworks/db2/library/techarticle/0301kline/0301kline.html

DB2 archive logs

With

logarchmeth1

configured, the archive log files are located at the path specified by the logarchmeth1 parameter.

With

logretain

configured, and if you are not configuring your system to use UserExit to archive logs into a different directory, archive log files stay in the same directory where active log files are located.

To find the location of the logs, run:

db2 => get database configuration for edc

Examine the Path to log files and the First active log file results of this command.

Infinite Active Logging and UserExit (deprecated)

If you chose to move log files not containing any non-committed or non-externalized transactions to a different storage location, DB2 offers a user exit, which is called by DB2 if a log file reaches this state.

The user-supplied

userexit

program will be invoked each time a log file is closed and this program can move unneeded log files to another location for permanent storage.

To prepare the userexit:

Copy db2uext2.cdisk from C:/Program Files/IBM/SQLLIB/samples/c to a working directory and rename the file to db2uext2.c.
Verify and modify the Installation Defined Variables to match with your environment. The default will backup the log files to C:/mylogs/.
Compile userexit using: cl db2uext2.c. You need to have a C++ compiler, such as Microsoft Visual C++, installed.
Copy the created EXE file to the c:/Program Files/IBM/SQLLIB/bin directory on Windows. On Unix, this program should be copied to the sqllib/adm directory.

Backing up the LiveCycle ES database

A backup image of a DB2 UDB database (or of a tablespace within a DB2 UDB database) can be created by executing the BACKUP command. The basic syntax for this command is:

BACKUP [DATABASE | DB] [DatabaseName],br /> <USER [UserName] <USING [Password]>> 
 <TABLESPACE ( [TS_Name],...) 
 <ONLINE> 
 <INCREMENTAL <DELTA>> 
 <TO [Location]> 
 <WITH [NumBuffers] BUFFERS> 
 <BUFFER [BufferSize]> 
 <PARALLELISM [ParallelNum]> 
 <WITHOUT PROMPTING>

where:

DatabaseName- Identifies the name assigned to the database that a backup image is to be created for.
UserName - the name assigned to a specific user whose authority the backup operation is to be performed under.
Password - the password that corresponds to the name of the user that the backup operation is to be performed under.
TS_Name- the name assigned to one or more specific tablespaces that are to be backed up.
Location- the directory or device where the backup image created is to be stored. (If no location is specified, the current location is used as the default.)
NumBuffers- the number of buffers that are to be used to perform the backup operation. (By default, two buffers are used if this option is not specified.)
BufferSize- the size, in pages, that each buffer used to perform the backup operation will be. (By default, the size of each buffer used by the BACKUP utility is determined by the value of the backbufsz DB2 Database Manager configuration parameter.)
ParallelNum- the number of tablespaces that can be read in parallel during a backup operation.

If the INCREMENTAL option is specified, an incremental backup image will be produced. An incremental backup image is a copy of all data that has changed since the last successful, full backup image was produced. Likewise, if the DELTA option is specified, a delta backup image will be produced. A delta backup image is a copy of all data that has changed since the last successful backup image of any type (full, incremental, or delta) was produced. DB2 will force you to make a full offline backup after enabling LOGRETAIN and/or USEREXIT to avoid error on the subsequent connection to the database. The following example shows how to create a full backup image of a database named EDC and store the image created in a directory named LCBACKUP/DB2 on logical disk drive C:. The following command connects to the EDC database as db2admin and pas$w0rd

db2 => connect to edc user db2admin using pas$w0rd 
 Database Connection Information 
 Database server = DB2/NT 8.2.0 
 SQL authorization ID = DB2ADMIN 
 Local database alias = EDC

This command forces all the active applications to disconnect from the database. You may need repeat this command or stop and restart the database to force all users off the system.

db2 => force application all 
  DB20000I The FORCE APPLICATION command completed successfully. 
 DB21024I This command is asynchronous and may not be effective immediately.

This command starts the offline backup of the database. The target directory must already exist.

db2 => backup db edc to c:/lcbackup/db2 
  Backup successful. The timestamp for this backup image is : 20080110153958

Once a full backup of the database is performed, the target database can be backed up online (full or incremental). The following snippets show how an online backup is obtained. The first command lists the active applications connecting to the database. The second command performs a hot backup of the EDC database, including the logs ready to be archived, to c:/lcbackup/db2 directory. The third command performs an incremental hot backup of the EDC database. Note that we don't provide user name and password because we already connect to the EDC database previously. Furthermore, unless you are utilizing user exit feature, you should always perform your backup with the include logs option. This allows you to recover your database in the case your disk media is corrupted.

List active applications 
 db2 => list applications

Auth Id Application Appl. Application Id DB # of 
 Name Handle Name Agents 
 -------- -------------- ------ -------------------------- -------- ----- 
 DB2ADMIN db2jccIDPSched 35 NF000001.HD0B.080110235001 EDC 1 
 DB2ADMIN db2jccSimpleTh 34 NF000001.HC0B.080110235000 EDC 1 
 DB2ADMIN db2jccSimpleTh 20 NF000001.HA0A.080110234200 EDC 1 
 DB2ADMIN db2jccIDPSched 19 NF000001.G40A.080110234128 EDC 1

Hot backup edc database

db2 => backup db edc online to c:/lcbackup/db2 include logs

Backup successful. The timestamp for this backup image is : 20080110155632

Hot incremental backup edc database

db2 => backup db edc online incremental to c:/lcbackup/db2 include logs

Backup successful. The timestamp for this backup image is : 20080110155709

The first command lists the active applications connecting to the database. The second command performs a hot backup of the EDC database, including the logs ready to be archived, to c:/lcbackup/db2 directory. The third command performs an incremental hot backup of the EDC database. Note that we don't provide user name and password because we already connect to the EDC database previously. Furthermore, unless you are utilizing user exit feature, you should always perform your backup with the include logs option. This allows you to recover your database in the case your disk media is corrupted.

Backup listing

You can view the contents of a database's recovery history file by executing the LIST HISTORY command from the DB2 Command Line Processor (CLP). The basic syntax for this command is:

LIST HISTORY 
 <BACKUP| ROLLFORWARD| DROPPED TABLE | LOAD | 
 CREATE TABLESPACE | ALTER TABLESPACE | 
 RENAME TABLESPACE | REORG> 
 [ALL | SINCE [Timestamp] | 
 CONTAINING <SchemaName.>ObjectName] 
 FOR [DATABASE | DB] [DatabaseName]

where:

Timestamp - Identifies a timestamp that is to be used as search criteria when retrieving entries from the recovery history file; only entries with timestamps that are greater than or equal to the timestamp provided are retrieved and displayed.
SchemaName- Identifies the name assigned to the schema that is to be used as search criteria when retrieving entries from the recovery history file; only entries that are associated with the schema name specified are retrieved and displayed.
ObjectName - Identifies the name assigned to an object that is to be used as search criteria when retrieving entries from the recovery history file; only entries that are associated with the object specified are retrieved and displayed.
DatabaseName- Identifies the name assigned to the database that recovery history file information is to be retrieved and displayed for.

Listing of a database edc would look something like this:

db2 => list history backup all for database edc

List History File for edc

Number of matching file entries = 2 
 Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID 
 -- --- ------------------ ---- --- ------------ ------------ -------------- 
 B D 20080110155632001 N D S0000019.LOG S0000019.LOG 
 ---------------------------------------------------------------------------- 
 Contains 4 tablespace(s):

00001 SYSCATSPACE 
 00002 USERSPACE1 
 00003 EDC_DATA_8K 
 00004 SYSTOOLSPACE 
 ---------------------------------------------------------------------------- 
 Comment: DB2 BACKUP EDC ONLINE 
 Start Time: 20080110155632 
 End Time: 20080110155705 
 Status: A 
 ---------------------------------------------------------------------------- 
 EID: 25 Location: c:/lcbackup/db2/EDC.0/DB2/NODE0000/CATN0000/20080110

Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID 
 -- --- ------------------ ---- --- ------------ ------------ -------------- 
 B D 20080110155709001 O D S0000019.LOG S0000020.LOG 
 ---------------------------------------------------------------------------- 
 Contains 4 tablespace(s):

00001 SYSCATSPACE 
 00002 USERSPACE1 
 00003 EDC_DATA_8K 
 00004 SYSTOOLSPACE 
 ---------------------------------------------------------------------------- 
 Comment: DB2 BACKUP EDC ONLINE 
 Start Time: 20080110155709 
 End Time: 20080110155730 
 Status: A 
 ---------------------------------------------------------------------------- 
 EID: 26 Location: c:/lcbackup/db2/EDC.0/DB2/NODE0000/CATN0000/20080110

Note: If you want to redirect your backup history listing into a text file, you can issue this command from the DOS command line as shown below:

D:/Program Files/IBM/SQLLIB/BIN>db2 list history backup all for database edc > history.txt

Pruning backup

You can delete a recovery history file entry by executing the PRUNE HISTORY command. The basic syntax for this command is:

PRUNE HISTORY [Timestamp] 
 <WITH FORCE OPTION>

where,

Timestamp - Identifies a timestamp that is to be used as search criterion when removing entries from the recovery history file; only entries with timestamps that are less than or equal to the timestamp provided are deleted, provided they are not part of the most recent restore set.

If the WITH FORCE OPTION option is specified, entries with timestamps that are less than or equal to the timestamp specified are deleted regardless of whether or not they are part of the most recent restore set.

This is an example of the pruning command:

db2 => prune history 20080111DB20000I The PRUNE command completed successfully.

Restoring a DB2 database

The most common way to restore DB2 database is by executing the RESTORE command. The basic syntax for this command is:

RESTORE [DATABASE | DB] [DatabaseName]

<USER [UserName] <USING [Password]>> 
 <TABLESPACE <ONLINE> | 
 TABLESPACE ( [TS_Name] ,... ) <ONLINE> | 
 HISTORY FILE <ONLINE>> 
 <INCREMENTAL <AUTO | AUTOMATIC | ABORT>> 
 <FROM [SourceLocation]> 
 <TAKEN AT [Timestamp]> 
 <TO [TargetLocation]> 
 <INTO [TargetAlias]> <NEWLOGPATH [LogsLocation]> 
 <WITH [NumBuffers] BUFFERS> 
 <BUFFER [BufferSize]> 
 <REPLACE EXISTING> 
 <REDIRECT> 
 <PARALLELISM [ParallelNum]> 
 <WITHOUT ROLLING FORWARD>

<WITHOUT PROMPTING>

where:

DatabaseName- Identifies the name assigned to the database that is associated with the backup image that is to be used to perform a recovery operation.
UserName- Identifies the name assigned to a specific user that the recovery operation is to be performed under.
Password - Identifies the password that corresponds to the name of the user that the recovery operation is to be performed under.
TS_Name - Identifies the name assigned to one or more specific tablespaces that are to be restored from a backup image.
ONLINE - is keyword, applicable only when performing a table space-level restore operation, is specified to allow a backup image to be restored online. This means that other agents can connect to the database while the backup image is being restored, and that the data in other table spaces will be available while the specified table spaces are being restored.
HISTORY FILE- this keyword is specified to restore only the history file from the backup image.
INCREMENTAL- Without additional parameters, INCREMENTAL specifies a manual cumulative restore operation. During manual restore the user must issue each restore command manually for each image involved in the restore. Do so according to the following order: last, first, second, third and so on up to and including the last image.
INCREMENTAL AUTO - Specifies an automatic cumulative restore operation.
INCREMENTAL ABORT - Specifies abortion of an in-progress manual cumulative restore operation.
SourceLocation - Identifies the directory or device where the backup image to be used is stored.
Timestamp - Identifies a timestamp that is to be used as search criterion when looking for a particular backup image to use for recovery. The time stamp is displayed after successful completion of a backup operation, and is part of the path name for the backup image. It is specified in the form yyyymmddhhmmss. A partial time stamp can also be specified. For example, if two different backup images with time stamps 19971001010101 and 19971002010101 exist, specifying 19971002 causes the image with time stamp 19971002010101 to be used. If a value for this parameter is not specified, there must be only one backup image on the source media.
TargetLocation - Identifies the directory where the database that will be created is to be stored, if the backup image is to be used to create a new database.
TargetAlias - Identifies the alias to be assigned to the new database to be created.
LogsLocation - Identifies the directory or device where log files for the new database are to be stored.
NumBuffers - Identifies the number of buffers that are to be used to perform the recovery operation. (By default, two buffers are used if this option is not specified.)
BufferSize - Identifies the size, in pages, that each buffer used to perform the backup operation will be. (By default, the size of each buffer used by the RESTORE utility is determined by the value of the restbufsz DB2 Database Manager configuration parameter.)
ParallelNum - Identifies the number of tablespaces that can be read in parallel during a backup operation.

When restoring to an existing database, you should not be connected to the database that is to be restored: the restore utility automatically establishes a connection to the specified database, and this connection is terminated at the completion of the restore operation. When restoring to a new database, an instance attachment is required to create the database. When restoring to a new remote database, you must first attach to the instance where the new database will reside. Then, create the new database, specifying the code page and the territory of the server.

You must have SYSADM, SYSCTRL, or SYSMAINT authority to restore to an existing database from a full database backup. To restore to a new database, you must have SYSADM or SYSCTRL authority.

When a backup image is used to restore a damaged or corrupted database, the database can only be returned to the state it was in at the time the backup image was made. All changes that were made to the database after the backup image was created will be lost when a recovery operation is performed. To return a database to the state it was in at any given point in time, roll-forward recovery must be used instead.

When a recoverable database is restored from a backup image, it is automatically placed in "Roll-forward pending" state unless the WITHOUT ROLLING FORWARD option is used with the RESTORE command ; while a database is in "Roll-forward pending state, it cannot be accessed by users and applications. At that point, the database can either be taken out of "Roll-forward pending" state (in which case all changes made to the database since the backup image used for recovery was made will be lost), or information stored in the database's transaction log files can be replayed to return the database to the state it was in at any given point in time. The rollforward command syntax is:

ROLLFORWARD [DATABASE | DB] [DatabaseName] 
 < USER [UserName] < USING [Password] > > 
 < TO [PointInTime] < AND COMPLETE | STOP > | 
 TO END OF LOGS < AND COMPLETE | STOP > | 
 COMPLETE | 
 STOP | 
 CANCEL | 
 QUERY STATUS < USING LOCAL TIME > > 
 < TABLESPACE ONLINE | 
 TABLESPACE < ( [TS_Name] ,... ) > < ONLINE > > 
 < OVERFLOW LOG PATH ( [LogDirectory],...) > 
 < RECOVER DROPPED TABLE [TableID] TO [Location] >

where:

DatabaseName - Identifies the name assigned to the database to be rolled forward.
UserName- Identifies the name assigned to the user under whose authority the roll-forward recovery operation will be performed.

Password - Identifies the password that corresponds to the name of the authorizing user.
PointInTime - Identifies a specific point in time, identified by a timestamp value in the form yyyy-mm-dd-hh.mm.ss.nnnnnn (year, month, day, hour, minutes, seconds, microseconds) to roll the database forward to. (Only transactions that took place before and up to the time specified will be reapplied to the database.)
TS_Name - Identifies the name assigned to one or more tablespaces to be rolled forward. (Use the db2logsforrfwd command to find out which log files are needed for a tablespace roll-forward recovery operation.)
LogDirectory - Identifies the directory that contains the offline archived log files used to perform the roll-forward recovery operation.
TableID - Identifies a specific table (by ID) that is to be restored as part of the roll-forward operation.
Location - Identifies the directory where files containing data stored in the tables that were dropped will be written to when the table is restored.

Note: Parameters shown in angle brackets (< >) are optional; parameters or options shown in normal brackets ([ ]) are required and must be provided; a comma followed by ellipses (...) indicates that the preceding parameter can be repeated multiple times. To view the complete syntax for the ROLLFORWARD command, see IBM DB2 Universal Database, Version 8 Command Reference. If you want to perform a roll-forward recovery operation on a database named EDC by reapplying all transactions that were committed at or before 01/01/2008, you would execute a ROLLFORWARD command that looks like this:

ROLLFORWARD DATABASE EDC TO 2008-01-01-00.00.00.0000 AND STOP

It's important to note that the time value specified is interpreted as a Coordinated Universal Time (UTC) - otherwise known as Greenwich Mean Time (GMT) - value. If you want to specify the local time, then you need to use the following format:

ROLLFORWARD DATABASE EDC TO 2008-01-01-00.00.00.0000 
 USING LOCAL TIME AND STOP

In general, you rollforward the database after you recover the database to the end backup time, and DB2 will provide the correct time that you need to rollforward to.

If you wanted to restore a database named EDC, (which already exists and uses archive log), using a backup image stored in the LCBackup directory on logical disk drive C:, you could do so as shown below:

db2 => disconnect all 
  DB20000I The SQL DISCONNECT command completed successfully.

db2 => force applications all 
  DB20000I The FORCE APPLICATION command completed successfully. 
 DB21024I This command is asynchronous and may not be effective immediately.

db2 => RESTORE DATABASE EDC FROM C:/LCBACKUP/DB2 TAKEN AT 20080212130327 REPLACE EXISTING WITHOUT PROMPTING 
  SQL2540W Restore is successful, however a warning "2539" was encountered 
 during Database Restore while processing in No Interrupt mode.

db2 => rollforward database edc user db2admin using pas$w0rd to 2008-02-12-13.04.05.00000 and stop 
 SQL1275N The stoptime passed to roll-forward must be greater than or equal to 
 "2008-02-12-21.03.53.000000", because database "EDC" on node(s) "0" contains 
 information later than the specified time.

db2 => rollforward database edc user db2admin using pas$w0rd to end of logs and stop

Rollforward Status

Input database alias = edc 
 Number of nodes have returned status = 1

Node number = 0 
 Rollforward status = not pending 
 Next log file to be read = 
 Log files processed = S0000035.LOG - S0000036.LOG 
 Last committed transaction = 2008-02-12-21.03.53.000000

DB20000I The ROLLFORWARD command completed successfully.

In the example above, we rollforward twice, the first time using the end of the backup time as the point in time of the rolling forward; this time is never correct, but it allows us to get the correct time from DB2.

If you are archiving your logs to a different directory using user exit, you need to use OVERFLOW LOG PATH to specify the directory where the user exit saves archived logs:

db2 => rollforward database edc to end of logs and stop overflow log path (c:/logs)

Rollforward Status

Input database alias = edc 
 Number of nodes have returned status = 1

Node number = 0 
 Rollforward status = not pending 
 Next log file to be read = 
 Log files processed = S0000013.LOG - S0000017.LOG 
 Last committed transaction = 2008-01-30-00.10.00.000000

Incremental recovery

As mentioned in the previous section, you need to set your database TRACKMOD option to YES in order for you to take incremental backup your database. To rebuild the database or the table space to a consistent state, the recovery process must begin with a consistent image of the entire object (database or table space) to be restored, and must then apply each of the appropriate incremental backup images. For an automatic incremental restore, the RESTORE command is issued only once specifying the target image to be used. DB2 then uses the database history to determine the remaining required backup images and restores them. For example:

db2 restore db sample incremental automatic taken at 20080114152133

Refer to the DB2 manual if you need to incremental recover your database manually.

Additional resources

This section provides references to articles relevant to database administration and back up tasks.

Note: The web sites referenced here were valid at the time of publication.

DB2 documentation

There are several good reference guides available from the IBM DB2 Information Center web site. You can search for the following documents or articles from this site:

Backing up DB2 with IBM Tivoli Storage Management (IBM form number SG24-6247-00)
High Availability and Scalability Guide for DB2 on Linux, UNIX, and Windows (IBM form number SG24-7363-01)

In addition, the following article provides valuable information:

DB2 Universal Data Base V8.1 Certification: Backup and Recovery available at www.informit.com

Ntbackup for Windows Server 2003 references

Ntbackup command reference for Windows Server 2003
Utilizing the Built-in Windows Backup (ntbackup.exe) for Windows at www.fishbrains.com

DB2 Backup and Recovery for LiveCycle ES