IDS Forum

Re: Table Extents Problem--No more Extents--

Posted By: Kern Doe
Date: Thursday, 21 June 2007, at 2:42 p.m.

In Response To: Table Extents Problem--No more Extents-- (OMER KHAN)

Maximum number of extents?
The maximum number of extents is derived from the maximum size of a table (32GB for versions 7 and 9 w/o enabling large chunks) associated with the Informix¢s feature of automatic extent doubling after every 16 extents. So, if using the default 1st and next extent sizes to be 8k (or 4pg on a 2K page size system), I interpret the calculation as:
1st next(auto) tableSize tablesize tot_num_ext

8k 8k (15*8) 120k 15
8k 16k (120+15*16) 360k 30
8k 32k (360+15*32) 840k 45
8k 64k (840+15*64) 1800k 60
8k 128k (1800+15*128) 3720k 75
8k 256k (3720+15*256) 7560k 90
8k 512k (7560+15*512) 15240k 105
8k 1024k (15240+15*1024) 30600k 120
8k 2048k (30600+15*2048) 61320k 135
8k 4096k (61320+15*4096) 122760k 150
8k 8192k (122760+15*8192) 245640k 165
8k 16384k (245640+15*16384) 491400k 180
8k 32768k (491400+15*32768) 982920k 195
8k 65536k (1048440+15*65536) 1965960k 210
8k 131072k (1965960+(15*131072) 3932040k 225
8k 262144k (3932040+15*262144) 7864200k 240
8k 524288k (7864200+15*524288) 15728520k 255
8k 1048576k (15728520+15*1048576) 31457160k 260
8k 2097152k 31457160+2097152 33554312k 261

Basically, for the 1st 15 extents before the next extent size is internally doubling, the tablesize is 120kb, after the 30th extents, the tablesize is 360kb, and so on.
So, for the 2k page size system, the max number of extent is 260+1=261. IDS cannot grab any more extent because it has reached its limit (table limit=32GB).
(My experiment shows that the maximum number of extents can go beyond 261 if my dbspace consists of many very small chunks)
If your table (not fragmented) hits the 32gb limit, I suppose you can also get the message "no more extents" even though your table does not have a lot of extents -- so don't be confused,

Two things you can monitor: number of extents and/or the table size.

I welcome any comments.
Kern --

----- Original Message ----
From: OMER KHAN <oskhan@i2cinc.com>
To: ids@iiug.org
Sent: Thursday, June 21, 2007 4:48:54 AM
Subject: Table Extents Problem--No more Extents-- [9397]

Hello All,

I would like to know that how can i monitor the no of extents for the table,
so that i could know in advance before the problem occurs.

Also let me know the best possible way of handling the situation, where we are
unable to insert new rows into a table due to no of extents. Also let me know
that what is the maximum limit of extents for one table in the page size of
2k.

I have verified that i have sufficient dbspace available for the allocation.

I will be looking forward for your response.

Regards,
Omer Saeed Khan

*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.

Messages In This Thread

• Table Extents Problem--No more Extents--
  OMER KHAN -- Thursday, 21 June 2007, at 4:48 a.m.
  • RE: Table Extents Problem--No more Extents--
    Jack Parker -- Thursday, 21 June 2007, at 7:28 a.m.
  • Re: Table Extents Problem--No more Extents--
    ART KAGEL, BLOOMBERG/ 731 LEXIN -- Thursday, 21 June 2007, at 9:39 a.m.
    • RE: Table Extents Problem--No more Extents--
      Jack Parker -- Friday, 22 June 2007, at 8:13 a.m.
  • Re: Table Extents Problem--No more Extents--
    Kern Doe -- Thursday, 21 June 2007, at 2:42 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.