Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2006
    Posts
    104

    Unanswered: How to get the last timestamp a table was altered

    Hi

    Db2 8.2.6 on windows

    Would like to know how to get the last timestamp a table was altered. I can get get the create_time of table from syscat.tables, but I cannot see anything there as to when a table was altered.


    Thanks

    Hari Kumar
    Last edited by sundaram; 05-11-07 at 00:34. Reason: Corerct typo in subject

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    You could write a stored procedure (sp) to keep that bookkeeping information for you.

    Each time a record is inserted, updated or deleted from that table another table could be updated to reflect that event.

    Or, something that is very often done, write a sp that stores per record when it was created, who did it and when it was last updated and by who and a counter that stores the number of updates.

    If you only need to know the information for a whole table, the fastest way to accomplish this is the first solution, though with high insert/update/delete loads, the table that stores the bookkeeping info could become a bottleneck.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Wim's solution would work, but only if you are able to force everyone to use the Stored Procedure to do all of the insert/update/deletes. If you cannot guarantee that every user will use the Stored Procedures, you should create triggers to log the information. That way no one will be able to bypass the logging. DB2 by itself does not track this info.

    Andy

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    I believe that the OP wants to know when the schema (number of columns, types of the columns, constraints, ...) of the table were changed - not the content of the table. As far as I know, there is nothing in the DB2 catalog to provide that information.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    My error, i meant to use triggers (instead of stored procedures) to do all that bookeeping.

    Quote Originally Posted by ARWinner
    Wim's solution would work, but only if you are able to force everyone to use the Stored Procedure to do all of the insert/update/deletes. If you cannot guarantee that every user will use the Stored Procedures, you should create triggers to log the information. That way no one will be able to bypass the logging. DB2 by itself does not track this info.
    Andy
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  6. #6
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    There is still something like a log to keep all the modifications one has applied to a databse.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  7. #7
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by Wim
    There is still something like a log to keep all the modifications one has applied to a databse.
    Right. But with that argument, you wouldn't need triggers or whatever to track data changes either.

    The log is not such a good alternative because it is (mostly) for DB2-internal purposes only to ensure the durability and consistency of a database.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  8. #8
    Join Date
    Mar 2006
    Posts
    104
    Thanks for the reply Stolze.

    I take it from your statement that there are no catlog info in DB2 as to when exactly a table structure was modified.

    hari

  9. #9
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by sundaram
    I take it from your statement that there are no catlog info in DB2 as to when exactly a table structure was modified.
    Not that I'm aware of, no.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  10. #10
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    ya there is nothing in catalog tables
    but if you activate db2audit then u can keep track of the ddl changes
    and all other changes made by various authorities
    Rahul Singh
    Certified DB2 9 DBA / Application Developer

Posting Permissions

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