The original problem was that a data load got this error:
271: Could not insert new row into the table. 136: ISAM error: no
more extents

I assumed that it had run out of space. But onstat -d (grepped for the
particular DBSPACE):

Dbspaces
address number flags fchunk nchunks flags owner name
2bacc318 7 1001 9 12 N informix eom_dbs

Chunks
address chk/dbs offset size free bpages flags pathname
2b9e5a90 9 7 0 1000000 6529 PO- eom_chk1.dbf
2b9e5d30 12 7 0 1000000 28884 PO- eom_chk2.dbf
2b9e5e10 13 7 0 1000000 146417 PO- eom_chk3.dbf
2b9e5ef0 14 7 0 1000000 185384 PO- eom_chk4.dbf
2b9f2830 15 7 0 250000 26983 PO- eom_chk5.dbf
2b9f29f0 17 7 0 1000000 66326 PO- eom_chk6.dbf
2b9f2e50 22 7 0 1000000 32814 PO- eom_chk7.dbf
2b9f31d0 26 7 0 1024000 49900 PO- eom_chk8.dbf
2b9f39b0 35 7 0 1024000 277394 PO- eom_chk9.dbf
2b9f3d30 39 7 0 1024000 300435 PO- eom_chk10.dbf
2c5c8018 41 7 0 128000 5162 PO- eom_chk11.dbf
2c9d3a30 42 7 0 256000 79660 PO- eom_chk12.dbf

So there's a fair bit of space, and some chunks with lots of space.
So then I went to a script I have which shows me the tables with
extents > 20 because I though that the offending table had too many
extents. After a lots of problems with the script I eventually opened
DBACCESS and did this:
select count(*) from sysextents
It took over 20 minutes to return 6491.
I've just done it again and it again took over 20 minutes to return
6492.

The full script does group by and having and order by and normally runs
in 1-2 minutes. So something very odd is happening when such a simple
query takes 20 minutes. And while the server does chug away, it's not
highly utilised. Here's sar 5 5:
15:57:21 %usr %sys %wio %idle
15:57:26 29 1 0 70
15:57:31 26 1 0 72
15:57:36 30 2 0 68
15:57:41 33 1 0 65
15:57:46 33 4 0 63

Average 30 2 0 68

Anyone got any ideas? This is well outside my area of knowledge.

Thanks in advance for any help you might give,
Chris Bullivant

HarryH wrote:
> The original problem was that a data load got this error:
> 271: Could not insert new row into the table. 136: ISAM error: no
> more extents
>
> I assumed that it had run out of space. But onstat -d (grepped for the
> particular DBSPACE):
>
> Dbspaces
> address number flags fchunk nchunks flags owner name
> 2bacc318 7 1001 9 12 N informix eom_dbs
>
> Chunks
> address chk/dbs offset size free bpages flags pathname
> 2b9e5a90 9 7 0 1000000 6529 PO- eom_chk1.dbf
> 2b9e5d30 12 7 0 1000000 28884 PO- eom_chk2.dbf
> 2b9e5e10 13 7 0 1000000 146417 PO- eom_chk3.dbf
> 2b9e5ef0 14 7 0 1000000 185384 PO- eom_chk4.dbf
> 2b9f2830 15 7 0 250000 26983 PO- eom_chk5.dbf
> 2b9f29f0 17 7 0 1000000 66326 PO- eom_chk6.dbf
> 2b9f2e50 22 7 0 1000000 32814 PO- eom_chk7.dbf
> 2b9f31d0 26 7 0 1024000 49900 PO- eom_chk8.dbf
> 2b9f39b0 35 7 0 1024000 277394 PO- eom_chk9.dbf
> 2b9f3d30 39 7 0 1024000 300435 PO- eom_chk10.dbf
> 2c5c8018 41 7 0 128000 5162 PO- eom_chk11.dbf
> 2c9d3a30 42 7 0 256000 79660 PO- eom_chk12.dbf
>
> So there's a fair bit of space, and some chunks with lots of space.
> So then I went to a script I have which shows me the tables with
> extents > 20 because I though that the offending table had too many
> extents. After a lots of problems with the script I eventually opened
> DBACCESS and did this:
> select count(*) from sysextents
> It took over 20 minutes to return 6491.
> I've just done it again and it again took over 20 minutes to return
> 6492.
>
> The full script does group by and having and order by and normally runs
> in 1-2 minutes. So something very odd is happening when such a simple
> query takes 20 minutes. And while the server does chug away, it's not
> highly utilised. Here's sar 5 5:
> 15:57:21 %usr %sys %wio %idle
> 15:57:26 29 1 0 70
> 15:57:31 26 1 0 72
> 15:57:36 30 2 0 68
> 15:57:41 33 1 0 65
> 15:57:46 33 4 0 63
>
> Average 30 2 0 68
>
> Anyone got any ideas? This is well outside my area of knowledge.
>
> Thanks in advance for any help you might give,
> Chris Bullivant
>

You should try to limit the number of extents. If you at the time of
creation have knowledge of how many rows will be loaded, the use EXTENT
SIZE to set an initial extent size that can hold all the rows:
CREATE TABLE my_tab (column defs ...) EXTENT SIZE 100000 NEXT SIZE 50000;
The values for FIRST EXTENT and NEXT EXTENT are in kilobytes and must be
a multiplum af the page size.

If you cannot recreate the table there's a couple of options to reduce
the number of extents.
First use "ALTER TABLE my_tab MODIFY NEXT SIZE xxx" where xxx is a size
that will hold (almost) all of the rows.
Then either do "ALTER INDEX idxname TO CLUSTER" or "ALTER FRAGMENT ON
my_table ... INIT IN dbspace". After the alter you have reduced the
number of extents. Please note that during either of the two alter
methods you must have space enough to hold two copies of the table.

PS. If you run 'finderr -136', you will get a fine explanation for the
problem you ran into.

I would unload this view and stick it into a new table in a new
database.

Create an index on the table so you can query it quickly.

Thanks very much to everyone who replied.

I've discovered why the select count(*) from sysextents was taking so
long . . .

The battery died on the RAID array so that all activity was being sent
directly to disk instead of being cached. Thus everything was taking
much longer.

Now I can get back to trying to solve my original problem.

Thanks again for all the help people.