Informix ISAM error 136: no more extents

Technote (troubleshooting)

Problem(Abstract)

When attempting to insert rows into a table, the following error is returned to the client:

ISAM error 136 : no more extents

Cause

IDS can only allocate a maximum of 16,777,215 disk pages per table partition or fragment. It should also be noted that this is independent of the dbspace configured page size.
Also, IDS can only track approximately 200 extents (logical grouping of disk pages) per table partition or fragment. The most likely root cause will be that one or both these values have been realized.

Diagnosing the problem

As user informix, run oncheck -pt <database>:<table name >".

In the resulting output, examine the value for "Number of pages used" or "Number of pages allocated". If either one is equated to 16,777,215 then this is the most likely root cause of the problem. 

However, should these values not be close to 16,777,215 then it's likely that the table has already allocated a high number of smaller extents (logical grouping of data or index pages).
If so , then the value for "Number of extents" will be close to or greater than 200, (there is no 'hard' limit due to various architectural reasons).

Example output to oncheck -pt :

TBLspace Report for dbase:informix.customer

Physical Address 1:36878
Creation date 801 Page Locking
TBLspace use 4 bit bit-maps
Maximum row size 134
Number of special columns 0
Number of keys 0
Number of extents 186
Current serial value 10139
Current SERIAL8 value 1
Current BIGSERIAL value 1
Current REFID value 2
First extent size 8
Next extent size 16
Number of pages allocated 16777215
Number of pages used 16777215
Number of data pages 16777215
Number of rows 1118341
Partition partnum 1049005
Partition lockid 1049005

Resolving the problem

If the value for Number of pages used or allocated is at 16777215, then try one of the following recommendations:

Non Fragmented Tables:

1. If the indexes defined for the table are attached , drop and recreate the table's indexes as "detached" (in their own separate partitions).

Note: A CREATE INDEX statement that does not specify IN TABLE as its storage clause creates indexes that store their pages in separate partitions from the data pages by default.

2. Fragment the table using a Round Robin or an ｅxpression Based strategy. This will result in additional partitions being created in order to track the table internally.

If the value for Number of extents is close or exceeds 200, then :

Reorganize the table using a larger initial extent size to defeat a high number of smaller extents being allocated.

3. Recreate the table in a dbspace with a larger page size. If you do this make sure you run dbschema -ss on the database first. This will capture the syntax for all database objects that depend on the table you are about to drop and create. After the table is created and loaded then run the dbschema output file. It's likely most of the objects will fail to create because they already exist but the objects that aren't there will be created. This method ensures you will not miss recreating any database objects dependant on the table ( synonyms, triggers, views, constraints, and so on ). Also, make sure you tune the BUFFERPOOL parameter for the new dbspace page size. If this wasn't already done then Informix will automatically create one for you with the default size.

http://www-01.ibm.com/support/docview.wss?uid=swg21395852