Views in Informix

Creating views can improve query response times by restricting the number of tables and columns searched when a data request is made. You can create views that appear to client applications as feature classes or nonspatial tables. An ArcSDE view can reference a single table or feature class, or you can create views to join data from multiple tables.

Any user that has the required permissions can create a view using SQL or the ArcSDE sdetable command. Additionally, views can be used to edit data if specific requirements are met. For information on privileges needed to create or edit views, consult the IBM Informix Dynamic Server Information Center for your release.

In Informix databases, view definitions are stored in the sysviews catalog table.

The sysviews system catalog table stores view information

When you create views in an Informix database using SQL or the sdetable command, the views are defined in the sysviews system catalog table. Therefore, you can query the sysviews system catalog table to find out how each view in the database is defined.

The sysviews system catalog table has the following columns:

Name	Type	Description
tabid	integer	ID of the view
seqno	smallint	Line number of the SELECT statement that created the view
viewtext	char(64)	The statement used to create the view

Informix view catalog table

You can first query the systables system catalog table to find the tabid of the view you want. The systables system catalog table stores a record for every database object, but you can query it so only views are returned, as shown in the following example:

SELECT * 
FROM informix.systables 
WHERE tabtype = 'V';

When you find the view for which you want to see the definition, query the sysviews table.

SELECT * 
FROM informix.sysviews 
WHERE tabid = <view_id>;