Results 1 to 13 of 13
  1. #1
    Join Date
    Jun 2003
    Posts
    77

    Lightbulb Unanswered: tablespace 100% full, state offline and not accessible

    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
    Last edited by preetim; 10-11-04 at 12:42.

  2. #2
    Join Date
    Mar 2003
    Posts
    343
    DMS file?

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

  3. #3
    Join Date
    Mar 2003
    Posts
    343
    Sorry I did not finish my post last time.

    After that do an alter tablespace switch online.

  4. #4
    Join Date
    Jun 2003
    Posts
    77
    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

  5. #5
    Join Date
    Jun 2003
    Posts
    77
    There is space in file system.

  6. #6
    Join Date
    Sep 2003
    Posts
    237
    What does "LIST TABLESPACEs show detail " SHOW?

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

  7. #7
    Join Date
    Jun 2003
    Posts
    77
    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....

  8. #8
    Join Date
    Sep 2003
    Posts
    237
    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.
    mota

  9. #9
    Join Date
    Jun 2003
    Posts
    77
    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.

  10. #10
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  11. #11
    Join Date
    May 2003
    Location
    Toronto
    Posts
    29
    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
    Quote Originally Posted by sathyaram_s
    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

  12. #12
    Join Date
    Jan 2003
    Posts
    1,605
    Hi preetim,

    Why don't you contact the IBM support?

    Hope this helsp,
    Grofaty

  13. #13
    Join Date
    Jun 2003
    Posts
    77

    Thumbs up

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •