Re: Temp tables not getting removed

In article <4vfckk$sa@ns.oar.net>, Scott Ratliff <scottr@carsinfo.com> writes > >Greetings, > >I am working on an HP-UX 10.01 system running On-Line 7.13.UC1 >the the ESQL/C and ISQL packages installed. Lately, I've >noticed a problem with temp tables not getting cleaned up. > >Initially I had DBSPACETEMP defined as the same dbspace as my >root dbspace. After running about a week, the root dbspace >filled to capacity. I looked at the SMI tables, oncheck -pe, >and onstat -d and noticed the free space was decreasing but >there was nothing in that space. After restarting the engine >it went through and cleaned up the temporary table space, and >I was back to normal. > >I created a new dbspace and defined it as a temp dbspace and >set DBSPACETEMP to this dbspace in my onconfig file. After >restarting the engine, now my temporary dbspace is filling >up with temp tables and the space is not being freed. > >The question is, is there something I've configured improperly >or is this something I must accept? If I must accept this, >is there a utility available that will search the dbspace and >free the unused temp tables while the engine is online? > DBSPACETEMP is used for a) explict temporary tables either CREATE TEMP TABLE or SELECT...INTO TEMP.. These should be delete when the application that creates them terminates. b) IMPLICT temp tables which are created when - queries are running with an ORDER BY or GROUP BY - Queries are running which use aggregate function min(), max() etc and unique/distinct - Statements using auto-index joins - Creating complex views. - DECLAREing scroll cursors - correlated sub-queries are running - queries are running which contain subqueries within an IN or ANY clause - queries are running which use sort-merge joins. - creating indicies Try running onstat -u to list sessions and then for each session-id run onstat -g ses <session-id>. At the bottom you will see temporary tables created by the session. Are there many of these?? Possibly all the temporary tables really are in use and you just need to allocate more space. -- David Williams