Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2008
    Posts
    11

    Unanswered: how to trace insert statement time

    Hi I want to know the insert time of every row inserted in a particular table.
    How can I achieve it.
    My requirement is to trace the insert time of each row in table A
    without changing the structure of table A so i have created another table
    B having one primary key same as table A and two more columns start and end
    start is the starting time and end is the ending time.

    Now how to capture the start and end time of each row inserted in table A.
    I tried with trigger but not able to create trigger on before insert event as i cant use INSERT statement in BEFORE INSERT trigger


    Thanks...

  2. #2
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    why you want to capture before n after time. usually inserts happens in microsecond range.... or if your inserts takes very long time i think a current timestamp before and after the insert might give you idea
    Rahul Singh
    Certified DB2 9 DBA / Application Developer

  3. #3
    Join Date
    Apr 2008
    Posts
    11
    Rahul,
    I am not talking about one insert or 2 inserts.
    the table contain 20000 insertion per min through application and is not possible for me to take the time before and after insertions.
    Thats why i need to make it automatized

    hope now you can understand the scenario.

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You need an AFTER INSERT trigger.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Apr 2008
    Posts
    11
    HI,
    After trigger will give me the timestamp of after insert.
    now ho can i get the insertion time. with before insert trigger.
    and before trigger is not working with INSERT statement.

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You have a misconception about the "current timestamp" in a SQL statement...

    Each SQL statement is executed at a single point in time only and the duration of the execution is not relevant. That means, whenever a SQL statement starts executing, it grabs the current timestamp value and "freezes" that for its own execution. Thus, the timestamps in a before and after trigger are always the same because triggers are compiled into the INSERT statement and, therefore, use the same frozen current timestamp.

    Here is an example that you can try yourself:
    Code:
     $ db2 "create table t ( a int, before timestamp )"
    DB20000I  The SQL command completed successfully.
    
    $ db2 "create trigger b before insert on t referencing new as n for eachrow set n.before = current timestamp"
    DB20000I  The SQL command completed successfully.
    
    $ db2 "create table t2 ( after timestamp )"
    DB20000I  The SQL command completed successfully.
    
    $ db2 "create trigger a after insert on t for each row insert into t2 values ( current timestamp )"
    DB20000I  The SQL command completed successfully.
    
    $ db2 "insert into t(a) values ( 1 )"
    DB20000I  The SQL command completed successfully.
    
    $ db2 "select * from t"
    
    A           BEFORE
    ----------- --------------------------
              1 2008-07-16-08.52.26.528022
    
      1 record(s) selected.
    
    $ db2 "select * from t2"
    
    AFTER
    --------------------------
    2008-07-16-08.52.26.528022
    
      1 record(s) selected.
    This is standard SQL behavior, and it makes perfect sense because each SQL statement is supposed to be "atomic". And having a duration for atomic operations is a bit counter-intuitive. Granted, it is impossible to execute statements without any duration, but conceptually it is possible to give applications such a view and that is what's done.

    If you need the real timestamp when the statement completed, you have to resort to some external facility to determine the timestamp, e.g. use a UDF to retrieve the information from an independent facility like the operating system. But note that "statement completed" is not really true there either. The trigger calling the UDF is executed as part of the INSERT statement, so the execution is not really completed yet.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    Apr 2008
    Posts
    11
    HI Stolze
    Thanks for clarificaiton.....!!!!!!!!

Posting Permissions

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