Who needs DB2's Incremental Delta Backup?

Wednesday Feb 5th 2003 by DatabaseJournal.com Staff
Share:

Learn how to organize and perform a delta incremental database backup in DB2.

Marin Komadina

This article explains how to organize and perform a delta incremental database backup.

Delta backup will be your IT manager's best friend, especially if he is fighting a budget, your DBA will love it because he will learn something new, and storage guys will complain because they will not get paid as before.

Delta backup has been supported since version 7.2 (7.1 Fixpack 3) with a small surprise:

APAR IY22633,INCREMENTAL BACKUP MIGHT BE MISSING PAGES, First Fixed in: DB2 V7 FixPak 4. UDB Version 8 includes several backup and recovery enhancements, but incremental delta backup functionality remains the same.

This article covers:

  • Backup Terminology
  • Online Backup Classification
  • Database parameters
  • Incremental Delta Backup Example
  • Backup Control

Backup Terminology and Parameters

As a brief refresher, lets review some backup terminology.

  • Backup Type
    • Offline database backup - database in deactivated status, log parameters settings for circular logging (LOGRETAIN = OFF and USEREXIT = OFF), only crash recovery or version recovery possible
    • Online database backup - database activated (database files in use), log parameters settings for archive logging (LOGRETAIN=ON and USEREXIT=ON), rollforward recovery enabled
  • Logging
    • Circular logging - reuse of log files in round robin, content not preserved
    • Archive logging - upon commit, log files are closed and become offline archived logs
  • Log file
    • Online log file - log file with active content in defined database log directory
    • Offline log file - filled log file, moved from log file path to archive storage
  • Recovery history file
    Special logging repository for tracking various database activity: backup, restore, rollforward, alter, rename, quiesce tablespace, load, drop, reorganization of table or update of table statistics. Entries from this file are read with the LIST HISTORY command.

Online Backup Classification

  • Full online database backup (full)
    • full copy of all database pages
    • standard metadata set (db configuration, table space definitions, db history)
  • Incremental online database backup (cumulative)
    • updated database pages since last successful full backup
    • standard metadata set (db configuration, table space definitions, db history)
    • full copy of changed LOB and LF data types from previous full, incremental or delta backup

      incremental backups

  • Incremental Delta online database backup (incremental)

    • updated db pages since last successful full, incremental or incremental delta backup
    • standard metadata set (db configuration, table space definitions, db history)
    • full copy of changed LOB and LF data types from previous full, incremental or incremental delta backup

Database Parameters

The main parameters for controlling backup behavior are:

  • Track modified pages
    TRACKMOD=ON - enable Incremental-/Delta-Backup by starting tracking database updates
    TRACKMOD=OFF - disable Incremental-/Delta-Backup
    (database version < V7.2 default: TRACKMOD=NO, database version >= V7.2 default: TRACKMOD=YES)
  • Log retain for recovery LOGRETAIN=ON - enable archive logging
    LOGRETAIN=OFF - enable circular logging
  • User exit for logging
    USEREXIT=ON - The userexit parameter causes the database manager to call a user exit program for archiving and retrieving database logs. This parameter allows roll-forward recovery and automatically enables logretain to be active.

Incremental Delta Backup Example

We can choose different storage mediums for saving a backup image. The most often used solutions are local or remote disk file system, or TSM (Tivoli Storage Manager). TSM is an enterprise-wide storage application for the network. It provides automated storage management services (including backup and restore, archive and retrieve, hierarchical space management and disaster recovery) to servers and workstations connected to the network. TSM has two main components which are important to delta backup: server and application program interfaces (API).

  • The TSM server is a dedicated server machine with a dedicated backup storage pool (tapes or optical drives).
  • TSM API code is a software interface that provides the DB2 backup utility direct backup on the TSM server.
  • Additionally, IBM provides db2uext2 program code for saving archived log files directly to TSM.

The TSM solution is widely used for large databases. I will explain the TSM solution in detail and give an example of hard disk usage.

Before we start the test, let's check what needs to be installed and configured on the system: >

  • Tivoli Storage Manager client API
  • C compiler for compiling user exit program db2uext2.c
  • TSM management classes for full backup, delta backup and DB2 logs
  • (disk space on separate file system in case we make backup on hard disk)

Next we need to configure the user exit program (db2uext2.c) to ensure that archived log files are correctly handled and saved on TSM. Usually you only need to change the log destination before compiling it.

Edit file ~/c/db2uext2.c and create directory structure (/logs/*):

#define ARCHIVE_PATH "/logs/archive"
#define RETRIEVE_PATH "/logs/retrieve"
#define AUDIT_ERROR_PATH "/logs/log"

Compile the source file. Take this warning into account:

IY09505:INCORRECT
COMPILE INSTRUCTIONS IN DB2UEXT2.CADSM FOR ADSM 3.1.6 OR HIGHER.

In the db2uext2.cadsm skip the documentation that tells you to use "cc -o db2uext2 db2uext2.c libApiDs.a" and use the following:

"cc_r -o db2uext2 db2uext2.c libApiDs.a".

This will use the re-entrant (thread safe) compiler.

Copy the final compiled version to destination ~/sqllib/adm/db2uext2. Now we are ready to go to the database.

Our test database ARTIST is running in no logging mode.

db2 => get db cfg for ARTIST

       Database Configuration for Database ARTIST

 Track modified pages                         (TRACKMOD) = OFF
 Log retain for recovery enabled             (LOGRETAIN) = OFF
 User exit for logging enabled                (USEREXIT) = OFF

We are going to change the configuration for Archive Logging and check the backup pending indicator.

db2 =>update db cfg for ARTIST using USEREXIT ON
DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully.

db2 =>update db cfg for ARTIST using LOGRETAIN ON
DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully.

$db2 "get db cfg for ARTIST" | grep -i "BACKUP PENDING"
Log retain for recovery enabled             (LOGRETAIN) = BACKUP PENDING

The backup pending indicator (LOGRETAIN) now has the value "BACKUP PENDING," which is the new recovery point for the database. DB2 requires an offline backup to establish this new recovery point and get the database out of the BACKUP PENDING state. Before making an offline backup we have to close all connections and restart the database.

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

$db2 connect reset
DB20000I  The SQL command completed successfully.

$db2stop 
SQL1064N DB2STOP processing was successful

$db2start
SQL1063N DB2START processing was successful.

TSM: 
$db2 backup db ARTIST to tsm
Backup successful. The timestamp for this backup image is : 20021111141448001

FILE SYSTEM 

$db2 backup db ARTIST to /backup_fs/artist/
Backup successful. The timestamp for this backup image is : 20021111141844001

If the backup ends successfully, then the updated history file will reset the backup pending flag from BACKUP PENDING to RECOVERY.

We've just produced a database image which will be a starting point for the recovery process if we need to rebuild the database to a consistent state.

 Database configuration release level                    = 0x0900
 Database release level                                  = 0x0900
 
 Log retain for recovery enabled             (LOGRETAIN) = RECOVERY
 User exit for logging enabled                (USEREXIT) = ON

Finally we have everything prepared for an online backup.

TSM:
db2 => backup database ARTIST online use tsm
Backup successful. The timestamp for this backup image is: 20021111152503

FILE SYSTEM 

$db2 backup db ARTIST online to /backup_fs/artist/
  Backup successful. The timestamp for this backup image is : 20021111142001005

If we were to try to run the delta backup now, the database log file would display the message: ( Incremental backup not permitted for tablespace 0 (SYSCATSPACE). TRACKMOD not enabled.)

We need to update the TRACKMOD parameter :


db2 => update db cfg for ARTIST using TRACKMOD ON 
DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully.

Restart the database after that change is made and make the offline backup.

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

$db2 connect reset
DB20000I  The SQL command completed successfully.

$db2stop
SQL1064N DB2STOP processing was successful

$db2start
SQL1063N DB2START processing was successful.

TSM:
$db2 backup db ARTIST to tsm 
Backup successful. The timestamp for this backup image is : 20021111161311001

$db2 backup db ARTIST online incremental delta use tsm
Backup successful. The timestamp for this backup image is : 20021111162015003

FILE SYSTEM :

$db2 backup db ARTIST online incremental delta to /backup_fs/artist
Backup successful. The timestamp for this backup image is : 20021111183317001

Backup Control

All important information is stored in one file, called the history file (db2rhist.asc). DB2 handles duplicated versions of the same file (db2rhist.bak) for recovery reasons.

For example, history file contains information of all the backups for database ARTIST:

$ db2 "list history backup all for ARTIST"

Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID
 -- --- ------------------ ---- --- ------------ ------------ --------------
  B  D  20021111162015003      E    A  S0002207.LOG S0002207.LOG  
 ----------------------------------------------------------------------------
  Contains 3 tablespace(s):
 
  00001 SYSCATSPACE                                                           
  00002 ARTIST0001A                                                           
  00003 ARTIST0001B                                                          
----------------------------------------------------------------------------
  Comment: DB2 BACKUP ARTIST ONLINE                                       
 Start Time: 20021111162015003
  End Time: 20021111162519001
 ----------------------------------------------------------------------------
  00040 Location: adsm/libadsm.a

or, using db2ckrst utility time based recovery in formations:

$db2ckrst -d artist -t 20021206010134
Suggested restore order of images using timestamp 20021206010134 for database artist.
====================================================================
 restore db artist incremental taken at 20021206010134
 restore db artist incremental taken at 20021204010129
 restore db artist incremental taken at 20021204172723
 restore db artist incremental taken at 20021205010133
 restore db artist incremental taken at 20021206010134
====================================================================

I recommend that every DBA check and compare the backup size on TSM or file system for Delta and full backup.

Compare on TSM:

  >> dsmc query backup "/ARTIST/DELTA.*.*"  

             Size      Backup Date        Mgmt Class A/I File
             ----      -----------        ---------- --- ----
API  7.944.346 K  11.11.2002 01:02:47    MC3650      A  /ARTIST/NODE0000/DELTA.20021111010247.1

>> dsmc query backup "/ARTIST/FULL.*.*"

             Size      Backup Date        Mgmt Class A/I File
             ----      -----------        ---------- --- ----
API  7.944.363 K  11.11.2002 01:05:52    MC3650      A  /ARTIST/NODE0000/FULL.20021111010552.1

This information is critical in making a final decision. In our case, delta backup is almost as large as a full backup and will not be the right solution for us.

Conclusion

So do you really need incremental/delta backup?

It depends in general on the size of your database, how many changes you make per day, the speed of you network, the maximum allowed downtime, LOBs... Incremental Delta backup would have less meaning on very active databases because of the large number of changed pages. However, if all changes are concentrated in a limited number of the pages, incremental backup image will save a lot of time and storage.

More than that, DBAs that support the database should understand the strategy and have a knowledge of incremental delta backup.

» See All Articles by Columnist Marin Komadina

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved