temp DB clean up question

hi: I am running 7.23 on solaris 2.6. I got a tempdb with rawspace. For some reason, when I look at the volumn of that dbspace, it went up to 38% and I am not running anything. How do you clean this out? thanks. yan

Yan Zhu wrote: > > hi: > I am running 7.23 on solaris 2.6. I got a tempdb with rawspace. For > some reason, when I look at the volumn of that dbspace, it went up to > 38% and I am not running anything. How do you clean this out? There are some conditions under which temp tables are not immediately released. Bouncing the engine will clean that up on restart. Art S. Kagel

Not necessarily true. We had the same problem last week. Even rebooted the sever and the temp spaces did not recover. Tech support said it was a known bug where some temp tables, particularly those associated with hash joing, do not get properly cleaned up by the engine. I'm not sure what version of the engine this is fixed in or even what versions are susceptible. Our platform was 7.22.fc2 on Digital Unix. The only solution was for Informix to dial in and clean up because you can't drop the temp dbspaces on your own. An error message says that the dbspace is not empty. The other solution would be to reinitialize. "Art S. Kagel" <kagel@bloomberg.net> wrote: >Yan Zhu wrote: >> >> hi: >> I am running 7.23 on solaris 2.6. I got a tempdb with rawspace. For >> some reason, when I look at the volumn of that dbspace, it went up to >> 38% and I am not running anything. How do you clean this out? >There are some conditions under which temp tables are not immediately >released. Bouncing the engine will clean that up on restart. >Art S. Kagel Barry Leb Sr DBA Information Technology Division Harland 2939 Miller Road Decatur, GA 30035 Phone: (770) 593-5050 x6137 Fax: (770) 593-5420 email: bleb@harland.net

Try running a couple queries: select name from sysdatabases where round(partnum / 1048576, 0) = <dbspace number from onstat -d> If anything comes up, someone created a database in your temp space. Don't laugh, I have actually found this to have happened more than once. One time, the temp space wasn't marked temp, another time, the temp space was marked as temp. You should also run: select tabname from <database>:systables where round(partnum / 1048576, 0) = <dbspace number from onstat -d> for all your databases. Hope this helps. On Mon, 11 Jan 1999, Barry Leb wrote: > Not necessarily true. We had the same problem last week. Even > rebooted the sever and the temp spaces did not recover. Tech support > said it was a known bug where some temp tables, particularly those > associated with hash joing, do not get properly cleaned up by the > engine. > > I'm not sure what version of the engine this is fixed in or even what > versions are susceptible. Our platform was 7.22.fc2 on Digital Unix. > > The only solution was for Informix to dial in and clean up because you > can't drop the temp dbspaces on your own. An error message says that > the dbspace is not empty. The other solution would be to > reinitialize. > > "Art S. Kagel" <kagel@bloomberg.net> wrote: > > >Yan Zhu wrote: > >> > >> hi: > >> I am running 7.23 on solaris 2.6. I got a tempdb with rawspace. For > >> some reason, when I look at the volumn of that dbspace, it went up to > >> 38% and I am not running anything. How do you clean this out? > > >There are some conditions under which temp tables are not immediately > >released. Bouncing the engine will clean that up on restart. > > >Art S. Kagel > > Barry Leb > > Sr DBA > Information Technology Division > Harland > 2939 Miller Road > Decatur, GA 30035 > > Phone: (770) 593-5050 x6137 > Fax: (770) 593-5420 > email: bleb@harland.net > > > Rob Wilson rwilson@informix.com