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

    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:

    -------------------- --------------------
    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,


  2. #2
    Join Date
    Apr 2011
    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.


  3. #3
    Join Date
    Apr 2012
    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