Oninit cdi / Archive
Talk to an expert →

Re: Who is filling my Temp space ?

Posted in 2018

Summary

Carlton Doe posted a sysmaster query (systabnames joined to sysptnhdr, flag 0x0020) showing temp space allocated per session on 12.10.xC8 or later. Dirk Moolman asked if it covered only explicit temp tables. Fernando Nunes said it covers any session temp allocation, including implicit temp tables, sorts, hash joins and views. Dirk was on 12.10.FC9W1 and was satisfied.

Auto-generated by Claude from the posts below — may be imperfect; read the full thread.

Topics: High Availability & Replication

Carlton Doe — — source: IIUG Forums & Mailing Lists

If youre on 12.10.xC8 or later the following query will return the size of temp tables for each active session 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 Carlton ------------------------------------------------- Carlton Doe Flower Mound, TX No trees were killed in the transmission of this email, however a large number of electrons were temporarily inconvenienced.

↪ replying to Carlton Doe
Dirk Moolman [ MTN South — — source: IIUG Forums & Mailing Lists

Is this for explicity temp tables, as mentioned in Benjamin's reply ? Dirk -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Carlton Doe Sent: Thursday, 08 March 2018 4:33 PM To: ids@iiug.org Subject: Re: Who is filling my Temp space ? [40829] If youre on 12.10.xC8 or later the following query will return the size of temp tables for each active session 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 Carlton ------------------------------------------------- Carlton Doe Flower Mound, TX No trees were killed in the transmission of this email, however a large number of electrons were temporarily inconvenienced. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

↪ replying to Carlton Doe
Dirk Moolman [ MTN South — — source: IIUG Forums & Mailing Lists

Thank you kindly ! /usr/informix> onstat - IBM Informix Dynamic Server Version 12.10.FC9W1 -- On-Line -- Up 16 days 17:18:41 -- 231305824 Kbytes Sorry, I always give the version, but forgot on this one ! Dirk -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Carlton Doe Sent: Thursday, 08 March 2018 4:33 PM To: ids@iiug.org Subject: Re: Who is filling my Temp space ? [40829] If youre on 12.10.xC8 or later the following query will return the size of temp tables for each active session 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 Carlton ------------------------------------------------- Carlton Doe Flower Mound, TX No trees were killed in the transmission of this email, however a large number of electrons were temporarily inconvenienced. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

↪ replying to Dirk Moolman [ MTN South
Fernando Nunes — — source: IIUG Forums & Mailing Lists

No. Should work for any temp space allocated by a session: - Explicit temp tables (CREATE TEMP TABLE...) - Implicit temp tables (SELECT ... INTO TEMP...) - Sort space - Hash joins - View materialization - ...? But you need 12.10.xC8+ Otherwise use the mentioned script. Regards. On Thu, Mar 8, 2018 at 2:50 PM, Dirk Moolman [ MTN South Africa ] < Dirk.Moolman@mtn.com> wrote: > Is this for explicity temp tables, as mentioned in Benjamin's reply ? > > Dirk > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > Carlton > Doe > Sent: Thursday, 08 March 2018 4:33 PM > To: ids@iiug.org > Subject: Re: Who is filling my Temp space ? [40829] > > If youre on 12.10.xC8 or later the following query will return the size of > temp tables for each active session > > 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 > > Carlton > > ------------------------------------------------- > Carlton Doe > Flower Mound, TX > > No trees were killed in the transmission of this email, however a large > number > of electrons were temporarily inconvenienced. > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...

↪ replying to Fernando Nunes
Dirk Moolman [ MTN South — — source: IIUG Forums & Mailing Lists

Great !! IBM Informix Dynamic Server Version 12.10.FC9W1 -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Fernando Nunes Sent: Thursday, 08 March 2018 5:03 PM To: ids@iiug.org Subject: Re: Who is filling my Temp space ? [40833] No. Should work for any temp space allocated by a session: - Explicit temp tables (CREATE TEMP TABLE...) - Implicit temp tables (SELECT ... INTO TEMP...) - Sort space - Hash joins - View materialization - ...? But you need 12.10.xC8+ Otherwise use the mentioned script. Regards. On Thu, Mar 8, 2018 at 2:50 PM, Dirk Moolman [ MTN South Africa ] < Dirk.Moolman@mtn.com> wrote: > Is this for explicity temp tables, as mentioned in Benjamin's reply ? > > Dirk > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > Carlton Doe > Sent: Thursday, 08 March 2018 4:33 PM > To: ids@iiug.org > Subject: Re: Who is filling my Temp space ? [40829] > > If youre on 12.10.xC8 or later the following query will return the > size of temp tables for each active session > > 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 > > Carlton > > ------------------------------------------------- > Carlton Doe > Flower Mound, TX > > No trees were killed in the transmission of this email, however a > large number of electrons were temporarily inconvenienced. > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Glossary