Who is filling my Temp space ?

I am trying to catch a session that fills up our TEMP spaces. I run queries against SYSMASTER, but ... these sysmaster queries are very slow. Often the space fills up, the sessions crashes, and space is released, before my sysmaster query returns a result. Is there a quicker way to do this ? Here is just a sample query. I play around with these queries, to find users and size of temp space used, etc. One of my queries: (takes long) ================ -- The following SQL lists all objects registered in the sysmaster database as being located in temp dbspaces: database sysmaster; SELECT t2.owner [1,8], t2.dbsname [1,18] AS database, t2.tabname [1,22] AS table, t3.name [1,10] AS dbspace, (CURRENT - DBINFO('utc_to_datetime', ti_created)) :: INTERVAL DAY(4) TO SECOND AS life_time, (ti_nptotal * ti_pagesize/1024) :: INT AS size_kb FROM systabinfo AS t1, systabnames AS t2, sysdbspaces AS t3 WHERE t2.partnum = ti_partnum AND t3.dbsnum = TRUNC(t2.partnum/1024/1024) AND TRUNC(MOD(ti_flags,256)/16) > 0 ORDER BY 6 DESC, 5 DESC SAMPLE OUTPUT: =============== owner mansoo_m database eppix table tmp_yt_sbu dbspace temp3dbs life_time 0 00:11:16 size_kb 50456 owner mansoo_m database eppix table tmp_yt_sbu dbspace temp4dbs life_time 0 00:11:16 size_kb 50456 owner mansoo_m database eppix table tmp_yt_sbu dbspace temp9dbs life_time 0 00:11:16 size_kb 50456 owner mansoo_m database eppix table tmp_yt_sbu dbspace temp1dbs life_time 0 00:11:16 size_kb 50456 owner mansoo_m database eppix table tmp_yt_sbu dbspace temp5dbs life_time 0 00:11:16 size_kb 50456 Database selected.

It can be difficult to answer this question depending on how the temp space is being used. You can only easily identify sessions that have explicitly created temporary tables, i.e. using CREATE TEMP TABLE or INTO TEMP clause. If your temp space is being used for sorting the results of a large query you can only easily identify the user and not the session. I wrote a blog article on this in 2015: https://informixdba.wordpress.com/2015/03/29/temporary-dbspaces/ Fernando Nunes also wrote this: http://informix-technology.blogspot.co.uk/2016/09/temporary-space-usage-uso-de-e spaco.html Fernando references the RFEs there are in this area, one of which is "under consideration": https://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=77869 Ben.

Just to add up to your answer: - The article you mentioned points to a script ( https://github.com/domusonline/InformixScripts/blob/master/scripts/ix/ixtempuse ) which should be able to answer these questions, for any type of object in any currently supported version of Informix. Note the "should". The script uses a very special "technique" (probably also mentioned in your blog article as it was used to some extent in a previous script in the IIUG repository). The script have been used in several situations successful, but it can be affected by platform, specific configurations etc. Besides that it is also slow. Besides that it could theoretically cause a "fork bomb". Current version has mechanisms to prevent the "fork bomb" and I could never reproduce it, after implementing the mechanism.... I guess what I'm trying to say is that the standard disclaimer applies... I also think I add some feedback about some required changes, but to be honest I've lost track of these due to lack of time.... In any case, it should be able to answer the question. - Regarding the RFEs... it's a "sensitive issue" for me. I still think the RFE's are not being looked properly. This particular case is emblematic as the RFE was already implemented in 12.10.xC8. sysmaster:sysptnhdr now has a new field "sid"... it contains the SID of the owner of the object. That would make the previous script irrelevant in recent versions... Actually I think one of the pending features was to detect the version and if 12.10.xC8+ then just query sysptnhdr.... Regards. On Thu, Mar 8, 2018 at 11:36 AM, BENJAMIN THOMPSON < benjamin.thompson@skybettingandgaming.com> wrote: > It can be difficult to answer this question depending on how the temp > space is > being used. You can only easily identify sessions that have explicitly > created > temporary tables, i.e. using CREATE TEMP TABLE or INTO TEMP clause. If your > temp space is being used for sorting the results of a large query you can > only > easily identify the user and not the session. > > I wrote a blog article on this in 2015: > https://informixdba.wordpress.com/2015/03/29/temporary-dbspaces/ > > Fernando Nunes also wrote this: > http://informix-technology.blogspot.co.uk/2016/09/ > temporary-space-usage-uso-de-espaco.html > > Fernando references the RFEs there are in this area, one of which is "under > consideration": > https://www.ibm.com/developerworks/rfe/execute? > use_case=viewRfe&CR_ID=77869 > > Ben. > > > ************************************************************ > ******************* > 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...

> This particular case is emblematic as > the RFE was already implemented in 12.10.xC8. > sysmaster:sysptnhdr now has a new field "sid"... it contains the SID of the > owner of the object. That would make the previous script irrelevant in > recent versions... Actually I think one of the pending features was to > detect the version and if 12.10.xC8+ then just query sysptnhdr.... Ok that's very interesting. I have a 12.10.FC10 instance with unidentified temp space usage. I'll let you know how I get on with that investigation. Ben.