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.

  • What DB2 version and platform? – mustaccio Dec 22 '15 at 15:30
  • The version is V6R1M0, on an AS400 – Roberto Linares Dec 22 '15 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 Dec 22 '15 at 16:25
  • I would also look into any locking views to see why things are waiting. – Chris Aldrich Dec 23 '15 at 14:07

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):

  • You sure SYSIBMADM.MON_LOCKWAITS exists in the IBM i 6.1 database? – mustaccio Jan 30 '16 at 16:53
  • oops, my bad!! it exists on DB2 luw. – alireza_amirbiglou Feb 1 '16 at 5:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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