Informix 7.23 on HPUX 10.20 has maximum record.

[Vimala Viriya]

I run Baan IVc4 application with Informix 7.23 on HP server K370 with HPUX 10.20 and memory 4GB.
On of the table reached 19.7 million record, the table can not be added with error message "can not add new extent" informix error 136. The Disk allocation is 8 GB and the table size at 19.7 million record is 5.1 GB.
The number of extend is 2.
Why the extents can not be added?

[Rainer von Bongartz]

I guess you don't have enough disk space for the
dbspace in which the table resides.

do an onstat -d and take a look at the 'free' value for dbspace where the table resides.

The extend that INFORMIX wishes to allocate for the table must be bigger than the free space in the dbspace.

Add a new chunk to the dbspace and off you go.

Regards
Rainer

[Vimala Viriya]

I have run the onstat -d and I attached the report.
Actually I still have large free space for the new extents and you can see in the report that the chunk 22 for data and chunk 23 for index.
Thank you for your help.

[Rainer von Bongartz]

Take a look at systables to find out how big the next extend will be.

Then you will see if this extend will fit in the space.

databases sysmaster;

select nextsize from systables where tabname = ;

Regards
Rainer

[Vimala Viriya]

Thank you for your help.
I run dbaccess to get the report as attach.
It seem that the space is enough since the next size is 32.

Regards

[Uli_3]

Hi,

have you checked the number of extents you have on your indices?

Please run the statement

oncheck -pt :

Attach the result, maybe I see the problem.

Uli

[Vimala Viriya]

Thank you for your help
I run the oncheck as you suggested and I
attached the file. Please help me.

Best regards

[Uli_3 · Accepted solution]

Hi,

what I see is that there are a lot of extents on your indices, the Baan-System tries to insert a row and the database can not add an extent for one of your Indices. I had the same problems here with Baan and Informix. So it is the best way to recreate the indices.
On the first Index you have 197 on the second 227 and on the third you have 229. So I think it is the best to recreate all of them. When I have done this, I run the dbschema to see how the table and the indices are defined. Then drop the index and create it new.
But you need downtime for this, because the table is locked when you create the index. For another table with more then 30 Mio. rows we need on a HP N-Class more than 4 hours. It is the best way to do this over a weekend. If possible try it on a test system or test table. For my interest, do you run Baan on Informix in level 1 or level 2 Mode.
I hope this helps you.

Uli

[Vimala Viriya]

Hi Uli,
Thanks for the help. It takes me sometime to figure out how to change the indices. I tried to change the inf_storage but it does not work. I dump the table and recreate with dump but I get the same number of extent for index. Could you assist me in reorganizing my databases so that the extent number can be smaller.
Thanks & regards

[Uli_3]

Hi,

to change the inf_storage helps you only for the extents of the table itself. Normaly it is enough to drop the index and recreate it. The reason for this is that informix does not how many row you have in the table at the end when you load the data.
The dbschema-comand from informix genterates an sql-systax for any table to create with all the indices (dbschema -d -t -ss ). You only need the part with the indices. Drop the original index and create it new. Please check the the FILLFACTOR-Parameter in the onconfig-file, we use 90. Change it before you create the index(I think you have to restart the database that this new value work). Maybe it is usefull to run an "update statistics low" for the table before you start the create of the index.
When you now start the creation of the index Informix knows how many rows you have in the table and normaly the index has not to much extents.

Uli