RE: Weird Sysmaster behaviour

The error can either indicate that the dbspace is full (which you have ruled out), or that the table itself has too many extents. Because of the structure of the table meta-data, there is a limit to the number of extents one table may have (it's dependent on the page size and the structure of the table; there's no hard limit). The query you show is a start, but will show the total number of extents for every table in the instance. Try: select count(*) from sysextents where dbsname = '<dbname>' and tabname ='<tabname>'; That should tell you about the table you're concerned about. There are several strategies for fixing this. The all revolve around getting a better extent size on the table (since it's most likely too small). I find the simplest is to work out how large your table is (oncheck -pt dbname:tabname, and look for "Number of Pages Used"), then: alter table <table> next size <number of KB>; Remember that the output from the oncheck is in pages, and the input into the alter table is in KB, so you'll need to convert. On AIX and Windows each page is 4K, on everything else it's 2K (unless you're using IDS 10 and configurable page sizes, in which case it's whatever you've assigned it to be). Now: alter fragment on table <table> init in <dbspace>; Where <dbspace> is the name of the space you want to store the table in. Keep in mind that this will lock the table for as long as it takes to rebuild it. Also, this method will only reduce the number of extents to 2, not one (because you can't alter the initial extent size), but this is probably good enough. You may also want to add some extra to your extent size to allow for growth. Finally, you will probably want to re-alter your next extent size down to a lower value, so that it won't attempt to allocate a large extent the next time it needs one. I hope that answers your question. Oh, one more note: if you're using Kernel I/O (instead of AIO VPs), the %wio column is always zero (at least, it is on the OS's I've seen). Your system may be I/O-bound. Sar -d will give you some idea of the I/O load in that case. DC > -----Original Message----- > From: owner-informix-list@iiug.org > [mailto:owner-informix-list@iiug.org] On Behalf Of HarryH > Sent: Sunday, November 27, 2005 10:06 PM > To: informix-list@iiug.org > Subject: Weird Sysmaster behaviour > > 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 sending to informix-list