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.

Our mystery is WHO CLEARED THE BACKUP TABLES?

--------
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...

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.

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>".