ADM6023I The table space cannot be accessed. SQLCODE -290

Tklm starts with errors

Table space is missing

After doing a successful restore on a TKLM server, TKLM starts with the following errors in systemOut.log and in db2diag.log:

2012-08-22-10.52.24.142383-300 E2796012A592 LEVEL: Info
PID : 3080672 TID : 13148 PROC : db2sysc
INSTANCE: tklmdb2 NODE : 000 DB : TKLMDB
APPHDL : 0-48 APPID: 127.0.0.1.43424.120822155222
AUTHID : TKLMDB2
EDUID : 13148 EDUNAME: db2agent (TKLMDB)
FUNCTION: DB2 UDB, buffer pool services, sqlbStartPools, probe:20
MESSAGE : ADM6023I The table space "SYSTOOLSPACE" (ID "11") is in state
0x"2001100". The table space cannot be accessed. Refer to the documentation for SQLCODE -290.

2012-08-22-10.52.24.142715-300 I2796605A521 LEVEL: Error
PID : 3080672 TID : 13148 PROC : db2sysc
INSTANCE: tklmdb2 NODE : 000 DB : TKLMDB
APPHDL : 0-48 APPID: 127.0.0.1.43424.120822155222
AUTHID : TKLMDB2
EDUID : 13148 EDUNAME: db2agent (TKLMDB)
FUNCTION: DB2 UDB, buffer pool services, sqlbStartPools, probe:20
MESSAGE : ZRC=0x80020035=-2147352523=SQLB_NOT_ALLOWED_RECOVERY_P "Access not allowed. Tblspc Recovery Pend."

Same errors as above in db2diag.log plus more:
2012-08-22-10.55.39.039322-300 E2798617A521 LEVEL: Error
PID : 5898744 TID : 1029 PROC : db2acd
INSTANCE: tklmdb2 NODE : 000
APPID : *LOCAL.tklmdb2.120822155541
EDUID : 1029 EDUNAME: db2acd
FUNCTION: DB2 UDB, Automatic Table Maintenance, atmRefreshInfoTable,
probe:320
DATA #1 : String, 11 bytes AutoStats:
DATA #2 : String, 90 bytes
[IBM][CLI Driver][DB2/AIX64] SQL0290N Table space access is not allowed. SQLSTATE=55039
DATA #3 : String, 5 bytes 55039

So collectively the error:
ADM6023I The table space "SYSTOOLSPACE" (ID "11") is in state 0x"2001100".
The table space cannot be accessed.
Refer to the documentation for SQLCODE -290.
ZRC=0x80020035=-2147352523=SQLB_NOT_ALLOWED_RECOVERY_P
"Access not allowed. Tblspc Recovery Pend."
[IBM][CLI Driver][DB2/AIX64] SQL0290N Table space access is not allowed. SQLSTATE=55039

Checked "as tklmdb2" ID: ( or else commands would fail)

$ db2 list db directory (good)
System Database Directory
Number of entries in the directory = 1
Database 1 entry:
Database alias = TKLMDB
Database name = TKLMDB
Local database directory = /home/tklmdb2
Database release level = d.00
Comment =
Directory entry type = Indirect
Catalog database partition number = 0
Alternate server hostname =
Alternate server port number =

$ db2 connect to TKLMDB (good)
Database Connection Information
Database server = DB2/AIX64 9.7.2
SQL authorization ID = TKLMDB2
Local database alias = TKLMDB

Here is the problem: The error was for: "SYSTOOLSPACE" (ID "11")
$ db2 list tablespaces (bad)

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

Tablespace ID = 1
Name = TEMPSPACE1
Type = System managed space
Contents = System Temporary data
State = 0x0000
Detailed explanation:
Normal

....... and so on until ID 11 .................

Tablespace ID = 10
Name = TKLM_SCHEDULER
Type = Database managed space
Contents = All permanent data. Large table
space.
State = 0x0000
Detailed explanation:
Normal

Tablespace ID = 11
Name = SYSTOOLSPACE
Type = Database managed space
Contents = All permanent data. Large table
space.
State = 0x2001100
Detailed explanation:
Restore pending
Storage must be defined
Storage may be defined

The problem is above last entry:

Tablespace ID = 11
Name = SYSTOOLSPACE
State = 0x2001100 < here state is not zero
Restore pending
Storage must be defined
Storage may be defined

To fix the state of non zero:

$ db2 drop tablespace SYSTOOLSPACE
DB20000I The SQL command completed successfully.

States should now be all zeros, to check:

$ db2 list tablespaces | grep State (good)
State = 0x0000
State = 0x0000
State = 0x0000
State = 0x0000
State = 0x0000
State = 0x0000
State = 0x0000
State = 0x0000
State = 0x0000
State = 0x0000
State = 0x0000

One more test to check the complete health of DB2:

Above should have automatically recreated the SYSTOOLSPACE:

$ db2 " CALL GET_DBSIZE_INFO(?, ?, ?, 0) "

SQL0443N Routine "SYSPROC.GET_DBSIZE_INFO" (specific name "GET_DBSIZE_INFO")
has returned an error SQLSTATE with diagnostic text "SQL0294". SQLSTATE=38553

Looked at db2diag.log due to above error:

DATA #1 : String, 12 bytes SYSTOOLSPACE
DATA #2 : Pool ID, PD_TYPE_SQLB_POOL_ID, 2 bytes 11
DATA #3 : unsigned integer, 8 bytes 1
DATA #4 : unsigned integer, 8 bytes 0
DATA #5 : unsigned integer, 8 bytes 8192
DATA #6 : unsigned integer, 1 bytes 0
DATA #7 : Boolean, 1 bytes true

2012-08-22-11.45.38.605944-300 I2906904A508 LEVEL: Error
PID : 3080672 TID : 14214 PROC : db2sysc
INSTANCE: tklmdb2 NODE : 000 DB : TKLMDB
APPHDL : 0-126 APPID: *LOCAL.tklmdb2.120822164540
AUTHID : TKLMDB2
EDUID : 14214 EDUNAME: db2agent (TKLMDB)
FUNCTION: DB2 UDB, buffer pool services, sqlbDMSCreatePool, probe:797
MESSAGE : ZRC=0x80020039=-2147352519=SQLB_CONTAINER_IN_USE
"Container is already being used"
.....
DATA #4 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes
sqlcaid : SQLCA sqlcabc: 136 sqlcode: -294 sqlerrml: 0
...

SYSTOOLSPACE did not really exist:

ls -l /home/tklmdb2/tklmdb2/NODE0000/
total 8
drwxr-x--x 4 tklmdb2 system 4096 Aug 22 10:45 SQL00001
drwx--x--x 6 tklmdb2 system 256 Aug 22 10:52 TKLMDB
drwxrwxr-x 2 tklmdb2 system 256 Aug 22 10:45 sqldbdir


ls -l /home/tklmdb2/tklmdb2/NODE0000/TKLMDB
total 0
drwx--x--x 2 tklmdb2 system 256 Aug 22 10:43 T0000000
drwx--x--x 3 tklmdb2 system 256 Aug 22 10:52 T0000001
drwx--x--x 2 tklmdb2 system 256 Aug 22 10:43 T0000002
drwx--x--x 2 tklmdb2 system 256 Mar 22 15:48 T0000011

Please note this line:
drwx--x--x 2 tklmdb2 system 256 Mar 22 15:48 T0000011
It has Mar not Aug like the others which is this month. So it did not
get created. So we look inside dir T0000011

ls -l /home/tklmdb2/tklmdb2/NODE0000/TKLMDB/T0000011
total 65536
drwx--x--x 2 tklmdb2 system 256 Mar 22 15:48 .
drwx--x--x 6 tklmdb2 system 256 Aug 22 10:52 ..
-rw------- 1 tklmdb2 system 0 Mar 23 08:58 .SQLCRT.FLG
-rw------- 1 tklmdb2 system 33554432 Mar 22 15:49 C0000000.LRG

To fix that:

stop TKLM

$ db2stop force
SQL1064N DB2STOP processing was successful.

Rename T0000011 under /home/tklmdb2/tklmdb2/NODE0000/TKLMDB to
T0000011.bak

"mv T0000011 T0000011.bak"

db2tart

db2 connect to TKLMDB

Below command would create a new T0000011 directory and a db2 list tablespaces
should now show a SYSTOOLSPACE again as follows:
db2 " CALL GET_DBSIZE_INFO(?, ?, ?, 0) "
Value of output parameters
--------------------------
Parameter Name : SNAPSHOTTIMESTAMP
Parameter Value : 2012-08-22-12.07.30.968791

Parameter Name : DATABASESIZE
Parameter Value : 112578560

Parameter Name : DATABASECAPACITY
Parameter Value : 1971741184

Return Status = 0

drwx--x--x 7 tklmdb2 system 256 Aug 22 12:07 .
drwxrwxr-x 5 tklmdb2 system 256 Aug 22 10:43 ..
-rw------- 1 tklmdb2 system 0 Mar 23 08:51 .SQLCRT.FLG
drwx--x--x 2 tklmdb2 system 256 Aug 22 10:43 T0000000
drwx--x--x 3 tklmdb2 system 256 Aug 22 12:07 T0000001
drwx--x--x 2 tklmdb2 system 256 Aug 22 10:43 T0000002
drwx--x--x 2 tklmdb2 system 256 Aug 22 12:07 T0000011
drwx--x--x 2 tklmdb2 system 256 Mar 22 15:48 T0000011.bak

Clean up the .bak directory T0000011.bak.

$ db2 list tablespaces showed last entry:
Tablespace ID = 11
Name = SYSTOOLSPACE
Type = Database managed space
Contents = All permanent data. Large table
space.
State = 0x0000
Detailed explanation:
Normal

Take a backup of TKLM.