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:

Copy to Clipboard

customer_numfull_nameaddressphone
101Ludwig Pauli213 Erstwild Court, Sunnyvale, CA 94086408-789-8075
102Carole Sadler785 Geary St, San Francisco, CA 94117415-822-1289
103Philip Currie654 Poplar, P. O. Box 3498, Palo Alto, CA 94303415-328-4543
104Anthony HigginsEast Shopping Cntr., 422 Bay Road, Redwood City, CA 94026415-368-1100
105Raymond Vector1899 La Loma Drive, Los Altos, CA 94022415-776-3249
106George Watson1143 Carver Place, Mountain View, CA 94063415-389-8789
107Charles Ream41 Jordan Avenue, Palo Alto, CA 94304415-356-9876
108Donald Quinn587 Alvarado, Redwood City, CA 94063415-544-8729
109Jane MillerMayfair Mart, 7345 Ross Blvd., Sunnyvale, CA 94086408-723-8789
110Roy Jaeger520 Topaz Way, Redwood City, CA 94062415-743-3611
  • 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:

Copy to Clipboard

customer_numfull_nameaddressphone
119Bob Shorter2405 Kings Highway, Cherry Hill, NJ 08002609-663-6079
120Fred Jewell6627 N. 17th Way, Phoenix, AZ 85016602-265-8754
121Jason WallackLake Biltmore Mall, 350 W. 23rd Street, Wilmington, DE 19898302-366-7511
122Cathy O'Brian543 Nassau Street, Princeton, NJ 08540609-342-0054
123Marvin Hanlon10100 Bay Meadows Ro, Suite 1020, Jacksonville, FL 32256904-823-4239
124Chris Putnum4715 S.E. Adams Blvd, Suite 909C, Bartlesville, OK 74006918-355-2074
125James Henry1450 Commonwealth Av, Brighton, MA 02135617-232-4159
126Eileen Neelie2539 South Utica Str, Denver, CO 80219303-936-7731
127Kim SatiferBlue Island Square, 12222 Gregory Street, Blue Island, NY 60406312-944-5691
128Frank LessorAthletic Department, 1817 N. Thomas Road, Phoenix, AZ 85008602-533-1817
  • 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:

Copy to Clipboard
objecttypeview
customerTv_cust_all
v_cust_allVv_cust_oos
  • 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:

Copy to Clipboard

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

Copy to Clipboard

That file contains:

Copy to Clipboard

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:

Copy to Clipboard

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.

About the Author: