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

tracking DML on important tables

P: n/a
Hi,

I want to track the DML changes that are occurring on important
tables.
I can create a trigger to track these but how can i get the Ip address
and SQl statement executed.

Is there a way that i can capture what DML changes were made by which
ip address and what were the SQL run for that and how many rows were
affected by that DML.

I will require all these information: ip address, no of affected rows,
SQL(if possible).

Thanks a lot

Rahul

Aug 28 '07 #1
Share this Question
Share on Google+
13 Replies


P: n/a
Rahul B wrote:
Hi,

I want to track the DML changes that are occurring on important
tables.
I can create a trigger to track these but how can i get the Ip address
and SQl statement executed.

Is there a way that i can capture what DML changes were made by which
ip address and what were the SQL run for that and how many rows were
affected by that DML.

I will require all these information: ip address, no of affected rows,
SQL(if possible).
The application ID contains the IP address AFAIK. No of affected rows is
the COUNT of the NEW/OLD TABLE in the trigger.
To the best of my knowledge there is no direct means to retrieve the SQL
statement that caused a trigger to execute.

There are 3. party products that sniff the DRDA flow for auditing
purposes. And DB2 Viper 2 has a policy driven auditing facility.

I'm surprised you want to know the SQL Statement. Typically the changes
themselves are logged....

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Aug 28 '07 #2

P: n/a
On Aug 28, 4:08 pm, Serge Rielau <srie...@ca.ibm.comwrote:
Rahul B wrote:
Hi,
I want to track the DML changes that are occurring on important
tables.
I can create a trigger to track these but how can i get the Ip address
and SQl statement executed.
Is there a way that i can capture what DML changes were made by which
ip address and what were the SQL run for that and how many rows were
affected by that DML.
I will require all these information: ip address, no of affected rows,
SQL(if possible).

The application ID contains the IP address AFAIK. No of affected rows is
the COUNT of the NEW/OLD TABLE in the trigger.
To the best of my knowledge there is no direct means to retrieve the SQL
statement that caused a trigger to execute.

There are 3. party products that sniff the DRDA flow for auditing
purposes. And DB2 Viper 2 has a policy driven auditing facility.

I'm surprised you want to know the SQL Statement. Typically the changes
themselves are logged....

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Serge,
I couldn't get what exactly you mean by "chenges themselves are
logged".
Pardon my ignorance, Where exactly are the SQL statements stored.

Rahul

Aug 28 '07 #3

P: n/a
On Aug 28, 4:08 pm, Serge Rielau <srie...@ca.ibm.comwrote:
Rahul B wrote:
Hi,
I want to track the DML changes that are occurring on important
tables.
I can create a trigger to track these but how can i get the Ip address
and SQl statement executed.
Is there a way that i can capture what DML changes were made by which
ip address and what were the SQL run for that and how many rows were
affected by that DML.
I will require all these information: ip address, no of affected rows,
SQL(if possible).

The application ID contains the IP address AFAIK. No of affected rows is
the COUNT of the NEW/OLD TABLE in the trigger.
To the best of my knowledge there is no direct means to retrieve the SQL
statement that caused a trigger to execute.

There are 3. party products that sniff the DRDA flow for auditing
purposes. And DB2 Viper 2 has a policy driven auditing facility.

I'm surprised you want to know the SQL Statement. Typically the changes
themselves are logged....

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Serge,

If i write a trigger, in the trigger how do i get the application id
which is making DML changes in the table leading to the trigger
invocation.
In Oracle, there is a function sys_context() which returns the ip
address.
I was wondering if there's the same in DB2.

In any case, in the trigger, how do i get the application id which is
making DML changes in the table leading to the trigger invocation?

Rahul

Aug 28 '07 #4

P: n/a
On Aug 28, 4:53 pm, Rahul B <rahul.babb...@gmail.comwrote:
On Aug 28, 4:08 pm, Serge Rielau <srie...@ca.ibm.comwrote:
Rahul B wrote:
Hi,
I want to track the DML changes that are occurring on important
tables.
I can create a trigger to track these but how can i get the Ip address
and SQl statement executed.
Is there a way that i can capture what DML changes were made by which
ip address and what were the SQL run for that and how many rows were
affected by that DML.
I will require all these information: ip address, no of affected rows,
SQL(if possible).
The application ID contains the IP address AFAIK. No of affected rows is
the COUNT of the NEW/OLD TABLE in the trigger.
To the best of my knowledge there is no direct means to retrieve the SQL
statement that caused a trigger to execute.
There are 3. party products that sniff the DRDA flow for auditing
purposes. And DB2 Viper 2 has a policy driven auditing facility.
I'm surprised you want to know the SQL Statement. Typically the changes
themselves are logged....
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Serge,

If i write a trigger, in the trigger how do i get the application id
which is making DML changes in the table leading to the trigger
invocation.
In Oracle, there is a function sys_context() which returns the ip
address.
I was wondering if there's the same in DB2.

In any case, in the trigger, how do i get the application id which is
making DML changes in the table leading to the trigger invocation?

Rahul
What i mean to say is that in the trigger, how do i know which is the
application id of the application responsible for invoking the
trigger.
I mean is there some sysproc.getcurrentApplicationId() or something
like this?

Aug 28 '07 #5

P: n/a
Rahul B wrote:
What i mean to say is that in the trigger, how do i know which is the
application id of the application responsible for invoking the
trigger.
I mean is there some sysproc.getcurrentApplicationId() or something
like this?
There is a built-in function named APPLICATION_ID() available for that. If
you are on an older DB2 version where this function is not yet available,
you can work around this with the function described here:
http://tinyurl.com/ey5xv

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany
Aug 28 '07 #6

P: n/a
Rahul B wrote:
On Aug 28, 4:53 pm, Rahul B <rahul.babb...@gmail.comwrote:
>On Aug 28, 4:08 pm, Serge Rielau <srie...@ca.ibm.comwrote:
>>Rahul B wrote:
Hi,
I want to track the DML changes that are occurring on important
tables.
I can create a trigger to track these but how can i get the Ip address
and SQl statement executed.
Is there a way that i can capture what DML changes were made by which
ip address and what were the SQL run for that and how many rows were
affected by that DML.
I will require all these information: ip address, no of affected rows,
SQL(if possible).
The application ID contains the IP address AFAIK. No of affected rows is
the COUNT of the NEW/OLD TABLE in the trigger.
To the best of my knowledge there is no direct means to retrieve the SQL
statement that caused a trigger to execute.
There are 3. party products that sniff the DRDA flow for auditing
purposes. And DB2 Viper 2 has a policy driven auditing facility.
I'm surprised you want to know the SQL Statement. Typically the changes
themselves are logged....
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Serge,

If i write a trigger, in the trigger how do i get the application id
which is making DML changes in the table leading to the trigger
invocation.
In Oracle, there is a function sys_context() which returns the ip
address.
I was wondering if there's the same in DB2.

In any case, in the trigger, how do i get the application id which is
making DML changes in the table leading to the trigger invocation?

Rahul

What i mean to say is that in the trigger, how do i know which is the
application id of the application responsible for invoking the
trigger.
I mean is there some sysproc.getcurrentApplicationId() or something
like this?
http://publib.boulder.ibm.com/infoce...c/r0011856.htm

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Sep 1 '07 #7

P: n/a
On Sep 1, 6:07 pm, Serge Rielau <srie...@ca.ibm.comwrote:
Rahul B wrote:
On Aug 28, 4:53 pm, Rahul B <rahul.babb...@gmail.comwrote:
On Aug 28, 4:08 pm, Serge Rielau <srie...@ca.ibm.comwrote:
>Rahul B wrote:
Hi,
I want to track the DML changes that are occurring on important
tables.
I can create a trigger to track these but how can i get the Ip address
and SQl statement executed.
Is there a way that i can capture what DML changes were made by which
ip address and what were the SQL run for that and how many rows were
affected by that DML.
I will require all these information: ip address, no of affected rows,
SQL(if possible).
The application ID contains the IP address AFAIK. No of affected rows is
the COUNT of the NEW/OLD TABLE in the trigger.
To the best of my knowledge there is no direct means to retrieve the SQL
statement that caused a trigger to execute.
There are 3. party products that sniff the DRDA flow for auditing
purposes. And DB2 Viper 2 has a policy driven auditing facility.
I'm surprised you want to know the SQL Statement. Typically the changes
themselves are logged....
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Serge,
If i write a trigger, in the trigger how do i get the application id
which is making DML changes in the table leading to the trigger
invocation.
In Oracle, there is a function sys_context() which returns the ip
address.
I was wondering if there's the same in DB2.
In any case, in the trigger, how do i get the application id which is
making DML changes in the table leading to the trigger invocation?
Rahul
What i mean to say is that in the trigger, how do i know which is the
application id of the application responsible for invoking the
trigger.
I mean is there some sysproc.getcurrentApplicationId() or something
like this?

http://publib.boulder.ibm.com/infoce...ic/com.ibm.db2....

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Hi,
As per your suggestions, i tried to write a trigger whose body was
something like

CREATE TRIGGER <trig_name>
AFTER
INSERT
ON <table_name>
REFERENCING
NEW AS NEW
FOR EACH ROW
MODE DB2SQL
BEGIN ATOMIC
DECLARE APPLI_ID VARCHAR(128);
DECLARE DONE_BY VARCHAR(128);
SET APPLI_ID = (SELECT SYSFUN.APPLICATION_ID()
FROM SYSIBM.SYSDUMMY1);
SET DONE_BY = (SELECT CLIENT_NNAME
FROM TABLE (SYSPROC.SNAPSHOT_APPL_INFO('<db_name>, -1)) SNAPAPPL
WHERE APPL_ID= APPLI_ID);
INSERT INTO DML_LOG(TABLE_NAME, DML_TYPE, DONE_AT, DONE_BY,
APPLICATION_ID
)
VALUES('<table_name>', 'INSERT', CURRENT_TIMESTAMP, DONE_BY,
APPLI_ID);
END;

As a result, the values are getting inserted, however, application_id
and done_by are coming as null.
Hence, the whole purpose is lost.

Can anybody tell where am i going wrong?

Thanks

Rahul

Sep 4 '07 #8

P: n/a
Rahul B wrote:
On Sep 1, 6:07 pm, Serge Rielau <srie...@ca.ibm.comwrote:
>Rahul B wrote:
On Aug 28, 4:53 pm, Rahul B <rahul.babb...@gmail.comwrote:
On Aug 28, 4:08 pm, Serge Rielau <srie...@ca.ibm.comwrote:
>>Rahul B wrote:
Hi,
I want to track the DML changes that are occurring on important
tables.
I can create a trigger to track these but how can i get the Ip
address and SQl statement executed.
Is there a way that i can capture what DML changes were made by
which ip address and what were the SQL run for that and how many
rows were affected by that DML.
I will require all these information: ip address, no of affected
rows, SQL(if possible).
The application ID contains the IP address AFAIK. No of affected rows
is the COUNT of the NEW/OLD TABLE in the trigger.
To the best of my knowledge there is no direct means to retrieve the
SQL statement that caused a trigger to execute.
There are 3. party products that sniff the DRDA flow for auditing
purposes. And DB2 Viper 2 has a policy driven auditing facility.
I'm surprised you want to know the SQL Statement. Typically the
changes themselves are logged....
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Serge,
>If i write a trigger, in the trigger how do i get the application id
which is making DML changes in the table leading to the trigger
invocation.
In Oracle, there is a function sys_context() which returns the ip
address.
I was wondering if there's the same in DB2.
>In any case, in the trigger, how do i get the application id which is
making DML changes in the table leading to the trigger invocation?
>Rahul
What i mean to say is that in the trigger, how do i know which is the
application id of the application responsible for invoking the
trigger.
I mean is there some sysproc.getcurrentApplicationId() or something
like this?

http://publib.boulder.ibm.com/infoce...ic/com.ibm.db2....

As per your suggestions, i tried to write a trigger whose body was
something like

CREATE TRIGGER <trig_name>
AFTER
INSERT
ON <table_name>
REFERENCING
NEW AS NEW
FOR EACH ROW
MODE DB2SQL
BEGIN ATOMIC
DECLARE APPLI_ID VARCHAR(128);
DECLARE DONE_BY VARCHAR(128);
SET APPLI_ID = (SELECT SYSFUN.APPLICATION_ID()
FROM SYSIBM.SYSDUMMY1);
SET DONE_BY = (SELECT CLIENT_NNAME
FROM TABLE (SYSPROC.SNAPSHOT_APPL_INFO('<db_name>, -1)) SNAPAPPL
WHERE APPL_ID= APPLI_ID);
INSERT INTO DML_LOG(TABLE_NAME, DML_TYPE, DONE_AT, DONE_BY,
APPLICATION_ID
)
VALUES('<table_name>', 'INSERT', CURRENT_TIMESTAMP, DONE_BY,
APPLI_ID);
END;

As a result, the values are getting inserted, however, application_id
and done_by are coming as null.
What I find strange is that APPLICATION_ID() is NULL. As for the DONE_BY,
this is just a follow-up error of APPLI_ID.
Hence, the whole purpose is lost.
You could simplify the trigger:

CREATE TRIGGER <trig_name>
AFTER INSERT ON <table_name>
REFERENCING NEW AS NEW
FOR EACH ROW
MODE DB2SQL
BEGIN ATOMIC
INSERT
INTO DML_LOG(TABLE_NAME, DML_TYPE, DONE_AT, DONE_BY, APPLICATION_ID)
VALUES ( '<table_name>', 'INSERT', CURRENT_TIMESTAMP,
( SELECT CLIENT_NNAME
FROM TABLE ( SYSPROC.SNAPSHOT_APPL_INFO(
'<db_name>', -1) ) AS t
WHERE APPL_ID = APPLICATION_ID() ),
APPLICATION_ID());
END@

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany
Sep 4 '07 #9

P: n/a
On Sep 4, 1:42 pm, Knut Stolze <sto...@de.ibm.comwrote:
Rahul B wrote:
On Sep 1, 6:07 pm, Serge Rielau <srie...@ca.ibm.comwrote:
Rahul B wrote:
On Aug 28, 4:53 pm, Rahul B <rahul.babb...@gmail.comwrote:
On Aug 28, 4:08 pm, Serge Rielau <srie...@ca.ibm.comwrote:
>Rahul B wrote:
Hi,
I want to track the DML changes that are occurring on important
tables.
I can create a trigger to track these but how can i get the Ip
address and SQl statement executed.
Is there a way that i can capture what DML changes were made by
which ip address and what were the SQL run for that and how many
rows were affected by that DML.
I will require all these information: ip address, no of affected
rows, SQL(if possible).
The application ID contains the IP address AFAIK. No of affected rows
is the COUNT of the NEW/OLD TABLE in the trigger.
To the best of my knowledge there is no direct means to retrieve the
SQL statement that caused a trigger to execute.
There are 3. party products that sniff the DRDA flow for auditing
purposes. And DB2 Viper 2 has a policy driven auditing facility.
I'm surprised you want to know the SQL Statement. Typically the
changes themselves are logged....
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Serge,
If i write a trigger, in the trigger how do i get the application id
which is making DML changes in the table leading to the trigger
invocation.
In Oracle, there is a function sys_context() which returns the ip
address.
I was wondering if there's the same in DB2.
In any case, in the trigger, how do i get the application id which is
making DML changes in the table leading to the trigger invocation?
Rahul
What i mean to say is that in the trigger, how do i know which is the
application id of the application responsible for invoking the
trigger.
I mean is there some sysproc.getcurrentApplicationId() or something
like this?
>http://publib.boulder.ibm.com/infoce...ic/com.ibm.db2....
As per your suggestions, i tried to write a trigger whose body was
something like
CREATE TRIGGER <trig_name>
AFTER
INSERT
ON <table_name>
REFERENCING
NEW AS NEW
FOR EACH ROW
MODE DB2SQL
BEGIN ATOMIC
DECLARE APPLI_ID VARCHAR(128);
DECLARE DONE_BY VARCHAR(128);
SET APPLI_ID = (SELECT SYSFUN.APPLICATION_ID()
FROM SYSIBM.SYSDUMMY1);
SET DONE_BY = (SELECT CLIENT_NNAME
FROM TABLE (SYSPROC.SNAPSHOT_APPL_INFO('<db_name>, -1)) SNAPAPPL
WHERE APPL_ID= APPLI_ID);
INSERT INTO DML_LOG(TABLE_NAME, DML_TYPE, DONE_AT, DONE_BY,
APPLICATION_ID
)
VALUES('<table_name>', 'INSERT', CURRENT_TIMESTAMP, DONE_BY,
APPLI_ID);
END;
As a result, the values are getting inserted, however, application_id
and done_by are coming as null.

What I find strange is that APPLICATION_ID() is NULL. As for the DONE_BY,
this is just a follow-up error of APPLI_ID.
Hence, the whole purpose is lost.

You could simplify the trigger:

CREATE TRIGGER <trig_name>
AFTER INSERT ON <table_name>
REFERENCING NEW AS NEW
FOR EACH ROW
MODE DB2SQL
BEGIN ATOMIC
INSERT
INTO DML_LOG(TABLE_NAME, DML_TYPE, DONE_AT, DONE_BY, APPLICATION_ID)
VALUES ( '<table_name>', 'INSERT', CURRENT_TIMESTAMP,
( SELECT CLIENT_NNAME
FROM TABLE ( SYSPROC.SNAPSHOT_APPL_INFO(
'<db_name>', -1) ) AS t
WHERE APPL_ID = APPLICATION_ID() ),
APPLICATION_ID());
END@

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany
Yes,
But i am unable to understand why should it be null?
I can execute it as standalone and it comes as fine.

Please suggest something as i really need to fix this one?

Rahul

Sep 4 '07 #10

P: n/a
On Sep 4, 3:28 pm, Rahul B <rahul.babb...@gmail.comwrote:
On Sep 4, 1:42 pm, Knut Stolze <sto...@de.ibm.comwrote:
Rahul B wrote:
On Sep 1, 6:07 pm, Serge Rielau <srie...@ca.ibm.comwrote:
>Rahul B wrote:
On Aug 28, 4:53 pm, Rahul B <rahul.babb...@gmail.comwrote:
>On Aug 28, 4:08 pm, Serge Rielau <srie...@ca.ibm.comwrote:
>>Rahul B wrote:
>>>Hi,
>>>I want to track the DML changes that are occurring on important
>>>tables.
>>>I can create a trigger to track these but how can i get the Ip
>>>address and SQl statement executed.
>>>Is there a way that i can capture what DML changes were made by
>>>which ip address and what were the SQL run for that and how many
>>>rows were affected by that DML.
>>>I will require all these information: ip address, no of affected
>>>rows, SQL(if possible).
>>The application ID contains the IP address AFAIK. No of affected rows
>>is the COUNT of the NEW/OLD TABLE in the trigger.
>>To the best of my knowledge there is no direct means to retrieve the
>>SQL statement that caused a trigger to execute.
>>There are 3. party products that sniff the DRDA flow for auditing
>>purposes. And DB2 Viper 2 has a policy driven auditing facility.
>>I'm surprised you want to know the SQL Statement. Typically the
>>changes themselves are logged....
>>Cheers
>>Serge
>>--
>>Serge Rielau
>>DB2 Solutions Development
>>IBM Toronto Lab
>Serge,
>If i write a trigger, in the trigger how do i get the application id
>which is making DML changes in the table leading to the trigger
>invocation.
>In Oracle, there is a function sys_context() which returns the ip
>address.
>I was wondering if there's the same in DB2.
>In any case, in the trigger, how do i get the application id which is
>making DML changes in the table leading to the trigger invocation?
>Rahul
What i mean to say is that in the trigger, how do i know which is the
application id of the application responsible for invoking the
trigger.
I mean is there some sysproc.getcurrentApplicationId() or something
like this?
>>http://publib.boulder.ibm.com/infoce...ic/com.ibm.db2....
As per your suggestions, i tried to write a trigger whose body was
something like
CREATE TRIGGER <trig_name>
AFTER
INSERT
ON <table_name>
REFERENCING
NEW AS NEW
FOR EACH ROW
MODE DB2SQL
BEGIN ATOMIC
DECLARE APPLI_ID VARCHAR(128);
DECLARE DONE_BY VARCHAR(128);
SET APPLI_ID = (SELECT SYSFUN.APPLICATION_ID()
FROM SYSIBM.SYSDUMMY1);
SET DONE_BY = (SELECT CLIENT_NNAME
FROM TABLE (SYSPROC.SNAPSHOT_APPL_INFO('<db_name>, -1)) SNAPAPPL
WHERE APPL_ID= APPLI_ID);
INSERT INTO DML_LOG(TABLE_NAME, DML_TYPE, DONE_AT, DONE_BY,
APPLICATION_ID
)
VALUES('<table_name>', 'INSERT', CURRENT_TIMESTAMP, DONE_BY,
APPLI_ID);
END;
As a result, the values are getting inserted, however, application_id
and done_by are coming as null.
What I find strange is that APPLICATION_ID() is NULL. As for the DONE_BY,
this is just a follow-up error of APPLI_ID.
Hence, the whole purpose is lost.
You could simplify the trigger:
CREATE TRIGGER <trig_name>
AFTER INSERT ON <table_name>
REFERENCING NEW AS NEW
FOR EACH ROW
MODE DB2SQL
BEGIN ATOMIC
INSERT
INTO DML_LOG(TABLE_NAME, DML_TYPE, DONE_AT, DONE_BY, APPLICATION_ID)
VALUES ( '<table_name>', 'INSERT', CURRENT_TIMESTAMP,
( SELECT CLIENT_NNAME
FROM TABLE ( SYSPROC.SNAPSHOT_APPL_INFO(
'<db_name>', -1) ) AS t
WHERE APPL_ID = APPLICATION_ID() ),
APPLICATION_ID());
END@
--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

Yes,
But i am unable to understand why should it be null?
I can execute it as standalone and it comes as fine.

Please suggest something as i really need to fix this one?

Rahul
Hi,

The issue has been resolved by using
"SET APPLI_ID = (SELECT SYSFUN.APPLICATION_ID() FROM
SYSIBM.SYSDUMMY1);"

instead of "SELECT SYSFUN.APPLICATION_ID() AS APPLI_ID FROM
SYSIBM.SYSDUMMY1"

Thanks a lot.

Rahul

Sep 4 '07 #11

P: n/a
Rahul B wrote:
The issue has been resolved by using
"SET APPLI_ID = (SELECT SYSFUN.APPLICATION_ID() FROM
SYSIBM.SYSDUMMY1);"

instead of "SELECT SYSFUN.APPLICATION_ID() AS APPLI_ID FROM
SYSIBM.SYSDUMMY1"
FYI, you do not need the SELECT.
You can do:
SET APPLI_ID = SYSFUN.APPLICATION_ID();
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Sep 4 '07 #12

P: n/a
Yes thats nice! But I'm missing a similar function for the application
handle - because we have an application server with third party ODBC-
driver (needed for the application) which opens all his connections
with the same application ID. The APPID is therefore not unique (as
the docs say...) and I can't assign the real owner (username) if more
than 1 connection is active.

And another Xmas wish: How about an event monitor for DDL?
Then we could track the creation/alertation/drop for tables, views,
etc...
And the grants can be done automiatically - OK a little deferred...
(This could be done also if it would be possible to create a trigger
on sysibm.systables (or so), but this is ommited. May be because of
avoiding strange situations because of the trigger.)
On Sep 4, 2:41 pm, Serge Rielau <srie...@ca.ibm.comwrote:
Rahul B wrote:
The issue has been resolved by using
"SET APPLI_ID = (SELECT SYSFUN.APPLICATION_ID() FROM
SYSIBM.SYSDUMMY1);"
instead of "SELECT SYSFUN.APPLICATION_ID() AS APPLI_ID FROM
SYSIBM.SYSDUMMY1"

FYI, you do not need the SELECT.
You can do:
SET APPLI_ID = SYSFUN.APPLICATION_ID();

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Sep 7 '07 #13

P: n/a
stefan.albert wrote:
Yes thats nice! But I'm missing a similar function for the application
handle - because we have an application server with third party ODBC-
driver (needed for the application) which opens all his connections
with the same application ID. The APPID is therefore not unique (as
the docs say...) and I can't assign the real owner (username) if more
than 1 connection is active.
DB2 Viper 2 has pretty sophisticated handling for 3 tier architectures.
The buzzword look for is "trusted context".
Prior to Viper 2 you can hook in the client ID through some special
registers.
Take a look at e.g.:
http://publib.boulder.ibm.com/infoce...c/r0005869.htm
>
And another Xmas wish: How about an event monitor for DDL?
Then we could track the creation/alertation/drop for tables, views,
etc...
And the grants can be done automiatically - OK a little deferred...
(This could be done also if it would be possible to create a trigger
on sysibm.systables (or so), but this is ommited. May be because of
avoiding strange situations because of the trigger.)
You want DDL triggers. Noted.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Sep 7 '07 #14

This discussion thread is closed

Replies have been disabled for this discussion.