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 |
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>;