Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399

    Unanswered: inserts, updates, deletes log?

    Need to trace insert, update, delete on tables (highly critical DB2 db). My questions are:
    - how and where the history of inserts, updates, deletes on a table recorded?
    - where else but in log files they are recorded?
    - how to access those records?

    I found an API called 'db2ReadLog' which does it but can not figure out how to run it from CLP. Please help
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Read about the DB2 audit facility
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    May 2007
    Location
    somewhere in dbforums
    Posts
    221
    An easier way would be to define triggers on the table which will capture the insert / delete / update requests in a separate table which can then be easily analyzed.... as per convenience

    As for the logs, the data is ordered in the sequence in which the transactions are carried out and hence even if you were able to extract the data formatting the same for each table would be a real pain ......

    Comparing both the scenarios, there will be a little overhead in triggers but then you have to consider the trade-off between ease of use and overhead.... Also you'll have to note the I / U / D frequency to get a better picture.
    IBM Certified Database Associate, DB2 9 for LUW

  4. #4
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    thx Nick, audit facility looks like a good way to do it but these 3 (insert, update, delete) are not 'auditable events'.

    http://publib.boulder.ibm.com/infoce...db.doc/toc.xml

    Any other thoughts besides audit or triggers?
    Last edited by MarkhamDBA; 12-17-08 at 13:13.
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by MarkhamDBA
    (insert, update, delete) are not 'auditable events'.
    They are in DB2 9.5, to which you are planning to migrate. In v. 8 I guess you are stuck with triggers.
    ---
    "It does not work" is not a valid problem statement.

  6. #6
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    Quote Originally Posted by n_i
    They are in DB2 9.5, to which you are planning to migrate. In v. 8 I guess you are stuck with triggers.
    Nick, I can not find them on the list in 9.5 either? Am I looking at the right place:

    http://publib.boulder.ibm.com/infoce.../r0053355.html
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Events for the EXECUTE category

    * COMMIT Execution of a COMMIT statement
    * CONNECT Establishment of a database connection
    * CONNECT RESET Termination of a database connection
    * DATA A host variable or parameter marker data values for the statement

    This event is repeated for each host variable or parameter marker that is part of the statement. It is only present in a delimited extract of an audit log.
    * GLOBAL COMMIT Execution of a COMMIT within a global transaction
    * GLOBAL ROLLBACK Execution of a ROLLBACK within a global transaction
    * RELEASE SAVEPOINT Execution of a RELEASE SAVEPOINT statement
    * ROLLBACK Execution of a ROLLBACK statement
    * SAVEPOINT Execution of a SAVEPOINT statement
    * STATEMENT Execution of an SQL statement
    * SWITCH USER Switching of a user within a trusted connection
    .......................
    ---
    "It does not work" is not a valid problem statement.

Posting Permissions

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