You can perform cross-node recovery using the db2adutl command, logarchopt1 and vendoropt database configuration parameters. This recovery is demonstrated in examples from a few different Tivoli® Storage Manager (TSM) environments.
For the following examples, computer 1 is called bar and is running the AIX® operating system. The user on this machine is roecken. The database on bar is called zample. Computer 2 is called dps. This computer is also running the AIX operating system, and the user is regress9.
This cross-node recovery example shows how to set up two computers so that you can recover data from one computer to another when log archives and backups are stored on a TSM server and where passwords are managed using the PASSWORDACCESS=GENERATE option.
bar:/home/roecken> db2 update db cfg for zample using LOGARCHMETH1 tsm
DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully.
db2 force applications all
db2 list applications
You
should receive a message that says that no data was returned. db2 backup db zample use tsm
Backup successful. The timestamp for this backup imagge is : 20090216151025
db2 connect to zample
bar:/home/roecken> db2 load from mr of del modified by noheader replace
into employee copy yes use tsm
where in this example,
the table is called employee, and the data is being
loaded from a delimited ASCII file called mr. The COPY YES option
is specified to make a copy of the data that is loaded, and the USE
TSM option specifies that the copy of the data is stored
on the TSM server. SQL3109N The utility is beginning to load data from file "/home/roecken/mr".
SQL3500W The utility is beginning the "LOAD" phase at time "02/16/2009
15:12:13.392633".
SQL3519W Begin Load Consistency Point. Input record count = "0".
SQL3520W Load Consistency Point was successful.
SQL3110N The utility has completed processing. "1" rows were read from the
input file.
SQL3519W Begin Load Consistency Point. Input record count = "1".
SQL3520W Load Consistency Point was successful.
SQL3515W The utility has finished the "LOAD" phase at time "02/16/2009
15:12:13.445718".
Number of rows read = 1
Number of rows skipped = 0
Number of rows loaded = 1
Number of rows rejected = 0
Number of rows deleted = 0
Number of rows committed = 1
bar:/home/roecken/sqllib/adsm> db2adutl query db zample
Retrieving FULL DATABASE BACKUP information.
1 Time: 20090216151025 Oldest log: S0000000.LOG Log stream: 0
Sessions: 1
Retrieving INCREMENTAL DATABASE BACKUP information.
No INCREMENTAL DATABASE BACKUP images found for ZAMPLE
Retrieving DELTA DATABASE BACKUP information.
No DELTA DATABASE BACKUP images found for ZAMPLE
Retrieving TABLESPACE BACKUP information.
No TABLESPACE BACKUP images found for ZAMPLE
Retrieving INCREMENTAL TABLESPACE BACKUP information.
No INCREMENTAL TABLESPACE BACKUP images found for ZAMPLE
Retrieving DELTA TABLESPACE BACKUP information.
No DELTA TABLESPACE BACKUP images found for ZAMPLE
Retrieving LOAD COPY information.
1 Time: 20090216151213
Retrieving LOG ARCHIVE information.
Log file: S0000000.LOG, Chain Num: 0, Log stream: 0,
Taken at: 2009-02-16-15.10.38
bar:/home/roecken/sqllib/adsm> db2adutl grant user regress9
on nodename dps for db zample
Successfully added permissions for regress9 to access ZAMPLE on node dps.
bar:/home/roecken/sqllib/adsm> db2adutl queryaccess
Node Username Database Name Type
--------------------------------------------------------------
DPS regress9 ZAMPLE A
--------------------------------------------------------------
Access Types: B - backup images L - logs A - both
dps:/home/regress9/sqllib/adsm> db2adutl query db zample
The
following information is returned: --- Database directory is empty ---
Warning: There are no file spaces created by DB2 on the ADSM server
Warning: No DB2 backup images found in ADSM for any alias.
dps:/home/regress9/sqllib/adsm> db2adutl query db zample nodename
bar owner roecken
The following information is returned: --- Database directory is empty ---
Query for database ZAMPLE
Retrieving FULL DATABASE BACKUP information.
1 Time: 20090216151025 Oldest log: S0000000.LOG Log stream: 0
Sessions: 1
Retrieving INCREMENTAL DATABASE BACKUP information.
No INCREMENTAL DATABASE BACKUP images found for ZAMPLE
Retrieving DELTA DATABASE BACKUP information.
No DELTA DATABASE BACKUP images found for ZAMPLE
Retrieving TABLESPACE BACKUP information.
No TABLESPACE BACKUP images found for ZAMPLE
Retrieving INCREMENTAL TABLESPACE BACKUP information.
No INCREMENTAL TABLESPACE BACKUP images found for ZAMPLE
Retrieving DELTA TABLESPACE BACKUP information.
No DELTA TABLESPACE BACKUP images found for ZAMPLE
Retrieving LOAD COPY information.
1 Time: 20090216151213
Retrieving LOG ARCHIVE information.
Log file: S0000000.LOG, Chain Num: 0, Log stream: 0,
Taken at: 2009-02-16-15.10.38
This information matches the TSM information that was
generated previously and confirms that you can restore this image
onto the dps computer. dps:/home/regress9> db2 restore db zample use tsm options
"'-fromnode=bar -fromowner=roecken'" without prompting
DB20000I The RESTORE DATABASE command completed successfully.
dps:/home/regress9> db2 rollforward db zample to end of logs and stop
SQL4970N Roll-forward recovery on database "ZAMPLE" cannot reach the
specified stop point (end-of-log or point-in-time) because of missing log
file(s) on node(s) "0".
dps:/home/regress9> db2 update db cfg for zample using logarchopt1
"'-fromnode=bar -fromowner=roecken'"
dps:/home/regress9> db2 update db cfg for zample using VENDOROPT
"'-fromnode=bar -fromowner=roecken'"
dps:/home/regress9> db2 rollforward db zample to end of logs and stop
Rollforward Status
Input database alias = zample
Number of members have returned status = 1
Member number Rollforward Next log to Log files processed Last committed transaction
status be read
------------- ----------- ----------- ------------------------- ------------------------------
0 not pending S0000000.LOG-S0000000.LOG 2009-05-06-15.28.11.000000 UTC
DB20000I The ROLLFORWARD command completed successfully.
This cross-node recovery example shows how to set up two computers so that you can recover data from one computer to another when log archives and backups are stored on a TSM server and where passwords are managed by the users. In these environments, extra information is required, specifically the TSM nodename and password of the computer where the objects were created.
NODENAME bar
dps:/home/regress9/sqllib/adsm> db2adutl query db zample nodename bar
owner roecken password *******
Query for database ZAMPLE
Retrieving FULL DATABASE BACKUP information.
1 Time: 20090216151025 Oldest log: S0000000.LOG Log stream: 0
Sessions: 1
Retrieving INCREMENTAL DATABASE BACKUP information.
No INCREMENTAL DATABASE BACKUP images found for ZAMPLE
Retrieving DELTA DATABASE BACKUP information.
No DELTA DATABASE BACKUP images found for ZAMPLE
Retrieving TABLESPACE BACKUP information.
No TABLESPACE BACKUP images found for ZAMPLE
Retrieving INCREMENTAL TABLESPACE BACKUP information.
No INCREMENTAL TABLESPACE BACKUP images found for ZAMPLE
Retrieving DELTA TABLESPACE BACKUP information.
No DELTA TABLESPACE BACKUP images found for ZAMPLE
Retrieving LOAD COPY information.
1 Time: 20090216151213
Retrieving LOG ARCHIVE information.
Log file: S0000000.LOG, Chain Num: 0, Log stream: 0,
Taken at: 2009-02-16-15.10.38
dps:/home/regress9> db2 create db zample
dps:/home/regress9> db2 update db cfg for zample using tsm_nodename bar
dps:/home/regress9> db2 update db cfg for zample using
tsm_password ********
dps:/home/regress9> db2 restore db zample use tsm options
"'-fromnode=bar -fromowner=roecken'" without prompting
SQL2540W Restore is successful, however a warning "2523" was
encountered during Database Restore while processing in No
Interrupt mode.
dps:/home/regress9> db2 rollforward db zample to end of logs and stop
SQL1268N Roll-forward recovery stopped due to error "-2112880618"
while retrieving log file "S0000000.LOG" for database "ZAMPLE" on node "0".
dps:/home/regress9> db2 update db cfg for zample using tsm_nodename bar
dps:/home/regress9> db2 update db cfg for zample using tsm_password *******
dps:/home/regress9> db2 update db cfg for zample using logarchopt1
"'-fromnode=bar -fromowner=roecken'"
dps:/home/regress9> db2 update db cfg for zample using VENDOROPT
"'-fromnode=bar -fromowner=roecken'"
dps:/home/regress9> db2 rollforward db zample to end of logs and stop
Rollforward Status
Input database alias = zample
Number of members have returned status = 1
Member number Rollforward Next log to Log files processed Last committed transaction
status be read
------------- ----------- ----------- ------------------------- ------------------------------
0 not pending S0000000.LOG-S0000000.LOG 2009-05-06-15.28.11.000000 UTC
DB20000I The ROLLFORWARD command completed successfully.
This cross-node recovery example shows how to set up two computers as proxy nodes so that you can recover data from one computer to another when log archives and backups are stored on a TSM server and where passwords are managed using the PASSWORDACCESS=GENERATE option.
In this example, the computers bar and dps are registered under the proxy name of clusternode. The computers are already setup as proxy nodes.
REGISTER NODE clusternode mypassword
GRANT PROXYNODE TARGET=clusternode AGENT=bar,dps
bar:/home/roecken> db2 update db cfg for zample using
LOGARCHMETH1 tsm logarchopt1 "'-asnodename=clusternode'"
DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully.
db2 force applications all
db2 list applications
You
should receive a message that says that no data was returned. db2 backup db zample use tsm options "'-asnodename=clusternode'"
Backup successful. The timestamp for this backup image is : 20090216151025
Instead of specifying the -asnodename option
on the BACKUP DATABASE command, you can update
the vendoropt database configuration parameter
instead. db2 connect to zample
bar:/home/roecken> db2 load from mr of del modified by noheader
replace into employee copy yes use tsmwhere
where in this
example, the table is called employee, and the data
is being loaded from a delimited ASCII file called mr.
The COPY YES option is specified
to make a copy of the data that is loaded, and the USE TSM option
specifies that the copy of the data is stored on the TSM server. SQL3109N The utility is beginning to load data from file "/home/roecken/mr".
SQL3500W The utility is beginning the "LOAD" phase at time "02/16/2009
15:12:13.392633".
SQL3519W Begin Load Consistency Point. Input record count = "0".
SQL3520W Load Consistency Point was successful.
SQL3110N The utility has completed processing. "1" rows were read from the
input file.
SQL3519W Begin Load Consistency Point. Input record count = "1".
SQL3520W Load Consistency Point was successful.
SQL3515W The utility has finished the "LOAD" phase at time "02/16/2009
15:12:13.445718".
Number of rows read = 1
Number of rows skipped = 0
Number of rows loaded = 1
Number of rows rejected = 0
Number of rows deleted = 0
Number of rows committed = 1
bar:/home/roecken/sqllib/adsm> db2adutl query db zample
options "-asnodename=clusternode"
Retrieving FULL DATABASE BACKUP information.
1 Time: 20090216151025 Oldest log: S0000000.LOG Log stream: 0
Sessions: 1
Retrieving INCREMENTAL DATABASE BACKUP information.
No INCREMENTAL DATABASE BACKUP images found for ZAMPLE
Retrieving DELTA DATABASE BACKUP information.
No DELTA DATABASE BACKUP images found for ZAMPLE
Retrieving TABLESPACE BACKUP information.
No TABLESPACE BACKUP images found for ZAMPLE
Retrieving INCREMENTAL TABLESPACE BACKUP information.
No INCREMENTAL TABLESPACE BACKUP images found for ZAMPLE
Retrieving DELTA TABLESPACE BACKUP information.
No DELTA TABLESPACE BACKUP images found for ZAMPLE
Retrieving LOAD COPY information.
1 Time: 20090216151213
Retrieving LOG ARCHIVE information.
Log file: S0000000.LOG, Chain Num: 0, Log stream: 0,
Taken at: 2009-02-16-15.10.38
dps:/home/regress9/sqllib/adsm> db2adutl query db zample
The
following information is returned: --- Database directory is empty ---
Warning: There are no file spaces created by DB2 on the ADSM server
Warning: No DB2 backup images found in ADSM for any alias.
dps:/home/regress9/sqllib/adsm> db2adutl query db zample
options="-asnodename=clusternode"
The following
information is returned: --- Database directory is empty ---
Query for database ZAMPLE
Retrieving FULL DATABASE BACKUP information.
1 Time: 20090216151025 Oldest log: S0000000.LOG Log stream: 0
Sessions: 1
Retrieving INCREMENTAL DATABASE BACKUP information.
No INCREMENTAL DATABASE BACKUP images found for ZAMPLE
Retrieving DELTA DATABASE BACKUP information.
No DELTA DATABASE BACKUP images found for ZAMPLE
Retrieving TABLESPACE BACKUP information.
No TABLESPACE BACKUP images found for ZAMPLE
Retrieving INCREMENTAL TABLESPACE BACKUP information.
No INCREMENTAL TABLESPACE BACKUP images found for ZAMPLE
Retrieving DELTA TABLESPACE BACKUP information.
No DELTA TABLESPACE BACKUP images found for ZAMPLE
Retrieving LOAD COPY information.
1 Time: 20090216151213
Retrieving LOG ARCHIVE information.
Log file: S0000000.LOG, Chain Num: 0, Log stream: 0,
Taken at: 2009-02-16-15.10.38
This information matches the TSM information that was
generated previously and confirms that you can restore this image
onto the dps computer. dps:/home/regress9> db2 restore db zample use tsm options
"'-asnodename=clusternode'" without prompting
DB20000I The RESTORE DATABASE command completed successfully.
dps:/home/regress9> db2 rollforward db zample to end of logs and stop
SQL4970N Roll-forward recovery on database "ZAMPLE" cannot reach the
specified stop point (end-of-log or point-in-time) because of missing log
file(s) on node(s) "0".
dps:/home/regress9> db2 update db cfg for zample using logarchopt1
"'-asnodename=clusternode'"
dps:/home/regress9> db2 update db cfg for zample using VENDOROPT
"'-asnodename=clusternode'"
dps:/home/regress9> db2 rollforward db zample to end of logs and stop
Rollforward Status
Input database alias = zample
Number of members have returned status = 1
Member number Rollforward Next log to Log files processed Last committed transaction
status be read
------------- ----------- ----------- ------------------------- ------------------------------
0 not pending S0000000.LOG-S0000000.LOG 2009-05-06-15.28.11.000000 UTC
DB20000I The ROLLFORWARD command completed successfully.
This example shows how to set up two members as proxy nodes so that you can recover data from one member to the other when log archives and backups are stored on a TSM server and where passwords are managed using the PASSWORDACCESS=GENERATE option.
In this example, the members member1 and member2 are registered under the proxy name of clusternode. In DB2® pureScale® environments, you can perform backup or data recovery operations from any member. In this example, data will be recovered from member2
REGISTER NODE clusternode mypassword
GRANT PROXYNODE TARGET=clusternode AGENT=member1,member2
member1:/home/roecken> db2 update db cfg for zample using
LOGARCHMETH1 tsm logarchopt1 "'-asnodename=clusternode'"
DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully.
db2 force applications all
db2 list applications global
You
should receive a message that says that no data was returned. db2 backup db zample use tsm options '-asnodename=clusternode'
Backup successful. The timestamp for this backup image is : 20090216151025
Instead
of specifying the -asnodename option on the BACKUP
DATABASE command, you can update the vendoropt database
configuration parameter instead. db2 connect to zample
member1:/home/roecken> db2 load from mr of del modified by noheader replace
into employee copy yes use tsmwhere
where in this
example, the table is called employee, and the data
is being loaded from a delimited ASCII file called mr.
The COPY YES option is specified
to make a copy of the data that is loaded, and the USE TSM option
specifies that the copy of the data is stored on the TSM server. SQL3109N The utility is beginning to load data from file "/home/roecken/mr".
SQL3500W The utility is beginning the "LOAD" phase at time "02/16/2009
15:12:13.392633".
SQL3519W Begin Load Consistency Point. Input record count = "0".
SQL3520W Load Consistency Point was successful.
SQL3110N The utility has completed processing. "1" rows were read from the
input file.
SQL3519W Begin Load Consistency Point. Input record count = "1".
SQL3520W Load Consistency Point was successful.
SQL3515W The utility has finished the "LOAD" phase at time "02/16/2009
15:12:13.445718".
Number of rows read = 1
Number of rows skipped = 0
Number of rows loaded = 1
Number of rows rejected = 0
Number of rows deleted = 0
Number of rows committed = 1
member1:/home/roecken/sqllib/adsm> db2adutl query db zample
options "-asnodename=clusternode"
Retrieving FULL DATABASE BACKUP information.
1 Time: 20090216151025 Oldest log: S0000000.LOG Log stream: 0
Sessions: 1
Retrieving INCREMENTAL DATABASE BACKUP information.
No INCREMENTAL DATABASE BACKUP images found for ZAMPLE
Retrieving DELTA DATABASE BACKUP information.
No DELTA DATABASE BACKUP images found for ZAMPLE
Retrieving TABLESPACE BACKUP information.
No TABLESPACE BACKUP images found for ZAMPLE
Retrieving INCREMENTAL TABLESPACE BACKUP information.
No INCREMENTAL TABLESPACE BACKUP images found for ZAMPLE
Retrieving DELTA TABLESPACE BACKUP information.
No DELTA TABLESPACE BACKUP images found for ZAMPLE
Retrieving LOAD COPY information.
1 Time: 20090216151213
Retrieving LOG ARCHIVE information.
Log file: S0000000.LOG, Chain Num: 1, Log stream: 1, Taken at: 2009-02-16-13.01.10
Log file: S0000000.LOG, Chain Num: 1, Log stream: 0, Taken at: 2009-02-16-13.01.11
Log file: S0000000.LOG, Chain Num: 1, Log stream: 2, Taken at: 2009-02-16-13.01.19
Log file: S0000001.LOG, Chain Num: 1, Log stream: 0, Taken at: 2009-02-16-13.02.49
Log file: S0000001.LOG, Chain Num: 1, Log stream: 1, Taken at: 2009-02-16-13.02.49
Log file: S0000001.LOG, Chain Num: 1, Log stream: 2, Taken at: 2009-02-16-13.02.49
Log file: S0000002.LOG, Chain Num: 1, Log stream: 1, Taken at: 2009-02-16-13.03.15
Log file: S0000002.LOG, Chain Num: 1, Log stream: 2, Taken at: 2009-02-16-13.03.15
Log file: S0000002.LOG, Chain Num: 1, Log stream: 0, Taken at: 2009-02-16-13.03.16
member2:/home/regress9/sqllib/adsm> db2adutl query db zample
options="-asnodename=clusternode"
The following
information is returned: --- Database directory is empty ---
Query for database ZAMPLE
Retrieving FULL DATABASE BACKUP information.
1 Time: 20090216151025 Oldest log: S0000000.LOG Log stream: 0
Sessions: 1
Retrieving INCREMENTAL DATABASE BACKUP information.
No INCREMENTAL DATABASE BACKUP images found for ZAMPLE
Retrieving DELTA DATABASE BACKUP information.
No DELTA DATABASE BACKUP images found for ZAMPLE
Retrieving TABLESPACE BACKUP information.
No TABLESPACE BACKUP images found for ZAMPLE
Retrieving INCREMENTAL TABLESPACE BACKUP information.
No INCREMENTAL TABLESPACE BACKUP images found for ZAMPLE
Retrieving DELTA TABLESPACE BACKUP information.
No DELTA TABLESPACE BACKUP images found for ZAMPLE
Retrieving LOAD COPY information.
1 Time: 20090216151213
Retrieving LOG ARCHIVE information.
Log file: S0000000.LOG, Chain Num: 1, Log stream: 1, Taken at: 2009-02-16-13.01.10
Log file: S0000000.LOG, Chain Num: 1, Log stream: 0, Taken at: 2009-02-16-13.01.11
Log file: S0000000.LOG, Chain Num: 1, Log stream: 2, Taken at: 2009-02-16-13.01.19
Log file: S0000001.LOG, Chain Num: 1, Log stream: 0, Taken at: 2009-02-16-13.02.49
Log file: S0000001.LOG, Chain Num: 1, Log stream: 1, Taken at: 2009-02-16-13.02.49
Log file: S0000001.LOG, Chain Num: 1, Log stream: 2, Taken at: 2009-02-16-13.02.49
Log file: S0000002.LOG, Chain Num: 1, Log stream: 1, Taken at: 2009-02-16-13.03.15
Log file: S0000002.LOG, Chain Num: 1, Log stream: 2, Taken at: 2009-02-16-13.03.15
Log file: S0000002.LOG, Chain Num: 1, Log stream: 0, Taken at: 2009-02-16-13.03.16
This
information matches the TSM information that was generated previously
and confirms that you can restore this image onto the member2 member. member2:/home/regress9> db2 restore db zample use tsm options
'-asnodename=clusternode' without prompting
DB20000I The RESTORE DATABASE command completed successfully.
member2:/home/regress9> db2 update db cfg for zample using VENDOROPT
"'-asnodename=clusternode'"
member2:/home/regress9> db2 rollforward db zample to end of logs and stop
Rollforward Status
Input database alias = zample
Number of members have returned status = 3
Member number Rollforward Next log to Log files processed Last committed transaction
status be read
------------- ----------- ----------- ------------------------- ------------------------------
0 not pending S0000001.LOG-S0000012.LOG 2009-05-06-15.28.11.000000 UTC
1 not pending S0000001.LOG-S0000012.LOG 2009-05-06-15.28.11.000000 UTC
2 not pending S0000001.LOG-S0000012.LOG 2009-05-06-15.28.11.000000 UTC
DB20000I The ROLLFORWARD command completed successfully.