Abstract

Have you ever been asked to refresh a test database instance from production, then been told afterwards that the definitions of some new objects under development have been lost? It’s obviously better to use a tool such as AGS Server Studio Schema Diff beforehand to save SQL to be reapplied. If that hasn’t happened, you need to have a record of the database schema before it was overwritten so you can list differences. You could purchase the AGS Sentinel Change Management Option to record version snapshots at regular intervals, but this article provides a shell script to achieve that, though without any GUI.

Content

This is the main script:

Copy to Clipboard


You will need to download Art Kagel’s utils2_ak package. The above needs additional options available in the “myschema” ESQL-C program that are not provided by standard “dbschema”. Building the package requires a C compiler and Informix Client SDK.

The following shell scripts are also called, which you may well also find useful in their own right:

Copy to Clipboard
Copy to Clipboard


The main script will run silently if all goes well. It will typically be called from a “cron” job like this:

Copy to Clipboard


That would run at 2am daily. The login profile should be sourced first so that scripts can be found in $PATH and mandatory Informix environment variables are set. The recommended job owner is user “informix” who will most likely have full access privileges to all databases. Files are created in the following directory structure:

$HOME -> schemas -> $INFORMIXSERVER -> database

That caters for systems containing more than one instance, each possibly containing more than one database of interest.

Within each database sub-directory, schema files have names of the form:

YYYY-mm-dd.sql

If the contents are identical to the last one, it is removed to prevent unnecessary duplicates from accumulating.

An example follows:

Copy to Clipboard


To view schema changes on November 23:

Copy to Clipboard

Conclusion

This article provides a mechanism to avoid ever losing the code for objects created or altered on a previous day, and the means to compare schemas from two different dates

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.

Contact Us

If you have any questions or would like to find out more about this topic, please contact us.

Author