db2adutl - Managing Db2 objects within TSM command

Allows users to grant and revoke access to objects on a TSM server. Also allows users to query, extract, verify, and delete backup images, logs, and load copy images that are saved by using Tivoli® Storage Manager (TSM). The validation performed by this tool is capable of identifying many types of structural integrity problems, including data, index, and xml object page checksum validation failures, and anomalies in meta information of these pages.

However, it is not possible to identify all imaginable integrity problems. Some limitations include, but are not limited to:

Whether the version of a data page reflects what Db2 last wrote to the table space container file on disk (that is, a lost I/O write) is not identified.
Any logical discontinuity between data on a page and the objects correlated to this data, such as indexes, constraints, or MQTs, is not identified.
Whether the version of a data page resides in the location within the table space container file on disk where Db2 wrote it (that is. a misplaced I/O write), will not be detected if the misplaced data page resides in a different table space in a location where a page of the same objectID is expected.
The integrity of LOB or Long Field data pages are not validated.
For SMS tablespaces, integrity validation is limited to assuring page counts are correct per object, no further validation is performed.

On UNIX operating systems, this utility is located in the sqllib/adsm directory. On Windows operating systems, it is located in sqllib\bin.

Note: db2adutl is not supported on objects created in a higher Db2 Version release.

Authorization

None

Required connection

None

Command syntax

db2-object-options

QUERY-options

EXTRACT-options

UPLOAD-options

DELETE-options

VERIFY-options

verify-options

access-control-options

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.

UPLOAD

Uploads backup images or archived logs that are stored on disk to the TSM server. You must specify the database name when this option is used.

DELETE

Either deletes 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.

ALL: Displays all available information.
CHECK: Displays results of checkbits and checksums.
CHECKDMS: Performs extended page validation of DMS and Automatic Storage tablespace data pages. This option is not implied or enabled by the ALL option.
Along with the basic checksum and structural validation performed on the data pages, extended validation will attempt to validate if meta information within the page headers appear valid and reflect normal operating bounds.
DMS: Displays information from headers of DMS and Automatic Storage table space data pages.
HEADER: Displays the media header information.
HEADERONLY: Displays the same information as HEADER but reads the 4 K media header information from the beginning of the image only. It does not validate the image.
LFH: Displays the log file header (LFH) data.
OBJECT: Displays detailed information from the object headers.
PAGECOUNT: Displays the number of pages of each object type that is found in the image.
SGF: Displays the automatic storage paths in the image.
SGFONLY: Displays only the automatic storage paths in the image but does not validate the image.
TABLESPACES: Displays the table space details, including container information, for the table spaces in the image.
TABLESPACESONLY: Displays the same information as TABLESPACES but does not validate the image.

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 load copy images only.

LOGS

Includes archived logs only. For UPLOAD functionality, if WITH LOGS clause is specified, this option will not be allowed.

BETWEEN sn1 AND sn2

Specifies that the logs between log sequence number 1 and log sequence number 2 are to be used.

CHAIN n

Specifies the chain ID of the logs to be used.

SHOW INACTIVE

Includes backup objects that are deactivated.

SUBSET

Extracts pages from an image to a file. To extract pages, you need an input and an output file. The default input file is called extractPage.in. You can override the default input file name by setting the DB2LISTFILE environment variable to a full path. The format of the input file is as follows:

For SMS table spaces:

  S <tbspID> <objID> <objType> <startPage> <numPages>

Note:

<startPage> is an object page number that is object-relative.

For DMS table spaces:

  D <tbspID> <objType> <startPage> <numPages>

Note:

<objType> is only needed if verifying DMS load copy images.
<startPage> is an object page number that is pool-relative.

For log files:

   L <log num> <startPos> <numPages>

For other data (for example, initial data):

   O <objType> <startPos> <numBytes>

The default output file is extractPage.out. You can override the default output file name by setting the DB2EXTRACTFILE environment variable to a full path.

TAKEN AT timestamp

Specifies the time stamp of the loadcopy image or the backup image to be uploaded to TSM.

KEEP n

Deletes all objects of the specified type except for the most recent n by time stamp.

OLDER THAN timestamp or n days

Specifies that objects with a time stamp earlier than timestamp or n days are deleted.

OLDER THAN sn1

Specifies that objects with a sequence number less than sn1 are to be deleted.

AND REMOVE

Specifies that backup images and log files are to be removed after they are successfully uploaded to TSM.

LOADCOPY

Specifies loadcopy images that are to be uploaded to TSM. Even if you specify an image file name, db2adutl still attempts to query the history file. If a corresponding entry is found in the history file, db2adutl uploads the image only if the file name given matches the location in the history file. If a corresponding entry is not found, the image is uploaded directly from the specified path and no history file update is performed upon completion. If you specify the LOADCOPY with the UPLOAD option, you must specify the database name.

BACKUP

Specifies backup images that are to be uploaded to TSM. Even if you specify an image file name, db2adutl still attempts to query the history file. If a corresponding entry is found in the history file, db2adutl uploads the image only if the file name given matches the location in the history file. If a corresponding entry is not found, the image is uploaded directly from the specified path and no history file update is performed upon completion. If you specify the BACKUP with the UPLOAD option, you must specify the database name.

WITH LOGS

Specifies that archived logs are to be used along with the backup image. If this option is specified, LOGS will not be allowed as a subsequent option.

filename

Specifies the loadcopy image file name or the backup image file name. If you do not specify this option, you must specify the database name.

LOGARCHMETH1 or LOGARCHMETH2

Specifies the archive location for the log files to be uploaded. LOGARCHMETH1 is the default.

MGMTCLASS mgmtclass

Specifies a TSM management class where the upload occurs.

DB2USER db2_username

Specifies userid to be used for the Db2 connection that must be made to update the recovery history file.

DB2PASSWORD db2_password

Specifies password for userid to be used for the Db2 connection that must be made to update the recovery history file.

COMPRLIB decompression-library | ENCRLIB decryption-library

Indicates the name of the library that is used to decompress or decrypt a backup image. The path to the following libraries is $HOME/sqllib/lib.

Encryption libraries: libdb2encr.so (for Linux® or UNIX based operating systems); libdb2encr.a (for AIX®); and db2encr.dll (for Windows operating systems)
Compression library: libdb2compr.so (for Linux or UNIX based operating systems); libdb2compr.a (for AIX); and db2compr.dll (for Windows operating systems)
Encryption and compression libraries: libdb2compr_encr.so (for Linux or UNIX based operating systems); libdb2compr_encr.a (for AIX); and db2compr_encr.dll (for Windows operating systems)
Encryption and NX842 compression library: libdb2nx842_encr.a (for AIX)
ZLIB-based compression library: libdb2zcompr.so (for Linux or UNIX based operating systems); libdb2zcompr.a (for AIX); and db2zcompr.dll (for Windows operating systems)
Encryption and ZLIB-based compression libraries: libdb2zcompr_encr.so (for Linux or UNIX based operating systems); libdb2zcompr_encr.a (for AIX); and db2zcompr_encr.dll (for Windows operating systems)

The name must be a fully qualified path that refers to a file on the server. If this parameter is not specified, the Db2 database system attempts to use the library that is stored in the image. If the backup image is not compressed or encrypted, the value of this parameter is ignored. If the specified library cannot be loaded, the operation fails.

COMPROPTS decompression-options | ENCROPTS decryption-options

Describes a block of binary data that is passed to the initialization routine in the decompression or decryption library. The Db2 database system passes this string directly from the client to the server. Any byte reversal or code page conversion issues are handled by the library. If the first character of the data block is @, the remainder of the data is interpreted by the Db2 database system as the name of a file that is found on the server. The Db2 database system then replaces the contents of the data block with the contents of this file and passes the new value to the initialization routine instead. The maximum length for the string is 1024 bytes.

For the default Db2 libraries libdb2compr_encr.so, libdb2zcompr_encr.so, or libdb2nx842_encr.a (compression and encryption) or libdb2encr.so (encryption only), the format of the ENCROPTS variable is as follows:

Master Key Label=label-name

Note: The libdb2zcompr_encr.so library is available in Db2 11.5.7 and later versions.

The master key label is optional. If no master key label is specified, the database manager looks in the keystore for a master key label that was used to create the backup image. If you are using other libraries, the format of the ENCROPTS variable depends on those libraries.

-kspassword password

Specifies the password to use when opening the keystore.

-kspassarg fd:file_descriptor | filename:file_name

Specifies the keystore password arguments. The file_descriptor parameter specifies a file descriptor that identifies an open and readable file or pipe that contains the password to use. The file_name parameter specifies the name of the file that contains the password to use.

-ksprompt

Specifies that the user is to be prompted for a password.

DATABASE database_name

Considers only those objects that are associated with the specified database name.

DBPARTITIONNUM db-partition-number

Considers only those objects that are created by the specified database partition number. If a value for the parameter is not specified when using the QUERY, EXTRACT, DELETE, or VERIFY options, the db2adutl utility considers objects that are created by all database partitions. If a value for the parameter is not specified when using the UPLOAD option, the db2adutl utility considers only those objects that are created by the database partition to which the user is attached.

LOGSTREAM log-stream-number

Considers only those objects that belong to the specified log stream number. If a value for the parameter is not specified, the db2adutl utility considers objects that are created by all log streams. In single-partition and multi-partition environments, the DBPARTITIONNUM and LOGSTREAM options are equivalent.

OPTIONS tsm_options

Specifies options to be passed to the TSM server during the initialization of the TSM session. OPTIONS is passed to the TSM server exactly as it was entered, without the double quotation marks. When you use the OPTIONS parameter, the db2adutl command returns any errors that are generated by the TSM server.

PASSWORD password

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

NODENAME node_name

Considers only those images that are associated with a specific TSM node name.

Important: The NODENAME parameter and the OPTIONS parameter with the -asnodename value are not compatible and cannot be used at the same time. You must use the OPTIONS "-asnodename" parameter for TSM environments that support proxy nodes configurations, and use the NODENAME parameter for other types of TSM configurations.

OWNER owner

Considers only those objects that are created by the specified owner.

Important: The OWNER parameter and the OPTIONS parameter with the -asnodename value are not compatible and cannot be used at the same time. You must use the OPTIONS "-asnodename" parameter for TSM environments that support proxy nodes configurations, and use the OWNER parameter for other types of TSM configurations.

WITHOUT PROMPTING

The user is not prompted for verification before objects are deleted.

VERBOSE

Displays more file information.

GRANT ALL | USER user_name

Adds access rights to the TSM files on the current TSM node to all users or to the users specified. Granting access to users gives them access for all current and future files that are related to the database specified.

REVOKE ALL | USER user_name

Removes access rights to the TSM files on the current TSM node from all users or to the users specified.

QUERYACCESS

Retrieves the current access list. A list of users and TSM nodes is displayed.

ON ALL | NODENAME node_name

Specifies the TSM node for which access rights are changed.

FOR ALL | DATABASE database_name

Specifies the database to be considered.

Examples

The following example is sample output from the command

db2 backup database rawsampl use
tsm

  Backup successful. The timestamp for this backup is : 20031209184503

The following example is sample output from the command db2adutl query issued following the backup operation:

  Query for database RAWSAMPL

  Retrieving FULL DATABASE BACKUP information.
      1 Time: 20031209184403, Oldest log: S0000050.LOG, Sessions: 1
 
  Retrieving INCREMENTAL DATABASE BACKUP information.
    No INCREMENTAL DATABASE BACKUP images found for RAWSAMPL

  Retrieving DELTA DATABASE BACKUP information.
    No DELTA DATABASE BACKUP images found for RAWSAMPL

  Retrieving TABLESPACE BACKUP information.
    No TABLESPACE BACKUP images found for RAWSAMPL

  Retrieving INCREMENTAL TABLESPACE BACKUP information.
    No INCREMENTAL TABLESPACE BACKUP images found for RAWSAMPL

  Retrieving DELTA TABLESPACE BACKUP information.
    No DELTA TABLESPACE BACKUP images found for RAWSAMPL

  Retrieving LOCAL COPY information.
    No LOCAL COPY images found for RAWSAMPL
 
  Retrieving log archive information.
     Log file: S0000050.LOG, Chain Num: 0, Log stream: 0,
      Taken at 2003-12-09-18.46.13
     Log file: S0000051.LOG, Chain Num: 0, Log stream: 0,
      Taken at 2003-12-09-18.46.43
     Log file: S0000052.LOG, Chain Num: 0, Log stream: 0,
      Taken at 2003-12-09-18.47.12
     Log file: S0000053.LOG, Chain Num: 0, Log stream: 0,
      Taken at 2003-12-09-18.50.14
     Log file: S0000054.LOG, Chain Num: 0, Log stream: 0,
      Taken at 2003-12-09-18.50.56
     Log file: S0000055.LOG, Chain Num: 0, Log stream: 0, 
      Taken at 2003-12-09-18.52.39

The following example is sample output from the command

db2adutl delete full taken at
20031209184503 db rawsampl

  Query for database RAWSAMPL
 
  Retrieving FULL DATABASE BACKUP information. 
    Taken at: 20031209184503  Log stream: 0    Sessions: 1
    
    Do you want to delete this file (Y/N)? y
 
      Are you sure (Y/N)? y


  Retrieving INCREMENTAL DATABASE  BACKUP information.
    No INCREMENTAL DATABASE BACKUP images found for RAWSAMPL

  Retrieving DELTA DATABASE  BACKUP information.
    No DELTA DATABASE BACKUP images found for RAWSAMPL

The following example is sample output from the command db2adutl query issued following the operation that deleted the full backup image. Note the time stamp for the backup image.

 
  Query for database RAWSAMPL
 
  Retrieving FULL DATABASE BACKUP information.
      1 Time: 20031209184403, Oldest log: S0000050.LOG, Sessions: 1
 
  Retrieving INCREMENTAL DATABASE BACKUP information.
    No INCREMENTAL DATABASE BACKUP images found for RAWSAMPL

  Retrieving DELTA DATABASE BACKUP information.
    No DELTA DATABASE BACKUP images found for RAWSAMPL

  Retrieving TABLESPACE BACKUP information.
    No TABLESPACE BACKUP images found for RAWSAMPL

  Retrieving INCREMENTAL TABLESPACE BACKUP information.
    No INCREMENTAL TABLESPACE BACKUP images found for RAWSAMPL

  Retrieving DELTA TABLESPACE BACKUP information.
    No DELTA TABLESPACE BACKUP images found for RAWSAMPL

  Retrieving LOCAL COPY information.
    No LOCAL COPY images found for RAWSAMPL
 
  Retrieving log archive information.
     Log file: S0000050.LOG, Chain Num: 0, Log stream: 0,
      Taken at 2003-12-09-18.46.13
     Log file: S0000051.LOG, Chain Num: 0, Log stream: 0,
      Taken at 2003-12-09-18.46.43
     Log file: S0000052.LOG, Chain Num: 0, Log stream: 0,
      Taken at 2003-12-09-18.47.12
     Log file: S0000053.LOG, Chain Num: 0, Log stream: 0,
      Taken at 2003-12-09-18.50.14
     Log file: S0000054.LOG, Chain Num: 0, Log stream: 0,
      Taken at 2003-12-09-18.50.56
     Log file: S0000055.LOG, Chain Num: 0, Log stream: 0,
      Taken at 2003-12-09-18.52.39

The following example is sample output from the command

db2adutl
queryaccess for all

   Node                 User                 Database Name      type
   -------------------------------------------------------------------
   bar2                 jchisan              sample             B
   <all>                <all>                test               B
   -------------------------------------------------------------------
   Access Types: B - Backup images  L - Logs  A - both

The following example is sample output that is displayed from a backup image of 3 members in a Db2 pureScale® environment.

BufAddr   MemberNum PoolID Token Type Offset FileSize ...
--------  --------- ------ ----- ---- ------ -------- ...
00000000:         0      0     0   19      0      268 ...

Output (continued):


... ObjectSize OrigSize Object Name                               
... ---------- -------- -----------                               
...        268        0 "BACKUP.START.RECORD.MARKER"              
            
    numTbspsInDB  : 3
    numTbspsInImg : 3

    Total members : 3
    Member numbers: 0,1,2

Usage notes

One parameter from each of the following groups 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.

When you use proxy nodes in TSM environments, to see the backup images or the log archives taken when the proxy node was used, you must specify the OPTIONS parameter with the shared TSM proxy node value by using the asnodename option (for example OPTIONS "-asnodename=cluster1" ). The OPTIONS parameter is available starting in Version 9.8 Fix Pack 3 and later fix packs.

TSM grants delete access to the owner of the object or to the root user. It might restrict delete access to other users.

Each log file name has the following format:

S0******.LOG

Before Version 9.8 Fix Pack 3, the log files on the TSM server were written to the ./NODE0***/TESTLOG/C0******/ directory. In Version 9.8 Fix Pack 3 and later fix packs, the log files on the TSM server are written to the ./NODE****/LOGSTREAM****/C0******/ directory.

If the db2adutl utility encounters errors with TSM the actual TSM return code is displayed and the TSM documentation might be referred for troubleshooting steps.