DB2 Backup to TSM

DB2 Backup to TSM

How to configure DB2 for backup to TSM.

Before you start

The TSM bit level must match the DB2 bit level but it need not match the O/S.
The db2adutl and dsmapipw utilities read their environment from userprofile every time they are run but db2 only reads it on instance start.
DB2 must be restarted for changes in userprofile to take effect.
Note that a SAP install will assign the DB2 instance owner the C shell.
This changes the way DSMI variables are set.

Some definitions:

userprofile	DB2 script to set envirnment variables, in /db2/db2<sid>/sqllib. This script will be read by users running bash, csh users will run usercshrc (in the same dir.) - at least that's the way it works in a SAP environment.
usercshrc	Equivalent of userprofile for csh users.

Check bit level of DB2

Compare DB2 bit level to TSM, they must be the same.

$ db2level
DB21085I  Instance "db2sp1" uses "64" bits and DB2 code release "SQL09072" with
level identifier "08030107".
Informational tokens are "DB2 v9.7.0.2", "special_24281", "IP23089_24281", and
Fix Pack "2".
Product is installed at "/u01/db2/db2sp1/db2_software".

$ ls -l /opt/tivoli/tsm/client/api
total 248
drwxr-xr-x 3 root bin   4096 Mar 25 07:47 bin
drwxr-xr-x 3 root bin   4096 May 31 08:37 bin64

This must be done as root and assumes you are on a 64bit server and that TSM has been installed.
If you plan to run filesystem backups alongside DB2 then it is as well to create separate TSM nodes for each - this worked for me.

Add stanza to dsm.sys

The dsm.sys file contains configuration parameters which the backup client uses to communicate with the TSM server.
In dsm.sys the 'SERVERNAME' parameter is just a name used to identify a set of parameters (a stanza), it does not refer to a physical server.
There is only one dsm.sys on each client but it can contain multiple servername stanzas.
The example below has two stanza, one for filesystem backups and one for DB2.

# cd /opt/tivoli/tsm/client/api/bin64
## Create or edit dsm.sys - add DB2 stanza
# vi dsm.sys
...
DEFAULTServer  tsm13

Servername tsm13
   Nodename           db260
   COMMmethod         TCPip
   TCPPort            1500
   TCPServeraddress   tsm13
   PASSWORDAccess     generate
   QUERYSCHEDPERIOD   6
   SCHEDLOGRETention  7
   ERRORLOGRetention  14
   ERRORLOGNAME       /var/log/dsmerror.log

Servername tsm13_db2
   Nodename           db260_DB2
   COMMmethod         TCPip
   TCPPort            1500
   TCPServeraddress   tsm13
   PASSWORDAccess     generate
   QUERYSCHEDPERIOD   6
   SCHEDLOGRETention  7
   ERRORLOGRetention  14
   ERRORLOGNAME       /db2/db2sp1/sqllib/log/dsmerror.log

Create dsm_db2.opt

The dsm.opt file tells the backup client which servername stanza in dsm.sys to use.
There can be multiple dsm.opt files on the client, each referring to a different stanza in dsm.sys.
For DB2 backups we have to create a new dsm.opt to pick up the DB2 configuration in dsm.sys.

# cd /opt/tivoli/tsm/client/api/bin64
## Create dsm_db2.opt
# vi dsm_db2.opt
...
SERVERNAME tsm13_db2

Set DSMI environment variables

Edit userprofile (and/or usercshrc if this is a SAP install).

DSMI_DIR	Location of libraries and dsm configuration files.
DSMI_CONFIG	Full path to dsm.opt
DSMI_LOG	Path to directory where log files will be written. DB2 instance owner must have read/write access.

For example:

$ cat /db2/db2sp1/sqllib/userprofile

export DSMI_DIR=/opt/tivoli/tsm/client/api/bin64
export DSMI_CONFIG=/opt/tivoli/tsm/client/api/bin64/dsm_db2.opt
export DSMI_LOG=/db2/db2sp1/sqllib/log

$ cat /db2/db2sp1/sqllib/usercshrc

setenv DSMI_DIR /opt/tivoli/tsm/client/api/bin64
setenv DSMI_CONFIG /opt/tivoli/tsm/client/api/bin64/dsm_db2.opt
setenv DSMI_LOG /db2/db2sp1/sqllib/log

Configure database parameters

There are four (maybe 5) TSM parameters that can be set in DB2:

$ db2 get db cfg for SP1 | grep TSM
 TSM management class                    (TSM_MGMTCLASS) = STANDARD
 TSM node name                            (TSM_NODENAME) =
 TSM owner                                   (TSM_OWNER) =
 TSM password                             (TSM_PASSWORD) =

If archive logs are to be sent directly to tape then the logarchmeth1 parameter has to be changed.

-- Example logarchmeth1 parameter (untested June 2011)
db2> UPDATE DB CFG FOR SP1 USING LOGARCHMETH1 TSM

Whether these parameters are set depends on configuration in dsm.sys.
Where dsm.sys includes “PASSWORDAccess generate” then only TSM_MGMTCLASS needs to be set.
If PASSWORDAccess is set to “prompt” then TSM_NODENAME,OWNER and PASSWORD must also be set.
If a parameter is not required it should be set to NULL, e.g.

db2 => update db cfg for SP1 using TSM_NODENAME NULL
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.
db2 =>  update db cfg for SP1 using TSM_OWNER NULL
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.
db2 =>  update db cfg for SP1 using TSM_PASSWORD NULL
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.

Note that changing parameters requires all applications to disconnect from the database before they become effective.
You will see this warning if there are other connections where you run 'update db cfg':

DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.
SQL1363W  One or more of the parameters submitted for immediate modification
were not changed dynamically. For these configuration parameters, all
applications must disconnect from this database before the changes become
effective.

Set the password for TSM node

Must be run as root

## Note - don't use 'su - root' - using 'su root' will ensure DSMI variables in current environment are preserved
## assuming they have been set of course :-)
$ su root
Password:

## Check DSMI variables
# env | grep DSMI
DSMI_DIR=/opt/tivoli/tsm/client/api/bin
DSMI_LOG=/db2/db2sp1/sqllib/log
DSMI_CONFIG=/opt/tivoli/tsm/client/api/bin/dsm.opt

# /db2/db2sp1/sqllib/adsm/dsmapipw

*************************************************************
* Tivoli Storage Manager                                    *
* API Version = 5.5.1                                       *
*************************************************************
Enter your current password:
Enter your new password:
Enter your new password again:

Your new password has been accepted and updated.

Test TSM access

Use db2adutl to query TSM (should not generate any errors), e.g.

8> db2adutl query
Warning: There are no file spaces created by DB2 on the TSM server
Warning: No DB2 backup images found in TSM for any alias.

The warnings are acceptable.

Restart DB2

This is required for DB2 to pick up any changes in userprofile.

For a SAP install this must be done using stopsap/startsap from the SAP instance owner <sid>adm.
For DB2 only this should be done by running db2stop/db2start from DB2 instance owner db2<sid>.

NOTE: SAP is stopped and started from the <sid>adm user.
This user does not call the userprofile script so the required DSMI variables are not set.
It is important that userprofile is sourced before running startsap. For example:

sp1adm 54> source /db2/db2sp1/sqllib/usercshrc
sp1adm 55> env | grep DSMI
DSMI_DIR=/opt/tivoli/tsm/client/api/bin64
DSMI_CONFIG=/opt/tivoli/tsm/client/api/bin64/dsm_db2.opt
DSMI_LOG=/db2/db2sp1/sqllib/log
sp1adm 56> startsap
...

Or add userprofile to <sid>adm profile.

Run a backup

Backups direct to TSM should be working, e.g.

db2> backup database SP1 online use tsm

Query backups in TSM using db2adutl, e.g.

db2sp1 1> db2adutl query full

Query for database SP1

Retrieving FULL DATABASE BACKUP information.
   1 Time: 20110618051754  Oldest log: S0000283.LOG  DB Partition Number: 0    Sessions: 2