DB2 WITH TIVOLI STORAGE MANAGER

DB2 WITH TIVOLI

Link:
http://www.ibm.com/developerworks/servicemanagement/library/sm-tsm-db2/

http://www.ibm.com/developerworks/data/library/techarticle/dm-0510pilaka/index.html

http://www-01.ibm.com/support/docview.wss?uid=swg21635414

Cross node recovery:

http://db2talk.com/2014/03/17/cross-node-database-recovery-from-tsm/

Tivoli Storage Manager:

IBM Tivoli Storage Manager is a software product that provides storage management services for data, primarily backup, restore, archive, and retrieve, by using a client/server model. 

In general terms, Tivoli Storage Manager backup-archive clients are installed on each system (such as file servers, database servers, client workstations).

Using a configured network transport such as TCP/IP, each Tivoli Storage Manager client sends copies of its files as either backup or archive objects to a Tivoli Storage Manager server.

 The server stores the client files in a centralized storage system (typically consisting of large amounts of disk or tape storage).

Tivoli Storage manager API:

The Tivoli Storage Manager application programming interface (API) provides a library of functions that allow independent software applications and custom-built applications to back up and archive their data to a Tivoli Storage Manager server.

The DB2 DBMS also uses the Tivoli Storage Manager API for backup and restore operations.

DB2 provides its own backup utility (Backup command) that can be used to back up data at the table space level or the database level.

If you set up this utility to use Tivoli Storage Manager as the backup media, DB2 communicates with the Tivoli Storage Manager API for backup and restore operations. 

Thus, both the Tivoli Storage Manager API client and backup-archive client work together to provide full data protection for your DB2 environment. 

The API client and the backup-archive client can run simultaneously on the same DB2 server. The Tivoli Storage Manager server considers them separate clients.

A Tivoli Storage Manager client API must be installed on each DB2 database server.

Installing and configuring TSM BACKUP  - ARCHIVE / API client:

The Tivoli Storage Manager API bit level needs to match the DB2 bit level; the bit level of the OS does not affect this. 

For example, if a 32bit DB2 Instances is installed on a 64bit AIX OS, then the 32bit Tivoli Storage Manager API should be installed to match the bit level of the DB2 instance.

Keep in mind that the db2adutl and dsmapipw utilities use the Tivoli Storage Manager API environment variables (the DSMI variables) that are set in their current working shell. 

Therefore, any changes to the DSMI environment variables will be seen immediately by these utilities. However, DB2 utilizes the environment variables from memory that are loaded when the instance is started. Therefore, any changes to these DSMI environment variables require the DB2 instance to be recycled (i.e. db2stop/db2start) for the changes to take affect.

If any of the Tivoli Storage Manager API files (such as dsmtca, libApiDS.a, or libApiTSM64.a) were copied from one Tivoli Storage Manager API install directory to another, from the Tivoli Storage Manager B/A client directory, or from one system to another, then it is suggested to uninstall the Tivoli Storage Manager API, delete the Tivoli Storage Manager API install directory, and then reinstall the Tivoli Storage Manager API to ensure that the appropriate bit level of the files exist in the correct location.

Set the environment variables

Set the following DSMI environment variables in either the operating system shell or the
/home/instance_home_dir/sqllib/userprofile file.
DSMI_DIR
DSMI_CONFIG
DSMI_LOG

Important: The DB2 DBMS reads these environment variables during the DB2 instance startup. If you change the variables, you must restart the DB2 instance for the changes to take effect.

DSMI_DIR

This variable points to the API installation directory. The dsmtca file, the dsm.sys file, and the language files must be in the directory pointed to by the DSMI_DIR environment variable. Setting the DSMI_DIR variable is optional. If it is not specified, the default directory is /usr/tivoli/tsm/client/api/bin64 (for AIX 64-bit).

DSMI_CONFIG

This variable points to the fully qualified path and file name of the Tivoli Storage Manager dsm.opt client options file. This file contains the name of the server to be used.

DSMI_LOG

This variable points to the directory path where the error log file, dsierror.log, is to be created.

Add the following lines in the userprofile file.

export DSMI_CONFIG=/usr/tivoli/tsm/client/api/bin64/dsm.opt

export DSMI_LOG=/home/db2inst1

export DSMI_DIR=/usr/tivoli/tsm/client/api/bin64

Log off and log in again as an instance user and run the .profile file.

$ ~/.profile

DB2 will only use the DSMI variables defined in the $HOME/sqllib/userprofile. Defining these DSMI variables solely in the $HOME/.profile is no longer sufficient as they will be ignored by DB2.

Determine the current setting for the Tivoli Storage Manager API environment variables:
set | grep DSMI

DSMI_DIR should specify the:
- /<opt or usr>/tivoli/tsm/client/api/bin directory if DB2 is 32bit
- /<opt or usr>/tivoli/tsm/client/api/bin64 directory if DB2 is 64bit

At the bottom of the $HOME/.profile (for Korn shell) or $HOME/.bash_profile (for Bash shell) or the equivalent for any other shell, source (i.e. execute/run) the DB2 file $HOME/sqllib/db2profile. For example: 

Add the following to the bottom of the $HOME/.profile

. /<db2home>/sqllib/db2profile

Note: <dot><space>/full/path/sqllib/db2profile

Add the following to the bottom of the $HOME/.bash_profile

source /<db2home>/sqllib/db2profile

By default, the .../sqllib/db2profile sources the .../sqllib/userprofile. Therefore, the DSMI variables will be exported into the DB2 user's shell.

To avoid confusion, it is recommended to remove the DSMI variables from the $HOME/.profile or $HOME/.bash_profile (if they exist) and only define the DSMI variables in the .../sqllib/userprofile

Create the dsm.sys client options file

Log in using the root user ID and create the dsm.sys file with the following entries (customize for your installation). This file must be in the directory that is specified by the DSMI_DIR environment variable.

servername tsmdb2              //name of this stanza 
commmethod tcpip
tcpserveraddress  x.xx.xxx.xxx //IP address of Tivoli Storage Manager server
tcpport 1500                   //Port where Server is listening
nodename tsmdb2                //Must match nodename on Tivoli Storage Manager server
passwordaccess generate

The dsm.sys file must exist, or have a symbolic link to it, within this DSMI_DIR directory

Create the dsm.opt client options file

Log in using the root user ID and create the dsm.opt file. 

This options file has only one line in it, which is a reference to the server stanza in the dsm.sys file. 

This file must be in the directory specified by the DSMI_CONFIG environment variable.

Servername   tsmdb2

DSMI_CONFIG should specify a full path and file name for Tivoli Storage Manager user options file (i.e., dsm.opt)

verify that "servername xyz" in this dsm.opt corresponds to an existing "servername xyz" stanza in the $DSMI_DIR/dsm.sys

DSMI_LOG, if defined, should specify a directory that is Read/Writable by DB2 instance user.

Note that the errorlogname parameter takes precedence over the DSMI_LOG variable.
If errorlogname exists in the stanza in the dsm.sys ensure the file specified has Read/Write access by the db2 user.

Recycle the DB2 instance

Stop and start the DB2 instance.

$ db2stop

SQL1064N DB2STOP processing was successful.

$ db2start

SQL1063N DB2START processing was successful.

Set the API password

To access the Tivoli Storage Manager server, client users (called nodes) must have a password to access the server.

The DB2 dsmapipw program uses the Tivoli Storage Manager API to create the encrypted password file. 

The DB2 application includes thedsmapipw utility, which is installed in the /home/instance_home_dir/sqllib/adsm directory.

Log in as root user to run the dsmapipw utility.

Before you run dsmapipw, you must set the DSMI environment variables similar to that on the DB2 instance, as shown in the following example:

$ ./dsmapipw
*************************************************************
* Tivoli Storage Manager                                    *
* API Version = 6.1.0                                       *
*************************************************************
Enter your current password:
Enter your new password:
Enter your new password again:


Your new password has been accepted and updated.

Tip: If you use the passwordaccess prompt option, you do not have to to run the dsmapipw utility.

If passworddir is specified in the dsm.sys, delete the TSM.PWD file in this directory then run dsmapipw again.

exit

 If using passwordaccess generate (in the dsm.sys), the DB2 database configuration parameters TSM_OWNER, TSM_NODE, and TSM_PASSWORD must be set to NULL. To verify, run:

db2 get db cfg for dbname | grep TSM

TSM management class (TSM_MGMTCLASS) = MYDB2CLASS
TSM node name (TSM_NODENAME) =
TSM owner (TSM_OWNER) =
TSM password (TSM_PASSWORD) =

The query will show blank values for the parameters that are already NULL. 
If you need to update any of these DB2 database configuration parameters, run:

db2 update db cfg for dbname using TSM_NODENAME NULL

The value for TSM_MGMTCLASS will not affect this sign-in or authentication, so it can be either NULL or populated with a valid management class. The mgmtclass specified will only be used for the DB2 database backups, not the db2 logs.

The TSM_OWNER, TSM_NODE, and TSM_PASSWORD parameters should only be populated when passwordaccess is set to prompt within the dsm.sys file.

Verifying TSM DB2 Configuration working or not:

db2adutl query

This should complete successfully, which verifies that the environment in the current shell is properly configured.

Backup techniques using TSM:

Use the USE TSM option to specify that Tivoli Storage Manager information be used during the database backup operation.

When the USE TSMoption is specified on the DB2 backup operation, the API is used to direct the database backup to the Tivoli Storage Manager server.

The USE TSM option instructs DB2 to use the DB2 API library interface that calls Tivoli Storage Manager.

Example:

db2 backup db sample use tsm

Recovery techniques using TSM:

Use the RESTORE DATABASE command with the USE TSM option to restore the database. The USE TSM option tells the DBMS to use the Tivoli Storage Manager API to read the backup file and restore it.

Cross node recovery with TSM:

On database server, there are two files dsm.sys and dsm.opt that have TSM server configuration in them. The location of these files (in most cases) is either “/opt/tivoli/tsm/client/api/bin64/” or “/usr/tivoli/tsm/client/api/bin64/”

Sample files on PROD could be:

$ more /opt/tivoli/tsm/client/api/bin64/dsm.sys

ServerName TSM_PROD_DB2
COMMMethod TCPip
TCPPort 1500
TCPServeraddress tsm02prod.example.com
PasswordAccess generate
NodeName prodclstr_db2

$ more /opt/tivoli/tsm/client/api/bin64/dsm.opt

ServerName TSM_PROD_DB2

On the DR server (say drhost01), you could (needs root authority) simply add one more stanza (about Prod TSM Info) in dsm.sys file. You do not have to delete/ modify existing stanza if any.

Example is shown below:

db2inst1@drhost01:/opt/tivoli/tsm/client/api/bin64> more dsm.sys
ServerName TSM_DR_DB2

COMMMethod TCPip
TCPPort 1500
TCPServeraddress tsmdr.example.com
PasswordAccess generate
NodeName drclstr_db2

ServerName TSM_PROD_DB2
COMMMethod TCPip
TCPPort 1500
TCPServeraddress tsm02prod.example.com
PasswordAccess generate
NodeName prodclstr_db2

The stanza that gets picked up from dsm.sys is actually determined by contents in dsm.opt file. For example if dsm.opt file has:

ServerName      TSM_DR_DB2

in it, then the DR TSM would be the active one and hence no Production backups would be listed when you issue ‘db2adutl query’ command.

Modifying dsm.opt file to “Production TSM” servername (shown below) is what needs to be done to be able to list production backups and logs using ‘db2adutl query’ command. You might only want to have one line in dsm.opt file to reflect the stanza that needs to be picked up from dsm.sys file.

ServerName TSM_PROD_DB2

However, what I do is to actually maintain 2 files named dsm.opt.prod and dsm.opt.dr. I have my UNIX Admin (requires root) list TSM Prod server name in dsm.sys.prod and TSM DR server name listed in dsm.sys.dr.

Here is an example:

db2inst1@drhost01:/opt/tivoli/tsm/client/api/bin64> more dsm.opt.prod

ServerName tsm_prod_db2

db2inst1@drlhost01:/opt/tivoli/tsm/client/api/bin64> more dsm.opt.dr

ServerName TSM_DR_DB2

The advantage with this approach is that I do not have to have “root” authority every time I want to switch from PROD to DR and back.

But how does actually DB2 know which file to pick up? This is where userprofile located at $HOME/sqllib ($HOME of instance owner) plays an important role.

When I want DR server to point to PROD TSM, userprofile file would look like shown below:

db2inst1@drhost01:~/sqllib> more userprofile
export DSMI_DIR=/opt/tivoli/tsm/client/api/bin64
export DSMI_CONFIG=/opt/tivoli/tsm/client/api/bin64/dsm.opt.prod
export DSMI_LOG=$HOME/sqllib/db2dump

When I want DR server to point to DR TSM, userprofile file would look like shown below:

db2inst1@drhost01:~/sqllib> more userprofile
export DSMI_DIR=/opt/tivoli/tsm/client/api/bin64
export DSMI_CONFIG=/opt/tivoli/tsm/client/api/bin64/dsm.opt.dr
export DSMI_LOG=$HOME/sqllib/db2dump

All that needs to be done after making change (requires instance owner authority) as mentioned above is to execute .profile which in turn should execute db2profile which in turn executes userprofile. Easy way to reflect changes would be to logout and log back in as instance owner.

How to check if you are pointing to TSM Prod or TSM DR server?

If you’re pointing to PROD TSM, “env” (environment) variable DSMI_CONFIG would indicate that.

db2inst1@drldwadm01:~> env | grep -i dsmi
DSMI_DIR=/opt/tivoli/tsm/client/api/bin64
DSMI_LOG=/db2home/db2inst1/sqllib/db2dump
DSMI_CONFIG=/opt/tivoli/tsm/client/api/bin64/dsm.opt.prod

You could then list production backups and logs using ‘db2adutl’ command. Sample is shown below:

db2inst1@drhost01:~> db2adutl query full database proddb

Query for database PRODDB
Retrieving FULL DATABASE BACKUP information.
1 Time: 20140317145106 Oldest log: S0000429.LOG DB Partition Number: 0 Sessions: 2
2 Time: 20140317145106 Oldest log: S0000188.LOG DB Partition Number: 1 Sessions: 2
3 Time: 20140317145106 Oldest log: S0000187.LOG DB Partition Number: 2 Sessions: 2
4 Time: 20140317145106 Oldest log: S0000188.LOG DB Partition Number: 3 Sessions: 2
5 Time: 20140317145106 Oldest log: S0000188.LOG DB Partition Number: 4 Sessions: 2
6 Time: 20140317145106 Oldest log: S0000158.LOG DB Partition Number: 5 Sessions: 2
7 Time: 20140317145106 Oldest log: S0000159.LOG DB Partition Number: 6 Sessions: 2
8 Time: 20140317145106 Oldest log: S0000158.LOG DB Partition Number: 7 Sessions: 2
9 Time: 20140317145106 Oldest log: S0000158.LOG DB Partition Number: 8 Sessions: 2
10 Time: 20140317145106 Oldest log: S0000158.LOG DB Partition Number: 9 Sessions: 2
11 Time: 20140317145106 Oldest log: S0000158.LOG DB Partition Number: 10 Sessions: 2
12 Time: 20140317145106 Oldest log: S0000159.LOG DB Partition Number: 11 Sessions: 2
13 Time: 20140317145106 Oldest log: S0000160.LOG DB Partition Number: 12 Sessions: 2

……………………

Once you are able to list the database backups and logs using ‘db2adutl’ command, it is evident that DR server is able to talk to PROD TSM server. Next steps would be to perform restore and rollforward. Few scenarios have been covered in a series of blog posts here,here and here. These detail tablespace recovery but could be good reads to grasp concept of restore and rollforward in a DPF environment.