IDS 9.x - temporary tables that the current session owns

onstat -g ses ### reports the temporary tables owned by the session-id###. Is there anyway to do this with sql? I've tried: select tn.tabname from sysmaster:systabnames tn,sysmaster:systabinfo ti where tn.partnum = ti.ti_partnum and owner = 'mdwilkie' and sysmaster:bitval(ti_flags,32) = 1 and ti_nkeys = 0; to get a list of temp tables that I own, but these might be from different sessions from the one I'm interested in. Is there a better way? By the way, this issue arises from a bug I've just found in IDS 9.4. The engine generates an assert failure when you close a connection after creating a temporary table with an rtree index on it. If you explicitly drop the table before closing the connection, all is well. Thanks,... Mike

This script was sent to me by Informix Tech Support several years ago when I was needing to find out what temp tables were in use. It works on 7.x. #!/bin/sh ########################################################################### ## # # Shell script name: chktemptbls.sh # Author: Peter Cheung # Date: 11/23/96 # Usage: sh chktemptbls.sh # Description: Display all the current temp tables inside the online engine. # ########################################################################### ## dbaccess sysmaster << !!! 2> /dev/null | awk -v partn="0x0" '{ if ($1 == "partnum") { if ($2 != partn) { partn = $2; pflag = 1; printf "\\ \\ ";} else pflag = 0; } if ($1 == "pagenum") { if (pflag == 1) { print "Extents"; printf "\\\\tLogical Page Physical Page Size\\ "; } printf "\\\\t s", $2; } else if ($1 == "physaddr") printf " s", $2; else if ($1 == "extsiz") printf " d\\ ", $2; else if (pflag == 1 && $0 != '\\ ') print $0; }' select hex(a.partnum) partnum, a.dbsname dbname, a.owner owner, a.tabname, hex(b.flags) flags, b.rowsize rowsize, b.nkeys num_of_keys, b.nextns number_extents, b.created time_created, b.nptotal nptotal, b.nrows nrows, c.pe_log pagenum, hex(c.pe_phys) physaddr, c.pe_size extsiz from systabnames a, sysptnhdr b, sysptnext c where bitval(b.flags, "0x0020") = 1 and b.partnum = a.partnum and b.partnum = c.pe_partnum order by partnum, pagenum !!! ########################################################################### ## HTH Colin Dawson > >onstat -g ses ### reports the temporary tables owned by the session-id>###. Is there anyway to do this with sql? > >I've tried: > >select tn.tabname > from sysmaster:systabnames tn,> sysmaster:systabinfo ti > where tn.partnum = ti.ti_partnum and owner = 'mdwilkie' > and sysmaster:bitval(ti_flags,32) = 1 and ti_nkeys = 0; > >to get a list of temp tables that I own, but these might be from different >sessions from the one I'm interested in. Is there a better way? > > >By the way, this issue arises from a bug I've just found in IDS 9.4. The >engine generates an assert failure when you close a connection after >creating a temporary table with an rtree index on it. If you explicitly >drop the table before closing the connection, all is well. > >Thanks,... >Mike > > > --=_72e143e4fe216b0f5a64bc9b94dc309d--

This script does not give the session id which created that temp table. I have tried every possible way of linking a temp table to a session and it never worked. Ravi ----- Original Message ----- From: "Colin Dawson " <colin@firmdata-systems.co.uk> To: <ids@iiug.org> Sent: June 18, 2003 03:36 Subject: RE: IDS 9.x - temporary tables that the current session owns [1383] > This script was sent to me by Informix Tech Support several years ago when > I was needing to find out what temp tables were in use. It works on 7.x. > > > #!/bin/sh > ########################################################################### > ## > # > # Shell script name: chktemptbls.sh > # Author: Peter Cheung > # Date: 11/23/96 > # Usage: sh chktemptbls.sh > # Description: Display all the current temp tables inside the online > engine. > # > ########################################################################### > ## > dbaccess sysmaster << !!! 2> /dev/null | awk -v partn="0x0" '{ > if ($1 == "partnum") { if ($2 != partn) { partn = $2; pflag = 1; printf > "\\ \\ ";} > else pflag = 0; } > if ($1 == "pagenum") { > if (pflag == 1) { > print "Extents"; > printf "\\\\tLogical Page Physical Page Size\\ "; > } > printf "\\\\t s", $2; > } else > if ($1 == "physaddr") printf " s", $2; else > if ($1 == "extsiz") printf " d\\ ", $2; else > if (pflag == 1 && $0 != '\\ ') print $0; > }' > select hex(a.partnum) partnum, a.dbsname dbname, a.owner owner, > a.tabname, hex(b.flags) flags, b.rowsize rowsize, > b.nkeys num_of_keys, b.nextns number_extents, > b.created time_created, b.nptotal nptotal, b.nrows nrows, > c.pe_log pagenum, hex(c.pe_phys) physaddr, c.pe_size extsiz > from systabnames a, sysptnhdr b, sysptnext c > where > bitval(b.flags, "0x0020") = 1 > and b.partnum = a.partnum > and b.partnum = c.pe_partnum > order by partnum, pagenum > !!! > > ########################################################################### > ## > > HTH > > Colin Dawson > > > > >onstat -g ses ### reports the temporary tables owned by the session-id> >###. Is there anyway to do this with sql? > > > >I've tried: > > > >select tn.tabname > > from sysmaster:systabnames tn,> > sysmaster:systabinfo ti > > where tn.partnum = ti.ti_partnum and owner = 'mdwilkie' > > and sysmaster:bitval(ti_flags,32) = 1 and ti_nkeys = 0; > > > >to get a list of temp tables that I own, but these might be from > different > >sessions from the one I'm interested in. Is there a better way? > > > > > >By the way, this issue arises from a bug I've just found in IDS 9.4. The > > >engine generates an assert failure when you close a connection after > >creating a temporary table with an rtree index on it. If you explicitly > >drop the table before closing the connection, all is well. > > > >Thanks,... > >Mike > > > > > > > > > > --=_72e143e4fe216b0f5a64bc9b94dc309d-- > >