Hello all!

DB2 v8.1, AIX v5.2

I noticed that there is one user doing update on specific column in one table that he/she shouldn't do. I want to find him somehow.
I was thinking about creating event monitor for that one table and I've found out that is impossible. The only possible is monitoring all tables. I expect him to do that in one day and monitoring all tables for one day costs lots of memory. There is no person except me(dbadm) who should change that column so I'm really curious who is that. I was thinking about triger but I don't know how to connect user's update with table. Every user has it user_id.
I don't have idea what to do.

Please if you can advice me...
Thank you in advance

NNicole

There are special registers: CURRENT CLIENT_USERID, CURRENT CLIENT_WRKSTNNAME, and CURRENT CLIENT_APPLNAME - that may help you to identify the user in the trigger code. More details can be found in the SQL reference.

Example of a trigger to 'log' the user id updating col i in table samp1 :

CREATE TRIGGER TRIG1 AFTER UPDATE OF I ON SAMP1 FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
insert into taudit values(USER,CURRENT TIMESTAMP) ;
END
@

If you want to stop users from updating the column, then a trigger like this may help :

CREATE TRIGGER TRIG2 NO CASCADE BEFORE UPDATE OF I ON SAMP1 FOR EACH ROW MODE DB2SQL
WHEN (USER <> 'ME')
SIGNAL SQLSTATE '85002' SET MESSAGE_TEXT='UPDATE OF COLUMN NOT ALLOWED'

Sathyaram_s, n_i

Thank you very much...
:-))