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
select dbinfo( "DBSPACE" , pe_partnum ) dbspace,
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
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.
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(dbinfo('DBSPACE', i.ti_partnum),1,15) dbspace,
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
order by 1,2,3,4