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.
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:
|101||Ludwig Pauli||213 Erstwild Court, Sunnyvale, CA 94086||408-789-8075|
|102||Carole Sadler||785 Geary St, San Francisco, CA 94117||415-822-1289|
|103||Philip Currie||654 Poplar, P. O. Box 3498, Palo Alto, CA 94303||415-328-4543|
|104||Anthony Higgins||East Shopping Cntr., 422 Bay Road, Redwood City, CA 94026||415-368-1100|
|105||Raymond Vector||1899 La Loma Drive, Los Altos, CA 94022||415-776-3249|
|106||George Watson||1143 Carver Place, Mountain View, CA 94063||415-389-8789|
|107||Charles Ream||41 Jordan Avenue, Palo Alto, CA 94304||415-356-9876|
|108||Donald Quinn||587 Alvarado, Redwood City, CA 94063||415-544-8729|
|109||Jane Miller||Mayfair Mart, 7345 Ross Blvd., Sunnyvale, CA 94086||408-723-8789|
|110||Roy Jaeger||520 Topaz Way, Redwood City, CA 94062||415-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:
|119||Bob Shorter||2405 Kings Highway, Cherry Hill, NJ 08002||609-663-6079|
|120||Fred Jewell||6627 N. 17th Way, Phoenix, AZ 85016||602-265-8754|
|121||Jason Wallack||Lake Biltmore Mall, 350 W. 23rd Street, Wilmington, DE 19898||302-366-7511|
|122||Cathy O'Brian||543 Nassau Street, Princeton, NJ 08540||609-342-0054|
|123||Marvin Hanlon||10100 Bay Meadows Ro, Suite 1020, Jacksonville, FL 32256||904-823-4239|
|124||Chris Putnum||4715 S.E. Adams Blvd, Suite 909C, Bartlesville, OK 74006||918-355-2074|
|125||James Henry||1450 Commonwealth Av, Brighton, MA 02135||617-232-4159|
|126||Eileen Neelie||2539 South Utica Str, Denver, CO 80219||303-936-7731|
|127||Kim Satifer||Blue Island Square, 12222 Gregory Street, Blue Island, NY 60406||312-944-5691|
|128||Frank Lessor||Athletic Department, 1817 N. Thomas Road, Phoenix, AZ 85008||602-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:
- 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:
- 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.
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.