Views in DB2

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 the view is created to allow edits. For information on privileges needed to create or edit views, consult the IBM DB2 Information Center for your release.

View definitions can be accessed through the SYSCAT.VIEWS system catalog view in DB2 databases.

SYSCAT.VIEWS shows view information

When you create views in DB2 using SQL or the sdetable command, they are stored in DB2 system tables. Rather than access the system tables, though, query the DB2 system catalog view SYSCAT.VIEWS to see view definitions. SYSCAT.VIEWS contains one or more rows for each view created.

Field name

Data type

Description

VIEWSCHEMA

VARCHAR(128)

Along with VIEWNAME, the qualified name of a view or table that is used to define a materialized query table or staging table

VIEWNAME

VARCHAR(128)

Along with VIEWSCHEMA, the qualified name of a view or table that is used to define a materialized query table or staging table

DEFINER

VARCHAR(128)

Authorization ID of the creator of the view

SEQNO

SMALLINT

Always 1

VIEWCHECK

CHAR(1)

The type of view checking

  • N = No check option
  • L = Local check option
  • C = Cascaded check option

READONLY

CHAR(1)

Y = Read-only view because of its definition or N = Not read-only view

VALID

CHAR(1)

Y = Valid view or materialized query table definition valid or X = Inoperative view or materialized query table definition; must be re-created

QUALIFIER

VARCHAR(128)

Contains value of the default schema at the time of object definition

FUNC_PATH

VARCHAR(254)

The SQL path of the view creator at the time the view was defined

When the view is used in data manipulation statements, this path must be used to resolve function calls in the view. (Use SYSIBM for views created before version 2.)

TEXT

CLOB(64K)

Text of the CREATE VIEW statement

The SYSCAT.VIEWS catalog view

11/18/2013