Re: can not sort rows

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. 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