Hi,

We are getting an error -271 while inserting records to a table even
though we see the database free space is more than 60%.

Our Informix Version is : Informix Dynamic Server 2000 Version
9.21.UC4 on Solaris 9

To sort the issue, I dropped the table and recreated an empty table.

Could you please let me know on what reason the database could not add
new rows to a table. During the insert error, there was around 5
million rows in the table. The row size on the table is 1720 and the
index size is 325. Extent size is 16 and next size is 16, lock more is
row.

Is there any table size restrictions on the DB version or can there be
any bug. We are daily performing update statistics on the full DB.

Please let me know your thoughts on the issue.

Dhaya

Informix always raises two errors. A generic one and a detailed one. The
later is the ISAM error code.
-271 is the "generic" code. We would need the ISAM error code also...
One usual error on these situations is the limit of extents a table can
have. This limit is not the same always. It varies with the table schema
(mostly with the number os special - VARCHAR - columns) and the page size.
On 2K page sizes the limit usually is around 200, but can be lower if your
table has lots of VARCHARs. On 4K page sizes it's around the double of that.

You probably should alter the extent definitions (ALTER TABLE ... NEXT SIZE
....)

V11.7 does not suffer from this issue.
In any case, always get the ISAM error code.

Regards.


On Thu, Mar 31, 2011 at 6:22 PM, sdhaya <dhaya.snidhi (AT) gmail (DOT) com> wrote:



--
Fernando Nunes
Portugal

http://informix-technology.blogspot.com
My email works... but I don't check it frequently...

On Mar 31, 8:43*pm, Fernando Nunes <domusonl... (AT) gmail (DOT) com> wrote:
Thanks for the reply...

For a table of this size and if would like to store around 10 millions
rows, could you please recommend the extent size and next size . Our
Page size is 2K.

Dhaya

Could be one of three problems, the ISAM level error code would tell you
which. Solutions included:


1. No space available in the dbspace.
1. Add chunks - but you indicate that this is not the problem
2. The table (including any attached index pages) exceeds the
16million page limit (2^24 pages actually)
1. Detach all indexes to free pages in the table's partition for more
data
2. Fragment the table across multiple dbspaces to permit more rows.
3. The table (' ') has more than the maximum number of extents which
is about 200 depending on the number of attached indexes and special columns
(VARCHAR, BLOBs, etc) the table has.
1. Reorg the table into fewer extents.

Extent sizing is dependent on many factors, here are a few guidelines:

- You only want one or two extents to be actively accessed, so size the
extent to the size of the active data. If six months of data are active,
then the next extent size should hold six months of data.
- Maximum extent size is a chunk, so in 9.21 maximum chunk size was
2GB....
- During a reorg some like to have all of the existing data in one or the
smallest number of chunks, so in this case, you will need 5million pages in
the initial extent, but your max is 1million pages (2GB chunk limit), so...

Art

Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/

Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. Neither do those opinions reflect those of other individuals
affiliated with any entity with which I am affiliated nor those of the
entities themselves.



On Thu, Mar 31, 2011 at 1:22 PM, sdhaya <dhaya.snidhi (AT) gmail (DOT) com> wrote:

initial extent size i would choose

<your page size > * 10 millions

(1720 row size means 1 row on a page.)


Next extent size is dependent on how fast this grows say 1 million a
year,
then i would probably have a next size of
<your page size > * 1 millions

there is an ugly hack on
ftp://ftp.iiug.org/pub/informix/pub/

called size9.sql


you can play around on a test box, it will calc the ext. size for you
based on the nr
of rows.
Simply create the tables and indexes... It does require handy work to
get it going!!


Superboer.