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.
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.
we have call to common subroutine program verifying the login info? and this call is taking a long time
If it is doing just that, then it should not take much time. Suggest you analyze the program again for unnecessary loopings.