IDS Forum

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

Posted By: Fernando Nunes
Date: Thursday, 22 September 2016, at 6:50 a.m.

In Response To: Re: Script to see who's (session) using temp space (Keith Simmons)

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

Messages In This Thread

• Script to see who's (session) using temp space
  Fernando Nunes -- Wednesday, 21 September 2016, at 6:52 p.m.
  • Re: Script to see who's (session) using temp space
    Keith Simmons -- Thursday, 22 September 2016, at 4:20 a.m.
    • Re: Script to see who's (session) using temp space
      Fernando Nunes -- Thursday, 22 September 2016, at 5:19 a.m.
      • Re: Script to see who's (session) using temp space
        Keith Simmons -- Thursday, 22 September 2016, at 6:10 a.m.
  • Re: Script to see who's (session) using temp space
    Keith Simmons -- Thursday, 22 September 2016, at 5:15 a.m.
    • Re: Script to see who's (session) using temp space
      Fernando Nunes -- Thursday, 22 September 2016, at 5:22 a.m.
    • Re: Script to see who's (session) using temp space
      Fernando Nunes -- Thursday, 22 September 2016, at 6:50 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.