Example: Querying and Restoring the Data and Log Backup Images to a Disk

You can query the DB2 backup history (data and log backups) details on SnapProtect using db2util.ps1 utility on Windows and db2util.sh on UNIX and restore these backup images to a disk from the query output.

You must be a valid DB2 User to run this DB2 Utility. The CommCell Console user must have the browse and view capability to query DB2 backup jobs.

Procedure

UNIX

  1. On the command line, type the following command.

    ./qlogin -cs <commserve_name> -u <commcell_user_name> -p <commcell_login_password>

  2. On the command line, type the following command to see the script options.

    ./db2util.sh -h

  3. On the command line, run the script to query the data or log backups and then restore the backups to disk.

    Usage

    ./db2util.sh -q LOG|DATA|ALL -d <db2_database_name> [-s <db2_instance_name>] [-c <client_name>] [-i <Simpana_instance_name>]

Query Options

The following options are available when you want to query SnapProtect for backup information.

Option Description
-q Query operation

This can be ALL for all data and logs; LOG for logs backup history and DATA for database backup history.

-c This is the client name presented on the CommCell Console. For a cluster setup, this client name is virtual machine client name.
-s This is the DB2 instance name  
-d This is the DB2 database name  
-i This is the SnapProtect instance name.  
-o Target database name. This is an optional option. If you do not provide this option, SnapProtect assumes the source database name to use.  
-r Retrieves the log files under the db2 retrieve path.
-h Prints usage information
-a Name of the file containing the script input parameters
-i SnapProtect Instance name. If no SnapProtect Instance is specified, Instance001 is used

Restore Options

The following options are available when you want to restore the data.

Option Description
-r Restore operation
LOG Restore Logs to retrieve directory
JOB Restore data and logs to a disk based on the backup job id. The logs will be restored if the backup job id contains logs.
-d Name of database to be restored
-j Backup job id for the data or log to be restored.
-f Start Log Sequence number
-t End Log sequence number
-p Directory to which backup job data has to be restored, valid for '-r JOB' case.
-s Source Db2 instance name
-c Source Client name
-n DPF To be specified for DPF clients
-i SnapProtect Instance name
-a Name of the file containing the script input parameters
-i SnapProtect Instance name. If no SnapProtect Instance is specified, Instance001 is used
  • When you restore the backup image that is on a disk (-r parameter with JOB), a new folder with this restore Job ID is created on the local computer in the directory that you specify in the -p option. The backup image and DB2 logs are restored under this Job ID folder. If you do not use the -p option, the system creates a new folder with the folder name dump_<JOB_ID> under the SnapProtect/Base directory. The utility restores the backup image to this directory. If you do not require the backup images, you must manually clean the dump_<JOB_ID> folder.
  • If you use -r JOB, the -f and -t for the range of DB22 logs are ignored.
  • This command line does not support the SnapProtect (non-traditional DB2 backup image) when you provide the Job ID. All Job IDs must be the traditional DB2 jobs.
  • If you use -r LOG, the -p target location is ignored. By default, the db2 retrieve path will be used for log files restore location. The -r LOG command request both -f and -t for db2 archive log beginning sequence # and last sequence #.
  • Use this query on a cross-machine restore to query and restore the backup image to the location. You must install the DB2 application and DB2 iDataAgent on the destination . The DB2 version must be the same or higher.
  • SnapProtect supports restoring the backup image to disk using db2util.sh for a single job id only. The last job id in one cycle will be only be considered. The multiple job IDs (one cycle) in command are ignored.
  • SnapProtect supports restoring the backup image to disk using db2util.sh if the backup has multiple streams. The backup job is restored to disk with multiple images which have the same timestamps as shown in the following example:

    TESTDB.0.DB2INST7.NODE0000.CATN0000.20130719155240.001
    TESTDB.0.DB2INST7.NODE0000.CATN0000.20130719155240.002

Input File Parameters

You can specify the following parameters in an input file.

Parameter Description
CVSRCCLIENTNAME Source client name
CVSRCDB2INSTANCENAME Source DB2 instance name
OPERATION Specify an operation QA/QL/QD(QUERY)or RL/RJ(RESTORE) to be performed on database
RESTOREDIR Directory to which backup job data has to be restored, valid for 'RJ' case.>
JOBID Backup job id for the data that will be restored
DBNAME Database name
APPLICATION DPF Only for DPF
FROMLSN The from log number
TOLSN The to log number

How to use this script to query the data, and log backups

Use the following command to query SnapProtect and then display the data and log files for the DB2 instance user that is currently logged in to the specified DB2 database.

./db2util.sh -q LOG|DATA|ALL -d <db2_db_name> [-s <db2_instance_name>] [-c <client_name>] [-i <Simpana_instance_name>]

How to use this script to restore to a disk

Use the following command and options to restore a backup image to a disk:

./db2util.sh -r JOB|LOG -d <dbname> <[-j <jobId>]|[-f <from seq> -t <to seq>]> [-p <restore directory>] [-s <db2 instance name>] [-c <client name>] [-n DPF] [-i <SnapProtect_instance>]

How to use this script to use the input file option

Use the following command and options to restore a backup image to a disk, by using an input file that includes all the commands.

./db2util.sh -a <option file> [-i <SnapProtect_instance>]

Example

# su - db2inst5
$ cd /opt/SnapProtect/iDataAgent
$ ./db2util.sh -q a -d testdb -i Instance001

Input:: DBNAME=testdb INSTANCE=Instance001

Querying Database: testdb

QScript[QS_querydb2backup] CS[dbserve20] DB[CommServ] SP[QS_querydb2backup]

Qscript Output:

jobId bkupLevel NodeNumber timeStamp streams
----- --------- ---------- --------- -------

Qscript Execution Succeeded!


QScript[QS_querydb2backup] CS[dbserve20] DB[CommServ] SP[QS_querydb2backup]

Qscript Output:

jobId nodeNumber chainNumber firstLogNumber lastLogNumber firstLogTime lastLogTime
----- ---------- ----------- -------------- ------------- ------------ -----------

Qscript Execution Succeeded!

$ cd /opt/SnapProtect/iDataAgent
$ ./db2_util.sh -q a -d testdb -i Instance001

Input:: DBNAME=testdb INSTANCE=Instance001

Querying Database: testdb

QScript[QS_querydb2backup] CS[dbserve20] DB[CommServ] SP[QS_querydb2backup]

Qscript Output:

jobId bkupLevel NodeNumber timeStamp streams
----- --------- ---------- --------- -------
2742 Full NODE0000 20130204124812 1
2745 Incr NODE0000 20130204125352 1
2748 Delta NODE0000 20130204125907 1
2763 Full NODE0000 20130204133429 1

Windows

Perform the following before you query for the DB2 data and log backup details and restore them to a disk:

  1. Start the powershell prompt. You can type powershell from the Windows prompt or you can start it from the command line prompt.
  2. On the command line, type the following command.

    ./qlogin -cs <commserve_name> -u <commcell_user_name> -p <commcell_login_password>

Use the following steps to run this script:

  1. Enable the powerShell script execution on the system:

    set db2instance=<db2_instance_name>
    powershell
    powershell Set-ExecutionPolicy Unrestricted

  2. Type the following command to get the usage information.

    PS C:\Program Files\CV\SnapProtect\iDataAgent> db2util.ps1

  3. On the command line, run the script to query the data or log backups and then restore the backups to disk.

    Usage

    db2util.ps1 -q LOG|DATA|ALL -d <db2_database_name> [-s <db2_instance_name>] [-c <client_name>] [-i <Simpana_instance_name>]

  4. After the job completes, use the qlogout command.

    Query Options

    The following options are available when you want to query SnapProtect for backup information.

    Option Description
    -q Query operation

    This can be ALL for all data and logs; LOG for logs backup history and DATA for database backup history.

    -c This is the client name presented on the CommCell. For a cluster setup, this client name is virtual machine client name.
    -s This is the DB2 instance name
    -d This is the DB2 database name
    -i This is the SnapProtect instance name.
    -o Target database name. This is an optional option. If you do not provide this option, SnapProtect assumes the source database name to use.
    -r Retrieves the log files under the db2 retrieve path.
    -h Prints usage information

    Restore Options

    The following options are available when you want to restore the data.

    Option Description
    -r Restore operation
    LOG Restore Logs to retrieve directory
    JOB Restore data and logs to a disk based on the backup job id. The logs will be restored if the backup job id contains logs.
    -d Name of database to be restored
    -j Backup job id for the data or log to be restored.
    -f Start Log Sequence number
    -t End Log sequence number
    -p Directory to which backup job data has to be restored, valid for '-r JOB' case.
    -s Source Db2 instance name
    -c Source Client name
    -n DPF To be specified for DPF clients
    -i SnapProtect Instance name
    • When you you restore the backup image that is on a disk (-r parameter with JOB), a new folder with this restore Job ID is created on the local computer in the directory that you specify in the -p option. The backup image and DB2 logs are restored under this Job ID folder. If you do not use the -p option, the system creates a new folder with the folder name dump_<JOB_ID> under the SnapProtect\Base directory. The utility restores the backup image to this directory. If you do not require the backup images, you must manually clean the dump_<JOB_ID> folder.
    • If you use -r JOB, the -f and -t options for the range of DB2 logs are ignored.
    • This command line does not support the SnapProtect (non-traditional DB22 backup image) when you provide the Job ID. All Job IDs must be the traditional DB2 jobs.
    • If you use -r LOG, the -p target location is ignored. By default, the db2 retrieve path will be used for log files restore location. The -r LOG command request both -f and -t for db2 archive log beginning sequence # and last sequence #.
    • Use this query on a cross-machine restore to query and restore the backup image to the location. You must install the DB2 application and DB2 iDataAgent on the destination . The DB2 version must be the same or higher.
    • SnapProtect supports restoring the backup image to disk using db2util.ps1 for a single job id only. The last job id in one cycle will be only be considered. The multiple job IDs (one cycle) in the command are ignored.
    • SnapProtect supports restoring the backup image to disk using db2util.ps1 if the backup has multiple streams. The backup job is restored to disk with multiple images which have the same timestamps as shown in the following example:

      TESTDB.0.DB2INST7.NODE0000.CATN0000.20130719155240.001
      TESTDB.0.DB2INST7.NODE0000.CATN0000.20130719155240.002

    Input File Parameters

    You can specify the following parameters in an input file.

    Parameter Description
    CVSRCCLIENTNAME Source client name
    CVSRCDB2INSTANCENAME Source DB2 instance name
    OPERATION Specify an operation QA/QL/QD(QUERY)or RL/RJ(RESTORE) to be performed on database
    RESTOREDIR Directory to which backup job data has to be restored, valid for 'RJ' case.>
    JOBID Backup job id for the data that will be restored
    DBNAME Database name
    APPLICATION DPF Only for DPF
    FROMLSN The from log number
    TOLSN The to log number

    How to use this script to query the data, and log backups

    Use the following command to query SnapProtect and then display the data and log files for the DB2 instance user that is currently logged in to the specified DB2 database.

    db2util.ps1 -q LOG|DATA|ALL -d <dbname> [-s <db2 instance name>] [-c <client name>] [-i <SnapProtect_instance>]

    How to use this script to restore to a disk

    Use the following command to restore a backup image to a disk:

    db2util.ps1 -r JOB|LOG -d <dbname> [-j <jobId>]|[-f <from seq> -t <to seq>] [-p <restore directory>] [-s <db2 instance name>] [-c <client name>][-n DPF] [-i <SnapProtect_instance>]

        How to use this script to use the input file option

        Use the following command to use an input options file which include all the query and restore commands for execution:

db2util.ps1 -a <option file> [-i <SnapProtect_instance>]

Example:

PS C:\Program Files\CV\Simpana22\iDataAgent> .\db2util.ps1 -q a -d testdb -i Instance001

Input:: DBNAME=testdb INSTANCE=Instance001

Querying Database: testdb

QScript[QS_querydb2backup] CS[dbserve20] DB[CommServ] SP[QS_querydb2backup]

Qscript Output:

jobId bkupLevel NodeNumber timeStamp streams
----- --------- ---------- --------- -------

Qscript Execution Succeeded!


QScript[QS_querydb2backup] CS[dbserve20] DB[CommServ] SP[QS_querydb2backup]

Qscript Output:

jobId nodeNumber chainNumber firstLogNumber lastLogNumber firstLogTime lastLogTime
----- ---------- ----------- -------------- ------------- ------------ -----------

Qscript Execution Succeeded!

PS C:\Program Files\CV\Simpana22\iDataAgent> .\db2util.ps1 -q a -d testdb30 -i Instance001

Input:: DBNAME=testdb30 INSTANCE=Instance001

Querying Database: testdb30

QScript[QS_querydb2backup] CS[dbserve20] DB[CommServ] SP[QS_querydb2backup]

Qscript Output:

jobId bkupLevel NodeNumber timeStamp streams
----- --------- ---------- --------- -------
2742 Full NODE0000 20130204124812 1
2745 Incr NODE0000 20130204125352 1
2748 Delta NODE0000 20130204125907 1
2763 Full NODE0000 20130204133429 1