DB2 Tablespace in Drop Pending state after restore

[MasterTech]

I am trying to restore a backup from IBM DB2 Express C 9.7.5 (Windows 64) to IBM Db2 Express C 10.1 (Windows 64). After the restore is completed, the large table space which contains all my important tables is in a state of OFFLINE/DROP PENDING and remains inaccessible. The source backup was a full, offline backup of the database which had circular logging enabled. The source tablespace (in 9.7.5) is online and fully functional, and contains no errors.

All other tables in the restore come online successfully. When I issue a "LIST TABLESPACES SHOW DETAIL" command, the following data is returned. Notice tablespace 4 is in a drop pending state.

I thought maybe the backup was corrupt, so I have made two separate backups of the source data and attempted to restore each on 32bit and 64bit versions of DB2 10.1. All attempts yield the same results. (I can't backup the tablespace itself independently since logging is circular.)

Any suggestions?

db2 => list tablespaces show detail

Tablespaces for Current Database

Tablespace ID = 0
Name = SYSCATSPACE
Type = Database managed space
Contents = All permanent data. Regular table space.

State = 0x0000
Detailed explanation:
Normal
Total pages = 57344
Useable pages = 57340
Used pages = 39968
Free pages = 17372
High water mark (pages) = 50640
Page size (bytes) = 4096
Extent size (pages) = 4
Prefetch size (pages) = 4
Number of containers = 1

Tablespace ID = 1
Name = TEMPSPACE1
Type = System managed space
Contents = System Temporary data
State = 0x0000
Detailed explanation:
Normal
Total pages = 1
Useable pages = 1
Used pages = 1
Free pages = Not applicable
High water mark (pages) = Not applicable
Page size (bytes) = 4096
Extent size (pages) = 32
Prefetch size (pages) = 32
Number of containers = 1

Tablespace ID = 2
Name = USERSPACE1
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0000
Detailed explanation:
Normal
Total pages = 8192
Useable pages = 8160
Used pages = 5056
Free pages = 3104
High water mark (pages) = 5120
Page size (bytes) = 4096
Extent size (pages) = 32
Prefetch size (pages) = 32
Number of containers = 1

Tablespace ID = 3
Name = SYSTOOLSPACE
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0000
Detailed explanation:
Normal
Total pages = 8192
Useable pages = 8188
Used pages = 468
Free pages = 7720
High water mark (pages) = 468
Page size (bytes) = 4096
Extent size (pages) = 4
Prefetch size (pages) = 4
Number of containers = 1

Tablespace ID = 4
Name = OWG0132LRG
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0xc000
Detailed explanation:
Offline
Drop Pending

Tablespace ID = 5
Name = SYSTOOLSTMPSPACE
Type = System managed space
Contents = User Temporary data
State = 0x0000
Detailed explanation:
Normal
Total pages = 1
Useable pages = 1
Used pages = 1
Free pages = Not applicable
High water mark (pages) = Not applicable
Page size (bytes) = 4096
Extent size (pages) = 4
Prefetch size (pages) = 4
Number of containers = 1

Tablespace ID = 6
Name = OWG32LRG
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0000
Detailed explanation:
Normal
Total pages = 50176
Useable pages = 50112
Used pages = 192
Free pages = 49920
High water mark (pages) = 192
Page size (bytes) = 32768
Extent size (pages) = 64
Prefetch size (pages) = 64
Number of containers = 1

[przytula]

is the restore on same machine ?
have you tried a re-directed restore ?
best regards, Guy Przytula

[fassor]

Hi,

I was having the same issue these days. I realized the restore command don't throws errors when it run outs of space in disk drives. The sympthom: after a restore I have access to some Schemas and other where not allowed. So just be sure you have enough room for the Tablespaces.

Good Look!
Carlos