Re: can not sort rows

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... While this is true, given the description of the problem, I feel it important to point out that starting with release 4.1 of OnLine, temp *files* used for sorting are located in the Unix file system (this change was made to facilitate parallel sorting, added in 4.1, by allowing users to specify a list of temp directories to use for PSORTing). Even if you are not using parallel sorting, the sort files needed will be located in DBTEMP (env. variable - /tmp if not set). Temp *tables* are still created in the root dbspace. So, as a note to the original poster of the problem, if you are using OnLine 4.1 or better, you may want to check the amount of space available in /tmp. I'd also recommend taking a peek at you ronline log, as -5 is an i/o error (at least on my box) and could result in one of your chunks being marked as down (also indicating possible disk problems). >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. One aspect you fail to consider is extent concatenation (when a new extent is needed for the temp table, or any table for that matter, and sufficient space is available in the chunk adjacent to an existing extent for that table, the existing chunk is simply made larger by the new space worth; thus rather than 2 smaller extents, you'll have one larger extent) which often comes into play when only one process is building a temp table (no competition for rootdbs space by other processes). So your 8meg is accurate as a worst-case scenario. In the best case, everything winds up in one enormous extent. Note that the 5.0 release improved on temp space allocation by using the estimated number of rows from the optimizer to calculate an appropriate extent size for the temp table (the goal being to get the whole thing in a single extent from the get-go). This could be troublesome if your statistics get out of date such that the optimizer is overestimating the number of rows returned (more space will be allocated than you really need). Another good reason to do a regular update statistics for volatile tables. Dave -- Disclaimer: These opinions are not those of Informix Software, Inc. ************************************************************************** The heart and the mind on a parallel course, never the two shall meet. -E. Saliers