Debugging "no free disk space" error

I've inherited an Informix 7.2 database, which I unfortunately do not know how to administer. I'm reading the documentation, but I hope that someone can give me a few pointers on how to track down this problem. (Really, even a one sentence reply with a command to look into would be great.) A somewhat complicated database query is failing with the errors: SQL: -264: Could not write to a temporary file. ISAM: -131: ISAM error: no free disk space The query includes joins, subqueries, and order by. It does not modify any tables, and the database it operates on is not in the rootdbs dbspace. My investigation suggests that the dbspace rootdbs is filling up (as opposed to a disk partition). onstat -d shows that rootdbs has size 10000 and free space 847 (these seem to be 2k blocks). When the query is running, onstat -d shows the free space decreasing to near 0, then the error occurs and the free space jumps back to 847. (df shows no partitions filling.) What I can't figure out is, what exactly is taking up space in rootdbs and (particularly) what is filling up. I suspect that it is a log that's filling, in which case I'd be happiest to lop it off, but I don't know how. The database does not have high reliability requirements (at least right now); much more important is that it not suck developer time as it is doing. Any suggestions on what I can do to forget about the database in the future are greatly appreciated. Last, I'm not exactly sure what set of tools I have on hand. I've seen many references to ON-Monitor in the docs, but I don't have an onmonitor command. As far as I can tell, I do not have any graphical tools, only terminal-based ones. My platform is Solaris 2.5.1. Thanks, Andrew Email Cc's appreciated. -- In general NT is what you get if you could do some portions of Unix all over again, and then managed to get it all terribly wrong. - Maury Markowitz <maury@remove_this.istar.ca>

I seem to have circumvented the problem described in the last message. I figured out how to add a chunk to the rootdbs dbspace with onspaces, and this allowed my query to finish (though it seemed to eat a good way into the second chunk in the process--which was freed when the query finished). I still do not understand what the space was being used for, however. In my onconfig file, DBSPACETEMP is not set, so (according to the comments), /tmp should be used for scratch space. In fact, I know this is the case because we filled /tmp on an earlier query. So rootdbs shouldn't be used for temp space. Furthermore, I discovered the oncheck -pe command, which lists detailed usage information for each chunk. I ran it once before the troublesome query, and once during. The diff showed free space disappearing, but didn't tell me where it went: --- oncheck1 Wed Feb 24 21:29:43 1999 +++ oncheck2 Wed Feb 24 21:29:48 1999 @@ -3,7 +3,7 @@ Chunk: 1 /web/informix_db/tripod/ROOTDBS.000 Size Used Free - 10000 9153 847 + 10000 9777 223 Disk usage for Chunk 1 Start Length ------------------------------------------- --------- --------- @@ -142,9 +142,8 @@ sysutils:informix.nsm_session 9079 8 sysutils:informix.nsm_lock 9087 8 sysutils:informix.nsm_serial 9095 8 - FREE 9103 416 TBLSPACE TBLSPACE 9519 50 - FREE 9569 431 + FREE 9745 255 If anyone can enlighten me as to what was going on, I'd be grateful. Thanks, Andrew -- In general NT is what you get if you could do some portions of Unix all over again, and then managed to get it all terribly wrong. - Maury Markowitz <maury@remove_this.istar.ca>

Andrew Pimlott wrote: > > I seem to have circumvented the problem described in the last message. > > I figured out how to add a chunk to the rootdbs dbspace with onspaces, and this > allowed my query to finish (though it seemed to eat a good way into the second > chunk in the process--which was freed when the query finished). > > I still do not understand what the space was being used for, however. In my > onconfig file, DBSPACETEMP is not set, so (according to the comments), /tmp > should be used for scratch space. In fact, I know this is the case because > we filled /tmp on an earlier query. So rootdbs shouldn't be used for temp > space. Rootdbs is used for temp table creation and /tmp for sort-work files in the absence of DBSPACETEMP and PSORT_DBTEMP values. The Group by clause caused a temporary table to be created. Also depending on the value of OPTCOMPIND your joins may have caused the creation of temporary indexes or hash tables which would normally also gon into DBSPACETEMP but are defaulting to ROOTDBS. Art S. Kagel