Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2011
    Posts
    20

    Unanswered: DB2 reorg on Delete ?

    Hi All

    Could anyone please let me know if delete on table needs reorg . If we donot perform reorg on frequent deletes would it impact performance.

    Is it a misconception that DB2 (LUW) delete would just mark the records for deletion but would only remove them physically after a reorg.

    Reorgchk does not return any tables for reorg. and we suspect this could be reason for poor performance

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by saechira View Post
    Hi All

    Could anyone please let me know if delete on table needs reorg . If we donot perform reorg on frequent deletes would it impact performance.

    Is it a misconception that DB2 (LUW) delete would just mark the records for deletion but would only remove them physically after a reorg.

    Reorgchk does not return any tables for reorg. and we suspect this could be reason for poor performance
    Deletes from table probably necessitates a reorg. It depends on how many rows were deleted from how many pages. If you have frequent deletes, then not reorging can affect performance--reorging would put the data on fewer pages, resulting is less I/O.

    I do not know if IBM just marks a row as deleted or "removes" the row. I do know that the space is not reclaimed until reorg. It doesn't really matter it it is marked as deleted, or removed, it still takes the same amount of I/O to do it (page write).

    Are your runstats up to date? If you are deleting lots of rows and reorgchk is not recommending anything, then something smells fishy. Either your runstats is out of date, or your clustering is staying constant with you deletes and inserts.

    Andy

  3. #3
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    As far as I know: rows are deleted from the table (but freed space is owned by the table until reorg); index keys are pseudo-deleted.

  4. #4
    Join Date
    Mar 2011
    Posts
    20
    Hi

    Thank you for the replies

    But could you please mention what "freed space is owned by table" signifies. Is it file system space ?

    We have single partitioned tables. Each partition with one container file for single tablespace.
    If we perform reorg on the table, we dont notice any reduction in the amount of space occupied by the table/tablespace

    Thank you in advance

  5. #5
    Join Date
    Apr 2012
    Posts
    1,034
    Provided Answers: 18
    In this thread you did not post your DB2-server version+fixpack and operating-system.

    You seem to be asking questions that are answered in the documentation.
    Please study the documentation.

    For v9.7
    REORG INDEXES/TABLE command - IBM DB2 9.7 for Linux, UNIX, and Windows

    For v9.5
    REORG INDEXES/TABLE

  6. #6
    Join Date
    Mar 2011
    Posts
    20
    Sorry to have missed the details of DB2

    Db2 version is 9.5 fp 6a and os is LUW ( our platform is zlinux)

    From the above link , I infer that table reorg ( using reorg table tablename)
    cleans up psuedo deleted keys and free its space ( here container pages) + reorganizes data. All the indexes are also taken care (reorg) during this process.


    CLEANUP ONLY releases the psuedo eleted empty pages + psuedo deleted keys from non empty pages as well . It would not perform any reorganization of data. The freed space will be made available to others.

    Please correct me if am wrong.

    Thank you for the clarification

Posting Permissions

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