System Error - Rebuilding tablespace (DB2 v9)

[Sam Durai]

Env: DB2 v9.1 Server / Win XP. DB is enabled for Archival Logging.

Was playing around with rebuild tablespace feature. My database 'CARD'
has tablespace named t1,t2,t3.

Taken couple of individual tablespace backup as below

C:\dir1>db2 "backup db card tablespace(syscatspace,userspace1,t1)"
Backup successful. The timestamp for this backup image is :
20070402114435

C:\dir1>db2 "backup db card tablespace(syscatspace,t2)"
Backup successful. The timestamp for this backup image is :
20070402114448

Then intentionally I dropped the tablespace t1,t2 to restore it from
backup.

Then I tried to restore but it fails
C:\dir1>db2 "restore db card rebuild with
tablespace(syscatspace,userspace1,t1) taken at 20070
402114435"
SQL2561W Warning! Rebuilding a database from a table space image or
using a
subset of table spaces. The target database will be overwritten. The
restore
utility also reports the following sqlcode "2539".
Do you want to continue ? (y/n) y
DB20000I The RESTORE DATABASE command completed successfully.

C:\dir1>db2 rollforward db card to end of logs
SQL1042C An unexpected system error occurred. SQLSTATE=58004

Please advice.

Thanks,
Sam

[Phil Sherman]

If you hadn't received this error, your database would be in very big
trouble.

You dropped two tablespaces; t1 and t2.

You restored two tablespaces; syscatspace and t1.

You then tried to rollforward the logs for syscatspace, userspace1,
t1,t2,t3.

You can't rollforward all of the tablespaces, only those you are
restoring. See the "ON TABLESPACE" clause of the ROLLFORWARD command.

You have two additional problems. You never restored t2, and, if you
rollforward to the end of the logs; you will include the drop tablespace
commands that were executed. You'd be better off testing this type of
recovery by deleting the physical data sets for the tablespaces and
recovering from there.

Rollforward to a point in time just before you dropped the tablespaces
may leave you with an inconsistent catalog because changes to it after
the drop tablespace commands will no longer exist. Point in time
recoveries also require that a new backup be immediately made to allow
future recoveries.

Phil Sherman
Sam Durai wrote:

Env: DB2 v9.1 Server / Win XP. DB is enabled for Archival Logging.

Was playing around with rebuild tablespace feature. My database 'CARD'
has tablespace named t1,t2,t3.

Taken couple of individual tablespace backup as below

C:\dir1>db2 "backup db card tablespace(syscatspace,userspace1,t1)"
Backup successful. The timestamp for this backup image is :
20070402114435

C:\dir1>db2 "backup db card tablespace(syscatspace,t2)"
Backup successful. The timestamp for this backup image is :
20070402114448

Then intentionally I dropped the tablespace t1,t2 to restore it from
backup.

Then I tried to restore but it fails
C:\dir1>db2 "restore db card rebuild with
tablespace(syscatspace,userspace1,t1) taken at 20070
402114435"
SQL2561W Warning! Rebuilding a database from a table space image or
using a
subset of table spaces. The target database will be overwritten. The
restore
utility also reports the following sqlcode "2539".
Do you want to continue ? (y/n) y
DB20000I The RESTORE DATABASE command completed successfully.

C:\dir1>db2 rollforward db card to end of logs
SQL1042C An unexpected system error occurred. SQLSTATE=58004

Please advice.

Thanks,
Sam