Tips for using Tivoli Storage Manager with DB2

As a
final word about Tivoli Storage Manager (TSM) database backup, here are a few tips
I have found working with the TSM system. Only the most interesting tips are
included since presenting all of them would be beyond the scope of this
article.

This
article covers:

  • Tips Using TSM

  • Conclusion

Tips Using TSM

Tip 1.User
Exit is archiving log files more than once

An unusual and confusing DB2
feature is the existence of duplicated archived log files on the TSM server. One
theory is that once a database log file is full a database manager is creating a
user exit program queue entry. The user exit program is starting with a log file
copy to the archive log destination. In our case, the archive log destination
is on the TSM server. Upon finishing, a database log file is renamed to a new name
with higher sequence log number. This explanation is as expected, unless a TSM
inventory is made.


$db2adutl query archive log

API 20.480 B 19.01.2004 20:55:25
/ARTIST/NODE0000/S0000018.LOG
Log file for DB2 database ARTIST
API 16.384 B 19.01.2004 23:51:34
/ARTIST/NODE0000/S0000019.LOG
Log file for DB2 database ARTIST
API 12.288 B 20.01.2004 01:06:14
/ARTIST/NODE0000/S0000020.LOG
Log file for DB2 database ARTIST
API 53.248 B 23.01.2004 18:51:56
/ARTIST/NODE0000/S0000021.LOG
Log file for DB2 database ARTIST
API 53.248 B 23.01.2004 18:56:31
/ARTIST/NODE0000/S0000021.LOG
Log file for DB2 database ARTIST
API 12.288 B 23.01.2004 18:57:31
/ARTIST/NODE0000/S0000022.LOG
Log file for DB2 database ARTIST
API 12.288 B 23.01.2004 18:57:31
/ARTIST/NODE0000/S0000023.LOG
Log file for DB2 database ARTIST
API 36.864 B 23.01.2004 18:51:51
/ARTIST/NODE0000/S0000024.LOG
Log file for DB2 database ARTIST
API 12.288 B 24.01.2004 01:05:07
/ARTIST/NODE0000/S0000025.LOG
Log file for DB2 database ARTIST
API 237.568 B 24.01.2004 02:43:33
/ARTIST/NODE0000/S0000026.LOG
Log file for DB2 database ARTIST

Listing 1: Listing archived log files directly
from the TSM server

The database log file S0000021.LOG had occurred two
times on the TSM server. Both archived log files have the same size; however,
there is a difference in the timestamp.

Knowing that the main principle of
database integrity is the existence of a unique archive log file, there may be
uncertainty about which one is the right one. The secret lies in the imperfection
of the user exit program and not in the DB2 database itself.

To demonstrate my claim, I have
created a scenario for generating duplicate archive log files on the TSM server:


$ db2 commit
-> database log file closed and ready for archiving
-> user exit program is taking copy of the file and starting with file
sending to the TSM server
$ db2 deactivate db artist
DB20000I The DEACTIVATE DATABASE command completed successfully.

-> request for database closing executed
-> database is closing, user exit did not return confirmation to DBM, that
that the archiving was finished successfully, database is closed
$ db2 activate db artist
DB20000I The ACTIVATE DATABASE command completed successfully.

-> database is actived, DBM is sending new archive request f or the same
database log file, and the user exit program is closing and sending log
file one again to the TSM server
Listing 2: Generation of duplicated archive log
files on the TSM server

Following this explanation, the user
exit program, initiated from DBM is creating two copies on the TSM server. It
has been my experience that we can even find two identical files with different
sizes, where the first file is smaller than the second one. Generally speaking,
this is not a problem, because the file with the later timestamp is only
considered during recovery.

Tip 2. Backup
restored from the TSM server having wired timestamp

During a DB2 database redirected,
restore operation there was an occasion to retrieve a database backup image from
the TSM on the local file system. After restoring the backup image, the restored
file has "strange" timestamp information associated.


$ dsmc res “/ARTIST/NODE0000/ARTIST.0.artist.NODE0000.CATN0000.20040116010133.000

Tivoli Storage Manager
Command Line Backup Client Interface – Version 4, Release 2, Level 2.1
(C) Copyright IBM Corporation, 1990, 2001, All Rights Reserved.

Restore function invoked.

Node Name: ARTIST
Session established with server TESTTSM001: AIX-RS/6000
Server Version 5, Release 1, Level 6.4
Server date/time: 17.01.2004 01:03:33 Last access: 10.01.2004 10:50:37

** Interrupted **
ANS1114I Waiting for mount of offline media.
Restoring /ARTIST/NODE0000/ARTIST.0.artist.NODE0000.CATN0000.20040116010133.000

—> /ARTIST/NODE0000/restore/ARTIST.0.artist.NODE0000.CATN0000.20040116010133.000 [Done]

Restore processing finished.

Total number of objects restored: 1
Total number of objects failed: 0
Total number of bytes transferred: 324,49 MB
Data transfer time: 26,10 sec
Network data transfer rate: 13.120,92 KB/sec
Aggregate data transfer rate: 1.638,27 KB/sec
Elapsed processing time: 00:03:29

->-> ls -lrt
—-rw-r– 1 artist db2 1119911936 Aug 15 1995
ARTIST.0.artist.NODE0000.CATN0000.20040116010133.000

Listing 3: Restoring database backup image from
the TSM server to the local filesystem

The restored backup image file has
an incorrect date-stamp, showing a file date-stamp of 15.08.1995, instead the regular
one of 01.16.2004. Searching for an explanation of these phenomena, all I found
was a "small incompatibility" between the Sun Solaris operating
system and the TSM Server. Ignoring that, the restored database backup was
fully usable.

Marin Komadina
Marin Komadina
Marin was born June 27, 1968 in Zagreb, Croatia. He graduated in 1993 form The Faculty for Electrotechnology and Computer Sciences, University of Zagreb in Croatia. He started his professional career as a System specialist and DBA for the Croatian company Informatika System. His most important project was the development and implementation of the enterprise, distributed point of sales solution, based on the Oracle technology. In 1999, Marin became the company CTO, where he played an active role in company development and technical orientation. After Informatika System, Marin worked as an IT Manager Assistant for the Austrian international retail company "Segro," on location in Graz (Austria) and Zagreb (Croatia). He was responsible for the company's technical infrastructure and operational support. Segro used IBM technology, OS/400 operating system and DB2 database. In 1998, Marin joined the international telecommunication company VIPNet GSM that was a part of greater concern, Mobilkom Austria& Western Wireless Int. USA. After one year, Marin took over the IT System Manager position, where he managed many multi-platform, telecommunication projects and was leading the IT system department. In 2001, Marin started to work in Germany as a senior system architect. He is currently working for German banks on different banking projects.

Latest Articles