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:
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:
The main script will run silently if all goes well. It will typically be called from a “cron” job like this:
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:
To view schema changes on November 23:
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.
