get application handle

[stefan.albert]

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()"

[james_dey]

On 30 Mar, 13:26, "stefan.albert" <[e-mail]:

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.

[stefan.albert]

On Mar 30, 4:07 pm, [e-mail] wrote:

On 30 Mar, 13:26, "stefan.albert" <[e-mail]:

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()!

[Ian]

stefan.albert wrote:

On Mar 30, 4:07 pm, [e-mail] wrote:

>On 30 Mar, 13:26, "stefan.albert" <[e-mail]:

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).