System Commands

db2adutl?Work with TSM Archived Images

This utility allows users to query, extract, verify, and delete backup images, logs, and load copy images saved using Tivoli Storage Manager (formerly ADSM). On UNIX-based operating systems, this utility is located in the sqllib/adsm directory. On Windows, it is located in sqllib\bin.

Usage:

• Anyone can access the utility.

• No connection to the database is required.

• Command parameters:

  QUERY: Queries the TSM server for DB2 objects

  EXTRACT: Copies DB2 objects from the TSM server to the current directory on the local machine

  DELETE: Either deactivates backup objects or deletes log archives on the TSM server

  VERIFY: Performs consistency checking on the backup copy that is on the server. This parameter causes the entire backup image to be transferred over the network.

  TABLESPACE: Includes only table space backup images

  FULL: Includes only full database backup images

  NONINCREMENTAL: Includes only non-incremental backup images

  INCREMENTAL: Includes only incremental backup images

  DELTA: Includes only incremental delta backup images

  LOADCOPY: Includes only load copy images

  LOGS: Includes only log archive images

  BETWEEN sn1 AND sn2: Specifies that the logs between log sequence number 1 and log sequence number 2 are to be used

  SHOW INACTIVE: Includes backup objects that have been deactivated

  TAKEN AT timestamp: Specifies a backup image by its timestamp

  KEEP n: Deactivates all objects of the specified type except for the most recent n by timestamp

  OLDER THAN timestamp or n days: Specifies that objects with a timestamp earlier than timestamp or n days will be deactivated

  DATABASE database_name: Considers only those objects associated with the specified database name.

  DBPARTITIONNUM db-partition-number: Considers only those objects created by the specified database partition number

  PASSWORD password: Specifies the TSM client password for this server, if required. If a database is specified and the password is not provided, the value specified for the tsm_password database configuration parameter is passed to TSM; otherwise, no password is used.

  NODENAME node_name: Considers only those images associated with a specific TSM node name

  WITHOUT PROMPTING: The user is not prompted for verification before objects are deleted

  OWNER owner: Considers only those objects created by the specified owner

  VERBOSE: Displays additional file information

Notes: One parameter from each group below can be used to restrict what backup images types are included in the operation:

• Granularity

  • FULL? include only database backup images.

  • TABLESPACE? include only table space backup images.

• Cumulativeness

  • NONINCREMENTAL? include only non-incremental backup images.

  • INCREMENTAL? include only incremental backup images.

  • DELTA? include only incremental delta backup images.

• Compatibilities

  • For compatibility with versions earlier than Version 8, the key word NODE can be substituted for DBPARTITIONNUM.

Take a database backup using TSM:

$ db2 backup database sample use tsm

Backup successful. The timestamp for this backup is : 20020811130942

Output from db2adutl:

$ db2adutl query

Query for database SAMPLE
Retrieving full database backup information.
full database backup image: 1, Time: 20020811130942,
Oldest log: S0000028.LOG, Sessions used: 1
full database backup image: 2, Time: 20020811142241,
Oldest log: S0000029.LOG, Sessions used: 1
Retrieving table space backup information.
table space backup image: 1, Time: 20020811094003,
Oldest log: S0000026.LOG, Sessions used: 1
table space backup image: 2, Time: 20020811093043,
Oldest log: S0000025.LOG, Sessions used: 1
table space backup image: 3, Time: 20020811105905,
Oldest log: S0000027.LOG, Sessions used: 1
Retrieving log archive information.
Log file: S0000025.LOG
Log file: S0000026.LOG
Log file: S0000027.LOG
Log file: S0000028.LOG
Log file: S0000029.LOG
Log file: S0000030.LOG
$ db2adutl delete full taken at 20020811093043 db sample

Query for database SAMPLE
Retrieving full database backup information. Please wait.
full database backup image:
SAMPLE.0.v8inst.NODE0000.CATN0000.20020811093043.001
Do you want to deactivate this backup image (Y/N)? y
Are you sure (Y/N)? y

$ db2adutl query

Query for database SAMPLE
Retrieving full database backup information.
full database backup image: 1, Time: 20020811130942,
Oldest log: S0000028.LOG, Sessions used: 1
full database backup image: 2, Time: 20020811142241,
Oldest log: S0000029.LOG, Sessions used: 1
Retrieving table space backup information.
table space backup image: 1, Time: 20020811094003,
Oldest log: S0000026.LOG, Sessions used: 1
table space backup image: 3, Time: 20020811105905,
Oldest log: S0000027.LOG, Sessions used: 1
Retrieving log archive information.
Log file: S0000026.LOG
Log file: S0000027.LOG
Log file: S0000028.LOG
Log file: S0000029.LOG
Log file: S0000030.LOG

db2ckbkp?Check Backup

This utility can be used to test the integrity of a backup image or multiple parts of a backup image and to determine whether the image can be restored. It can also be used to display the metadata stored in the backup header.

Usage:

• Anyone can access the utility, but users must have read permissions on image backups in order to execute this utility against them.

• No connection to the database is required.

• Command parameters:

  - a: Displays all available information
  
  

  - c: Displays results of checkbits and checksums
  
  

  - d: Displays information from the headers of DMS table space data pages
  
  

  - h: Displays media header information, including the name and path of the image expected by the restore utility
  
  

  - H: Displays the same information as ?h but reads only the 4-KB media header information from the beginning of the image. It does not validate the image. This option is not valid in combination with any other option.
  
  

  - l: Displays log file header data
  
  

  - n: Prompts for tape mount; assume one tape per device
  
  

  - o: Displays detailed information from the object headers
  
  

  - p: Displays the number of pages of each object type
  
  

  image_name: The name of the backup image file. One or more files can be checked at a time.

Notes:

• If the complete backup consists of multiple objects, the validation will succeed only if db2ckbkp is used to validate all of the objects at the same time.

• If a backup image was created using multiple sessions, db2ckbkp can examine all of the files at the same time. Users are responsible for ensuring that the session with sequence number 001 is the first file specified.

• This utility can also verify backup images that are stored on tape (except images that were created with a variable block size). This is done by preparing the tape as for a restore operation, then invoking the utility, specifying the tape device name.

  For example, on UNIX: db2ckbkp ?h /dev/rmt0

  Or on Windows: db2ckbkp ?d \\.\tape1

• If the image is on a tape device, specify the tape device path. You will be prompted to ensure it is mounted, unless option '?n' is given. If there are multiple tapes, the first tape must be mounted on the first device path given. (That is the tape with sequence 001 in the header.)

Take a database backup:

$ db2 backup database sample to /data/dbbackup

Backup successful. The timestamp for this backup image is : 20020828231900

The following is sample output from db2ckbkp:

$ db2ckbkp  /data/dbbackup/SAMPLE.0.v8inst.NODE0000.CATN0000.001

[1] Buffers processed:  #####
Image Verification Complete - successful.

$ db2ckbkp ?h /data/dbbackup/SAMPLE.0.v8inst.NODE0000.CATN0000.001

=====================
MEDIA HEADER REACHED:
=====================
    Server Database Name           -- SAMPLE
    Server Database Alias          -- SAMPLE
    Client Database Alias          -- SAMPLE
    Timestamp                      -- 20020828231900
    Database Partition Number      -- 0
    Instance                       -- v8inst
    Sequence Number                -- 1
    Release ID                     -- A00
    Database Seed                  -- 719E1F66
    DB Comment's Codepage (Volume) ?- 0
    DB Comment (Volume)            --
    DB Comment's Codepage (System) ?- 0
    DB Comment (System)            --
    Authentication Value           -- 255
    Backup Mode                    -- 0
    Backup Type                    -- 0
    Backup Gran.                   ?- 0
    Status Flags                   -- 1
    System Cats inc                -- 1
    Catalog Partition Number       -- 0
    DB Codeset                     -- ISO8859-1
    DB Territory                   --
    Backup Buffer Size             -- 4194304
    Number of Sessions             -- 1
    Platform                       -- 4

The proper image file name would be:
SAMPLE.0.v8inst.NODE0000.CATN0000.20020828231900.001
[1] Buffers processed:  #####
Image Verification Complete - successful.

db2ckrst?Check Incremental Restore Image Sequence

This utility allows the user to query the database history in order to generate a suggested sequence of backup image timestamps. It also gives a simplified restore syntax needed for a manual restore.

Usage:

• Anyone can access the utility.

• No connection to the database is required.

• Command parameters:

  - d database name /alias: Specifies the alias name for the database that will be restored
  
  

  - t timestamp: Specifies the timestamp for a backup image that will be incrementally restored
  
  

  - r: Specifies the type of restore that will be executed. The default is database.
  
  

  - n tablespace name: Specifies the name of one or more table spaces that will be restored
  
  

  - h/?u/??: Displays help information. When this option is specified, all other options are ignored, and only the help information is displayed.
  
  

Notes:

• The database history must exist in order for this utility to be used. If the database history does not exist, specify the HISTORY FILE option in the RESTORE command before using this utility.

• If the FORCE option of the PRUNE HISTORY command is used, you will be able to delete entries that are required for recovery from the most recent, full database backup image. The default operation of the PRUNE HISTORY command prevents required entries from being deleted. It is recommended that you do not use the FORCE option of the PRUNE HISTORY command.

• This utility should not be used as a replacement for keeping records of your backups.

The following is sample output from db2ckrst:

$ db2ckrst ?d sample ?t 20020828233730 ?r database

Suggested restore order of images using timestamp 20020828233730 for database sample.
=================================
restore db sample incremental taken at 20020828233730
restore db sample incremental taken at 20020828233647
restore db sample incremental taken at 20020828233730
=================================

$ db2ckrst ?d sample ?t 20020828233730 ?r  tablespace ?n userspace1

Suggested restore order of images using timestamp 20020828233730 for database sample.
=================================
restore db sample tablespace ( USERSPACE1 ) incremental taken at 20020828233730
restore db sample tablespace ( USERSPACE1 ) incremental taken at 20020828233647
restore db sample tablespace ( USERSPACE1 ) incremental taken at 20020828233730
=================================

db2flsn?Find Log Sequence Number

This utility returns the log file name that contains the log record identified by the given log sequence number (LSN). The input LSN must be a string of length 12 or 16 representing the LSN in hex.

Usage:

• Anyone can access the utility.

• No connection to the database is required.

• Command parameters:

  - q: Specifies that only the log file name be printed. No error or warning messages will be printed, and status can be determined only through the return code. Valid error codes are:
  
  

  • 100 Invalid input

  • 101 Cannot open LFH file

  • 102 Failed to read LFH file

  • 103 Invalid LFH

  • 104 Database is not recoverable

  • 105 LSN too big

  • 500 Logical error

  - Other valid return codes are:

  • 0 Successful execution

  • 99 Warning: the result is based on the last known log file size.

Notes:

• The log header control file SQLOGCTL.LFH must reside in the current directory.

• Since this file is located in the database directory, the tool can be run from the database directory, or the control file can be copied to the directory from which the tool will be run.

• The tool uses the LOGFILSIZ database configuration parameter. DB2 records the three most recent values for this parameter and the first log file that is created with each LOGFILSIZ value; this enables the tool to work correctly when LOGFILSIZ changes. If the specified LSN predates the earliest recorded value of LOGFILSIZ, the tool uses this value and returns a warning. This tool can be used only with recoverable databases. A database is recoverable if it is configured with LOGRETAIN set to ON or USEREXIT set to ON.

The following is sample output from these commands:

$ db2flsn 000000BF0030
Given LSN is contained in log file S0000002.LOG
$ db2flsn ?q 000000BF0030
S0000002.LOG

$ db2flsn 000000BE0030
Warning: the result is based on the last known log file size.
The last known log file size is 23 4K pages starting from log extent 2.
Given LSN is contained in log file S0000001.LOG

$ db2flsn ?q 000000BE0030
S0000001.LOG

db2inidb?Initialize a Mirrored Database

This utility initializes a mirrored database in a split mirror environment. The mirrored database can be initialized as a clone of the primary database, placed in rollforward pending state, or used as a backup image to restore the primary database.

Usage:

• User must have one of the following: sysadm, sysctrl, or sysmaint.

• No connection to the database is required.

• Command parameters:

  database_alias: Specifies the alias of the database to be initialized

  SNAPSHOT: Specifies that the mirrored database will be initialized as a clone of the primary database

  STANDBY: Specifies that the database will be placed in rollforward pending state. New logs from the primary database can be fetched and applied to the standby database. The standby database can then be used in place of the primary database if it goes down.

  MIRROR: Specifies that the mirrored database is to be used as a backup image, which can be used to restore the primary database

  RELOCATE USING config_file: Specifies that the database files are to be relocated, based on the information listed in the configuration file

Sample command from db2inidb:

$ db2inidb <database_alias> as < snapshot | standby |mirror > [ relocate using <config_file> ]

$ db2initdb sample as snapshot