Re[3]: Question on the contents of tempdbs

I am talking about temp tables that are in the temp dbspaces. Example: I am in dbaccess running an sql that is writing to a temp table, and I CRTL-C to stop it. This orphans the temp table and it is not released and the temp dbspace is not empty. With 7.11, if I bounce the database the table is released and the temp dbspace is now empty. And I could drop the temp dbspace if I needed/wanted to. With 7.22, given the same scenario, if I orphan a temp table in the temp dbspace I can never release it and get the temp dbspace empty, no matter what I try. And, therefore, I can never drop the dbspace either. With 7.23, I can CTRL-C it and the work is rolled back and the dbspace space comes back. Sorry, I don't have a bug number. Upgrading to 7.23 was my solution. Dianne ______________________________ Reply Separator _________________________________ Subject: Re[2]: Question on the contents of tempdbs Author: sujata_soman_at_omm-la2-infotech-001@internet.omm.com at INTERNET Date: 11/7/97 2:33 PM Dianne, Thanks for the reply. Are you talking about temp TABLES or temp DBSPACES. Because, I am having a problem with TEMP DBSPACES. If it was DBSPACES that you meant, is there a bug number with INFORMIX ? What is the solution to this ? Did anybody else face this problem ?? Thanks Sujata ____________________Reply Separator____________________ Subject: Re: Question on the contents of tempdbs Author: <dianne.pendleton@autodesk.com (Dianne Pendleton)> Date: 11/7/97 12:08 PM I had this too with HP-UX 10.20. History: On 7.11 if temp tables get orphaned they do not release unless you bounce the database. I tested 7.20, 7.21, 7.22 looking for a fix. 7.20 and 7.21 acted like 7.11 if I remember correctly. 7.22 was a disaster in that even bouncing the database did NOT release the tables. And then you couldn't drop the non-empty dbspace as you described. We decided to wait for 7.23 and it does appear to fix all of the problems. Dianne ______________________________ Reply Separator _________________________________ Subject: Question on the contents of tempdbs Author: sujata_soman_at_omm-la2-infotech-001@internet.omm.com at INTERNET Date: 11/6/97 3:39 PM Hi everybody, I have a very funny situation here. I am unable to drop my temporary dbspace because accoding to ODS "7.22", " the dbspace is not empty ". Now, what on earth could exist in the tempdbs in Quiescent mode, or when nothing is going on ??!! I did an oncheck pe and this is what it looks like DBspace Usage Report: tmpdbs Owner: informix Created: 08/07/96 Chunk: 3 /dev/tmpdbs Size Used Free 125000 101259 23741 Disk usage for Chunk 3 Start Length ---------------------------------------- --------- --------- OTHER RESERVED Pages 0 2 CHUNK FREE LIST PAGE 2 1 TBLSPACE TBLSPACE 3 50 TBLSPACE TBLSPACE 821 50 TBLSPACE TBLSPACE 1671 50 TBLSPACE TBLSPACE 2521 50 TBLSPACE TBLSPACE 3371 50 ...... ..... ( no actual table names specified ) The only thing that I ought to mention here is that I have two chunks in this dbspace. ( tmpdbs and tmp2dbs ). All I am trying to do is drop the second chunk and re-create it as another dbspace. The disk usage for the other chunk is pretty much the same, except that it has a "FREE" space alternating with every TBLSPACE Any ideas ?? I need that second chunk, since it is rarely getting used in this setup. Speaking of which, is there a way to tell how much tempspace a process would require / or how much it used ?? Also, is there a way to reduce it without changing the code ?? One of my processess is taking up all temp space and then it aborts since it cannot find any more. Thanks Sujata