Cannot drop temp table in rootdbs

I noticed in an onstat -d output that our rootdbs, which is usually empty, had jumped to 96% full. Using AGS's Server Studio, I could see a temp table called username._temptable in rootdbs. I could also find the temp table using this query: select TRIM(dbsname)||":"||TRIM(owner)||"."||TRIM(tabname) , sysmaster:partdbsnum(T.partnum) dbspace_number, dbinfo('dbspace',T.partnum)dbspace_name from sysmaster:sysptnhdr P, sysmaster:systabnames T where P.partnum = T. partnum AND ( sysmaster:bitval(flags,64)>0 OR sysmaster:bitval(flags, 32) > 0 OR sysmaster:bitval(flags, 128) > 0 OR sysmaster:bitval(flags, 16384) > 0) ; (expression) db_tst:username._temptable dbspace_number 1 dbspace_name rootdbs However, the table has resisted my attempts to drop it -- DBMS continually tells me the table doesn't exist. I've quoted, escaped, prepended, fully qualified and otherwise used every way I could think of to reference the table. All to no avail (fie on thee, username!). I can cross my fingers and bounce the instance, but would rather find a reliable way to deal with problem immediately since I suspect it will recur. Any suggestions from the Informix intelligencia? Using IDS 10.00.FC8 on RHEL5.4 kernel 2.6.18.

On 22 August 2011 21:32, red_valsen <red_valsen@yahoo.com> wrote: > I noticed in an onstat -d output that our rootdbs, which is usually > empty, had jumped to 96% full.  Using AGS's Server Studio, I could see > a temp table called username._temptable in rootdbs.  I could also find > the temp table using this query: > > select TRIM(dbsname)||":"||TRIM(owner)||"."||TRIM(tabname) , > sysmaster:partdbsnum(T.partnum) dbspace_number, > dbinfo('dbspace',T.partnum)> dbspace_name > from > sysmaster:sysptnhdr P, sysmaster:systabnames T > where > P.partnum = T. partnum > AND > ( sysmaster:bitval(flags,64)>0 OR > sysmaster:bitval(flags, 32) > 0 OR > sysmaster:bitval(flags, 128) > 0 OR > sysmaster:bitval(flags, 16384) > 0) ; > > (expression)    db_tst:username._temptable > dbspace_number  1 > dbspace_name    rootdbs > > However, the table has resisted my attempts to drop it -- DBMS > continually tells me the table doesn't exist.  I've quoted, escaped, > prepended, fully qualified and otherwise used every way I could think > of to reference the table.  All to no avail  (fie on thee, username!). > > I can cross my fingers and bounce the instance, but would rather find > a reliable way to deal with problem immediately since I suspect it > will recur. > > Any suggestions from the Informix intelligencia? > > Using IDS 10.00.FC8 on RHEL5.4 kernel 2.6.18. > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > Red This temp table is probably generated by the system in response to a large sort/merge query, find the query, stop it and the table will disappear. Bouncing the instance will also clear the table. To prevent this happening in future ensure you have at least one temp temporary and one logged temporary dbspace configured to hold these (and other similar) temp tables. Sizes will depend on the queries being run but should be at least twice the size of the largest data set being returned that requires sorting. Keith

On Aug 22, 3:32 pm, red_valsen <red_val...@yahoo.com> wrote: > I noticed in an onstat -d output that our rootdbs, which is usually > empty, had jumped to 96% full.  Using AGS's Server Studio, I could see > a temp table called username._temptable in rootdbs.  I could also find > the temp table using this query: > > select TRIM(dbsname)||":"||TRIM(owner)||"."||TRIM(tabname) , > sysmaster:partdbsnum(T.partnum) dbspace_number, > dbinfo('dbspace',T.partnum)> dbspace_name > from > sysmaster:sysptnhdr P, sysmaster:systabnames T > where > P.partnum = T. partnum > AND > ( sysmaster:bitval(flags,64)>0 OR > sysmaster:bitval(flags, 32) > 0 OR > sysmaster:bitval(flags, 128) > 0 OR > sysmaster:bitval(flags, 16384) > 0) ; > > (expression)    db_tst:username._temptable > dbspace_number  1 > dbspace_name    rootdbs > > However, the table has resisted my attempts to drop it -- DBMS > continually tells me the table doesn't exist.  I've quoted, escaped, > prepended, fully qualified and otherwise used every way I could think > of to reference the table.  All to no avail  (fie on thee, username!). > > I can cross my fingers and bounce the instance, but would rather find > a reliable way to deal with problem immediately since I suspect it > will recur. > > Any suggestions from the Informix intelligencia? > > Using IDS 10.00.FC8 on RHEL5.4 kernel 2.6.18. User created temp tables can only be dropped by the session that created them. Worse case is that somehow the session that created the temp table has already exited and left the table behind. In this case, the only way to reclaim the space would be to bounce the server. However, if the session is still connected you could try disconnecting it from the server and I believe it should drop any temp tables it has open as part of the session clean up. As for figuring out which session owns the temp table, I know in later versions the onstat -g ses <session id> output listed the partnumbers for all that sessions temp tables, but I don't recall if that is the case in 10.x. Also, you might be able to find the part number of the temp table in onstat -g opn output which I believe shows the part numbers for alltables that each thread has open, and then you just need to map the thread id to a session. Jacques Renaut IBM Informix Advanced Support APD Team

Kill all sessions belonging to user "username". If that doesn't release the temp table, then bounce the instance. Also, upgrade to a current release which supports the new ONCONFIG parameter TEMPTAB_NOLOG which will prevent logged temp tables from being created. For now, the only defense you have to keep logged temp tables out of ROOTDBS is to include one or more non-temp dbspaces in DBSPACETEMP so that logged temp tables go there instead. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, Aug 22, 2011 at 4:32 PM, red_valsen <red_valsen@yahoo.com> wrote: > I noticed in an onstat -d output that our rootdbs, which is usually > empty, had jumped to 96% full. Using AGS's Server Studio, I could see > a temp table called username._temptable in rootdbs. I could also find > the temp table using this query: > > select TRIM(dbsname)||":"||TRIM(owner)||"."||TRIM(tabname) , > sysmaster:partdbsnum(T.partnum) dbspace_number, > dbinfo('dbspace',T.partnum)> dbspace_name > from > sysmaster:sysptnhdr P, sysmaster:systabnames T > where > P.partnum = T. partnum > AND > ( sysmaster:bitval(flags,64)>0 OR > sysmaster:bitval(flags, 32) > 0 OR > sysmaster:bitval(flags, 128) > 0 OR > sysmaster:bitval(flags, 16384) > 0) ; > > (expression) db_tst:username._temptable > dbspace_number 1 > dbspace_name rootdbs > > However, the table has resisted my attempts to drop it -- DBMS > continually tells me the table doesn't exist. I've quoted, escaped, > prepended, fully qualified and otherwise used every way I could think > of to reference the table. All to no avail (fie on thee, username!). > > I can cross my fingers and bounce the instance, but would rather find > a reliable way to deal with problem immediately since I suspect it > will recur. > > Any suggestions from the Informix intelligencia? > > Using IDS 10.00.FC8 on RHEL5.4 kernel 2.6.18. > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >