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

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:

By submitting your information, you agree that databasejournal.com may send you databasejournal offers via email, phone and text message, as well as email offers about other products and services that databasejournal believes may be of interest to you. databasejournal will process your information in accordance with the Quinstreet Privacy Policy.

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Feb 5, 2003

Who needs DB2's Incremental Delta Backup?

By DatabaseJournal.com Staff

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.

DB2 Archives

Comment and Contribute


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