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:
0 2 * * * . ./.profile ; save_schemas.sh
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:
$ pwd
/home/informix/schemas/instance1/stores_demo
$ ls -o
total 40
-rw-r--r-- 1 informix 18836 Nov 20 02:00 2023-11-20.sql
-rw-r--r-- 1 informix 19224 Nov 23 02:00 2023-11-23.sql
To view schema changes on November 23:
$ diff 2023-11-20.sql 2023-11-23.sql
601a602,607
> CREATE PROCEDURE schema_change_test ()
> END PROCEDURE;
>
> REVOKE EXECUTE ON PROCEDURE schema_change_test () FROM "public";
> GRANT EXECUTE ON PROCEDURE schema_change_test () TO "public";
>
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.