In DB2 database administration, what is the REORG utility and in which situations is it typically used?

Introduction / Context:
The REORG utility is a key part of DB2 housekeeping. Over time, repeated inserts, updates, and deletes can fragment table spaces and indexes, leading to poor clustering and degraded performance. This question checks whether the candidate understands that REORG addresses physical organization problems, not statistics collection or security administration.

Given Data / Assumptions:

• We are using DB2 tablespaces and indexes that have been subjected to many data changes over time.
• There may be fragmented pages, out of order rows, and overflow records.
• We want to improve performance and clustering without changing the logical schema of the table.

Concept / Approach:
REORG reads the data from the table space or index, sorts it into an optimal order (often by clustering index), and rewrites it into newly organized pages. This reduces internal fragmentation, reclaims free space, and improves the physical order of rows relative to key values. It is typically used after bulk loads, large delete operations, or when monitoring shows low clustering ratios and poor performance. Therefore, the correct option must mention physical reorganization and performance improvement as its purpose.

Step-by-Step Solution:
Step 1: Identify that the question is about a DB2 utility, specifically REORG. Step 2: Recall that REORG operates at the storage level, reorganizing table and index pages. Step 3: Remember common triggers, such as many row deletions, heavy random inserts, or significant key changes that disturb clustering. Step 4: Note that after REORG, access paths that rely on clustering can run faster due to more efficient I O patterns. Step 5: Choose the option that succinctly describes physical reorganization and performance improvement.

Verification / Alternative check:
In practice, DB2 performance monitors often report clustering ratios, leaf page split counts, and other indicators. When these metrics deteriorate, running REORG typically improves them. Observing lower elapsed times and fewer I O operations for key queries after REORG further confirms that the utility reorganizes data physically rather than acting as a simple statistics or backup tool.

Why Other Options Are Wrong:
Option B is wrong because statistics collection is the job of RUNSTATS, not REORG, although the two utilities may be run together in maintenance schedules.
Option C is wrong because granting and revoking privileges in DB2 is handled by GRANT and REVOKE statements, not by REORG.
Option D is wrong because backup operations are handled by image copy utilities and external storage solutions, not by REORG.

Common Pitfalls:
A common mistake is to rely only on RUNSTATS for performance tuning without considering physical fragmentation. Another pitfall is running REORG too frequently without evidence of benefit, which can consume unnecessary resources. DBAs should monitor key indicators and schedule REORG based on measured need rather than fixed assumptions. Understanding the difference between REORG and RUNSTATS is essential for effective maintenance planning.

Final Answer:
The correct description is that REORG physically reorganizes tables and indexes to remove fragmentation, restore clustering, and improve access performance after significant data changes.