By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,333 Members | 1,214 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,333 IT Pros & Developers. It's quick & easy.

get application handle

P: n/a
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()"

Mar 30 '07 #1
Share this Question
Share on Google+
3 Replies


P: n/a
On 30 Mar, 13:26, "stefan.albert" <stefan.alb...@spb.dewrote:
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()"
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.
Mar 30 '07 #2

P: n/a
On Mar 30, 4:07 pm, james_...@hotmail.com wrote:
On 30 Mar, 13:26, "stefan.albert" <stefan.alb...@spb.dewrote:
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()"

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.
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_tex t) 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()!

Mar 30 '07 #3

P: n/a
Ian
stefan.albert wrote:
On Mar 30, 4:07 pm, james_...@hotmail.com wrote:
>On 30 Mar, 13:26, "stefan.albert" <stefan.alb...@spb.dewrote:

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.

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.
Be careful, because the snapshot_% table functions require elevated
privileges (users must be a member of SYSMON_GROUP or higher).

Mar 31 '07 #4

This discussion thread is closed

Replies have been disabled for this discussion.