Restoring DB2 Tables to a Different Database on the Same Instance on the Source Host

Use this procedure to restore the DB2 tables to a different database on the same instance on the source host.

Note: When you restore a DB2 table, you can restore to any existing database schema on the destination.

You can view the schema and table combination after the restore by using SQL commands.

Example

You restore db2inst5.table1 to db2inst6.table1.

After the restore, run the following command to see the change.

db2inst6>db2 “select * from db2inst5.table1” 
 EMPNO             ENAME                                                 ADD1
  ----------- -------------------------------------------------- ---------------- 
 1                 Jane Doe                                          Any Street 
 2                 Fred Bloggs                                       Any Street

Before You Begin

Perform a table level backup.
Configure the database on the CommCell Console host that you use for the restore.
The restore uses a staging path on the target and places all input and output files in the dump file path. These paths must have read and write access for the DB2 user.

Procedure

From the CommCell Browser, expand Client Computers > client > DB2 >instance.
Right-click the backup_set, point to All Tasks and select Browse and Restore.
Select the Table View check box and click View Content.

Optional: You can include or exclude any of the following tables in the restore, dependent, referenced and recursively dependent tables.

You can view the table architecture in the left pane. The architecture has the following format:

\+table view+\<db2_instance_name>\<db2_db_name>\<db2_db_schema_name>\<db2_table_names>

Right-click the table, and choose whether to include or exclude dependent or referenced tables.

Option	Description
Deselect All Dependent Tables	Exclude all dependent tables
Select All Dependent Tables	Include all dependent tables
Deselect All Dependent Tables	Exclude all recursive referenced tables
Select All Dependent Tables	Include all recursive referenced tables
Deselect All Referenced Tables	Exclude all referenced tables
Select All Referenced Tables	Include all referenced tables

From the Browse window, select the tables to restore and click Recover All Selected.

The Table Restore Options dialog box appears.
On the General tab, the selected tables are listed in the Users/Tables selected for restore box.

Optional: On the General tab, choose whether to use an auxiliary database:

The Commvault software uses the auxiliary database to export the tables. You can restore the tables to an auxiliary database regardless of whether the database exists.

Option	Steps
Use an auxiliary database	Select the Auxiliary Database check box. Select the auxiliary database host from the Destination Computer list. Select the auxiliary database instance from the Destination Instance list. Enter the auxiliary database name in the Target Database Name box. Enter the full path for the target database in the Target Database Path box. Enter the full path to the directory for the table dump files in the Export Destination for Table Dump Files box. The dump file directory contains the export, import and table creation information. The information is stored for reuse. Warning: If the auxiliary database exists, the database is overwritten to the Target Database Path.
Do not use an auxiliary database	If you choose the option to not use an auxiliary database, the Commvault software automatically creates a temporary auxiliary database with the name j<job_id> on the same DB2 instance as the source database and then uses it for the restore. Enter the full path for the target database in the Target Database Path box. Note: The directory must be empty. Enter the full path to the directory for the table dump files in the Export Destination for Table Dump Files box.

Option

Steps

Use an auxiliary database

Select the Auxiliary Database check box.
Select the auxiliary database host from the Destination Computer list.
Select the auxiliary database instance from the Destination Instance list.
Enter the auxiliary database name in the Target Database Name box.
Enter the full path for the target database in the Target Database Path box.
Enter the full path to the directory for the table dump files in the Export Destination for Table Dump Files box.

The dump file directory contains the export, import and table creation information. The information is stored for reuse.

Warning: If the auxiliary database exists, the database is overwritten to the Target Database Path.

Do not use an auxiliary database

If you choose the option to not use an auxiliary database, the Commvault software automatically creates a temporary auxiliary database with the name j<job_id> on the same DB2 instance as the source database and then uses it for the restore.

Enter the full path for the target database in the Target Database Path box.

Note: The directory must be empty.
Enter the full path to the directory for the table dump files in the Export Destination for Table Dump Files box.

Optional: To have the Commvault software automatically clean the auxiliary database when the restore is successful, select the Clean Up Auxiliary Database after Export check box.

The database is stored in the DB2 system. The software reuses the database if the restore job fails.
On the Table Restore Options tab, select the restore location:
1. Select the Import to a different DB option.
2. Select a host other than the source host from the Import Destination Computer list.
3. Select the source instance from the Import Destination Instance list.
4. Enter the database name in the Import Database Name box.
  
  The database name must be configured in the CommCell Console.
5. Enter the DB2 user name and password.
6. Enter the DB2 import instance port number in the Database Server Port box.
  
  The software makes the Database Server Port box available when you select the Use an auxiliary database option. For information on how to find the DB2 Instance port number, see How do I Find Out the DB2 Instance Port Number.
Optional: To delete the tables prior to the restore, on the Table Restore options tab, select the Drop Table for Import check box.

Note: By default, when you restore tables, the restore operation replaces all existing data if the same tables exist on the destination database. You can select the Drop Table for Import option to have the software drop and recreate the tables. If you have the same table name but a different table structure on destination, you have to drop the tables before the restore.
Optional: To generate the Data Definition Language (DDL) authorization statements for the tables, on the Table Restore options tab, select the Generate Authorization DDL check box.
Optional: To add additional parameters to the DDL statements, on the Table Restore options tab, select the Specify additional db2look parameters check box and enter the parameters.

For additional information on db2look, go to the DB2 Knowledge Center, db2look.

To set the DB2 grant privilege on the restored table, select the Specify additional db2look parameters check box and enter -x as the parameter.
On the Restore Options tab, select the restore time option.

By default the database tables are restored to the current time.
- To restore the database tables to the end of the logs (the current time), select the To the End of the logs option.
- To restore the database tables to a point-in-time, select the To a Point in Time option and enter the date and time.
- To restore the database tables to the earliest time during a rollforward operation that the database was consistent, select the To End of Backup option.
  
  For additional information on this option, go to the IBM website, END OF BACKUP.
Click OK to close the Table Restore Options dialog box.