Results 1 to 4 of 4
  1. #1
    Join Date
    May 2003
    Posts
    57

    Question Unanswered: How to monitor events on only one table?

    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

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    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.

  3. #3
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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'
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  4. #4
    Join Date
    May 2003
    Posts
    57
    Sathyaram_s, n_i

    Thank you very much...
    :-))

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •