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
------------------------------
#Db2