Hello All,

Good day. I'm back again with a new problem

We are currently using 8.1.1.89 Fix Pack 9 - The DB2 resides in an RS6000 with AIX 5.2 as an OS. It uses 16GB of memory. One of our applications makes a call to a procedure. Unfortunately the procedure never ends or cannot be stopped. This is in the test environment. However, using the same tables and data set, this procedure ends in one hour in the production environment.

We attempted to stop it by using "db2 force applications all". It stopped everything except the call to the procedure.

Here's a snapshot:

http://amorsiko.com/images/db2-spotlight-check.jpg

We also tried "db2 db2stop" but it returns this error:

"SQL1025N The database manager was not stopped because databases are still active."

Forcing the procedure call from Quest Spotlight results into nothing...

Is there a way to just stop this call from the command line? And and suggestions on what is causing this issue?

Thank you very much for the help. A good day to you all.

Drogo

Hi, I noticed that the cpu and log usage for this stored procedure is very high. Once you do the force applications, have you checked to see if the stored procedure is maybe doing a rollback? This could take a while.

If it is an unfenced (i.e. SQL) procedure I think it may not even know that you killed the application calling it until it's time to return control to the application, because unfenced procedures run in the database manager, not in separate processes. Just a guess.

This issue happens when running a unit of work and also during rollback. Does this mean that called procedures with an active rollback cannot easily be killed? Or is it to complicated.

Here is how interrupt handling works in general in DB2:

• DB2 receives the (asynchronous) cancellation request and sets internally a flag - a force is just that.
• This flag is checked at certain points during the statement execution and if found to be set, necessary steps are taken to terminate processing and clean up whatever needs to be cleaned up

So for LANGUAGE SQL procedures, you should be fine because the processing is done in the DB2 code itself. For other procedures (e.g. LANGUAGE C or LANGUAGE JAVA), you are pretty much out of luck because the procedure has no clue that it shall be terminated and just happily churns on. For fenced routines, DB2 may actually shut down, ignoring the fenced process; for trusted (not fenced) routines, there is no way to do that, short of a hard killing the thread or db2agent process, which may have rather undesired side effects. Note that I don't know for sure whether DB2 LUW handles fenced procedures as I laid out.