Informix

nested-group-icon.png

DB2

Expand all | Collapse all

onstat statement

  • 1.  onstat statement

    Posted 23 days ago
    Dears,

             Kindly let me know if I run onstat - g stm command to check current sql statement which give the Session ID but sometimes if I try to kill the session by onmode -z <id> , it doesn't kill the session. So do I need to connect the session id with onstat -k session id ?

    kindly suggest.


    Thanks 
    Amit Patel

    ------------------------------
    AMIT PATEL
    ------------------------------


  • 2.  RE: onstat statement

    Posted 23 days ago

    Hi AMit

     

     

    Killing the session may take some(and sometimes a lot) of time because what it does is rollback the transaction. If the transaction is "intensive", i.e lots of SQL statements, it can really take long.
    You can check what this session does with onstat -g tpf and look at the 'isrb' column and the 'is*' columns in general

     

     






  • 3.  RE: onstat statement

    Posted 23 days ago
    Amit - sometimes a session can't be killed.  It can get itself into a state where it is simply hung.  This doesn't happen often, but it can.

    I doubt that it's a locking issue which is preventing the termination of the session (which is what you would be looking at with onstat -k), although you can check on the locks held this way, and see if locks are being released.

    You should look at the session with onstat -g ses <session id>, and check the flags and the thread status.  If this is a long rollback (as Eric suggested, and which is most likely), then you will see an "R" in the 3rd position of the flags, and you can track progress of the rollback with onstat -x and compare the beginning log position of the transaction with the current log position.

    I have run into some bugs before with mutexes, which have prevented a session from being killed.  In this case the 1st position of the flags showed an "S", and they only solution was to restart Informix.  If something unusual has happened, like an assert failure, the session may be shown as "defunct" and, again, there's not much you can do.

    Mike



    ------------------------------
    Mike Walker
    ------------------------------



  • 4.  RE: onstat statement

    Posted 22 days ago

    Look at the stack – that can sometimes help as to why you have an unkill'able session

     

    Cheers

    Paul