From Informix Dynamic Server (IDS) 11.70, syntax exists to merge extents for any table or index, even system catalog tables for which there was previously no solution. This article examines why it matters and provides methods to defragment any number of them in one run.


As an object (table or index) grows, its container (partition) initially has space within it to grow (allocated versus used pages). Allocating some storage in advance improves insert performance, and reading the object back will be quicker with less overhead if those pages are stored together. The amount of space pre-allocated for a table (an extent) can be specified as in the following example (or changed later with ALTER TABLE):

Copy to Clipboard

That reserves 4MB initially, with further extents of 1MB each time it needs to grow. These sizes are declared in KB, with the default being 16 for both values. A typical policy might be to set the next extent size to an eighth of the total table size in a mature database. Depending on the page size, type of object and IDS version, the maximum number of extents can be as little as 220. Should the extent size prove too small, the database engine compensates by employing an “Extent size doubling” algorithm. Extent sizes of indexes are based on those of the corresponding table.

The IBM documentation page “Partition defragmentation” states:

“You can improve performance by defragmenting partitions to merge non-contiguous extents. A frequently updated table can become fragmented over time, which degrades performance every time the table is accessed by the server. Defragmenting a table brings data rows closer together and avoids partition header page overflow problems. Defragmenting an index brings the entries closer together, which improves the speed at which the table information is accessed.”

In IDS 11.50 or earlier, a common intractable problem is the number of extents in system catalog table “sysprocbody”. If the application has many large procedures or functions written in the Informix Stored Procedure Language (SPL), that table often has around 90 extents, with no way to defragment the table due to ALTER not being permitted on any system catalog.

From IDS 11.70, syntax exists to “Dynamically defragment partition extents” in the SQL admin API, for example:

Copy to Clipboard

This does not seem to cause significant logical log turnover, so will not result in “Long transaction aborted” on large tables.

For indexes, the alternate syntax must be used to specify the object by part number (unique partition ID), for example:

Copy to Clipboard

InformixHQ provides an easy graphical interface to choose multiple objects to be defragmented in one request:

The following was written to the message log (actually requested in two sets):

Copy to Clipboard

InformixHQ must have run unnecessary steps that would make real jobs on larger tables take longer. Compression was not selected, and in any case is impossible on system catalogs or with IDS Editions other than Developer or Enterprise.

Note that, for large tables not in a dedicated dbspace, it is likely that the number of extents cannot be reduced below a certain number if a sufficiently large contiguous block of free pages does not exist. Conversely, if a large table is in a dedicated dbspace, it will always have just one extent, as IDS will be able to coalesce the next extent at the end of the current one each time it grows.

Another approach is this SQL:

Copy to Clipboard

Tables and indexes are included above if they have:

  1. more than 10 extents (less than that may not be possible);
  2. less than 100MB used pages (larger objects may need special handling).

The above criterial can be changed in the SQL as preferred.

Two output files are produced:

  1. a report in CSV format;
  2. SQL to defragment the objects.

Examples from running on a test system follow, with column headings added:

Copy to Clipboard

Executing the above generated SQL should result in “OK” being returned by each statement. Nothing is written to the message log. As a guide to run time, running this recently on 551 tables in a real instance containing 400GB used pages in application dbspaces took 30 minutes.


Informix tables and indexes can become fragmented, adversely affecting performance and even preventing further growth, but that can easily be resolved using the “defragment” operation which can be driven in two ways across an entire instance while it remains fully available.


Suggestions above are provided “as is” without warranty of any kind, either express or implied, including without limitation any implied warranties of condition, uninterrupted use, merchantability, fitness for a particular purpose, or non-infringement.