DB2 Backup Control
As a part of the default
installation, a utility called db2adutl is automatically installed into the /$HOME/sqllib/bin
directory as a link to the DB2 software installation directory.
$ pwd$
/home/artist/sqllib/bin$
>> ls -lrt db2adutl
lrwxrwxrwx 1 root other
30 Jul 26 2002 db2adutl ->
/opt/IBMdb2/V7.1/adsm/db2adutl
Listing 7: db2adutl system command, default
location
The DB2 system command db2adutl is
used to directly
manage database backup and database archived log files on the TSM. Functionality
integrated in the db2adutil makes the TSM server content management possible. Operations
such a list, delete or retrieve database backups, load copy images and archived
database logs are achievable without the need to use dedicated TSM client
tools. Supplementary,
db2adutil provides the
database backup history file content management.
Performing the full database
backup management on the TSM using db2adutl program:
$ db2adutl query
Retrieving full database backup information.
full database backup image: 1, Time: 20040119130942,Oldest log: S0000005.LOG, Sessions used: 1
full database backup image: 2, Time: 20040119142241,Oldest log: S0000006.LOG, Sessions used: 1
Retrieving table space backup information.
table space backup image: 1, Time: 20040119094003,Oldest log: S0000004.LOG, Sessions used: 1
table space backup image: 2, Time: 20040119093043,Oldest log: S0000005.LOG, Sessions used: 1
Retrieving log archive information.
Log file: S0000005.LOG
Log file: S0000006.LOG
$ db2adutl delete full taken at 20040119130942
db artist
Query for database ARTIST
Retrieving full database backup information. Please wait.
full database backup image: ARTIST.0.artist.0.20040119130942.000
Do you want to deactivate this backup image (Y/N)? y
Are you sure (Y/N)? y
$ db2adutl query
Query for database artist
Retrieving full database backup information.
full database backup image: 2, Time: 20040119142241,Oldest log: S0000006.LOG, Sessions used: 1
Retrieving table space backup information.
table space backup image: 1, Time: 20040119094003,Oldest log: S0000004.LOG, Sessions used: 1
table space backup image: 2, Time: 20040119093043,Oldest log: S0000005.LOG, Sessions used: 1
Retrieving log archive information.
Log file: S0000005.LOG
Log file: S0000006.LOG
Listing 8: Query and delete backup image using
db2adutl system command
The DB2 system command
db2adutil cannot be used to delete individual incremental or delta backups. The
db2adutil command syntax covers only full, tablespace or load copy backup
delete operations. For deleting incremental and delta backups, we are obligated
to use time options such as OLDER THAN or OLDER.
+-DELETE--+-------------------------------------------------------+----------+
| +-+------------+---+----------------------------------+-+ |
| | +-TABLESPACE-+ +-KEEP--n--------------------------+ | |
| | +-FULL-------+ +-OLDER--+-------+---+-timestamp-+-+ | |
| | '-LOADCOPY---' | '-THAN--' '-n--days---' | | |
| | '-TAKEN AT--timestamp--------------' | |
| '-LOGS--+-------------------------+---------------------' |
| '-BETWEEN--sn1--AND--sn2--'
Picture
3: db2adutl delete option
DB2 Archive Logs
Control
The database archive log files are saved in a separate
management class: archive management class. A default setting for the archive
management class, affecting all archive files, causes them to never expire.
Archived log files have to be deleted manually.
Performing check for the archived
log files on the TSM server using db2adutl program:
>>more /opt/tivoli/tsm/client/ba/bin/inclexcl.def
include /ARTIST/NODE0000/S*.LOG MGMT_LOGS
>> dsmc query archive "/ARTIST/NODE0000/S*.LOG"
API 40.968.192 B 01.02.2004 09:01:03
/ARTIST/NODE0000/S0000001.LOG Log file for DB2 database ARTIST
API 40.968.192 B 01.02.2004 09:01:47
/ARTIST/NODE0000/S0000002.LOG Log file for DB2 database ARTIST
API 40.968.192 B 02.02.2004 03:02:18
/ARTIST/NODE0000/S0000003.LOG Log file for DB2 database ARTIST
API 40.968.192 B 03.02.2004 15:01:03
/ARTIST/NODE0000/S0000004.LOG Log file for DB2 database ARTIST
API 40.968.192 B 04.02.2004 02:01:47
/ARTIST/NODE0000/S0000005.LOG Log file for DB2 database ARTIST
Listing 9: Query archive log files using db2adutil
system command
> db2adutl DELETE LOGS BETWEEN S0000000 and S0000002
Query for database ARTIST
Retrieving LOG ARCHIVE information.
Log file: S0000000.LOG, Node: 1, Taken at: 2004-02-01-09.01.03
Do you want to delete this log image (Y/N)? Y
Are you sure (Y/N)? Y
Error: dsmEndTxn failed with ADSM reason code 27
Listing 10: Delete archive log files using
db2adutil system command
In the example above, deleting archived log files from the
TSM server, using db2adutl was unsuccessfully terminated with the reason code
27. As stated before, a standard TSM configuration prohibits the immediately deletion
of the archive log files from the TSM server, using db2adutil command, signaling
for a delete command as not authorized.
/* ----------------------------------------------------------------- */
/* User Exit Supported Return Codes */
/* NOTE: DB2 will reinvoke the user exit for the same request */
/* after 5 minutes if return code is 4 or 8. */
/* */
/* For other non-zero return codes, DB2 will not invoke */
/* user exit for the database for at least 5 minutes. */
/* If this request is to archive a log file, DB2 will not */
/* make another archive request for this file, or other */
/* log files produced during the 5 minute time period. */
/* These log files will only be archived when all */
/* applications disconnect from and the database, and the */
/* database is reopenned. */
/* ----------------------------------------------------------------- */
#define RC_OK 0 /* ok */
#define RC_RES 4 /* resource allocation error */
#define RC_OPATTN 8 /* operator/user attention required*/
#define RC_HARDWARE 12 /* hardware error */
#define RC_DEFECT 16 /* software error */
#define RC_PARM 20 /* invalid parameters */
#define RC_AUDIT 21 /* error open audit file */
#define RC_NOTFOUND 24 /* db2uext2() / file not found */
#define RC_UNKNOWN 28 /* unknown error */
#define RC_OPCAN 32 /* operator/user terminated */
Listing 11: User exit program return codes
An instance of the user exit archive log for the successfully
externalized archive log and for the error due to system error (error code 16):
>> vi USEREXIT.ERR | more
********************************************************************************
Time Started: Wed Jan 24 17:18:12 2004
Parameter Count: 8
Parameters Passed:
Database name: ARTIST
Logfile name: S0000378.LOG
Logfile path: /ARTIST/NODE0000/
Node number: NODE0000
Operating system: Solaris
Release: SQL07025
Request: ARCHIVE
System Action: ARCHIVE from /ARTIST/NODE0000/ file S0000378.LOG to ADSM for database ARTIST
Media Type: ADSM
User Exit RC: 0 ' saving archive log successfully executed
Time Completed: Wed Jun 24 17:18:12 2004
*******************************************************************************
Time of Error: Fri Jan 26 13:02:15 2004
Parameter Count: 8
Parameters Passed:
Database name: ARTIST
Logfile name: S0000394.LOG
Logfile path: /ARTIST/NODE0000/
Node number: NODE0000
Operating system: Solaris
Release: SQL07025
Request: ARCHIVE
Audit Log File: /home/sqllib/tsm/ARCHIVE.LOG
System Call Parms:
Media Type: TSM
User Exit RC: 16 ' software error
Listing 12: Archive database log files
The reason, for this behavior is quite obvious. Missing one
archive log file from many of saved on the TSM server, entirely disables the recovery
chain. Accidental deletion is not possible with the default setting. On the
other hand, we can change these default settings, enabling the database
administrator the purging rights for the database archive log files on the TSM
server.
Conclusion:
Regular
notes from IBM do not cover some "situations" which appear during regular
TSM usage. For this reason, I will pay special attention to a group of
interesting situations I have found working with the TSM, on the Sun Solaris
operating system, in the next article.
»
See All Articles by Columnist Marin Komadina