Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2010
    Posts
    99

    Unanswered: Reorg with notruncate - how to reclaim space

    DB2 9.7.10 RedHat 5.3

    I have a table that needs to be reorg'd frequently and is set to APPEND ON. To reorg it online I temporarily change it to append off then execute an inplace reorg with the no truncate keyword:

    reorg table mytablename inplace allow write access notruncate table

    Afterwards i run the reduce container command and lower high water mark. Then I run runstats, and then select npages, fpages from sysibm.systables.

    I expected fpages = npages , instead i get this:

    NPAGES FPAGES
    -------------------- --------------------
    758440 1305515


    Only when I remove the NOTRUNCATE do the f/npages match. However a lock is taken on the table, and the applications get timeouts which I would like to avoid.

    My question is there any other way of reclaiming space without locking the table?

    Thanks in advance,

    James

  2. #2
    Join Date
    Apr 2011
    Posts
    14
    Hi James,
    We have Admin table move utility to reclaim space and clear the fragmentation.
    Hope you can use that.

    Please test in your sand/dev box if you never done this.

    Rgds/
    Ravi

  3. #3
    Join Date
    Apr 2012
    Posts
    156
    Adding to what Ravi replied. I have a similar case as yourself. The quickest / cleanest way I found to take care of this is to create two new tablespaces. One will contain your problem table, the other will be empty. During a non-peak time I use the stored procedure admin_move_table to move the table to the empty tablespace. Try to avoid a busy time for your system as this will require db2 to take a longer time for the final synch. When that is complete I drop the original tablespace and recreate it as an empty tablespace (frees up the storage) and it is ready for the next reorg. I have had this running for a year or so without any issue (scripted so no manual intervention). Best of luck!

Posting Permissions

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