RE: Weird Sysmaster behaviour

Harry, The reason why queries from sysextents can take a long time is that you are trying to run an aggregate function (count(*)) on a sysmaster table. I've always found problems when doing that. Sometimes it works but more often than not it will present problems. But the information you want is already available in another sysmaster table. The query that I would use is SELECT dbsname, tabname, ti_nextns FROM systabnames, systabinfo WHERE partnum=ti_partnum ORDER BY ti_nextns DESC That will tell you the offending tables very quickly. If your system is very dynamic - and I think it might be from the timings to select from sysextents - then I would recommend SELECT * FROM systabnames INTO TEMP t1; SELECT * FROM systabinfo INTO TEMP t2;and then run the query from the temp tables. That is really the safest way of doing things where sysmaster is concerned. IMHO Regards Malcolm -----Original Message----- From: owner-informix-list@iiug.org [mailto:owner-informix-list@iiug.org] On Behalf Of HarryH Sent: 28 November 2005 05:06 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 sysextents It took over 20 minutes to return 6491. I'vejust 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

I thought systabinfo only include table info that has been cached by IDS? The query below seems ok in the latest 10.00.TC3 release. select dbsname, tabname, count(*), sum(pe_size) from systabnames a, sysptnext b where a.partnum = b.pe_partnum group by 1,2 order by 1,2 Estimated Cost: 171 Estimated # of Rows Returned: 1 Temporary Files Required For: Order By Group By 1) djw1.a: SEQUENTIAL SCAN 2) djw1.b: INDEX PATH (1) Index Keys: pe_partnum pe_extnum Lower Index Filter: djw1.a.partnum = djw1.b.pe_partnum NESTED LOOP JOIN