Results 1 to 13 of 13
  1. #1
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557

    Question Unanswered: SQL not getting killed

    We are on DB2 v8.1.1.96 FixPak 10

    Our desktop people are deploying a Client Ver 8 and now we have some developers screaming that Ver8 has bug where they kill their SQL on thier side but it is still running if you do list applications.

    I remember when we were on 7 and running 7 client we did have that problem, but was not aware of this in ver 8.

    Can someone either confirm or denny this?

    Thank you, Alex.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  2. #2
    Join Date
    Nov 2005
    Location
    Toronto
    Posts
    65
    sometime db2 will not get noticed if the application being killed on client side. Therefore the application still exist in list applications list. (it's not defect)
    You can use force application command to stop it:
    http://publib.boulder.ibm.com/infoce...6c%69%63%22%20

    Sometime it may not successfully remove the application from the list. At this time, you can do nothing except db2_kill (i.e. db2stop force will not work, since it at the same level of force application. if force application doesn't work, db2stop force will hang), ipclean, ipcs and kill all the processes and do crash recovery
    IBM Certified DBA for DB2 UDB
    IBM Certified Database Developer for DB2 UDB
    DB2 Tech Support, IBM Toronto Software Lab

  3. #3
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    Right, we were using db2force on ver7 and it worked fine.

    Are you saying that this still exists on ver8?

    Thank you.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  4. #4
    Join Date
    Nov 2005
    Location
    Toronto
    Posts
    65
    yes db2 force still exist:

    C:\Program Files\IBM\SQLLIB\bnd>db2 connect to sample

    Database Connection Information

    Database server = DB2/NT 8.2.3
    SQL authorization ID = TAOEWANG
    Local database alias = SAMPLE

    SQL1478W The defined buffer pools could not be started. Instead, one small
    buffer pool for each page size supported by DB2 has been started.
    SQLSTATE=01626

    C:\Program Files\IBM\SQLLIB\bnd>db2 list applications

    Auth Id Application Appl. Application Id DB # of
    Name Handle Name Agents

    -------- -------------- ---------- ------------------------------ -------- -----

    TAOEWANG db2bp.exe 596 *LOCAL.DB2.051122215243 SAMPLE 1



    C:\Program Files\IBM\SQLLIB\bnd>db2 force application 596
    SQL0104N An unexpected token "596" was found following "APPLICATION".
    Expected tokens may include: "ALL". SQLSTATE=42601

    C:\Program Files\IBM\SQLLIB\bnd>db2 force application (596)
    DB20000I The FORCE APPLICATION command completed successfully.
    DB21024I This command is asynchronous and may not be effective immediately.


    C:\Program Files\IBM\SQLLIB\bnd>db2 list applications
    SQL1611W No data was returned by Database System Monitor. SQLSTATE=00000

    C:\Program Files\IBM\SQLLIB\bnd>db2level
    DB21085I Instance "DB2" uses "32" bits and DB2 code release "SQL08023" with
    level identifier "03040106".
    Informational tokens are "DB2 v8.1.10.812", "s050811", "WR21362", and FixPak
    "10".
    Product is installed at "C:\PROGRA~1\IBM\SQLLIB".
    IBM Certified DBA for DB2 UDB
    IBM Certified Database Developer for DB2 UDB
    DB2 Tech Support, IBM Toronto Software Lab

  5. #5
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    I think you have misunderstood what I meant. I was asking if the cituation when process does not go away still in Ver8.

    I know that force is still alive and kicking
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  6. #6
    Join Date
    Nov 2005
    Location
    Toronto
    Posts
    65
    yes I have seen this problem happened several times~~~
    IBM Certified DBA for DB2 UDB
    IBM Certified Database Developer for DB2 UDB
    DB2 Tech Support, IBM Toronto Software Lab

  7. #7
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    Quote Originally Posted by wangzhonnew
    yes I have seen this problem happened several times~~~
    Thank you again.

    In one of your earlier post you are saying that it is not a defect? I fail to undertand why not. Please explain.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  8. #8
    Join Date
    Nov 2005
    Location
    Toronto
    Posts
    65
    we had some PMRs asked for assistance about this issue. None of them treated as defect.

    There are two situation:
    1) application doesn't exist, DB2 can see the application in list application, and can force it using force application.
    In that case, it usually caused by application unexpected stopped.
    The server relies on the OS keepalive function to determine whether a
    connection should be killed due to inactivity. The default for AIX is 3
    hours. Solaris is about 2 hours. So, after applicaiton finishes, the
    server won't consider dropping the connection until after about 2 hours.


    2) application doesn't exist, DB2 can see the application in list application however we cannot force them using force application.
    This usually means we're in a hung state or the app handle is a phantom process.
    This command also won't force applications if they are in
    critical section.
    At this point, we would recommend to run:
    -db2_kill -brings down db2
    -ipclean -cleans up all process id's and segments for DB2
    -db2start

    I've found many PMRs reporting similar issues and after monitoring it was discovered that applications for some reason were hung in critical sections (ie. in a deadlock situation).
    IBM Certified DBA for DB2 UDB
    IBM Certified Database Developer for DB2 UDB
    DB2 Tech Support, IBM Toronto Software Lab

  9. #9
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    Great info.

    So, it is more of the OS problem then DB?

    Is there anything can be done to prevent this happening?

    And as a refresher. Since Ver7 has the same issue, it does not matter what client to put on, correct?
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  10. #10
    Join Date
    Nov 2005
    Location
    Toronto
    Posts
    65
    correct, it's more OS related. And doesn't related much with Client either.
    (but it shouldn't always happen, if you see it like twice per day maybe you should open PMR to investigate what's going on)
    As I have never heard any workaround to prevent this happened, I guess db2 force application / db2_kill are the only ways to get out of this problem.
    IBM Certified DBA for DB2 UDB
    IBM Certified Database Developer for DB2 UDB
    DB2 Tech Support, IBM Toronto Software Lab

  11. #11
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    Thank you for your prompt responses.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  12. #12
    Join Date
    Feb 2004
    Location
    Copenhagen
    Posts
    220

    Angry

    But Guys

    Thats just not good enough - if kill is the only
    option, its not at all applicable to business
    enviroments. I can't just kill my database because
    a process hangs.

    Furthermore the hung process will typically lock ressources
    (tables etc) preventing further work and leaving expensive
    developers to drink coffee at 200 bucks an hour.

    We have to persuade the guys in Toronto to come up
    with other solutions in future fixpaks.
    Kristian K. Hansen
    Project Supervisor
    National Board of Health

  13. #13
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    Kristian,

    YOu start and we are right behind you.

    I haven't heard it happening in our shop in a long time, since we went to 8, but it used to be normal accurance on 7. And IBM was never able to provide any solid info on it. At the same time I don't remember ever having to bring the whole db down because of it.

    Quote Originally Posted by Tank
    Furthermore the hung process will typically lock ressources
    (tables etc) preventing further work and leaving expensive
    developers to drink coffee at 200 bucks an hour.
    200 bucks an hour? WOW. We pay them in coffee and warm blanket here in Chicago.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

Posting Permissions

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