how to check and reduce the call time for stored proc?

vinay_care · New User Joined: 22 Sep 2006 Posts: 17

Hi,

We have multiple systems speaking to DB2, and those are inter connected by some middle wear applications.
those applications provides trigger to DB2 Stored procedures and internally in this SP we have call to common subroutine program verifying the login info? and this call is taking a long time
and this wait time is letting the system to crash because the threads get inactive after a certain wait time and new threads are put in queues.

so its been difficult to track or resolve this, please suggest. your input is very valuable.

Thanks,
Vinay

Bharath Bhat · Posted: Tue Feb 17, 2009 4:04 pm

You can run strobe to check the time taken by the SP.

If you have identified the program/routine which is taking time, then try to reduce the burden on it.

If it is a routine, then make sure the call is static. Check the SQL statements in the routine, if there is a better way to write the SQL ( better performing), then go ahead and change it. Try to reduce unnecessary statements in the program.