Have you ever needed to write a pair of scripts: one to create some tables, data types, or user-defined functions and a second to uninstall the same? How do you test that the uninstall script did its job and didn’t leave any database objects behind? Today, using a tool that comes with DB2, I’ll show you how to ensure that your cleanup scripts do their jobs.
I test my cleanup scripts using a tool called db2look. It is a powerful tool for extracting statistics and DDL from a database and it has several command line options. In my case, since I know that my program installs new data types, tables, variables, functions, and procedures, I use db2look with the -e option, which generates all the DDL needed to duplicate the database.
Say you have two scripts: install.sql and uninstall.sql. You want to verify that any database objects created by install.sql get cleaned up when you run uninstall.sql. You can verify this using db2look as follows (assuming you have the authority to create a fresh database on your development system):
- Create a new database to provide a baseline:
create database test - Run db2look on your new database and store the result:
db2look -d test -e -o db2look.baseline - Run your install script:
db2 -tf install.sql - Run you uninstall script:
db2 -tf uninstall.sql - Rerun db2look and store the result:
db2look -d test -e -o db2look.postuninstall - Run your favorite diff tool on the two db2look result files you generated:
diff db2look.postuninstall db2look.baseline
If your uninstall script did its job perfectly, the only difference you should see is a one-line mismatch in which a timestamp of when db2look was run is printed. Otherwise, the diff output tells you exactly what types, procedures, or tables your uninstall script failed to clean up. This kind of test can easily be made a part of any automated testing process you have for testing your scripts. In fact, if you do this as part of your automated testing and create your baseline before your tests are run, it would also detect if any of your automated tests failed to clean up any database objects they created as well.
If the timestamp line makes automated comparison difficult, you can always filter it out beforehand from each file using something like sed:
sed 's/-- Timestamp.*//' db2look.baseline > db2look.baseline.notimestamp
sed 's/-- Timestamp.*//' db2look.postuninstall > db2look.postuninstall.notimestamp
diff db2look.postuninstall.notimestamp db2look.baseline.notimestamp