Db2 for z/OS & Db2ZAI

 View Only
Expand all | Collapse all

Finding last updated time of a DB2 table

  • 1.  Finding last updated time of a DB2 table

    Posted Mon October 17, 2022 03:07 PM
    I m looking for mechanisms
     - to find the time at which a table got updated by insert/delete/statement statements.
     - to compute the checksum of the table - This is required to detect duplicates in data across tables.

    If you know of any mechanism without server side configuration preferably or with server side configuration, please let me know.

    Thanks in advance for any help.
    ..Radhakrishnan

    ------------------------------
    radhakrishnan thangamuthu
    ------------------------------

    #Db2forz/OS


  • 2.  RE: Finding last updated time of a DB2 table

    Posted Mon October 17, 2022 03:12 PM
    Look If SYSIBM.SYSTABLESPACESTATS.LASTDATACHANGE could help.

    ------------------------------
    Sergi Padró i Blasi
    ------------------------------



  • 3.  RE: Finding last updated time of a DB2 table

    Posted Tue October 18, 2022 01:24 AM
    Thanks a lot - will  try the idea given in the response. Still looking for information on how to find checksum of a DB2 table . If you know any way of computing it, please answer.

    ------------------------------
    radhakrishnan thangamuthu
    ------------------------------



  • 4.  RE: Finding last updated time of a DB2 table

    IBM Champion
    Posted Tue October 18, 2022 12:19 PM
    Look at the lastused date in sysibm. Systables, it will give a timestamp when the last DML was performed on the table.

    ------------------------------
    Douglas Partch
    ------------------------------



  • 5.  RE: Finding last updated time of a DB2 table
    Best Answer

    Posted Tue October 18, 2022 07:30 AM

    Try:

     

        SELECT MAX(ROW CHANGE TIMESTAMP FOR EMP ) FROM EMP; 

     

    2022-04-22-07.54.12.648485                                        

    DSNE610I NUMBER OF ROWS DISPLAYED IS 1                            

    DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100       

     

    -- FOR TABLES WITHOUT A ROW CHANGE TIMESTAMP COLUMN,                    

    --  THE "ROW CHANGE TIMESTAMP" EXPRESSION RETURNS A TIMESTAMP           

    --  VALUE THAT REFLECTS CHANGES MADE TO THE PAGE INSTEAD OF TO THE ROW. 



    ------------------------------
    John Totzke
    ------------------------------



  • 6.  RE: Finding last updated time of a DB2 table

    Posted Tue October 18, 2022 11:54 AM
    Thanks a lot @John Totzke

    ------------------------------
    radhakrishnan thangamuthu
    ------------------------------



  • 7.  RE: Finding last updated time of a DB2 table

    Posted Tue October 18, 2022 10:09 AM

    Add a "Row Change Timestamp" column...

     

    FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP

    Specifies that the column is a timestamp column for the table.  Db2 generates a value for the column for each row as the row is inserted, and for any row in which any column is updated.  The value that is generated for a row change timestamp column is a timestamp that corresponds to the insert or update time of the row.  If multiple rows are inserted or updated with a single statement, the value for the row change timestamp column might be different for each row.  A table can only have one row change timestamp column.  If data-type is specified, it must be TIMESTAMP WITHOUT TIME ZONE with a precision of 6.  A row change timestamp column cannot have a DEFAULT clause.  NOT NULL must be specified for a row change timestamp column.

     

     

    This e-mail and files transmitted with it are confidential, and are intended solely for the use of the individual or entity to whom this e-mail is addressed. If you are not the intended recipient, or the employee or agent responsible to deliver it to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you are not one of the named recipient(s) or otherwise have reason to believe that you received this message in error, please immediately notify sender by e-mail, and destroy the original message.





  • 8.  RE: Finding last updated time of a DB2 table

    Posted Tue October 18, 2022 11:54 AM
    Thanks a lot @Mike Brauweiler

    ------------------------------
    radhakrishnan thangamuthu
    ------------------------------



  • 9.  RE: Finding last updated time of a DB2 table

    Posted Thu October 20, 2022 03:17 AM
    No such thing as a checksum of a table or of a row.

    There are HASH functions which can produce a checksum of a string.  Map the result to a number, add the resulting numbers in a row and you'll have something approaching a checksum of a row.  Add all of them together and that will be the closest you'll get.

    James Campbell

    ------------------------------
    James Campbell
    ------------------------------