Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2011
    Posts
    85

    Unanswered: DB2 Statistics detect for deletions

    Hi

    I have a perl script to detect statistics add,alter,update

    " select indname from syscat.indexes where CREATE_TIME >= TRUNC(current timestamp) - 7 days and TABSCHEMA = 'TCSUPER'
    union
    select tabname from syscat.tables where CREATE_TIME >= TRUNC(current timestamp) - 7 days and TABSCHEMA = 'TCSUPER'
    union
    select indname from syscat.indexes where STATS_TIME >= TRUNC(current timestamp) - 7 days and TABSCHEMA = 'TCSUPER'
    union
    select tabname from syscat.tables where STATS_TIME >= TRUNC(current timestamp) - 7 days and TABSCHEMA = 'TCSUPER' "

    I need a way to detect delete items as well

    Please suggest me any ideas

    Note that i include the query i used to detect stats
    I need a way to detect deleted items [It can be either a query like the above or any other way]

    Thanx

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    The only way to detect if something is no longer there, is to know that it was there in the first place. To do this, make a copy of the base table data. Then on the detect phase, you check the base table against the copy and anything in the copy that is not in the base was deleted. Then you replace the copy with the base table data again.

    Andy

  3. #3
    Join Date
    Sep 2011
    Posts
    85
    Thank for the reply

    I like the suggestion. But i have 700 tables PROD database, and more than 1000 indexes
    So comparing each table will be a hard way to do this..
    If you have a better way to do this comparison please advice me that way..

    Thanx so much for the 1st reply

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    DELETE Triggers are the only other way I can think of.

    Andy

Posting Permissions

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