Db2 (On Premises and Cloud)

Expand all | Collapse all

DB2 11.5 How to force all connections to close

  • 1.  DB2 11.5 How to force all connections to close

    Posted Thu July 01, 2021 12:48 PM
    We have a script that drops and redefines all objects in our databases.  It runs as part of an application build.

    At the time the script executes there may be applications connected and there may be units of work in progress.  None are expected to be long running.  We can not guarantee that there will be no in-flight transactions at the time the script executes.

    At the head of the script there is a DEACTIVATE DB command.  Our intent is to force all user connections to close.  If applications are found to have not closed we attempt to force them to close. 

    Some do not close.  The script reports that connections are still active.  The processes it refers to in its messages no longer exist.  They have long stopped.  They may have been killed, but they are not there.  Guaranteed.

    Example

    SQL1495W  Deactivate database is successful, however, there is still a 
    connection to the database.
    
    SP_SGT   DataFlowEngine 13085      10.138.152.125.64050.210629230001                              IPSDEV   1    
    SETPROXY DataFlowEngine 13078      10.138.152.125.64040.210629230000                              IPSDEV   1    
    SP_HCMT  DataFlowEngine 13102      10.138.152.125.64042.210629230000                              IPSDEV   1    
    force application ('13085')
    DB20000I  The FORCE APPLICATION command completed successfully.
    DB21024I  This command is asynchronous and may not be effective immediately.
    
    
    force application ('13078')
    DB20000I  The FORCE APPLICATION command completed successfully.
    DB21024I  This command is asynchronous and may not be effective immediately.
    
    
    force application ('13102')
    DB20000I  The FORCE APPLICATION command completed successfully.
    DB21024I  This command is asynchronous and may not be effective immediately.

    Our wish is that any application connections present at the time the script is run are closed so that the rest of the script can run without error.  

    We are happy for any in flight transactions to simply be killed.  We do not care about recovery at this stage because the script is about to drop, redefine and reload all database objects.  Remember this is part of a build.  It's not a production app where we want to protect any data.

    The only way to release us from this situation has been to stop the database manager, i.e.

    db2 stop db manager force

    However there is more than one database in the instance.  Stopping the entire database manager affects other application instances.

    What options do we have to forcefully stop all database connections off that might be less drastic?

    Please note, application recoding is not an option.  At all.

    ------------------------------
    MARK JEYNES
    ------------------------------


  • 2.  RE: DB2 11.5 How to force all connections to close

    Posted Thu July 01, 2021 04:31 PM
    Edited by Samuel Pizarro Mon August 02, 2021 09:31 AM
    What you are looking for is called QUIESCE database  

    QUIESCE DATABASE IMMEDIATE FORCE CONNECTIONS

    off course,  the ID performing this must have the proper authority,   and all others  must also not have higher authorities/privileges on Db,  that would allow them to connect in a QUIESCED db.   

    After you have done your work,   you must UNQUIESCE it,  in order to allow regular connections to the database 

    Regards

    ------------------------------
    Samuel Pizarro
    ------------------------------



  • 3.  RE: DB2 11.5 How to force all connections to close

    Posted Sun August 01, 2021 07:46 PM
    Thanks for your reply.

    I discovered that QUIESCE offers a FORCE CONNECTIONS option, but no FORCE APPLICATIONS option.

    I also found that this does not force DB2 to release the application connections to the database.  The numbered handles do not disappear.  They persist in DB2 - even though the connecting client is completely stopped.  If left these connections do not seem to expire or terminate.  The only solution I can find is to stop the whole DB manager; this has widespread impact on the applications that share the instance.

    Surely there must be a way for the admin to release the affected database from these stale connections without taking the whole database down?  Is there any setting that can cause DB2 to terminate stale application handles?


    ------------------------------
    MARK JEYNES
    ------------------------------



  • 4.  RE: DB2 11.5 How to force all connections to close

    Posted Mon August 02, 2021 03:28 AM
    Hello Mark,

    Look at DB2 Workload manager -  CONNECTIONIDLETIME to target applications connections that are idle.  Also, be

    careful forcing of idle connections with Db2 Workload manager CONNECTIONIDLETIME   as  application that setup these idle connections may use them at a later date. I seen this before, where application sets up is own connection pool  of  connections - e.g. 200 connections, and only use avg of 20/30 connections a time. When we attempted to use   DB2 Workload manager -  CONNECTIONIDLETIME to force off these remaining idle connections , the application broke as it was round robining which connection to use for each new connection .  So the only way to fix the problem was to get the application to reduce the size of the connection pool, it had setup.

    Regards

    Brian Walker



    ------------------------------
    Brian Walker
    ------------------------------



  • 5.  RE: DB2 11.5 How to force all connections to close

    Posted Mon August 02, 2021 08:52 AM

    Hi @MARK JEYNES

    I use quiesce here very often,  and it does work.   

    FORCE CONNECTIONS  is exactly the same as "FORCE APPLICATIONS".  it's just a matter of wording. 

    If your application are surviving the QUIESCE FORCE ,  it means they have higher privilege on db , to survive!  You need to look what authorities and privileges such IDs have against your db,  and make sure they don't have too much privilege.  

    QUIESCE DATABASE results in all objects in the database being in the quiesced mode. Only the allowed user or group and SYSADM, SYSMAINT, DBADM, or SYSCTRL will be able to access the database or its objects.

    In other words,  your application IDs should not have any of these authorities, otherwise ​they will survive the QUIESCE 

    Regards



    ------------------------------
    Samuel Pizarro
    ------------------------------