View dependencies

Abstract

Informix supports views like other database products, which behave much like tables but are in fact SELECT statements stored for convenience and reuse. If a table or view is dropped and recreated during a schema change, any views in the same database that depended on it will have been lost. This article covers this problem, and includes SQL to list dependencies and definitions of views.

Content

Examples in this article were produced using the provided “stores_demo” database in a Docker container from ibmcom/informix-developer-database.

Let’s say we create a view on “customer” that combines name and address components into single columns:

• See documentation for CREATE VIEW
• Because the SELECT statement returns expressions, we must specify view column names.
• Source columns are of type CHAR, so we need to discard trailing spaces with TRIM.
• We can handle NULL values in “address” with the built-in function NVL.
• Unlike tables, views do not have public SELECT privilege by default, so usually need GRANT.

Then let’s say a requirement comes up to list customers in the above format but only if they are out of state, with the company being in California. A quick solution using another view would be:

• Because the SELECT statement returns simple columns, we do not need to specify view column names.
• By using the existing view, we do not need to repeat the complex logic for combined columns.

Sometime later, let’s say you need to make a small change to the code of “v_cust_all”. There is no syntax in Informix such as “alter view” or “replace view”. The only option is to drop and recreate the view. You therefore need a way to check for dependent views before dropping and recreating a table or view, and this is in fact quite simple:

• This is a query on system catalog tables in “stores_demo” (not “sysmaster”).
• View dependency relationships are contained in “sysdepend“.
• Each table ID is looked up to get the table name from “systables“.
• Dependent views in other databases are not recorded or dropped.

An alternative solution is Server Studio which can show dependencies graphically.

Armed with that, we know that we would need to recreate “v_cust_oos” after “v_cust_all”. If we have lost the original scripts used to create views, the provided tool is “dbschema“, which the following shell script uses:

We can regenerate and save SQL to recreate our views as follows:

That file contains:

You can see that the original source code formatting has not been retained. Informix stores the SQL of views in the above standard format in “sysviews“. Because of this, it is able to update them if a selected object is renamed, but you must keep your view creation scripts somewhere safe if you want to maintain more readable code.

Server Studio can also generate view schemas with the same proviso that formatting is lost, although there is a code beautifier. More importantly, its DB Diff feature can compare and synchronise the schema from another copy of the database, which can both identify missing views and produce the SQL to create them in a single step.

Note that “dbschema” and Server Studio are able to extract the original source for stored procedures from “sysprocbody” rows with “datakey” containing “T” for that procedure ID. Conversely, stored procedures are not updated when an object it uses is renamed.

The best solution is to keep a single source code file for a hierarchy of views, for example:

Conclusion

• This article provides scripts to identify dependent views that would need recreating if an object on which it depends is recreated, and to save their definitions.
• Keeping view source code somewhere safe is important both to retain original readability and if inadvertently dropped.

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.