Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2007

    Unanswered: Whodunnit in DB2 Tables????

    Hello All,

    Good day.

    Here is the mystery:

    Our project group runs an I2 implementation every Monday. Several scripts and procedures inserts data into several backup tables that are first cleared before insert. All these tables do not have any constraints. The script that inserts data into these table is the last one to make any updates. The data source of that script is also a table that always contains data. It is this data that is being inserted into the backup tables.

    After the I2 run everything is stopped. Tables are checked and all check out good. After about a day, the backup tables are suddenly cleared. Everybody in the project group claims not to have deleted or cleared any table. No script , procedure or workflow has been run.


    My question is: Is there a log or a way to know when a table has been last update, inserted, deleted or queried. If there is... based on the time or date we will be able to compare it with the schedule of the project members... granting that someone is lying. Or is there any other non-human reason the tables where cleared.

    Thanks for helping me solve this whodunnit mystery...

  2. #2
    Join Date
    Jan 2007
    Jena, Germany
    Have a look at DB2's auditing facilities (see db2audit tool). You could also analyze the DB2 logs and th evarious histories (see LIST HISTORY command). Tracking insert/update/delete operations can be done with triggers as well.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Sep 2004
    Quote Originally Posted by drogomoss
    Or is there any other non-human reason the tables where cleared.
    Yes, there could be:
    - Check the catalog to find out whether there are triggers defined which are deleting rows from the backup tables.
    - Check whether there are programs that would in normal circumstances do no harm, but e.g. get a rollback somewhere in the middle (e.g. because of a timeout or so) and as a consequence of that are not restoring those tables in the expected state, as they would do in normal circumstances.
    Check the catalog (or the dynamic statement cache) for queries containing a "DELETE FROM <table_name>".
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting

Posting Permissions

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