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

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

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Apr 28, 2004

Tips for using Tivoli Storage Manager with DB2

By Marin Komadina

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   
	Log file for DB2 database ARTIST
API 16.384 B  19.01.2004 23:51:34   
	Log file for DB2 database ARTIST
API 12.288 B  20.01.2004 01:06:14   
	Log file for DB2 database ARTIST
API 53.248 B  23.01.2004 18:51:56   
	Log file for DB2 database ARTIST
API 53.248 B  23.01.2004 18:56:31   
	Log file for DB2 database ARTIST
API 12.288 B  23.01.2004 18:57:31   
	Log file for DB2 database ARTIST
API 12.288 B  23.01.2004 18:57:31   
	Log file for DB2 database ARTIST
API 36.864 B  23.01.2004 18:51:51   
	Log file for DB2 database ARTIST
API 12.288 B  24.01.2004 01:05:07   
	Log file for DB2 database ARTIST
API 237.568 B  24.01.2004 02:43:33  
	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 

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.

DB2 Archives