Topic
  • 3 replies
  • Latest Post - ‏2014-01-28T15:57:55Z by LukeNumrych
Kasparek
Kasparek
4 Posts

Pinned topic Terminate db2bp Processes for All Logged on Users

‏2014-01-27T14:55:44Z |

In case of SAP, typically there are two main users for administration purposes - <sid>adm and db2<sid>. Is there a way to force a temination of db2bp for all logged on users before db2stop? Imagine a situation where someone forgots to log off his user session after he run a SQL query and afterwards you want to stop DB2 instance.

  • LukeNumrych
    LukeNumrych
    107 Posts

    Re: Terminate db2bp Processes for All Logged on Users

    ‏2014-01-27T20:02:26Z  

    I do not know anything about SAP, but could you not just issue a DB2 CLP FORCE command?  Sorry, you did not specify what flavor of DB2 you are dealing with, so I linked to DB2 10.5 LUW article...

  • Kasparek
    Kasparek
    4 Posts

    Re: Terminate db2bp Processes for All Logged on Users

    ‏2014-01-27T20:22:54Z  

    I do not know anything about SAP, but could you not just issue a DB2 CLP FORCE command?  Sorry, you did not specify what flavor of DB2 you are dealing with, so I linked to DB2 10.5 LUW article...

    According to my experience 'db2 force application all' does not terminate db2bp processes.

  • LukeNumrych
    LukeNumrych
    107 Posts

    Re: Terminate db2bp Processes for All Logged on Users

    ‏2014-01-28T15:57:55Z  
    • Kasparek
    • ‏2014-01-27T20:22:54Z

    According to my experience 'db2 force application all' does not terminate db2bp processes.

    I am not sure about "db2 force application all", and I do not know about your particular platform, but db2bp can be forced:

    D:\>db2 connect to toolsdb
     
       Database Connection Information
     
     Database server        = DB2/NT64 9.7.9
     SQL authorization ID   = xxxxxx
     Local database alias   = TOOLSDB
     
     
    D:\>db2 list applications | find /i "db2bp"
    xxxxxx db2bp.exe      5453       *LOCAL.DB2.140128152256                                        TOOLSDB  1
     
    D:\>db2 force applications (5453)
    DB20000I  The FORCE APPLICATION command completed successfully.
    DB21024I  This command is asynchronous and may not be effective immediately.
     
     
    D:\>db2 list applications | find /i "db2bp"

    ....

     

    However, the "force" command limits what can be forced:

    To preserve database integrity, only users who are idling or executing interruptible database operations can be terminated.

    The following types of users and applications cannot be forced:

    • users creating a database
    • system applications

     

    Is it possible you are running into one of the above scenarios?

     

    I have developed a one-liner that I use whenever I find that I need to force several applications with the same name, or from the same database, or some combination of these two criteria.  It could be any criteria as long as the field is in the display of "list applications" or "list applications show detail".

    It lets me do a "mass force", but with a much greater degree of precision than "force all".  Here is the one-liner, in the Windows OS form, which should be easily adaptable to a *nix shell syntax...

    for /f "tokens=2,3" %a in ('db2 list applications ^| find /i "databasename" ^| find /i "db2bp.exe" ') do (echo Forcing application ^(%a^), handle ^(%b^) && db2 -v force application ^(%b^))

    Here is a sample run:

    D:\>for /f "tokens=2,3" %a in ('db2 list applications ^| find /i "db2bp.exe" ') do (echo Forcing application ^(%a^), handle ^(%b^) && db2 -v force application ^(%b^))
     
    D:\>(echo Forcing application (db2bp.exe), handle (52197)   && db2 -v force application (52197) )
    Forcing application (db2bp.exe), handle (52197)
    force application (52197)
    DB20000I  The FORCE APPLICATION command completed successfully.
    DB21024I  This command is asynchronous and may not be effective immediately.
     
     
     
    D:\>db2 list applications | find /i "db2bp"
     
    D:\>db2 list applications
     
    Auth Id  Application    Appl.      Application Id                                                 DB       # of
             Name           Handle                                                                    Name    Agents

    .....

     

    Hope this helps.

    Updated on 2014-01-28T15:58:54Z at 2014-01-28T15:58:54Z by LukeNumrych