Re: can not sort rows

In article <9382@emory.mathcs.emory.edu> dave.snyder@snide.com (Dave Snyder) writes: >Quoting clay irving... >> >> We've written a I-4GL program to process an order table contain 17,000 >> rows. The table is being joined to several other tables. There is a good >> amount of substring manipulation and temporary tables. We receive a >> message "Can not sort rows" -- "System error -5". An Informix SE >> indicated that temporary tables are being built in rootdbs, regardless of >> the dbspace the working database is in. I added a 100MB chunk to rootdbs >> and a 150MB chunk to the working dbspace, but I still get the same error >> message. I hate to keep throwing disk space at the problem until it >> <hopefully> goes away -- Does anyone have an idea what's going on and a >> possible solution? >> >Wow, I knew this could happen but I've never heard of it until now. First >of all, ALL tmp tables are stored in the rootdbs. Now for the answer to >your problem... > >An online table can have a maximum of ~200 extents. The minimum size of >an extent is 16K. Now quick math will say, "DAMN! A table can only be >3.2 meg." That's not correct thinking though. Every so many extents, >online doubles the size of the extent. For example: > Extent # Size > -------- ---- > 1 16K > 64 32K > 128 64K > 192 128K > etc. etc. Bug 10207 asserts that this doubling occured too slowly, and as of 4.10, doubling occurs every 16 extents rather than every 64. Craig >Now I forget the exact numbers but you get the idea. Anyway, when a tempory >table is created by a SELECT statement in online, it gets the default extent >size and the default next_extent size. This will eventually give a limit >on the maximum size of a table... approx. 8 megabytes. Although I can't >remember the above numbers, that 8meg sticks in my mind because we did >the calculations in Online Administrator's class. This is my guess at >what your problem is based on what you said. My suggestion is to create >the temp tables yourself (with appropriate extent sizes), select into them, >and then drop them when you are finished. > >DAS >-- >David A. Snyder @ Snide Inc. - Folcroft, PA Current Release > is db4glgen-3.7 >UUCP: ..!uunet!das13!dave INTERNET: dave.snyder@snide.com