5

I have a DB2 database with a table for daily transactions, which is cleared to a history table at the end of the day, every day.

I insert rows to this table via java-JDBC INSERT commands and they usually take less than a second to complete but sometimes (I'm still not sure what is the pattern) it can take up to a minute to complete the INSERT command and since I don't have a timeout configured on the JDBC driver, it waits until the command is completed and then carries on.

These INSERT commands are executed thousands of times per day but this only happens on about 5 or 10 of them almos every day. The table has some indexes and a trigger attached on AFTER INSERT event.

Is there any tool or technique I can use to measure what is taking so long to execute on my INSERT command? I want to know if it is the indexes, the trigger or, for any reason, the query itself.

Improve this question
4
  • What DB2 version and platform?
    – mustaccio
    Commented Dec 22, 2015 at 15:30
  • The version is V6R1M0, on an AS400
    – Roberto Linares
    Commented Dec 22, 2015 at 16:03
  • 2
    The database on System i is tightly integrated with the operating system. You might be able to get some hints from the system monitoring toos, as explained here.
    – mustaccio
    Commented Dec 22, 2015 at 16:25
  • I would also look into any locking views to see why things are waiting.
    – Chris Aldrich
    Commented Dec 23, 2015 at 14:07

1 Answer 1

Reset to default
-2

I guess it's a locking issue.

While such inserts take so long use admin view below. The column HLD_CURRENT_STMT_TEXT shows the blocker statement (what cause the insert took so long):

db2 select * from SYSIBMADM.MON_LOCKWAITS
Improve this answer
2
  • You sure SYSIBMADM.MON_LOCKWAITS exists in the IBM i 6.1 database?
    – mustaccio
    Commented Jan 30, 2016 at 16:53
  • oops, my bad!! it exists on DB2 luw.
    – alireza_amirbiglou
    Commented Feb 1, 2016 at 5:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.