Map temp tables to session

[DA]

Hello,

We are running informix 11.50.FC6X5. Is there anyway to map temp tables back to the original session that created those tables?

We have reporting users that don't use explicit temp tables (create temp table), though they do have many subqueries and multisets. Most of those "temp" tables end up in the dbspaces specified in the DBSPACETEMP tempdbs:tempdbs2:tempdbs3 onconfig parameter. However, there seems to be a few where the temp tables end up in the rootdbs. We are trying to find those queries.

Thank You,

Dave

[m***@gmail.com]

Post by DA
Hello,
We are running informix 11.50.FC6X5. Is there anyway to map temp tables back to the original session that created those tables?
We have reporting users that don't use explicit temp tables (create temp table), though they do have many subqueries and multisets. Most of those "temp" tables end up in the dbspaces specified in the DBSPACETEMP tempdbs:tempdbs2:tempdbs3 onconfig parameter. However, there seems to be a few where the temp tables end up in the rootdbs. We are trying to find those queries.
Thank You,
Dave

You could use the following query to locate the sessionid.

database sysmaster;
SELECT hex(i.ti_partnum) partition,
trim(n.dbsname) || ":" || trim(n.owner) || ":" || trim(n.tabname) table,
i.ti_nptotal allocated_pages
FROM systabnames n, systabinfo i
WHERE bitval(i.ti_flags, "0x0020") = 1 AND i.ti_partnum = n.partnum


HTH

[m***@gmail.com]

Post by m***@gmail.com

Post by DA
Hello,
We are running informix 11.50.FC6X5. Is there anyway to map temp tables back to the original session that created those tables?
We have reporting users that don't use explicit temp tables (create temp table), though they do have many subqueries and multisets. Most of those "temp" tables end up in the dbspaces specified in the DBSPACETEMP tempdbs:tempdbs2:tempdbs3 onconfig parameter. However, there seems to be a few where the temp tables end up in the rootdbs. We are trying to find those queries.
Thank You,
Dave

You could use the following query to locate the sessionid.
database sysmaster;
SELECT hex(i.ti_partnum) partition,
trim(n.dbsname) || ":" || trim(n.owner) || ":" || trim(n.tabname) table,
i.ti_nptotal allocated_pages
FROM systabnames n, systabinfo i
WHERE bitval(i.ti_flags, "0x0020") = 1 AND i.ti_partnum = n.partnum
HTH

According to J. Leffler you should be able to using the onstat -g opn, onstat -u and onstat -g ses.

Onstat -g opn uses the thread id as well as the onstat -u. Onstat -u also has the session id. I modified the query to include the session id from the syssessions table and add the check for the owner but it will include multiple entries.

You may want to send Leffler an email.

[m***@gmail.com]

Post by m***@gmail.com

Post by DA
Hello,
We are running informix 11.50.FC6X5. Is there anyway to map temp tables back to the original session that created those tables?
We have reporting users that don't use explicit temp tables (create temp table), though they do have many subqueries and multisets. Most of those "temp" tables end up in the dbspaces specified in the DBSPACETEMP tempdbs:tempdbs2:tempdbs3 onconfig parameter. However, there seems to be a few where the temp tables end up in the rootdbs. We are trying to find those queries.
Thank You,
Dave

You could use the following query to locate the sessionid.
database sysmaster;
SELECT hex(i.ti_partnum) partition,
trim(n.dbsname) || ":" || trim(n.owner) || ":" || trim(n.tabname) table,
i.ti_nptotal allocated_pages
FROM systabnames n, systabinfo i
WHERE bitval(i.ti_flags, "0x0020") = 1 AND i.ti_partnum = n.partnum
HTH

According to J. Leffler you should be able to using the onstat -g opn, onstat -u and onstat -g ses.

Onstat -g opn uses the thread id as well as the onstat -u. Onstat -u also has the session id. I modified the query to include the session id from the syssessions table and add the check for the owner but it will include multiple entries.

You may want to send Leffler an email.