I ran REORGCHK on SALES.ORDERS and retrieved the current statistics from SYSCAT.TABLES. Here’s what I found:

• CARD (row count): 100,000
• OVERFLOW: 15,203 rows (15.2% of total rows)
• F1 value: 15.2 — threshold is > 5% ⚠

F1 is violated because 15,203 rows have overflow records. Each of those rows was updated in a way that made it too large to fit in its original page slot. Db2 stored the overflow portion on a separate page and left a pointer at the original location.

The practical impact: every query that reads one of these rows requires two page reads instead of one. Across a full table scan, this means up to 15,000 additional I/O operations. You will see measurable increases in query elapsed time and buffer pool read activity.

Root cause: The table’s VARCHAR comment and notes columns were updated repeatedly, with each update progressively adding more text. With PCTFREE currently set to 0, there was no reserved space on the original pages for the rows to expand into.

Based on the update pattern I observed — rows growing approximately 35–45 bytes per update cycle, with up to 4 cycles — I recommend setting PCTFREE to 20 on this table.

PCTFREE reserves a percentage of each page for future row expansion. At 20%, each 8 KB page keeps roughly 1.6 KB free. Given your average final row size of ~82 bytes, this provides headroom for approximately 20 row expansions per page before overflow occurs.

Set the new value and fix existing overflow records in one step:

ALTER TABLE SALES.ORDERS PCTFREE 20;  CALL SYSPROC.ADMIN_CMD('REORG TABLE SALES.ORDERS');

After the REORG, run RUNSTATS and re-check REORGCHK — F1 should return to 0%.

I retrieved the current statistics for ANALYTICS.EVENTS (ORGANIZE BY COLUMN):

• CARD: 24,891 rows (49.8% of the original 50,000)
• COL_OBJECT_L_SIZE: 8,192 KB (current compressed column storage)
• Estimated post-REORG size: ~4,200 KB (~48% reduction)

The deletions have left gaps in every column group simultaneously. Because columnar compression works best on dense, continuous data, the compression ratio has degraded from ~3:1 to approximately 1.6:1. Column scans now process the same number of pages for half the useful data.

To reclaim extents and restore compression, reorganise the table:

CALL SYSPROC.ADMIN_CMD('REORG TABLE ANALYTICS.EVENTS');

Important: Columnar table reorganisation is always offline — there is no INPLACE option. Db2 must decompress every column group, filter out deleted rows, re-encode, and recompress from scratch. This cannot be done while the table is accessible. For ~25,000 remaining rows, expect the operation to complete in under 60 seconds.

Expected results after REORG:

• COL_OBJECT_L_SIZE reduced from ~8 MB to ~4.2 MB (~48% storage saving)
• Compression ratio restored to ~3:1 or better
• Column scan I/O reduced by ~50%
• Analytical query elapsed time improvement: 30–50%, depending on column access pattern

I queried SYSCAT.DATAPARTITIONS for SALES.ORDERS_HISTORY. Here are the current partition boundaries and row counts:

Yes — the JAN_2023 partition contains all 6,204 rows with ORDER_DATE prior to 2023-02-01. It is the only partition that falls entirely within your requested date range, making it a clean archiving candidate.

To detach it into a standalone archive table (this is an instantaneous metadata operation — no rows are copied or moved):

ALTER TABLE SALES.ORDERS_HISTORY
  DETACH PARTITION JAN_2023
  INTO SALES.ORDERS_ARCHIVE_JAN;

After detachment, SALES.ORDERS_ARCHIVE_JAN becomes a regular table containing all 6,204 January rows. You can back it up, export it to cold storage, or drop it depending on your retention policy. The main table continues serving FEB, MAR, and APR partitions with zero interruption.

I scanned SYSCAT.TABLES and SYSCAT.INDEXES for all user tables in schema SALES and evaluated the REORGCHK formulas. Here is a summary of tables with active violations, ordered by severity:

Critical — Immediate action recommended:

• SHIPMENTS — F4 (Cluster Ratio) = 45% ✗ — range scan queries on IDX_SHIP_DATE are incurring severe random I/O overhead
• ORDERS — F1 (Overflow) = 15.2% ✗ — 15,203 rows require two I/O operations per read

Warning — Reorganisation recommended soon:

• LINEITEM — F2 (Space utilisation) = 49.8% ⚠ — ~5 MB reclaimable, full-scan I/O doubled
• INVENTORY.PRODUCTS — F3 (Empty pages) = 26.1% ⚠ — 326 completely empty pages being scanned unnecessarily
• ANALYTICS.EVENTS — F2 = 49.8% ⚠ — columnar table with degraded compression (8 MB → ~4.2 MB after REORG)
• HR.AUDIT_LOG — F7 (Pseudo-deleted index entries) = 30.4% ⚠ — index IDX_AUDIT_ID contains 30,400 ghost entries

Healthy — No action needed:

• FINANCE.TRANSACTIONS — All formulas within thresholds
• FINANCE.ACCOUNTS — Within thresholds

Ask me about any of these tables for a detailed analysis and the exact remediation commands.

Askari Naqvi is a Staff Software Engineer in IBM Toronto Lab with 25+ experience with Db2 LUW engine development and testing experience across various Db2 products, currently working on Db2 Agentic AI development and specifically for Db2 AI Assistant. He can be reached at askarin@ca.ibm.com.

Krishna Guntuka is a Lead Software Engineer with 9+ years of experience across various Db2 products, currently leading Agentic AI development for the Db2 Database Assistant — building intelligent autonomous agents using Python, LangChain, LangGraph, RAG pipelines, and LLMs — with a focus on making Db2 products smarter, faster, and more efficient.

Naveen Balachandran is a Software Developer in the Db2 Agentic AI team, working on Agentic AI technologies and AI-driven solutions for enterprise applications. His work focuses on building AI-powered capabilities, including LLM-based applications, RAG pipelines & intelligent agents. Previously, he worked in the Db2 Text Search and Extenders team, contributing to search and data management capabilities within Db2. His experience across Db2 search technologies and Agentic AI enables him to build intelligent solutions that make Db2 products smarter, more efficient, and easier to use.