Let’s say you are doing a reorg or something else and suddenly all the connections to DB2 are forced off. Then you check the instance and it is down. So you just type db2start hoping to restart the instance and make available all its databases.
Then you receive the information that the instance is up, and you try to connect back to the database you were connected before all this started, however you start receiving the message “SQL0290N Table space access is not allowed” and DB2 doesn’t allow you in. Maybe the database is in recover mode, or is something else.
In case like this, you need to check the db2diag.log and the notify file.
The default location of these files is: ~/sqllib/db2dump, however this can be changed at the instance level. If the file is not there, check the parameter DIAGPATH, and you will see something like this:
Diagnostic data directory path (DIAGPATH) = /home/instsand/sqllib/db2dump
You can do this with the following command:
db2 get dbm cfg | grep –i diagpath
In these files you should check for something like this:
2008-06-20-10.59.51.632607 Instance:instsand Node:000
PID:2040166(db2agent (EIW) 0) TID:3343 Appid:18.104.22.168.55558
buffer pool services sqlbStartPools Probe:63 Database:EIW
ADM6049E The database cannot be restarted because one or more
table spaces cannot be brought online. To restart the database
specify the "DROP PENDING TABLESPACES" option on the RESTART
DATABASE command. WARNING! PUTTING A TABLESPACE INTO THE DROP
PENDING STATE MEANS THAT NO FURTHER ACCESS TO THE TABLESPACE
WILL BE ALLOWED. ITS CONTENTS WILL BE INACCESSIBLE THROUGHOUT
THE REMAINDER OF THE LIFE OF THE TABLESPACE AND THE ONLY OPERATION
THAT WILL BE ALLOWED ON IT IS "DROP TABLESPACE". There is no way
in which it can be brought back. It is important that you consider
the consequences of this action as data can be lost as a result.
Before proceeding consult the DB2 documentation and contact
IBM support if necessary. The table spaces to specify in the
DROP PENDING TABLESPACES list are: "DENISTEMP ".
If this was your case, you need to restart the database and let DB2 aware that you will drop that tablespace, you can do this issue the command:
RESTART DATABASE database_name DROP PENDING TABLESPACES
After restarting the database you can connect to it, and after you connect you should drop that tablespace, just like this:
CONNECT TO database_name
DROP TABLESPACE tablespace_name
After all this your problem should be fixed by now.