Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2002
    Location
    Chennai, India
    Posts
    184

    Unanswered: Identify records modified based on time

    I have a small requirement where by I want to retrieve all the records in a table modified since last 24 hours. Can anyone please help me out with this query?

    Is this possible?

    TIA.

  2. #2
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    add a column (datatype timestamp) and for each new row update this column as current timestamp
    Rahul Singh
    Certified DB2 9 DBA / Application Developer

  3. #3
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    or create trigger on that table
    Rahul Singh
    Certified DB2 9 DBA / Application Developer

  4. #4
    Join Date
    Aug 2002
    Location
    Chennai, India
    Posts
    184
    Thanks for the input provided.

    However, in absence of such a column for the table, can we also use the transaction logs to identify the inserts/updates to a table? I was thinking of using Recovery Expert for this? Is this feasible?

    TIA.

  5. #5
    Join Date
    Jan 2003
    Posts
    1,605
    ggnanaraj, rahul_s80 suggestion is nice one - creating trigger is one of solution.

    Sample (Trigger fires up before update of column col1):
    Code:
    CREATE TABLE DB2ADMIN.TAB1 (
                      COL1 INTEGER ,
                      COL2 TIMESTAMP DEFAULT CURRENT TIMESTAMP)@
    
    INSERT INTO DB2ADMIN.TAB1 VALUES (1,CURRENT TIMESTAMP) @
    INSERT INTO DB2ADMIN.TAB1 VALUES (2,CURRENT TIMESTAMP) @
    
    CREATE TRIGGER DB2ADMIN.TRIG
    BEFORE UPDATE OF COL1 ON DB2ADMIN.TAB1
    REFERENCING NEW AS NEW
    FOR EACH ROW
    BEGIN ATOMIC
    SET NEW.COL2 = CURRENT TIMESTAMP;
    END @
    Save commands in file.sql and execute above commands by:
    Code:
    db2 -td@ -f file.sql
    Note: default definition at col2 column is to get the timestamp executed by insert statements.

    See data before update:
    Code:
    SELECT * FROM DB2ADMIN.TAB1
    Execute the following SQL to see the change:
    Code:
    UPDATE DB2ADMIN.TAB1 SET COL1=100 WHERE COL1=2
    Check data one more time:
    Code:
    SELECT * FROM DB2ADMIN.TAB1
    Have you noticed the timestamp has only changed in col1=100 row.

    Hope this helps,
    Grofaty
    Last edited by grofaty; 09-25-07 at 05:41.

  6. #6
    Join Date
    Jan 2003
    Posts
    1,605
    Quote Originally Posted by ggnanaraj
    Thanks for the input provided.
    However, in absence of such a column for the table
    Hi,
    create new column:
    alter table schema.table_name add column new_column_name

    Quote Originally Posted by ggnanaraj
    I was thinking of using Recovery Expert for this?
    Why searching for a difficult solution if there is simple one...

    Hope this helps,
    Grofaty

Posting Permissions

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