1 - Our environment.

partitioned database name: Sample

number of partitions: 3

table to be restored: department

2 - 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

3 - 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 and 2. The output would be:

DBPGNAME DBPARTITIONNUM IN_USE

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

IBMDEFAULTGROUP 0 Y

IBMDEFAULTGROUP 1 Y

IBMDEFAULTGROUP 2 Y

4 - Getting the restore timestamps for each node.

Check in the TSM server the backup timestamps for each node. To check from a specific server, run the command with rah.

Command: db2 adutl query db < DB_NAME >

db2 adutl query db SAMPLE

Query for database SAMPLE

Retrieving FULL DATABASE BACKUP information.

    1. Time: 20090316205138 Oldest log: S0000010.LOG  DB Partition Number: 0 Sessions: 1

    2. Time: 20090316202049 Oldest log: S0000010.LOG  DB Partition Number: 2 Sessions: 1

    3. Time: 20090316194052 Oldest log: S0000010.LOG  DB Partition Number: 1 Sessions: 1

Retrieving INCREMENTAL DATABASE BACKUP information.

    1. Time: 20090319092209 Oldest log: S0000010.LOG  DB Partition Number: 0 Sessions: 1

    2. Time: 20090319091502 Oldest log: S0000010.LOG  DB Partition Number: 2 Sessions: 1

    3. Time: 20090319090741 Oldest log: S0000010.LOG  DB Partition Number: 1 Sessions: 1

Save the INCREMENTAL DATABASE BACKUP information in a file, for example:

db2inst1@home:~$ cat backupTimes.temp

1. Time: 20090319092209 Oldest log: S0000010.LOG  DB Partition Number: 0 Sessions: 1

2. Time: 20090319091502 Oldest log: S0000010.LOG  DB Partition Number: 2 Sessions: 1

3. Time: 20090319090741 Oldest log: S0000010.LOG  DB Partition Number: 1 Sessions: 1

And run the the following command:

Command: awk '{ print "export DB2NODE="$10";db2 terminate > /dev/null; echo NODE $DB2NODE; db2ckrst -d SAMPLE -t", $3, "-r tablespace -n USERSPACE1" }'

awk '{ print "export DB2NODE="$10";db2 terminate > /dev/null; echo
NODE $DB2NODE; db2ckrst -d SAMPLE -t", $3, "-r tablespace -n
USERSPACE1" }' backupTimes.temp

Will create the following output:

NODE 0

Suggested restore order of images using timestamp 20090319092202 for
database SAMPLE
====================================================================
 restore db SAMPLE tablespace ( USERSPACE1 ) incremental taken at 20090319092202
 restore db SAMPLE tablespace ( USERSPACE1 ) incremental taken at 20090316205138
 restore db SAMPLE tablespace ( USERSPACE1 ) incremental taken at 20090319092202
====================================================================

NODE 2

Suggested restore order of images using timestamp 20090319091502 for
database SAMPLE.
====================================================================
 restore db SAMPLE tablespace ( USERSPACE1 ) incremental taken at 20090319091502
 restore db SAMPLE tablespace ( USERSPACE1 ) incremental taken at 20090316202049
 restore db SAMPLE tablespace ( USERSPACE1 ) incremental taken at 20090319091502
====================================================================

NODE 1

Suggested restore order of images using timestamp 20090319090741 for
database SAMPLE.
====================================================================
 restore db SAMPLE tablespace ( USERSPACE1 ) incremental taken at 20090319090741
 restore db SAMPLE tablespace ( USERSPACE1 ) incremental taken at 20090316194052
 restore db SAMPLE tablespace ( USERSPACE1 ) incremental taken at 20090319090741
====================================================================

4 - Creating the restore commands:

for example, restoring tablespace USERSPACE1 from SAMPLE database on nodes 0 to 2 using the timestamps provided in the above item.

export DB2NODE=0
db2 terminate
date
echo "Restoring node $DB2NODE"
db2 "restore database SAMPLE tablespace ( USERSPACE1 ) online use tsm open 1 session 
     taken at 20090319092202 without prompting"
db2 "restore database SAMPLE tablespace ( USERSPACE1 ) online use tsm open 1 session 
     taken at 20090316205138 without prompting"
db2 "restore database SAMPLE tablespace ( USERSPACE1 ) online use tsm open 1 session 
     taken at 20090319092202 without prompting"
date

export DB2NODE=2
db2 terminate
date
echo "Restoring node $DB2NODE"
db2 "restore database SAMPLE tablespace ( USERSPACE1 ) online use tsm open 1 session 
     taken at 20090319091502 without prompting"
db2 "restore database SAMPLE tablespace ( USERSPACE1 ) online use tsm open 1 session 
     taken at 20090316202049 without prompting"
db2 "restore database SAMPLE tablespace ( USERSPACE1 ) online use tsm open 1 session 
     taken at 20090319091502 without prompting"
date

export DB2NODE=1
db2 terminate
date
echo "Restoring node $DB2NODE"
db2 "restore database SAMPLE tablespace ( USERSPACE1 ) online use tsm open 1 session 
     taken at 20090319090741 without prompting"
db2 "restore database SAMPLE tablespace ( USERSPACE1 ) online use tsm open 1 session 
     taken at 20090316194052 without prompting"
db2 "restore database SAMPLE tablespace ( USERSPACE1 ) online use tsm open 1 session 
     taken at 20090319090741 without prompting"
date