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 ) ,
sysmasterartdbsnum(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 (AT) yahoo (DOT) com> wrote:
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... (AT) yahoo (DOT) com> wrote:
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 all
tables 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 (AT) yahoo (DOT) com> wrote: