RE: Weird Sysmaster behaviour

Harry, If your table is not fragmented, you may have reached the maximum number of pages for a tablespace -- 16.7 million. Trying to exceed 16.7 million pages in a tablespace will produce the extents error you received. Use oncheck -pT to verify how many pages your table is using. Also, make sure you run queries in sysmaster with dirty read isolation level. -Dave David Buchholz Sr. DBA Vital Processing Services -----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: nomore 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 sysextentsIt 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