Hi,

I am facing a very strange situation with table tfgld418 which is allocating a new extent nearly every week.

Date / number of rows / total disk spaceused / extends
1 Jun 02 / 12.300.000 / 5.7 Gb / 5
5 Jul 02 / 8.400.000 / 6.3 Gb / 6
12 Jul 02 / 8.750.000 / 7 Gb / 7
18 Jul 02 / 10.200.000 / 7.6 Gb / 8

As you can see above, the row size of the table has never reached the number on 1 Jun. But it is still allocating new extends. And every extend size is 640 Mb ,therefore I need to stop this growth.
In June I deleted about 5.500.000 rows from the table.
I can monitor that I have deleted The rows from informix and from baan . what I am anxious is that the index pages are not deleted or not freed for reuse.

In May 2002 we had a data migration, untill this date we had two dbspces; datadbs for data and idxdbs for indexes, and each of the dbspaces were on different disks thus the index of the of each table was on a different disk and different dbspace. With the data migration we added a new disk named data3dbs to informix and mixed all the data and indexes in the same disks. thus each tables index is now in the same disk.

With the old configuration I had deleted many times such a mass deletion. and never faced this problem .

Has anybady faced a situation like this before?

We are using baan4 c3 / Informix DS 7.31 /Hp Unix

Thanks

Caner

if you delete records from table you can run from baan reorganize table ttaad4225m000 only Data and indicies.
Read ttaad4225m000 help
See the attach help file.

No user on baan and backup before.

Hi,

i remember, there are some problems in informix, with freeing
indexpages. So, if you delete a lot of rows, it could be happend, that the indexpage is not completly freed. so the indexpage could not went to a freepage. In informix 7.3 indexpages and datapages were created in the same extent. So, if the indexpage could not be freed completly, the page is not reused for a new index, only for indexes with the same key values.

Solution : recreate the index

Martin

Hi,

You can do a dump from the table, delete the table, import the dump.
But are your settings in the INF_STORAGE file correct??
(An extend of 640Mb is a very big one)

GN

Hi / Merhaba Cenk

I do such a mass deletion every month and cannot reorganize it every time. I we did not face it before our new database configuration . With the old configuration I wasn't facing a problem like this. With the old configuration I was able to say after how many rows , my table will allocate extend. But now cannot consider when it will allocate a new extend.

if the reason for this is , not freed index pages then all other tables are in the same situation and I then I will need to reorganize whole the database in frequently times.


Thanks

Caner

Hi,
the setting in the inf_storage are Ok.
We planned the 640 Mb extend size. so that it should take less number of extends .


Martin ,
How can I recreate the indexes. without playing with data.
and is this problem solved with informix latest versions


Thanks

Caner

Caner,

from IDS9.2 onwards, datapages and indexpages where written in different extents.

With IDS7.3 you could create the index seperat with

CREATE INDEX index_name ON table_name IN dbspace

To rebuild the index, i think you must drop the index and create the index new. (to create a cluster-index with ALTER INDEX indexname TO CLUSTER is not practicable because there is only 1 clusterindex per table allowed. You need the space for the table 2 times because IDS build a copy of the table, sort the table in index-sequence an write the copy to disk. After this the old index was dropped.)



Seems, you have a lot of work.