DB2 Database and Tivoli Storage Manager

My first DB2 article, "Who needs incremental delta backup" published on January 29, 2003 was an introduction to the DB2 backup and recovery technology on the SUN Solaris platform. Since then, I have been asked many times to write on some very interesting topics that are not covered in detail in the existing IBM documentation. All requests from the readers have been collected, resulting in this new series of DB2 articles. This first article examines DB2 backup techniques with the Tivoli Storage Manager (TSM) Application programming interface (API) on the Sun Solaris operating system.

This article covers:

• • TSM System
  • TSM Server Configuration
  • UNIX Server Configuration
  • DB2 Database Setup for TSM usage
  • DB2 Database Features using TSM
  • Conclusion

TSM System

For those who have worked with the ADSM -ADSTAR Distributed Storage Manager, this product was replaced 1999 with the new Tivoli Storage Manager (TSM) from Tivoli Systems Inc. The TSM is just a part of the Tivoli Storage Management product set intended for providing universal, enterprise-wide backup, archive, restore and retrieve solution. The most recent TSM commercial version has many new, integrated functions, such as disaster recovery and specific storage management functions. Nevertheless, the basic TSM functions remain unchanged, providing the network cross-platform backup and archive solutions. The focus of this article is on TSM's integration with the DB2 database; however, even though the DB2 database has integrated support for Tivoli Storage Manager, TSM is sold as a completely separate product.

Figure 1. The TSM system infrastructure consisting of the TSM Server with the DB2 TSM flat database and TSM Storage. Depending upon the data type, various storage mediums are used; for example, variable data is stored on magnetic tape and permanent data is stored on optical or magneto-optical discs.

The TSM backup system and the main components:

a.) TSM Administrator Workstation, with an installed TSM administrator console, manages a transparent database network backup to the TSM System.

b.) TSM server, provides backup, archive, and space management services storage for the defined clients. The TSM server uses the local DB2 database for logging and storing activity logs.

c.) TSM API client turns a local machine into the TSM client node, making backup, archive, restore, and retrieve services feasible on the TSM Server. The TSM API provides wide flexibility for making database backups with opened database files for full or table space level backup.

d.) The DB2 database comes packaged with the TSM support, in the form of special utilities for handling data on the TSM server or integrated DB2 database parameters.

Figure 2. Functional DB2 TSM backup using TSM API exhibits of distinction between archive log files and database backup files.

Next, just a few words about internal TSM server structure. Several database servers, nodes in TSM terminology, are logically grouped into a policy domain. The members of one policy domain distribute the common storage requests. Every policy domain has one policy set, constituted of several management classes, associated with it. The management classes are the marrow of whole structure and main configuration point. The rules defined in the management class govern data flow to an appropriate media and control how the data is managed by TSM. Most important to mention, the management class is used for setting an expiration period for the backup or the archive log files. The TSM API client sends the backup and archive data to the TSM Server, together with a description of the object and management class for the object. The database backups and the table space backups result in the TSM backup objects, while the database log file results in the TSM archive objects.

TSM Server Configuration

For demonstration purposes, I have created one multimode DB2 database named ARTIST, belonging to the TSM API configuration on the ARTIST_DB2 server machine, running on a SUN Solaris operating system. The TSM administrators are responsible for the TSM server organization and configuration , however, they need clear, technical input from the DB2 administrators. The allied forces of the DB2 and TSM administrators have resulted in the following exhibit TSM standards and configuration used in this article:

Click for larger image Figure 3. The TSM System configuration files and settings, used during article test case.

a.)     The node ARTIST_DB2 has been assigned to the policy domain DB2, which has been created on the TSM server for handling the DB2 backup and archive data.

b.)   The following is a configuration review, taken directly from the TSM server configuration database listing the new node ARTIST_DB2 on Sun Solaris, and belong to the policy domain DB2:

tsm> q node
 
Node Name                     Platform     Policy Domain      Days Since     Days Since     Locked?
                                           Name                     Last       Password
                                                                  Access            Set
-------------------------     --------     --------------     ----------     ----------     -------
AIX                           AIX          TEST1                      <1          1 093       No
ARTIST_DB2                    Sun          DB2                        <1            630       No
ARTIST1                       WinNT        STUFF                      81          1 091       No
ARTIST5                       AIX          STUFF                      <1            821       No
MARIN_DB2                     Linux86      DB2                       975          1 037       No
MARIN_NT                      WinNT        STUFF                     603            645       No
DB2                           AIX          DB2                        <1          1,742       No   
 

c.)   Displaying newly defined management classes MGMT_BACKUP and MGMT_LOGS on the TSM server associated with the new DB2 policy domain:

tsm> q mgmt
 
Node Name: TESTTSM001
Domain Name               : DB2
Activated Policy Set Name : PSDB
Activation date/time      : 01.01.2004 16:42:33
Default Mgmt Class Name   : MC3650
Grace Period Backup Retn. : 9999 day(s)
Grace Period Archive Retn.: 9999 day(s)
 
MgmtClass Name                  : MGMT_BACKUP
Description                     : MC for DB2 backups
MgmtClass Name                  : MGMT_LOGS
Description                     : MC for DB2 logs
MgmtClass Name                  : MC365
Description                     : MC for 365 days
MgmtClass Name                  : MC3650
Description                     : MC for 3650 days

The newly created management class, MGMT_BACKUP, is the backup management class, with the associated copy group. The backup copy group has the following fixed retention settings:

VEREXISTS=1 - every database backup has unique name, duplicated backups will be deleted, backup is kept according definition for the management class

VERDELETED=0 deleted backup are automatically expired from TSM server

RETONLY=0 - inactive, expired backups are deleted automatically

The new archive management class, MGMT_LOGS, has similar technical characteristics to the backup management class, MGMT_BACKUP, with one important exception.

Archive logs are kept on the TSM server for a strictly defined period only. Upon expiration, the TSM will automatically delete archived objects. Manual clearing of archive log files from the TSM server using the DB2 commands is prohibited. Supplementary, duplicate archive log files are accepted, something that is not permitted for the backup management class. The archive copy group has the following defined retention settings:

RETVER=NOLIMIT

d.)   As a last bit of information, the storage pools listing from TSM server:

tsm> q stgpool
 
Storage      Device       Estimated    Pct    Pct  High  Low  Next       
Pool Name    Class Name    Capacity   Util   Migr   Mig  Mig  Storage    
                               (MB)                 Pct  Pct  Pool       
-----------  ----------  ----------  -----  -----  ----  ---  -----------
BACKUPPOOL   DISK           2,048.0    0.0    0.0    80   20  TAPEPOOL   
ARCHPOOL     DISK           2,048.0    0.0    0.0    80   20  TAPEPOOL2
TAPEPOOL     MAGSTARMP    109,490.8   37.2  100.0    90   70             
TAPEPOOL1    MAGSTARMP     75,295.0   25.2  100.0    90   70

The archived log files are saved in the ARCHPOOL TSM storage pool, and the database backup files are saved in the BACKUPPOOL storage pool.

UNIX Server Configuration

On the SUN Solaris machine that holds my test database ARTIST, the TSM API client, version 4.2.2.1 has been installed. The TSM API is used to demonstrate the TSM system usage.

# pkginfo -l TIVsmCapi
   PKGINST:  TIVsmCapi
      NAME:  Tivoli Storage Manager 
	     Solaris 2.6/7/8 API Client
  CATEGORY:  application
      ARCH:  sparc
   VERSION:  Version 4 Release 2 Level 2
   BASEDIR:  /opt/tivoli/tsm/client
    VENDOR:  Tivoli Systems Inc.
    PSTAMP:  2002.172.07:58:15
  INSTDATE:  Sep 06 2002 11:31
    STATUS:  completely installed
     FILES:      120 installed pathnames
                  11 directories
                   5 executables
                   2 setuid/setgid executables
               25570 blocks used (approx)
 
>> dsmc
Tivoli Storage Manager
Command Line Backup Client Interface - 
   Version 4, Release 2, Level 2.1 
(C) Copyright IBM Corporation, 1990, 2001, 
   All Rights Reserved.

The TSM client installation results in new entries in the DB2 instance owner profile. New in the profile defined environment variables and their corresponding contents:

Variable Name	Content
DSMI_DIR	path to TSM system options file ( dsm.sys )
DSMI_CONFIG	path to TSM client options file ( dsm.opt )
DSMI_LOG	Path to TSM API error log file ( dsierror.log )

# cat .profile | grep DSMI
 
export DSMI_CONFIG=$HOME/sqllib/tsm/dsm.opt
export DSMI_DIR=$HOME/adsm/api/bin
export DSMI_LOG=$HOME/sqllib/tsm

The unique, server specific TSM system options file dsm.sys has been updated with the following new entries:

# cat dsm.sys 
************************************************************
 SERVERNAME              TESTTSM001
 COMMMETHOD              tcpip
 TCPPORT                 1500
 TCPSERVERADDRESS        localhost
 NODENAME                ARTIST_DB2
 COMMRESTARTDURATION     10
 COMPRESSION             yes
 INCLEXCL                /opt/tivoli/tsm/client/ba/bin/inclexcl.def
 ERRORLOGNAME            /opt/tivoli/tsm/client/ba/bin/dsmerror.log
 PASSWORDACCESS          generate
 TCPBUFFSIZE             32
 TCPWINDOWSIZE           640
 TXNBYTELIMIT            25600                                                 
 CHANGINGRETRIES         4
 COMMRESTARTDURATION     10
 DIRMC                   DEFAULT

Additionally, the listed INCLEXCL parameter file explains the TSM API configuration:

# cat inclexcl.def 
include         /ARTIST/DB2_BACKUP*.*                          MGMT_BACKUP
include         /ARTIST/NODE0000/S*.LOG                        MGMT_LOGS

The TSM client option file, dsm.opt, one per client on the UNIX machine, has only one entry:

# cat dsm.opt
SERVERNAME              TESTTSM001

The parameter SERVERNAME, given in the client configuration file, is only an alias to the system options file dsm.sys. All necessary configuration entries have been done through the dsm.sys options file.

The final preparation step is the configuration of the database user exit program, db2uext2.cadsm, devoted to TSM usage. This add-on IBM program manages the database archived log files and externalizes them on the TSM server backup media. During a recovery operation, the user exit program retrieves the log files from the TSM server, putting them into the database log path directory.

Some peculiarities of the user exit program:

• • filed but still active database log file is opened in read only mode and archived by the user exit program, and remains in the log path directory until the transaction ends
  • archived log files are renamed and reused--the user exit program never deletes them
  • following database deactivation, the last active log file (full or partially filled) is closed and via the user exit program saved to the TSM
  • all log files are truncated before archiving, in free unused log space

IBM suggests using the user exit program that comes with database installations. Nevertheless, the customer has the choice to use his own exit program in the same manner. The provided user exit programs need to be compiled and adopted before actual usage.

The procedure for the user exit program arrangements:

artist$ su -
root# cd $HOME/sqlib/adm
root# cp $HOME/sqllib/samples/c/db2uext2.cadsm $HOME/sqllib/adm/db2uext2.c
root# vi db2uext2.c
 
#define ARCHIVE_PATH "/ARTIST/NODE0000"
#define RETRIEVE_PATH "/ARTIST/NODE0000"
#define AUDIT_ERROR_PATH "$HOME/sqllib/tsm"
 
root# exit
artist$ cc_r -o db2uext2 db2uext2.c libApiDs.a

A compiled user exit program is afterwards saved in the /sqllib/adm instance software directory.

Conclusion

It was important to present a real-world example of the TSM infrastructure, which can be used as template for other more complicated configurations. DB2 administrators need to be familiar with the infrastructure so that they can suggest backup improvements or in the case of errors, trace faulty backup execution. Part two of this article will examine DB2 database backup techniques and reveal some little known situations that arise only when using TSM server.