Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2016
    Posts
    4

    Question Answered: DB2: How to detect users who has made changes to database?

    Hi,
    I have a question about DB2 logging stuff...
    Is there possible to detect what user (id, name?) and when has made changes to the database???
    I'm more interested in specific operations to specific tables..for example, is it possible to define users who made update SQL queries to some table in database..

    Is there any special db2 stuff or utilities that could perform that action?

    ___________
    Best regards,
    Tatsiana

  2. Best Answer
    Posted by mark.bb

    "Hi,

    1. You can create an audit policy with the EXECUTE category, and use it in the AUDIT TABLE statement against your tables. But you will catch every statement using this table and not only ones doing changes.

    2. Event monitor for statements with filtering for EXECUTE* statements only using the DB2_EVMON_STMT_FILTER registry variable. No way to set for some particular tables. But you have an ability to make this monitor write to pipe and filter the pipe's output if you are familiar with perl/awk.

    3. WLM (if you have the permission to use it): create a work class set with the work class WRITE. Work action for such a work class may be COLLECT ACTIVITY DATA (passing the information to some active event monitor for activities). No way to set for some particular tables.

    4. db2set DB2_LOGGING_DETAIL=APPLINFO
    Setting this registry variable causes DB2 to write an additional informational log record for each transaction. But you need to analyze DB2 logs with some program using DB2 LOG API like DB2 Recovery Expert or self-maid utility using such API."


  3. #2
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    294
    Provided Answers: 45
    Hi,

    1. You can create an audit policy with the EXECUTE category, and use it in the AUDIT TABLE statement against your tables. But you will catch every statement using this table and not only ones doing changes.

    2. Event monitor for statements with filtering for EXECUTE* statements only using the DB2_EVMON_STMT_FILTER registry variable. No way to set for some particular tables. But you have an ability to make this monitor write to pipe and filter the pipe's output if you are familiar with perl/awk.

    3. WLM (if you have the permission to use it): create a work class set with the work class WRITE. Work action for such a work class may be COLLECT ACTIVITY DATA (passing the information to some active event monitor for activities). No way to set for some particular tables.

    4. db2set DB2_LOGGING_DETAIL=APPLINFO
    Setting this registry variable causes DB2 to write an additional informational log record for each transaction. But you need to analyze DB2 logs with some program using DB2 LOG API like DB2 Recovery Expert or self-maid utility using such API.
    Regards,
    Mark.

  4. #3
    Join Date
    Oct 2007
    Posts
    161
    Provided Answers: 9
    if its just a couple of tables, you could set up update/delete triggers to capture that info in another table, even get what they changed and what they changed it to. Plenty of info on the site here about using triggers to capture audit info. I've, also, seen some places where update user and update timestamp were columns in the table and were populated via a trigger.

Tags for this Thread

Posting Permissions

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