Managing Extents (Informix) 

Use

You need to manage the extents of your Informix database to avoid "extent overflow". This occurs when a table reaches the maximum number of extents allowed.

Tables with a high number of extents can threaten continuous availability of the system, because they might in future cause downtime for reorganization (that is, if growth continues and an extent overflow occurs). The following diagram illustrates how storage is allocated in an Informix database:

Storage Allocation for Informix

Although some impact on performance is possible, extent overflow does not usually cause performance degradation because the SAP system has been optimized to use indexes (however, a high number of extents might cause problems for batch access). Even tables with very many extents (for example, 150) might not necessarily suffer performance degradation. Furthermore, the Informix server has the following features to minimize this problem:

·        Extent fusion

Whenever possible, a new extent is allocated adjacent to an existing extent with the advantage that the number of extents is not increased and the table storage remains contiguous.

·        Extent size doubling

When a table is repeatedly extended, the next extent size is automatically doubled at regular intervals, that is, every 16th time that the table is extended with a distinct (that is, non-contiguous) new extent.

The following factors lead to a large number of small extents being allocated, which in turn can lead to extent overflow:

·        Insufficient next extent size

Every table has a next extent size defined for it. If the next extent size is too small in relation to the growth rate of the table, then the table must be extended using a large number of small extents.

·        Insufficient gap size within chunks of a dbspace

The chunks comprising a dbspace have gaps that can be used to satisfy the requirement for new extents on tables that need to be extended. If the dbspace in which the table resides has insufficient gaps and the table needs a new extent, there might not be enough contiguous space available to satisfy the requirement completely. Therefore, the table must be extended using a large number of small extents.

The interaction between the next extent size (this differs between tables) and the gap size (differs between chunks allocated to a dbspace), determines whether tables are able to grow normally, that is, with the full next extent size being allocated.

The limit for the maximum number of extents that one table can have (see table below) depends on the page size of the operating system and, to a lesser extent, on certain parameters specific to a table. If an object reaches the limit for the number of extents, you must reorganize it.

Page Sizes and Number of Extents for Informix on Different Platforms

Platform	Page Size (KB)	Maximum Number of Extents
HP, SUN, DEC, SNI	2	200 – 230
IBM, NT	4	400 – 460

Procedure

...

       1.      Monitor tables for number of extents. Refer to Managing Extents (Informix).

Look for the following:

¡        Tables with a high number of extents allocated, to make sure that the maximum limit for a table is not reached. Refer to Analyzing Tables by Fill Level, Size, and Extents with SAPDBA. The Computing Center Management System (CCMS) also provides information about extent allocation as well.

Reorganize tables with a high number of extents (see next step).

¡        Tables for which the normal next extent size could not be satisfied. Refer to Analyzing Tables for Critical Next Extent Size with SAPDBA.

Extend the dbspace in which such tables reside to avoid future problems. Refer to Managing Dbspaces (Informix).

       2.      Reorganize tables with a high number of extents.

This lets you consolidate the table into a small number of initial extents and also increase the next extent size for future extensions.

With Informix databases, you do not need to regularly reorganize. However, if a table has a high number of extents, this is a good reason to reorganize it.

Refer to Reorganizing a Single Table with SAPDBA.

Result

You avoid extent overflow and so avoid downtime for your Informix database and the SAP system.