Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2004
    Posts
    3

    Unanswered: SQL execution and commits time

    Hi guys,

    Can you please let me know your thoughts on the following:

    We have on update query, which simply updates on record in a table with where condition as it primary key, but it take about 0.5 to 1 sec. This particular query is excecuted so many times because it is used in sequence number generation in our application.

    Q. In SQL snapshots, does the excecution time shown includes the time it takes to execution+commit or just excecution?

    In our implementation we have a table with one record for every parallel sequence that we need in the application, and we store the current sequence no.
    We update this current sequence no = sequence no +1 on every transaction's commit. This particular update is taking a very long time and also generating locks on the table.

    Q. What can be done to avoid this?

    Thanks,
    Diwakar

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    To know the execution time of a SQL Statement, event monitors should be used ... Event monitors give a clear split up of the time taken by various processes (like execution, open cursor, prepare, close cursor, commit/rollback) including sort time, lock wait time etc.

    For Q2, why not use SEQUENCE Objects which are not locked ? BTW, what version of db2 are you on ?

    Cheers
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Sep 2004
    Posts
    3
    Satya,

    We are using DB2 UDB v7.2... Will the snapshot/event monitor will give the commit time too...

    Thanks,
    Diwakar

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Yes ...

    And 7.2 does have sequences, which you can make use of ..

    Cheers
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

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