Is it possible to relate an Informix temporary table to the session that created it via sysmaster?

Via systabnames, systabinfo and sysdbstab, it's fairly straightforward to produce a list of temp tables (and hash-temp sorts) and the amount of space they're currently consuming.

But when you have many users/processes running the same SQL and generating temp tables with the same name, 开发者_高级运维you wind up with what appears to be highly duplicate output.

Is there a table in sysmaster via which the partnum of the temp table can be associated to the session that created it?

This question has been asked on a number of occasions over the past few years in other Informix-related forums, and there isn't a truly satisfactory answer.

You are probably familiar with some variant of:

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

which should be rewritten using JOIN notation as:

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

But this only gives the information that you already know about.

I did find a technique reported using onstat -g opn to detect open partitions that somehow connected back to the sessions via onstat -u. Unfortunately, onstat -g opn is reporting data that is not available from SMI and sysmaster, so this would fall in the category of 'scripting analysis of ON-Stat output'. If you want me to dig the details, such as they were, out of an obscure email account's archive (circa 2007, IIRC), then leave a comment.