Unanswered: The age old question: how best to drop a database with live connections???
Within my organisation, we often wish to recreate (from production clones) the databases of our test environments. Frequently, the dropping of the old DB will fail because of live connections, beginning recurring, tedious wild goose chases to unearth the errant client.
DBAs repeatedly tell us there is nothing they can do, usually adding witheringly that the development function should know which applications are connected to a database, and that applications should be written to facilitate this kind of administration. But it's not that simple. A lingering connection could be from (for example) a Squirrel client on the machine of a developer who has rang in sick. Or perhaps a pool that is deliberately configured to attempt reconnection in the event of a killed connection.
I know this is an age-old question, but is there really no accepted administrative pattern that can be reliably applied to drop one* particular database? A pattern that doesn't rely upon unrealistic minute-to-minute, manual cataloguing and policing of what is and isn't connected to a given database? Or rely upon applications being built and deployed with strange characteristics (e.g. connection pools that don't attempt reconnection) just to suit a non-production administrative use case?
For example, among the ideas we have mulled are...
a) have one user per-environment, used by all applications/tools. During recreation of that database, revoke all of that user's connection privileges
b) create some kind of tunnel (e.g. TCP/IP) between all servers/resources and the relevant database with which they communicate. During recreation of the database, disable the tunnel
Disappointingly, all of the ideas are coming from the development function rather than the DBAs.
Any advice, greatly appreciated!
* - one among many, so the server can not be bounced en mass
you cannot drop a database with live connections on this database. The drop database puts an exclusive lock on the database. If users are connected to this database, no way to drop it.
onstat -g sql will allow you to identify which sessions are connected to which database. onstat -u will tell you who those sessions belong to. You can also make a query with the sysmasters tables to have a more direct result.
To clean the remaining sessions, use onmode -z sessionnumber and wait till transactions are closed. Another way is to switch to Quiescent mode onmode -u
The best way to prevent users from connecting to any database (ie you want noone to connect while your are performing your admin tasks) is to switch the instance to the administration mode by onmode -j
Only the informix user will be able to perform SQL statements.
To go back to multi-user mode: onmode -u
This won't do it exactly as you want, but will at lease provide you with a viable and safe solution.