Db2 for z/OS

Expand all | Collapse all

how to get list of tables inserted/updated/deleted after a specific timestamp.

  • 1.  how to get list of tables inserted/updated/deleted after a specific timestamp.

    Posted Fri May 21, 2021 05:07 PM
    I am using db2 oz version 11
    I'm attempting to run a select count query using the timestamp for a Prod database, but each time I get a different count. It seems data is constantly changing. To recognize newly added rows Is there a seeded 'WHO' column at the table level in DB2?
    If the answer is yes, what should the query be? I require a DML query because  currently I have read-only access to this database.
      'LASTUSED' column in 'SYSCAT.TABLES' does not work for my db2 database.

    ------------------------------
    Andrew GK
    ------------------------------


  • 2.  RE: how to get list of tables inserted/updated/deleted after a specific timestamp.

    Posted Tue May 25, 2021 12:58 AM

    Simple answer: No there is no such column but lots of table designers do add such a column like UPDATE_TS , LAST_USED or INSERTED_TS so you may be lucky!

    On z/OS the catalog tables are all in the SYSIBM schema not in SYSCAT so looking at the RTS you would use SYSIBM.SYSTABLESPACESTATS

     

    Roy Boxwell

    SOFTWARE ENGINEERING GmbH and SEGUS Inc.
    -Product Development-


    Vagedesstrasse 19
    40479 Dusseldorf/Germany
    Tel. +49 (0)211 96149-675
    Fax +49 (0)211 96149-32
    Email:
    R.Boxwell@seg.de
    Web  http://www.seg.de

    Link zur Datenschutzerklärung


    Software Engineering GmbH
    Amtsgericht Düsseldorf, HRB 37894
    Geschäftsführung: Gerhard Schubert, Ulf Heinrich

     






  • 3.  RE: how to get list of tables inserted/updated/deleted after a specific timestamp.

    Posted Tue May 25, 2021 04:10 PM

    "db2 oz version 11" is ... what?  If you mean Db2 11 (for LUW)  then I cannot help -apart from commenting that those who might would probably like to know if you mean 11.1 or 11.5 - it might make a difference.

    If you mean Db2 11 for z/OS - then the SYSCAT views are not maintained.  Someone (not, as I recollect, from IBM) once wrote some basic views, but I cannot remember where they were.   Also, I should mention, Db2 11 for z/OS is now out of support; so time to get a move on and migrate.

    If Db2 for z/OS is what you want you (or your DBAs) would need to have added SYSTEM or BUSINESS time columns to the table, and, perhaps, enabled a history table which would keep "as they were then" copies of updated or deleted rows. The SYSTEM or BUSINESS times would tell you when a current row was actually updated.  Perhaps a suitable trigger to tell you if the last update was an update (and there's a history row for the previous version of the row) or an insert (there isn't).   But this is of no help to you now - you need to plan for your requirements.

    What you could do is use (if you have ) or acquire one of the log scrape tools - IBM's Log Analysis Tool, BMC's Log Master or Broadcom/CA's Log Analyser.  These tools enable you to read the Db2 log and extract the data of inserted and deleted rows; and, depending on DATA CAPTURE CHANGES, they could also get details of changes to rows.  If you go down this path the vendor will be able to assist with actual usage.



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