1 - You need to know in witch tablespace the table you need to restore exist:

Command: db2 "select rtrim(TABSCHEMA) as TABSCHEMA, rtrim(TABNAME) as TABNAME, rtrim(TBSPACE) as TBSPACE from syscat.tables where TABNAME='<table name>'"

db2 "select rtrim(TABSCHEMA) as TABSCHEMA, rtrim(TABNAME) as TABNAME, \
rtrim(TBSPACE) as TBSPACE from syscat.tables where TABNAME='TB_NAME'"

where TB_NAME = the name of the table you need to restore.

The output will give you the table schema, table name and table space name like:

TABSCHEMA TABNAME TBSPACE

---------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------

DB2INST1 DEPARTMENT USERSPACE1

2 - Checking in witch node the tablespace exist.

Command: db2 "select tbspace, tbspaceid, ngname from syscat.tablespaces where tbspace='TBS_NAME'"

db2 "select tbspace, tbspaceid, ngname from syscat.tablespaces where tbspace='TBS_NAME'"

where TBS_NAME = the name of the tablespace that your table resides.

TBSPACE TBSPACEID NGNAME
------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------
USERSPACE1 2 IBMDEFAULTGROUP

With the NGNAME you can check in SYSCAT.DBPARTITIONGROUPDEF what are the nodes the tablespace exist.

Command:  db2 "select * from SYSCAT.DBPARTITIONGROUPDEF where dbpgname='NG_NAME'"

 db2 "select * from SYSCAT.DBPARTITIONGROUPDEF where dbpgname='NG_NAME'"

where NG_NAME = the name of the database partition group that contains the database partition.

DBPGNAME DBPARTITIONNUM IN_USE

-------------------------------------------------------------------------------------------------------------

IBMDEFAULTGROUP 0 Y

In this case the IBMDEFAULTGROUP exist only in node 0. If it exist on nodes 0, 1, 2 and 3. The output would be:

DBPGNAME DBPARTITIONNUM IN_USE

-------------------------------------------------------------------------------------------------------------

IBMDEFAULTGROUP 0 Y

IBMDEFAULTGROUP 1 Y

IBMDEFAULTGROUP 2 Y

IBMDEFAULTGROUP 3 Y

4 - Creating the restore command for each node.

for example, restoring tablespace USERSPACE1 from SAMPLE database on nodes 0 to 2.

export DB2NODE=0
db2 terminate
date
echo "Restoring node $DB2NODE"
db2 "restore database SAMPLE tablespace ( USERSPACE1 ) online taken at <timestamp_Node0> without prompting"
date

export DB2NODE=1
db2 terminate
date
echo "Restoring node $DB2NODE"
db2 "restore database SAMPLE tablespace ( USERSPACE1 ) online taken at <timestamp_Node1> without prompting"
date

export DB2NODE=2
db2 terminate
date
echo "Restoring node $DB2NODE"
db2 "restore database SAMPLE tablespace ( USERSPACE1 ) online taken at <timestamp_Node2> without prompting"
date

where

timestamp_Node0 = backup timestamp for node 0.

timestamp_Node1 = backup timestamp for node 1.

timestamp_Node2 = backup timestamp for node 2.

5 - Tips

1. If the instance file system is running on more than one server, you can create a script for each server and start the scripts in parallel. You can check the nodes for each server in the file" ..sqllib/db2nodes.cfg file.

2. Before start the restore, you must check if there are others tables in the tablespace you want to restore. You can use the following command:

db2 "select tabname, tbspaceid from syscat.tables where tbspaceid='<table_space_id>'"

3. The rollforward command should be issued only in the catalog node. You can check what is the catalog node with the command:

db2 list db directory

Database 3 entry:

 Database alias                       = SAMPLE

 Database name                        = SAMPLE

 Local database directory             = /home/db2inst1

 Database release level               = c.00

 Comment                              =

 Directory entry type                 = Indirect

 Alternate server hostname            =

 Alternate server port number         =