Track activity on a particular table
User needed to monitor a specific database table for delete operations in an Informix SAP application. They created a delete trigger calling a stored procedure and asked how to retrieve username, PID, and hostname when deletions occur. IBM support provided a solution: query sysmaster:syssessions using DBINFO('sessionid') to get current session details including sid, username, uid, pid, hostname, and connection time.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi folks, We have an SAP application using an Informix database. One user is currently deleting records from a database table. I would like to monitor that particular table for all delete operations. I created a delete trigger that calls a stored procedure to do the job. In this case, what would be the correct SQL statement to launch in order to get the username, his pid, the hostname... whenever a delete operation occurs in that table Thanks _________________________________________________________________ The new Windows Live Messenger. You dont want to miss this. http://www.microsoft.com/windows/windowslive/messenger.aspx
This should get you going, the trick is that DBINFO('sessionid') returns the current users session id. select sid, username, uid, pid, hostname, dbinfo('UTC_TO_DATETIME', connected) from sysmaster:syssessions where sid = DBINFO('sessionid'); John F. Miller III STSM, Support Architect miller3@us.ibm.com 503-578-5645 IBM Informix Dynamic Server (IDS) "Georges Martin" <georges_martin_1 @hotmail.com> To Sent by: ids@iiug.org ids-bounces@iiug. cc org Subject Track activity on a particular 02/02/2009 01:23 table [14681] PM Please respond to ids@iiug.org Hi folks, We have an SAP application using an Informix database. One user is currently deleting records from a database table. I would like to monitor that particular table for all delete operations. I created a delete trigger that calls a stored procedure to do the job. In this case, what would be the correct SQL statement to launch in order to get the username, his pid, the hostname... whenever a delete operation occurs in that table Thanks _________________________________________________________________ The new Windows Live Messenger. You don’t want to miss this. http://www.microsoft.com/windows/windowslive/messenger.aspx ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
Ahhhh! I=B9m blinded.
--=20
Jonathan Smaby
Pomona College
From: John Miller iii <miller3@us.ibm.com>
Reply-To: <ids@iiug.org>
Date: Mon, 2 Feb 2009 16:35:42 -0500 (EST)
To: <ids@iiug.org>
Subject: Re: Track activity on a particular table [14682]
This should get you going, the trick is that DBINFO('sessionid') returns
the current
users session id.
select sid, username, uid, pid, hostname, dbinfo('UTC_TO_DATETIME',
connected)
from sysmaster:syssessions
where sid = DBINFO('sessionid');
John F. Miller III
STSM, Support Architect
miller3@us.ibm.com
503-578-5645
IBM Informix Dynamic Server (IDS)
"Georges Martin"
<georges_martin_1
@hotmail.com> To
Sent by: ids@iiug.org
ids-bounces@iiug. cc
org
Subject
Track activity on a particular
02/02/2009 01:23 table [14681]
PM
Please respond to
ids@iiug.org
Hi folks,
We have an SAP application using an Informix database.
One user is currently deleting records from a database table. I would like
to
monitor that particular table for all delete operations.
I created a delete trigger that calls a stored procedure to do the job.
In this case, what would be the correct SQL statement to launch in order to
get the username, his pid, the hostname... whenever a delete operation
occurs
in that table
Thanks
_________________________________________________________________
The new Windows Live Messenger. You don’t want to miss this.
http://www.microsoft.com/windows/windowslive/messenger.aspx
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
***************************************************************************=
*
***=20
Forum Note: Use "Reply" to post a response in the discussion forum.
-------------------------------------------------------------
This message has been scanned by Postini anti-virus software.
=0D
This should get you going, the trick is that DBINFO('sessionid') returns the current users session id. select sid, username, uid, pid, hostname, dbinfo('UTC_TO_DATETIME', connected) from sysmaster:syssessions where sid = DBINFO('sessionid'); John > > "Georges Martin" <georges_martin_1@hotmail.com> > Sent by: ids-bounces@iiug.org > > 02/02/2009 01:23 PM > > Please respond to > ids@iiug.org > > To > > ids@iiug.org > > cc > > Subject > > Track activity on a particular table [14681] > > Hi folks, > We have an SAP application using an Informix database. > One user is currently deleting records from a database table. I would like to > monitor that particular table for all delete operations. > I created a delete trigger that calls a stored procedure to do the job. > In this case, what would be the correct SQL statement to launch in order to > get the username, his pid, the hostname... whenever a delete operation occurs > in that table > Thanks > _________________________________________________________________ > The new Windows Live Messenger. You don’t want to miss this. > http://www.microsoft.com/windows/windowslive/messenger.aspx > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >
This should get you going, the trick is that DBINFO('sessionid') returns the current users session id. select sid, username, uid, pid, hostname, dbinfo('UTC_TO_DATETIME', connected) from sysmaster:syssessions where sid = DBINFO('sessionid'); John ids-bounces@iiug.org wrote on 02/02/2009 01:23:15 PM: > Hi folks, > We have an SAP application using an Informix database. > One user is currently deleting records from a database table. I would like to > monitor that particular table for all delete operations. > I created a delete trigger that calls a stored procedure to do the job. > In this case, what would be the correct SQL statement to launch in order to > get the username, his pid, the hostname... whenever a delete operation occurs > in that table > Thanks > _________________________________________________________________ > The new Windows Live Messenger. You don’t want to miss this. > http://www.microsoft.com/windows/windowslive/messenger.aspx > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >
Well, at least it's different than the last two attempts. > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of John > Miller iii > Sent: Monday, February 02, 2009 3:58 PM > To: ids@iiug.org > Subject: Re: Track activity on a particular table [14685] > > VGhpcyBzaG91bGQgZ2V0IHlvdSBnb2luZywgdGhlIHRyaWNrIGlzIHRoYXQgREJJTkZPKCdz ZX > Nz > aW9uaWQnKSByZXR1cm5zDQp0aGUgY3VycmVudA0KdXNlcnMgc2Vzc2lvbiBpZC4NCg0Kc2Vs ZW > N0
It's secret code that only the few can decipher .... Peter Logan Senior Database Administrator Phone: 616/878-8309 From: "Everett Mills" <eemills@nationalbeef.com> To: ids@iiug.org Date: 02/02/2009 05:00 PM Subject: RE: Track activity on a particular table [14686] Sent by: ids-bounces@iiug.org Well, at least it's different than the last two attempts. > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of John > Miller iii > Sent: Monday, February 02, 2009 3:58 PM > To: ids@iiug.org > Subject: Re: Track activity on a particular table [14685] > > VGhpcyBzaG91bGQgZ2V0IHlvdSBnb2luZywgdGhlIHRyaWNrIGlzIHRoYXQgREJJTkZPKCdz ZX > Nz > aW9uaWQnKSByZXR1cm5zDQp0aGUgY3VycmVudA0KdXNlcnMgc2Vzc2lvbiBpZC4NCg0Kc2Vs ZW > N0 ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
John.... looks like you encrypted this mail..... ps. repost in plain
English.
On Mon, Feb 2, 2009 at 4:57 PM, John Miller iii <miller3@us.ibm.com> wrote:
>
> This should get you going, the trick is that DBINFO('sessionid') returns
> the current
> users session id.
>
> select sid, username, uid, pid, hostname, dbinfo('UTC_TO_DATETIME',
> connected)
> from sysmaster:syssessions
> where sid = DBINFO('sessionid');>
>
> John
>
> ids-bounces@iiug.org wrote on 02/02/2009 01:23:15 PM:
>
> > Hi folks,
> > We have an SAP application using an Informix database.
> > One user is currently deleting records from a database table. I would
> like to
> > monitor that particular table for all delete operations.
> > I created a delete trigger that calls a stored procedure to do the job.
> > In this case, what would be the correct SQL statement to launch in order
> to
> > get the username, his pid, the hostname... whenever a delete operation
> occurs
> > in that table
> > Thanks
> > _________________________________________________________________
> > The new Windows Live Messenger. You don’t want to miss this.
> > http://www.microsoft.com/windows/windowslive/messenger.aspx
> >
> >
> >
> *******************************************************************************
>
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Giri Raju
--0016364577ee14b6130461f6cb89
Onstat -g ppf or sysmaster:sysptprof shows you that kind of information by partition, you just only ask/filter for the partition you are interested in. There is a parameter config that inhibit of gathering that stat but I cant remember right now which one is... Walter -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Georges Martin Sent: Monday, February 02, 2009 3:23 PM To: ids@iiug.org Subject: Track activity on a particular table [14681] Hi folks, We have an SAP application using an Informix database. One user is currently deleting records from a database table. I would like to monitor that particular table for all delete operations. I created a delete trigger that calls a stored procedure to do the job. In this case, what would be the correct SQL statement to launch in order to get the username, his pid, the hostname... whenever a delete operation occurs in that table Thanks _________________________________________________________________ The new Windows Live Messenger. You don't want to miss this. http://www.microsoft.com/windows/windowslive/messenger.aspx ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
I just reread your question, and I missed the point, just ignore it. Thanks Walter -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Walter Milan Sent: Monday, February 02, 2009 4:46 PM To: ids@iiug.org Subject: RE: Track activity on a particular table [14689] Onstat -g ppf or sysmaster:sysptprof shows you that kind of information by partition, you just only ask/filter for the partition you are interested in. There is a parameter config that inhibit of gathering that stat but I cant remember right now which one is... Walter -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Georges Martin Sent: Monday, February 02, 2009 3:23 PM To: ids@iiug.org Subject: Track activity on a particular table [14681] Hi folks, We have an SAP application using an Informix database. One user is currently deleting records from a database table. I would like to monitor that particular table for all delete operations. I created a delete trigger that calls a stored procedure to do the job. In this case, what would be the correct SQL statement to launch in order to get the username, his pid, the hostname... whenever a delete operation occurs in that table Thanks _________________________________________________________________ The new Windows Live Messenger. You don't want to miss this. http://www.microsoft.com/windows/windowslive/messenger.aspx ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
Hi You might also be interested in www.lintel.co.uk/infotrace Regards David Linthwaite Lintel Software Consultancy Ltd IBM Business Partner Tel.: 01244 357250 Fax.: 01244 357248 mailto:dlinthwaite@lintel.co.uk -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Georges Martin Sent: 02 February 2009 21:23 To: ids@iiug.org Subject: Track activity on a particular table [14681] Hi folks, We have an SAP application using an Informix database. One user is currently deleting records from a database table. I would like to monitor that particular table for all delete operations. I created a delete trigger that calls a stored procedure to do the job. In this case, what would be the correct SQL statement to launch in order to get the username, his pid, the hostname... whenever a delete operation occurs in that table Thanks _________________________________________________________________ The new Windows Live Messenger. You don't want to miss this. http://www.microsoft.com/windows/windowslive/messenger.aspx **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.
Hello, when running SAP as application you might be interested in the table logging feature of the application (transaction SCU3, se11 table technical properties, profile parameter rec/client). Regards, Andreas Kutsche ------------------------------------------- SPAR Österreichische Warenhandels-AG Hauptzentrale A - 5015 Salzburg, Europastrasse 3 FN 34170 a Tel: +43 662 4470 24223 Mobile: +43 664 6259575 E-Mail: Andreas.KUTSCHE@spar.at Internet: http://www.spar.at Wichtiger Hinweis: Der Inhalt dieser E-Mail kann vertrauliche und rechtlich geschützte Informationen, insbesondere Betriebs- oder Geschäftsgeheimnisse, enthalten, zu deren Geheimhaltung der Empfänger verpflichtet ist. Die Informationen in dieser E-Mail sind ausschließlich für den Adressaten bestimmt. Sollten Sie die E-Mail irrtümlich erhalten haben so ersuchen wir Sie, die Nachricht von Ihrem System zu löschen und sich mit uns in Verbindung zu setzen. Über das Internet versandte E-Mails können leicht manipuliert oder unter fremdem Namen erstellt werden. Daher schließen wir die rechtliche Verbindlichkeit der in dieser Nachricht enthaltenen Informationen aus. Der Inhalt der E-Mail ist nur rechtsverbindlich, wenn er von uns schriftlich bestätigt und gezeichnet wird. Sollte trotz der von uns verwendeten Virus-Schutzprogramme durch die Zusendung von E-Mails ein Virus in Ihre Systeme gelangen, haften wir nicht für evtl. hieraus entstehende Schäden. Wir danken für Ihr Verständnis. Important notice: The contents of this e-mail may contain confidential and legally protected information that is in particular related to operational and trade secrets, which the recipient is obliged to treat as confidential. The information in this e-mail is made available exclusively for use by the addressee. In the event that the e-mail may have been sent to you in error, we would ask you to kindly delete this communication from your system and to contact us. E-mails sent via the Internet can be easily manipulated or sent out under someone else's name. We therefore do not accept legal liability for the information contained in this communication. The contents of the e-mail are only legally binding if they have been confirmed and signed by us in writing. If, in spite of our using Antivirus protection software, a virus may have penetrated your system through the sending of this e-mail, we do not accept liability for any damage that may possibly arise as a result of this. We trust that you appreciate our position. ------------------------------------------- -----Ursprüngliche Nachricht----- Von: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] Im Auftrag von David Linthwaite Gesendet: Dienstag, 3. Februar 2009 11:25 An: ids@iiug.org Betreff: RE: Track activity on a particular table [14697] Hi You might also be interested in www.lintel.co.uk/infotrace Regards David Linthwaite Lintel Software Consultancy Ltd IBM Business Partner Tel.: 01244 357250 Fax.: 01244 357248 mailto:dlinthwaite@lintel.co.uk -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Georges Martin Sent: 02 February 2009 21:23 To: ids@iiug.org Subject: Track activity on a particular table [14681] Hi folks, We have an SAP application using an Informix database. One user is currently deleting records from a database table. I would like to monitor that particular table for all delete operations. I created a delete trigger that calls a stored procedure to do the job. In this case, what would be the correct SQL statement to launch in order to get the username, his pid, the hostname... whenever a delete operation occurs in that table Thanks _________________________________________________________________ The new Windows Live Messenger. You don't want to miss this. http://www.microsoft.com/windows/windowslive/messenger.aspx **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
Hi John MIller, would it be possible to resent your mail it's still unreadable Art, lester, any hints? Thanks > To: ids@iiug.org> From: georges_martin_1@hotmail.com> Subject: Track activity on a particular table [14681]> Date: Mon, 2 Feb 2009 16:23:15 -0500> > Hi folks, > We have an SAP application using an Informix database. > One user is currently deleting records from a database table. I would like to > monitor that particular table for all delete operations. > I created a delete trigger that calls a stored procedure to do the job. > In this case, what would be the correct SQL statement to launch in order to > get the username, his pid, the hostname... whenever a delete operation occurs > in that table > Thanks > _________________________________________________________________ > The new Windows Live Messenger. You dont want to miss this. > http://www.microsoft.com/windows/windowslive/messenger.aspx > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > _________________________________________________________________ How fun is this? IMing with Windows Live Messenger just got better. http://www.microsoft.com/windows/windowslive/messenger.aspx
A witch turned him into a newt! Bob ----- Original Message ----- From: "Peter Logan" <Peter_Logan@spartanstores.com> To: ids@iiug.org Sent: Monday, February 2, 2009 5:02:04 PM GMT -05:00 US/Canada Eastern Subject: RE: Track activity on a particular table [14687] It's secret code that only the few can decipher .... Peter Logan Senior Database Administrator Phone: 616/878-8309 From: "Everett Mills" <eemills@nationalbeef.com> To: ids@iiug.org Date: 02/02/2009 05:00 PM Subject: RE: Track activity on a particular table [14686] Sent by: ids-bounces@iiug.org Well, at least it's different than the last two attempts. > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of John > Miller iii > Sent: Monday, February 02, 2009 3:58 PM > To: ids@iiug.org > Subject: Re: Track activity on a particular table [14685] > > VGhpcyBzaG91bGQgZ2V0IHlvdSBnb2luZywgdGhlIHRyaWNrIGlzIHRoYXQgREJJTkZPKCdz ZX > Nz > aW9uaWQnKSByZXR1cm5zDQp0aGUgY3VycmVudA0KdXNlcnMgc2Vzc2lvbiBpZC4NCg0Kc2Vs ZW > N0 ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
This should get you going, the trick is that DBINFO('sessionid') returns the
current users session id.
select sid, username, uid, pid, hostname, dbinfo('UTC_TO_DATETIME', connected)
from sysmaster:syssessions
where sid = DBINFO('sessionid');