SQL Reference

Appendix D. Catalog Views

The database manager creates and maintains two sets of system catalog views. This appendix contains a description of each system catalog view, including column names and data types. All the system catalog views are created when a database is created with the CREATE DATABASE command. The catalog views cannot be explicitly created or dropped. The system catalog views are updated during normal operation in response to SQL data definition statements, environment routines, and certain utilities. Data in the system catalog views is available through normal SQL query facilities. The system catalog views cannot be modified using normal SQL data manipulation commands with the exception of some specific updatable catalog views.

The catalog views are supported in addition to the catalog base tables from Version 1. The views are within the SYSCAT schema and SELECT privilege on all views is granted to PUBLIC by default. Application programs should be written to these views rather than the base catalog tables. A second set of views formed from a subset of those within the SYSCAT schema, contain statistical information used by the optimizer. The views within the SYSSTAT schema contain some updatable columns.
Warning:The intention is to enable applications to update certain columns using the SYSSTAT views, but have the SYSCAT views read only. Currently, the SYSCAT views are not read only. Applications developers are warned to ensure that applications are written to only update catalog information using the SYSSTAT views. The SYSCAT views will become read only views after the next version migration.

The catalog views are designed to use more consistent conventions than the underlying catalog base tables. As such, the order of columns may change from release to release. To protect from this affecting programming logic, always specify explicitly the columns in a select list rather then letting them default by using SELECT *. Columns have consistent names based on the type of objects that they describe:

Described Object
Column Names
 
 
Table
TABSCHEMA, TABNAME
Index
INDSCHEMA, INDNAME
View
VIEWSCHEMA, VIEWNAME
Constraint
CONSTSCHEMA, CONSTNAME
Trigger
TRIGSCHEMA, TRIGNAME
Package
PKGSCHEMA, PKGNAME
Type
TYPESCHEMA, TYPENAME, TYPEID
Function
FUNCSCHEMA, FUNCNAME, FUNCID
Column
COLNAME
Schema
SCHEMANAME
Table Space
TBSPACE
Nodegroup
NGNAME
Buffer pool
BPNAME
Event Monitor
EVMONNAME
Creation Timestamp
CREATE_TIME


[ Top of Page | Previous Page | Next Page ]