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 Aug 22, 3:32 pm, red_valsen 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 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