473,327 Members | 1,896 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

tracking DML on important tables

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
13 3302
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
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
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
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
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
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
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
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
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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

11
by: hawkon | last post by:
Hi all, I have an important question to ask about how to trap events when the user close the browser window. I'm a ASP programmer and I have s MSSQL database with a user table where I'm able to...
12
by: Dan Greenblatt | last post by:
I am writing some software that, among other things, needs to track the state of database tables. This includes occasionally checking the table to see what records or added, modified, or deleted....
1
by: fred tate via .NET 247 | last post by:
I'm working on a project that will track a great deal of data forindividuals and will keep track of users for a very long time (5- 10) years. I'm looking for options as far as tracking anddisplaying...
2
by: anony | last post by:
Maybe this feature is already out there. I guess you could write triggers to do some of this. Often when designing a database I add a start_date and end_date column to the table. The start_date...
6
by: A.M-SG | last post by:
Hi, We are developing a SmartClient application and we are planning to expose business objects layer to SmartClient application by using ASP.NET SOAP web services.
4
by: Middletree | last post by:
I have asked this here before, but am still trying to decide what's best, and would appreciate the input of seasoned Asp developers. It's an ASP-built Intranet app, so I can't show you the site,...
5
by: dee | last post by:
My wife is school secretary who inherited the job of tracking about 100 keys to about 150 school employees for thee next school year. Their current system, comprised of 2 non connected...
0
by: Rahul B | last post by:
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...
2
by: sparks | last post by:
At first they just wanted to keep a record of who logged in and when. Then it was if they made changes. Now its who changed what. BUT since this made no since to them. WHO put it in and who...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.