Oninit cdi / Archive
Talk to an expert →

Re: Who is filling my Temp space ?

Posted in 2018

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