Script to see who's (session) using temp space

Hi, I've been working on a script to allow a DBA to see which session(s) are consuming temp space. Basically a workaround for the problem covered in http://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=77869 The script is accessible in https://github.com/domusonline/InformixScripts/tree/master/scripts/ix (ixtempuse) A more detailed description can be found here: http://informix-technology.blogspot.pt/2016/09/temporary-space-usage-uso-de-espa co.html The script should be able to show: - temporary structures created by HASH JOINS, ORDER BY and GROUP BY clauses, materialization of vies, OLAP window functions, temporary tables (explicitly created and implicit with SELECT INTO TEMP...) - show the structures for the current query and any opened cursors - show the temporary tables columns/datatypes and indexes I'm almost sure I have missed something but it should be ready for wider audience testing. Feel free to try it and please provide feedback, suggestions, bug reports etc. Regards. -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --001a113e9b2ec163b3053d0c6524

Fernando Many thanks for this, an invaluable tool. Works for IDS9.4 :-( on AIX6, however there does appear to be a type on line 651. Is po_sid correct or should it be po_id as this is the column name in syspoollst ? Keith On 21 September 2016 at 23:52, Fernando Nunes <domusonline@gmail.com> wrote: > Hi, > > I've been working on a script to allow a DBA to see which session(s) are > consuming temp space. > Basically a workaround for the problem covered in > http://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=77869 > > The script is accessible in > > https://github.com/domusonline/InformixScripts/tree/master/scripts/ix > (ixtempuse) > > A more detailed description can be found here: > > http://informix-technology.blogspot.pt/2016/09/temporary- > space-usage-uso-de-espaco.html > > The script should be able to show: > - temporary structures created by HASH JOINS, ORDER BY and GROUP BY > clauses, materialization of vies, OLAP window functions, temporary tables > (explicitly created and implicit with SELECT INTO TEMP...) > - show the structures for the current query and any opened cursors > - show the temporary tables columns/datatypes and indexes > > I'm almost sure I have missed something but it should be ready for wider > audience testing. > Feel free to try it and please provide feedback, suggestions, bug reports > etc. > > Regards. > > -- > Fernando Nunes > Portugal > > http://informix-technology.blogspot.com > My email works... but I don't check it frequently... > > --001a113e9b2ec163b3053d0c6524 > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11477af8b0f6fa053d145627

Fernando Correction to my previous, this does not work with 9.4, I just get the message: ixtempuse.sh: Currently there are no sessions consuming temporary space Keith On 22 September 2016 at 09:20, Keith Simmons <smiley73@gmail.com> wrote: > Fernando > > Many thanks for this, an invaluable tool. Works for IDS9.4 :-( on AIX6, > however there does appear to be a type on line 651. Is po_sid correct or > should it be po_id as this is the column name in syspoollst ? > > > Keith > > On 21 September 2016 at 23:52, Fernando Nunes <domusonline@gmail.com> > wrote: > >> Hi, >> >> I've been working on a script to allow a DBA to see which session(s) are >> consuming temp space. >> Basically a workaround for the problem covered in >> http://www.ibm.com/developerworks/rfe/execute?use_case= >> viewRfe&CR_ID=77869 >> >> The script is accessible in >> >> https://github.com/domusonline/InformixScripts/tree/master/scripts/ix >> (ixtempuse) >> >> A more detailed description can be found here: >> >> http://informix-technology.blogspot.pt/2016/09/temporary-spa >> ce-usage-uso-de-espaco.html >> >> The script should be able to show: >> - temporary structures created by HASH JOINS, ORDER BY and GROUP BY >> clauses, materialization of vies, OLAP window functions, temporary tables >> (explicitly created and implicit with SELECT INTO TEMP...) >> - show the structures for the current query and any opened cursors >> - show the temporary tables columns/datatypes and indexes >> >> I'm almost sure I have missed something but it should be ready for wider >> audience testing. >> Feel free to try it and please provide feedback, suggestions, bug reports >> etc. >> >> Regards. >> >> -- >> Fernando Nunes >> Portugal >> >> http://informix-technology.blogspot.com >> My email works... but I don't check it frequently... >> >> --001a113e9b2ec163b3053d0c6524 >> >> >> ************************************************************ >> ******************* >> Forum Note: Use "Reply" to post a response in the discussion forum. >> >> > --001a1146aede145b3e053d151be7

Keith, Thansk for testing and providing feedback. I believe this is not a typo... the definition of the table in 11.50 is this: { Pools } create table syspoollst { Internal Use Only } ( po_id integer, { id of this pool } po_address int8, { address of this pool } po_next int8, { pointer to next pool in list } po_prev int8, { pointer to prev pool in list } po_lock integer, { lock to synchronise } po_name char(12), { name of pool } po_class smallint, { pool class 1=resident, 2=virtual, 3=message} po_flags smallint, { notify if forget to free } po_freeamt int8, { total amount in free list } po_usedamt int8, { total amount in used list } po_freelist int8, { address of free block list } po_list int8, { address of pools block list } po_sid integer { sid of the owner of this pool } ); I suppose you don't have the po_sid column in your version? (sorry, but I currently don't have a 9.4 environment to check). If that's the case, at first glance it may be a big issue.... The script was not intended to work on versions before 11.50 (all are unsupported today). Nevertheless I'll try to figure out a way around this. If I can find one I'll update you. By the way... A warning that I wrote in the blog but not here: 1- The script is slow and it will be very difficult to improve that 2- If the script says it reached the "max iterators" it probably menas it has an error. Think very careful before increasing the limit. It may cause a fork bomb on your system. Regards and thanks. On Thu, Sep 22, 2016 at 9:20 AM, Keith Simmons <smiley73@gmail.com> wrote: > Fernando > > Many thanks for this, an invaluable tool. Works for IDS9.4 :-( on AIX6, > however there does appear to be a type on line 651. Is po_sid correct or > should it be po_id as this is the column name in syspoollst ? > > Keith > > On 21 September 2016 at 23:52, Fernando Nunes <domusonline@gmail.com> > wrote: > > > Hi, > > > > I've been working on a script to allow a DBA to see which session(s) are > > consuming temp space. > > Basically a workaround for the problem covered in > > http://www.ibm.com/developerworks/rfe/execute? > use_case=viewRfe&CR_ID=77869 > > > > The script is accessible in > > > > https://github.com/domusonline/InformixScripts/tree/master/scripts/ix > > (ixtempuse) > > > > A more detailed description can be found here: > > > > http://informix-technology.blogspot.pt/2016/09/temporary- > > space-usage-uso-de-espaco.html > > > > The script should be able to show: > > - temporary structures created by HASH JOINS, ORDER BY and GROUP BY > > clauses, materialization of vies, OLAP window functions, temporary tables > > (explicitly created and implicit with SELECT INTO TEMP...) > > - show the structures for the current query and any opened cursors > > - show the temporary tables columns/datatypes and indexes > > > > I'm almost sure I have missed something but it should be ready for wider > > audience testing. > > Feel free to try it and please provide feedback, suggestions, bug reports > > etc. > > > > Regards. > > > > -- > > Fernando Nunes > > Portugal > > > > http://informix-technology.blogspot.com > > My email works... but I don't check it frequently... > > > > --001a113e9b2ec163b3053d0c6524 > > > > > > ************************************************************ > > ******************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --001a11477af8b0f6fa053d145627 > > > ************************************************************ > ******************* > 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... --001a114261229455a5053d152102

Keith: Look at my previous answer. That message would be normal if your sessions were not consuming space. However if you changed the query (column name) it may break the script logic. That part of the script/query is needed to find out the SORT structures (ORDER BY clause). In order to make sure you are using temporary strucutres, just create a temporary table and run it again (unless of course you're sure your existing sessions already have temp tables) As I mentioned, it was not my intention to make the script compatible with pre-11.50 versions. This doesn't mean I can't try... Regards. On Thu, Sep 22, 2016 at 10:15 AM, Keith Simmons <smiley73@gmail.com> wrote: > Fernando > > Correction to my previous, this does not work with 9.4, I just get the > message: ixtempuse.sh: Currently there are no sessions consuming temporary > space > > Keith > > On 22 September 2016 at 09:20, Keith Simmons <smiley73@gmail.com> wrote: > > > Fernando > > > > Many thanks for this, an invaluable tool. Works for IDS9.4 :-( on AIX6, > > however there does appear to be a type on line 651. Is po_sid correct or > > should it be po_id as this is the column name in syspoollst ? > > > > > > Keith > > > > On 21 September 2016 at 23:52, Fernando Nunes <domusonline@gmail.com> > > wrote: > > > >> Hi, > >> > >> I've been working on a script to allow a DBA to see which session(s) are > >> consuming temp space. > >> Basically a workaround for the problem covered in > >> http://www.ibm.com/developerworks/rfe/execute?use_case= > >> viewRfe&CR_ID=77869 > >> > >> The script is accessible in > >> > >> https://github.com/domusonline/InformixScripts/tree/master/scripts/ix > >> (ixtempuse) > >> > >> A more detailed description can be found here: > >> > >> http://informix-technology.blogspot.pt/2016/09/temporary-spa > >> ce-usage-uso-de-espaco.html > >> > >> The script should be able to show: > >> - temporary structures created by HASH JOINS, ORDER BY and GROUP BY > >> clauses, materialization of vies, OLAP window functions, temporary > tables > >> (explicitly created and implicit with SELECT INTO TEMP...) > >> - show the structures for the current query and any opened cursors > >> - show the temporary tables columns/datatypes and indexes > >> > >> I'm almost sure I have missed something but it should be ready for wider > >> audience testing. > >> Feel free to try it and please provide feedback, suggestions, bug > reports > >> etc. > >> > >> Regards. > >> > >> -- > >> Fernando Nunes > >> Portugal > >> > >> http://informix-technology.blogspot.com > >> My email works... but I don't check it frequently... > >> > >> --001a113e9b2ec163b3053d0c6524 > >> > >> > >> ************************************************************ > >> ******************* > >> Forum Note: Use "Reply" to post a response in the discussion forum. > >> > >> > > > > --001a1146aede145b3e053d151be7 > > > ************************************************************ > ******************* > 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... --001a1141ce5ce58091053d152e76

Fernando Thanks, however see my later post. pagesize is not a column on sysdbstab (in 9.4) and the script reports no temp space being used (when I know there is a lot in use). Unfortunately I think using it on 9.4 is a non-starter. Keith On 22 September 2016 at 10:19, Fernando Nunes <domusonline@gmail.com> wrote: > Keith, > > Thansk for testing and providing feedback. > I believe this is not a typo... the definition of the table in 11.50 is > this: > > { Pools } > > create table syspoollst { Internal Use Only }> > ( > > po_id integer, { id of this pool } > > po_address int8, { address of this pool } > > po_next int8, { pointer to next pool in list } > > po_prev int8, { pointer to prev pool in list } > > po_lock integer, { lock to synchronise } > > po_name char(12), { name of pool } > > po_class smallint, { pool class 1=resident, 2=virtual, > 3=message} > > po_flags smallint, { notify if forget to free } > > po_freeamt int8, { total amount in free list } > > po_usedamt int8, { total amount in used list } > > po_freelist int8, { address of free block list } > > po_list int8, { address of pools block list } > > po_sid integer { sid of the owner of this pool } > > ); > > I suppose you don't have the po_sid column in your version? (sorry, but I > currently don't have a 9.4 environment to check). > If that's the case, at first glance it may be a big issue.... The script > was not intended to work on versions before 11.50 (all are unsupported > today). > Nevertheless I'll try to figure out a way around this. If I can find one > I'll update you. > > By the way... A warning that I wrote in the blog but not here: > 1- The script is slow and it will be very difficult to improve that > 2- If the script says it reached the "max iterators" it probably menas it > has an error. Think very careful before increasing the limit. It may cause > a fork bomb on your system. > > Regards and thanks. > > On Thu, Sep 22, 2016 at 9:20 AM, Keith Simmons <smiley73@gmail.com> wrote: > > > Fernando > > > > Many thanks for this, an invaluable tool. Works for IDS9.4 :-( on AIX6, > > however there does appear to be a type on line 651. Is po_sid correct or > > should it be po_id as this is the column name in syspoollst ? > > > > Keith > > > > On 21 September 2016 at 23:52, Fernando Nunes <domusonline@gmail.com> > > wrote: > > > > > Hi, > > > > > > I've been working on a script to allow a DBA to see which session(s) > are > > > consuming temp space. > > > Basically a workaround for the problem covered in > > > http://www.ibm.com/developerworks/rfe/execute? > > use_case=viewRfe&CR_ID=77869 > > > > > > The script is accessible in > > > > > > https://github.com/domusonline/InformixScripts/tree/master/scripts/ix > > > (ixtempuse) > > > > > > A more detailed description can be found here: > > > > > > http://informix-technology.blogspot.pt/2016/09/temporary- > > > space-usage-uso-de-espaco.html > > > > > > The script should be able to show: > > > - temporary structures created by HASH JOINS, ORDER BY and GROUP BY > > > clauses, materialization of vies, OLAP window functions, temporary > tables > > > (explicitly created and implicit with SELECT INTO TEMP...) > > > - show the structures for the current query and any opened cursors > > > - show the temporary tables columns/datatypes and indexes > > > > > > I'm almost sure I have missed something but it should be ready for > wider > > > audience testing. > > > Feel free to try it and please provide feedback, suggestions, bug > reports > > > etc. > > > > > > Regards. > > > > > > -- > > > Fernando Nunes > > > Portugal > > > > > > http://informix-technology.blogspot.com > > > My email works... but I don't check it frequently... > > > > > > --001a113e9b2ec163b3053d0c6524 > > > > > > > > > ************************************************************ > > > ******************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > --001a11477af8b0f6fa053d145627 > > > > > > ************************************************************ > > ******************* > > 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... > > --001a114261229455a5053d152102 > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a1146aede921fac053d15e029

Keith.... we can try a workaround.... Not sure if 9.40 will accept this syntax. and I apologize for this strange SQL, but I'd need CHARINDEX() to make it simpler, but 9.40 doesn't have it.... Can you try replacing the line: sysscblst.sid = po_sid AND with this: sysscblst.sid || '' = SUBSTR(po_name, 1, (SELECT CASE WHEN po_name[2] = '_' THEN 1 WHEN po_name[3] = '_' THEN 2 WHEN po_name[4] = '_' THEN 3 WHEN po_name[5] = '_' THEN 4 WHEN po_name[6] = '_' THEN 5 WHEN po_name[7] = '_' THEN 6 WHEN po_name[8] = '_' THEN 6 WHEN po_name[9] = '_' THEN 8 WHEN po_name[10] = '_' THEN 9 ELSE -1 END FROM systables WHERE tabid = 1)) AND The pool I'm looking for has "SID_SORT_*" where SID is the session ID i was getting from the po_sid column. So I'm trying to cut the string before the first "_". Without CHARINDEX or a custome equivalent this is the best I can think of... If you can give it a try.... I hope I don't have any syntax error, but with the above idea you should be able to workaround it. The empty sctring concatenation is just to make sure I compare string to string.... otherwise the engine could try to convert theresult of the SUBSTR to an INTEGER and that could fail for many of the pool names. To be sure it works (not giving syntax error is not enough, please make sure you run a query that needs to create a temporary sort structure and make sure the query is still displaying the results on dbaccess while you run the changed script. To be sure, an "oncheclk -pe" should show up "SORTTEMP" in the temporary dbspaces. Good luck. If it works I can make the script adjust itself to older versions.... and I'll publish an update. Regards. On Thu, Sep 22, 2016 at 10:21 AM, Fernando Nunes <domusonline@gmail.com> wrote: > Keith: Look at my previous answer. > That message would be normal if your sessions were not consuming space. > However if you changed the query (column name) it may break the script > logic. > That part of the script/query is needed to find out the SORT structures > (ORDER BY clause). > > In order to make sure you are using temporary strucutres, just create a > temporary table and run it again (unless of course you're sure your > existing sessions already have temp tables) > > As I mentioned, it was not my intention to make the script compatible with > pre-11.50 versions. This doesn't mean I can't try... > > Regards. > > On Thu, Sep 22, 2016 at 10:15 AM, Keith Simmons <smiley73@gmail.com> > wrote: > >> Fernando >> >> Correction to my previous, this does not work with 9.4, I just get the >> message: ixtempuse.sh: Currently there are no sessions consuming temporary >> space >> >> Keith >> >> On 22 September 2016 at 09:20, Keith Simmons <smiley73@gmail.com> wrote: >> >> > Fernando >> > >> > Many thanks for this, an invaluable tool. Works for IDS9.4 :-( on AIX6, >> > however there does appear to be a type on line 651. Is po_sid correct or >> > should it be po_id as this is the column name in syspoollst ? >> > >> > >> > Keith >> > >> > On 21 September 2016 at 23:52, Fernando Nunes <domusonline@gmail.com> >> > wrote: >> > >> >> Hi, >> >> >> >> I've been working on a script to allow a DBA to see which session(s) >> are >> >> consuming temp space. >> >> Basically a workaround for the problem covered in >> >> http://www.ibm.com/developerworks/rfe/execute?use_case= >> >> viewRfe&CR_ID=77869 >> >> >> >> The script is accessible in >> >> >> >> https://github.com/domusonline/InformixScripts/tree/master/scripts/ix >> >> (ixtempuse) >> >> >> >> A more detailed description can be found here: >> >> >> >> http://informix-technology.blogspot.pt/2016/09/temporary-spa >> >> ce-usage-uso-de-espaco.html >> >> >> >> The script should be able to show: >> >> - temporary structures created by HASH JOINS, ORDER BY and GROUP BY >> >> clauses, materialization of vies, OLAP window functions, temporary >> tables >> >> (explicitly created and implicit with SELECT INTO TEMP...) >> >> - show the structures for the current query and any opened cursors >> >> - show the temporary tables columns/datatypes and indexes >> >> >> >> I'm almost sure I have missed something but it should be ready for >> wider >> >> audience testing. >> >> Feel free to try it and please provide feedback, suggestions, bug >> reports >> >> etc. >> >> >> >> Regards. >> >> >> >> -- >> >> Fernando Nunes >> >> Portugal >> >> >> >> http://informix-technology.blogspot.com >> >> My email works... but I don't check it frequently... >> >> >> >> --001a113e9b2ec163b3053d0c6524 >> >> >> >> >> >> ************************************************************ >> >> ******************* >> >> Forum Note: Use "Reply" to post a response in the discussion forum. >> >> >> >> >> > >> >> --001a1146aede145b3e053d151be7 >> >> >> ************************************************************ >> ******************* >> 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... > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --94eb2c19dd0255d744053d166f9b