Hi All,

I'm using DB2 v7.2 on windows 2000.
There is a serious problem in one of our tablespaces.

It is showing as 100% full (pages used) in control center and the state is offline.
It is a DMS db, the tablespace is Regular.

We have come to the limit of expanding it. So we can not add more containers in this tablespace. Reorg is failing with -290 code.

Please help. Any ideas of why the state is offline or any thoughts are appreciated.

Thanks,
-Preeti

DMS file?

Extend the tablespace if there's space in the filesystem.

Sorry I did not finish my post last time.

After that do an alter tablespace switch online.

Thanks a lot for replying.

I can not increase the size of tablespace by adding more containers since its already 64GB which is the limit of a Regular tablespace size in DMS database.
Once the normal operations are allowed on it, then we have plans to attach this tablespace's table to a new long tablespace.

So, my current concern is to make its state normal so that we can do some repair work on it. Currently the reorg and runstats are failing.

Please suggest.
Do you think tablespace 100% full can be the cause of state offline and not accessible?
If you know any possible reasons why a tablespace goes offline, that will help too in this situation.

Thanks again.
-Preeti

There is space in file system.

What does "LIST TABLESPACEs show detail " SHOW?

if you do an alter tablespace switch online what errors are you getting?

List tablespaces show details command is not showing any detail information under this tablespace. Its giving information till state = 0x4000. After this, the Detail Explaination is blank.
We've not tried the command alter tablespace switch state. I dont want to run that command unless we know what the cause of offline state was.

Thanks a lot for your replies. Still looking for the solution/answers....

Anything at all in diaglog? I don't think alter tablespace online will cause any catastrophic damage; it may give clues to what the server thinks is the problem.

The d2diag.log shows that first it had put 3 tablespaces in x4000 (offline state) after commnad db2 list tablespace failed in checking containers for these tablespaces. (now i know that 3 tablespaces are in this state).

So, the sequence is-

1. Ran list tablespace command, failed in checking tablespaces, but the state was normal(x0). -

diag.log-
Error checking container 6 (D:\TsHdr6.DB) for tbsp 23. Rc = FFFFE60A

2004-10-07-19.23.10.218002 InstanceB2 Node:000
PID:1224(db2syscs.exe) TID:1968 Appid:*LOCAL.DB2.041008002310
buffer_pool_services sqlbDMSStartPool Probe:800 Database:TESTDB
DIA9999E An internal error occurred. Report the following error code :
"FFFFC11E".
:
:
Tablespace T1DATA (23) is in state x0.
Starting it failed. rc=1ec1 ffff

same with 2 other tablespaces...

2. Ran list tablespace command again, failed, and the state was switched to x4000 by db2.

db2diag.log -

Error checking container 6 (D:\TsDataHdr6.DB) for tbsp 23. Rc = FFFFE60A
:
:
2004-10-07-19.37.46.750004 InstanceB2 Node:000
PID:1220(db2syscs.exe) TID:2380 Appid:*LOCAL.DB2.041007221150
buffer_pool_services sqlbStartPools Probe:30 Database:TESTDB

Tablespace T1DATA (23) is in state x4000.
Starting it failed. rc=1ec1 ffff .Áÿÿ

Now its repeating the same error in db2diag.log on doing list tablespace operation.

The database is in this state since then. Not performed alter tablespace switch online command yet.

I guess there may be error messages in db2diag.log file and/or dump files at the time the tablespace went offline ... Check those messages , which may give a clue of why it went offline ...

Though I have never come across this scenario, I won't be surprised if hitting the 64Gb limit takes the tablespace offline ...

Unless you get it online, you may not be able to do RUNSTATS/REORG on the table ...

Do you have a backup of the database in a good state that you can recover and roll the db forward to a point just before the tablespace hit 64Gigs?? In case you do, why not get the tablespace online ???

Remember, to alter a table to use a long tablespace, you will have to drop and recreate the table ...

Cheers
Sathyaram

Sathyaram, I had several tablespaces which hit 64GB limitation and without any problem. Therefore, it should not cause the tablespace offline. Of course, in my case, it is in AIX not Windows.

I definitely agree with going to db2diag.log and get the clue of why it went offline.

And I also met the special situation, the disks/filesystems temporaryly offline and recovery automatically, but tablespaces(containers) on the disks/filesystems went offline and did not online automatically, I had to switch them on manually. I am not sure that happened in your server. But you really need to check db2diag.log.

Thanks,

David

Hi preetim,

Why don't you contact the IBM support?

Hope this helsp,
Grofaty

Thanks to you all for your help.

I diagnosed the diag.log error codes, there was - RC=FFFFE60A, which means- file does not exists.

When looked for all the container files on disk, it was found that one of the containers was accidentally deleted and hence the tablespaces were offline.
Fortunately we had a backup, and we restored from it.

So, it turned out that tablespace being full had nothing to do with the tablespace being offline.

Now we are doing operations to add a long tablespace to the same table( first creating a new table connected to long tblspace, then transferrring the data to new table and then swapping table names).

Thanks again for all responses. This forum is always very helpful.

-Preeti