ArcGIS Server Banner

Views in Informix

Views in Informix

Release 9.3 E-mail This TopicPrintable VersionGive Us feedback
Generally speaking, a view is a predefined, named query stored in the database. Once created, views can be queried in much the same way that tables can be queried. Views contain rows and columns, like tables do. However, views are not tables but representations of data stored in, or calculated from, one or more other tables (business tables) or views. The columns of the view are the items from the select list of the query that defines the view.

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 client applications see as feature classes, and you can create views to bring in data from other tables.

Creating a view with the sdetable create_view operation is much like any other Informix view creation. If you want to create a view using a layer and you want the resulting view to appear as a feature class to client applications, include the feature class's spatial column in the view definition.

For information on privileges needed to create views, consult the Informix documentation.

IDS 10 Infocenter

IDS 11 Infocenter

Querying the sysviews system catalog table

When you create views in an Informix database using SQL, the views are defined in the sysviews system catalog table. The same is true when you create views using sdetable –o create_view. That means you can query the sysviews system catalog table to find out how each view is defined.

The sysviews system catalog table has the following columns:


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

Creating a view with a join

The following view returns the employee ID, employee name, and employee salary by joining two tables, EMPLOYEES (owned by the connected user) and SALARY (from the schema PAYROLL):

Ready: sdetable –o create_view –T view_dept_100_salaries –t "employees,payroll.salaries" –c 'employees.objectid,employees.name,payroll.salaries.salary' –w "department = 100 and employees.objectid = payroll.salaries.employeeid" –u gdb –p gdb

If the tables are not owned by the connected user, the name must be in the format owner.table. When using multiple tables, you must qualify the names of each column listed after –c with the name of the table using the format table.column or owner.table.column.

See Also

  • Using database views