Results 1 to 6 of 6

Thread: Doubt in Reorg

  1. #1
    Join Date
    Aug 2010
    Location
    Singapore
    Posts
    197

    Red face Unanswered: Doubt in Reorg

    I am currently working on a issue ..

    Env :db2 V9.7 Fp4
    Aix 7.1

    " I tried to re-org a complete database, One of the table is failed with the SQL2216N and rc 968 "
    Things i checked:
    Table is accessible,
    FS is full and space is not reclaimed after the reorg failure
    In db2top i could see Resize was failed

    Tablespace has got only 1 Table and 16 containers in it. FS is 350 Gig and fully used,
    Table size is 207 Gig

    Now i have my question as i was asked to re-run the reorg to reclaim the space.

    This is a database migrated from 9.1 to 9.7.. So i guess a direct lower high water mark will not work :P

    Steps in my mind

    1. perform a dart to reduce/lower high water
    2. run reorg on that table with tempspace (tempspace is not sufficient, So planning to create new tempspace with more containers on different FS)
    3. do a INPLACE reorg for that table (I don know how exactly will INPLACE reorg works.. If i mention tempspace, )

    Please let me know what would be the best way..
    - Ananth
    DB2 DBA LUW
    "coming second, just means you are the first loser"

  2. #2
    Join Date
    Apr 2012
    Posts
    156
    Not sure I fully understand your issue, but what I have done when I need to reclaim space is use the procedure admin_move_table. I create new tablespaces and then move the tables to the new tablespace. When that is done you can drop the original tablespace. Keep in mind it does impact the system when you run this procedure so run during off peak time. there are lots of options so I will let you look at that, I personally do not like to use db2dart unless I absolutely need to, and in your case you do not absolutely need. For more info on admin_move_table look at:
    IBM DB2 9.7 for Linux, UNIX and Windows Information Center

    This of course is for tablespaces that can not be shrunk.

  3. #3
    Join Date
    Aug 2010
    Location
    Singapore
    Posts
    197
    Firstly, Thanks for the reply. I cannot go with db2move.. As i don have enough space on the FS to create 1 more tablespace .. I am trying to reclaim the space that is occupied as part of the failed reorg.
    - Ananth
    DB2 DBA LUW
    "coming second, just means you are the first loser"

  4. #4
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by alwayssathya View Post
    I am currently working on a issue ..

    Env :db2 V9.7 Fp4
    Aix 7.1

    " I tried to re-org a complete database, One of the table is failed with the SQL2216N and rc 968 "
    Things i checked:
    Table is accessible,
    FS is full and space is not reclaimed after the reorg failure
    In db2top i could see Resize was failed

    Tablespace has got only 1 Table and 16 containers in it. FS is 350 Gig and fully used,
    Table size is 207 Gig

    Now i have my question as i was asked to re-run the reorg to reclaim the space.

    This is a database migrated from 9.1 to 9.7.. So i guess a direct lower high water mark will not work :P

    Steps in my mind

    1. perform a dart to reduce/lower high water
    2. run reorg on that table with tempspace (tempspace is not sufficient, So planning to create new tempspace with more containers on different FS)
    3. do a INPLACE reorg for that table (I don know how exactly will INPLACE reorg works.. If i mention tempspace, )

    Please let me know what would be the best way..
    I beleive (can't check right now) that 968 means filesystem full. What happens during reorg is that db2 more or less creates a "copy" of the table, so it requires free space to hold the copy. You can specify a tempspace where the "copy" will be placed:

    REORG TABLE ... USE TEMPSPACE1

    if you have the space, reorg in the same ts will be faster. However, for ts created before 9.7 it will also raise the HW. Since your fs is full I think you will have to use a separate tempspace for the reorg.
    --
    Lennart

  5. #5
    Join Date
    Aug 2010
    Location
    Singapore
    Posts
    197
    Hello lennart

    Thanks for the response, Yes i thought of that option as well. But i wonder my tempspace is 40GB (currently). but the table size in 207GB.
    So i wonder if i can create a temp tablespace with 100Gb will it be helpful.. Or if i can use INPLACE reorg with TRUNCATE.. which will be better .

    Pls. share your idea .. i can plan it from that..
    - Ananth
    DB2 DBA LUW
    "coming second, just means you are the first loser"

  6. #6
    Join Date
    Aug 2010
    Location
    Singapore
    Posts
    197
    resolved after doing dart .. which recommended for reorg with longlobdata.. it worked..
    - Ananth
    DB2 DBA LUW
    "coming second, just means you are the first loser"

Posting Permissions

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