Maybe someone can shine some light on this for me.
running Informix 7.31 TC5 on WIN NT

I have a few stored procedures that create TEMP tables,
after the procedure exits the TEMP table still hangs out in the tempdbs, but I can not select from it. So the next time the procedure runs and tries to create the TEMP table it fails. The tables are created with NO LOG.
The way i found that the tables are still in the dbspace is by doing this query

database sysmaster;

select dbinfo( "DBSPACE" , pe_partnum ) dbspace,
dbsname[1,10],
tabname,
pe_phys start,
pe_size size
from sysptnext, outer systabnames
where pe_partnum = partnum
order by dbspace, start;

And looking at tablenames, the TEMP’s are still in the tmpdbspace.

If I run oncheck -pe there is nothing in the TEMP dbspaces

If I reboot the server everything is fine.

Has anybody seen anything like this?

Hi artemka,

This is correct, the temps tables, created with "WHIT NO LOG" or not, remain during life of session.
Into the stored procedure can you error handler whit "ON EXCEPTION".
The table only can access the owner of session.

Gustavo.

I know that the tables are created for the life of the sesion.

for some reason they sit in sysmaster database after the sesion finishes.

Hi Armtenka,

Please, run the following scripts for look temp tables and owner's.
__________________________________________________ ____
{
dbaccess sysmaster 2>/dev/null << EOF
SELECT substr(n.dbsname,1,15) type,
substr(n.owner,1,15) users,
substr(n.tabname,1,18) table,
substr(dbinfo('DBSPACE', i.ti_partnum),1,15) dbspace,
round(i.ti_nptotal*2,0) kb
FROM systabnames n, systabinfo i
WHERE (bitval(i.ti_flags, 32) = 1
OR bitval(i.ti_flags, 64) = 1
OR bitval(i.ti_flags, 128) = 1)
AND i.ti_partnum = n.partnum
into temp ggtmp with no log;
SELECT type[1,12], users, table, dbspace[1,12], kb
from ggtmp
order by 1,2,3,4
EOF
} |more
__________________________________________________ ____

Gustavo.