Abstract
Scripts are often created to refresh test databases – either scheduled overnight or on demand – so that QA or development teams can validate or debug schema and application changes against the latest realistic data. An easy solution is to run a full instance restore using ontape or onbar from the last production archive, but sometimes many copies of the database are required for different projects or testing stages running in parallel, and avoiding the overhead of a separate instance each can be desirable. This means that, before any database can be restored using dbimport or similar, it first needs to be dropped, which is impossible when in use, as an exclusive database lock must be applied.
This article provides a script to achieve that, even if there are continual concurrent connection attempts by applications.
Content
Links in this article not shown in full are for pages within the new IBM Documentation site:
https://www.ibm.com/docs/en/informix-servers/14.10
The test system used for this article is an IDS 14.10.FC6 Developer Edition Linux Docker container created with the image available from:
https://hub.docker.com/r/ibmcom/informix-developer-database
This shell script is a complete solution:
- DBACCNOIGN must be set for dbaccess to exit on any error (not the default behaviour).
- IFX_DIRTY_WAIT can help acquire exclusive locks needed for schema changes.
- Edit the test on “whoami” if “informix” is not the DBA user on your system.
- SQL is run in background which will wait until users have been kicked off.
- The DELETE statement has the effect of barring other users from the database so they can’t reconnect. Such direct action on system tables is not normally recommended, but this is far easier than constructing and executing separate REVOKE statements on all necessary users.
- The PID of the background process is available with “$!”, which is used to look up its Informix session ID with “onstat -g ses” so it can be excluded from termination.
- The list of all sessions using the database is obtained with “onstat -g sql“.
- Each of those is then terminated in turn with “onmode -z“.
- The script waits until the background process has finished, which should be very quick if all goes well, or otherwise after the lock timeout.
Here is a useful script to run in another session while testing “drop_database.sh”:
Caveats
All testing was with the “bash” shell: only minor changes would be needed for “ksh” if any.
In the unlikely event that you are running Informix directly in a Windows operating system, WSL should be able to run these scripts, but that is untested.
Do not install on a production system!
Conclusion
Should you regularly need to drop a database which is always heavily used, the script in this article could save much effort and provide a more reliable result.
Disclaimer
Suggestions above are provided “as is” without warranty of any kind, either express or implied, including without limitation any implied warranties of condition, uninterrupted use, merchantability, fitness for a particular purpose, or non-infringement.
