Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

DB2

Posted Feb 5, 2003

Who needs DB2's Incremental Delta Backup? - Page 2

By DatabaseJournal.com Staff

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



DB2 Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.