get application handle

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

stefan.albert - 30 Mar 2007 13:26 GMT

Hello,

I'm interested in the application handle the session has.
Is there a way to return it (UDF) so I can protocol it into a table
(or something like that)?

e.g. "values myapphandle()"

Reply to this Message

james_dey@hotmail.com - 30 Mar 2007 15:07 GMT

> Hello,
>
[quoted text clipped - 3 lines]
>
> e.g. "values myapphandle()"

It ain't pretty but the following would work:-

drop function myapphandle;

create function myapphandle(uniq int)
returns bigint
begin atomic
declare retval bigint;--
set retval=(
select agent_id from table(snapshot_statement('',-1)) stmt
where (stmt_stop is null or stmt_stop = '1970-01-01-01.00.00.000000')
and stmt_type = 2
and locate('myapphandle('||rtrim(char(uniq))||')',stmt_text) >
0);--
return retval;--
end
;

To execute "values(myapphandle(9999))"

Problem with above is that you can't use a sequence or parameterise
it. You have to supply a genuine unique number.

Reply to this Message

stefan.albert - 30 Mar 2007 16:54 GMT

On Mar 30, 4:07 pm, james_...@hotmail.com wrote:

> > Hello,
>
[quoted text clipped - 26 lines]
> Problem with above is that you can't use a sequence or parameterise
> it. You have to supply a genuine unique number.

Hello James,

that's great! Make a snapshot on the "own" SQL ... very good idea.
I had to turn on statement monitor switch to get this result.
It is also possible to replace the INT parameter and use a random
value - but this has to be evaluated in an earlier step.
(It is very volatile to spaces so if you can rely on the uniqeness of
the parameter you can skip the search for the own name)
Here is a modification with "current timestamp" - this should be quite
uniq for the system:

db2 "drop function myapphandle"
db2 -td@ "create function myapphandle(ts varchar(30))
returns bigint
begin atomic
declare retval bigint;--
set retval=( select agent_id from table(snapshot_statement('',-1))
stmt
where (stmt_stop is null or stmt_stop = '1970-01-01-01.00.00.000000')
and stmt_type = 2
-- and locate('myapphandle('''||rtrim(ts)||''')',stmt_text) > 0);--
and locate(ts,stmt_text) > 0);--
return retval;--
end
@"

# To execute : TS=`db2 -x "values(current timestamp)"` ; db2 -x
"values(myapphandle('$TS'))"

Much thanks for your help - but IBM should add a function like they
did for application_id().
You can JOIN this to snapshot_appl_info to get the agent_id BUT this
only works when you have a uniq application_id() - we don't have.
There is an application server which makes sessions with all the same
application_id()!

Reply to this Message

Ian - 31 Mar 2007 04:27 GMT

> On Mar 30, 4:07 pm, james_...@hotmail.com wrote:
>>
[quoted text clipped - 21 lines]
> that's great! Make a snapshot on the "own" SQL ... very good idea.
> I had to turn on statement monitor switch to get this result.

Be careful, because the snapshot_% table functions require elevated
privileges (users must be a member of SYSMON_GROUP or higher).

Reply to this Message