Users using temporary dbspaces
Ruben Garcia asked how to list users using temporary dbspaces. Benjamin Thompson gave a sysmaster query (systabnames with sysptnhdr, flag 0x0020) for 12.10.xC8 and later, and linked his 2015 blog post for older versions. Ruben asked if it covered all sessions or only temp tables. Doug Lawry linked an article and a pending enhancement request.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
Hi, I need to know a list of users who are using a temporary dbspace in a moment. I have tried with combination of "onstats" and a SMI's sentences, but i can't find the way to do it, if it is possible...Can anyone help me? Regards
If you have 12.10.xC8+ it is possible to see what is consuming your temporary space using this query against sysmaster: SELECT i.sid, hex(i.flags) flags, hex(i.partnum) partition, trim(n.dbsname) || ":" || trim(n.owner) || ":" || trim(n.tabname) table, i.nptotal allocated_pages FROM sysmaster:systabnames n, sysmaster:sysptnhdr i WHERE (sysmaster:bitval(i.flags, "0x0020") = 1) AND i.partnum = n.partnum; This is by far the easiest way. If you don't it becomes a lot more tricky. I posted about this problem in 2015: https://informixdba.wordpress.com/2015/03/29/temporary-dbspaces/ Ben.
Hi Ben, can i see this way all the sessions using the temporary dbspaces? or just the ones who are created temporary tables?
See also: https://www.oninitgroup.com/listing-temp-dbspace-contents This request for enhancement is under consideration: http://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=77869 Regards, Doug Lawry