Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2013
    Posts
    3

    Unanswered: mis-detached partitions

    I have a problem with my 9.7 database. Maybe someone can advice.

    I have a large range-partitioned table TBL1, from which i detached partition to a separate table. But, because of my wrong actions detaching is not finished(apd is still filling diag log file). The problem is that the tablespace of the detached part is in "not available" condition. It is in RF pending state and after i RF it, it remains "not available after rf" (because i lost required logs). So i cannot drop the main table TBL1, i cannot complete detach, and i cannot drop the mis-detached table as well.

    The question is: can i somehow get rid of the table and its partitions? Its data is lost anyway. I would leave them, but their containers occupy large space.
    If not, how about manually deleting containers for the table? I could accept leaving corrupted table, because relocating/recreating whole database is not acceptible (too much data in DB).

    any suggestions?

  2. #2
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Hee "Notabene" (dutch?) you got yourself in quite a mess. Are there any more tables in that same tablespace? Can they be read? I would not know how this database can be salvaged. Maybe you can take a deep-dive into the db2dart utility. If there is a solution you will find it there. If you cannot find the solution yourself you can open a PMR at IBM.
    Somewhere between " too small" and " too large" lies the size that is just right.
    - Scott Hayes

  3. #3
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Try using db2dart option DDEL

    DB2DART - Hints & Tips for the DBA

    I have had success in the past using this option when the log files of the database "went missing" , though I must say that the tables I used on were not partitioned.


    ---
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  4. #4
    Join Date
    Mar 2013
    Posts
    3
    Hee "Notabene" (dutch?)
    No, I am georgian. Nota Bene actually comes from latin

    Thanks for your suggestion. I will take a look at db2dart.

    Are there any more tables in that same tablespace? Can they be read?
    The partitioned table TBL1 uses a few tablespaces - each for 1 ranged partition. And each of these tablespaces are assigned only to this table. The unimpacted tablespaces can be read, yes. Well i figure out that i can drop them to get space but that wouldnt solve problem as a whole.

  5. #5
    Join Date
    Apr 2012
    Posts
    156
    Unless you are comfortable with using db2dart, and have somewhere to test this before doing on your production system you really should open a pmr to IBM. Your situation is bad enough as it is you do not want to risk things getting worse. Also, I would move the remaining "good" data/tables you need from the problem tablespace to a new tablespace before making any changes. That way the issue is contained to a single tablespace without any data that you need.

  6. #6
    Join Date
    Mar 2013
    Posts
    3
    The saga continues

    I managed to save/dump data using db2dart. I also freed up space by relocating containers of the damaged tablespaces to a slow cheap storage.
    And now i need to drop the damaged table. Which i can't because of inaccessible tablespaces in it. Is there any way to drop the table and all of these tablespaces?
    db2dart cannot do this afaik.

    Because of this hanged table and its tablespaces a cannot perform full backups, so i am sitting on an explosive :|

  7. #7
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Quote Originally Posted by Notabene View Post
    And now i need to drop the damaged table. Which i can't because of inaccessible tablespaces in it. Is there any way to drop the table and all of these tablespaces?
    |
    What is the error message you got when attempting to drop the tablespace(s)?

Posting Permissions

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