Questions on the extents

[retroman]

Hi,

I just come to learn about administration of Informix DB. Wish you can give me some good advice. I have many large tables (>50MB) in my Informix IUS 9.14 DB (running on Irix 6.5) which all has extent of 16 pages (the default) when these tables are first created. So I think these tables probably has many extents cluster all over in the disk areas which I believe does contribute to the poor performance. Here are my questions:

1)How can i confirm a table has got more than 1 extent?

2)If I do a archive and delete the old data from these tables, will the engine automatically reclaim the extents/pages for reuse since the data is reduced? Will the reclaimed pages able to reuse for other tables?

3)If I intend to drop and recreat these table with bigger extent to have a contiguous page for each tables. How should I go about doing it in order to have a clean sheet of pages for each table out of the existing cluster dbspace?

any informative comments is welcome.

 

[jbcamel]

I am not using your version of Informix but hopefully things are similar:

1. - First of all if your table is anything other than tiny I can guarantee you have too many extents. There are a few ways to see how many extents there are. I think the easiest way is:
- first find out exactly how many rows are in the table (select nrows from systables where tabname = "whateverthenameis";

- search for table in onstat -T command
onstat -T |grep nrows (so if there are 1000 rows it's 'onstat -T|grep 1000)

Unless you have more than one table with the exact same number of rows you should get one line which shows that table. The last number on the right is the number of extents currently allocated. If you do have more than one table it gets more complicated. You would have to figure out the 'tblnum' which is the partnum from the 'systables' table converted to hex.

2. - Deleting rows from a table only frees space up for that table. If you want to the space to be available to other tables you have to 'alter' the table by adding or deleting a column somewhere in the middle of the row. This will rebuild the table and free up deleted space. For very large tables this can be very time consuming.

3. - To rebuild the table with the correct extent sizes you need to onload it with the unload command (unload to "somefile" select * from table. Drop the original table, recreate it with the correct extent sizes and then reload the data. You are not guaranteed to get contiguous pages. It will depend up how fragmented your entire instance is. If you other tables are not scattered all over the place then you should be OK.

Hope this helps.

Jane