Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2007
    Posts
    30

    Question Unanswered: Unstopable Procedure

    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

  2. #2
    Join Date
    Mar 2009
    Posts
    3
    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.

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    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.
    ---
    "It does not work" is not a valid problem statement.

  4. #4
    Join Date
    Mar 2007
    Posts
    30
    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.

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •