Maintaining data organization

Data that is physically well-organized can improve the performance of access paths that rely on index or table scans, and reduce the amount of disk storage used for the data.

About this task

Depending on the number of changes, you might encounter performance degradations for the following types of operations when your data becomes disorganized:
  • Dynamic SQL queries.
  • Updates and deletes. For example, delete operations sometimes result in pseudo-deleted index entries, which can result in additional lock contention. You can reduce the necessity of frequently reorganizing your data by enabling automated index cleanups.
  • ALTER statements (especially those that run concurrently).
  • Concurrent REORG and LOAD utilities.
  • Unloading a table that has had many changes before reorganization.

Procedure

To determine when to reorganize your data, use any of the following approaches:

  • Monitor statistics for increases in the following values:
    • I/O operations
    • Get page operations
    • Processor consumption

    When performance degrades to an unacceptable level, analyze the statistics to develop your own rules for when to reorganize the data in your particular environment.

  • Invoke the DSNACCOX stored procedure to get recommendations based on real-time statistics values.
  • Query the catalog.
    Member DSNTESP of the SDSNSAMP data set contains sample useful queries for determining whether to reorganize
  • Use the REORG utility.
    The REORG utility embeds the function of catalog queries. If a query returns a certain result (you can use the default or supply your own), REORG either reorganizes or does not reorganize. Optionally, you can have REORG run a report instead of actually doing the reorganization. The following REORG options invoke the catalog queries:
    • The OFFPOSLIMIT and INDREFLIMIT options of REORG TABLESPACE
    • The LEAFDISTLIMIT option of REORG INDEX
  • Always reorganize your data after table definitions change.
    When ALTER TABLE statements are used to make any to the following changes to table the table space is placed in advisory REORG-pending (AREO*) status:
    • Add columns
    • Data type changes
    • Changed column lengths

    When an existing column is changed, the table space is placed in AREO* status because the conversion to the new definition is not immediate. Reorganizing the table space causes the rows to be reloaded with the data converted to the new definition. Until the table space is reorganized, the changes must be tracked and applied as the data is accessed, which might degrade performance.