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