Catalog views
The database manager creates and maintains two sets of catalog views that are defined on top of the base system catalog tables.
- SYSCAT views are read-only catalog views that are found in the SYSCAT schema. The RESTRICT option on CREATE DATABASE statement determines how SELECT privilege is granted. When the RESTRICT option is not specified, SELECT privilege is granted to PUBLIC.
- SYSSTAT views are updatable catalog views that are found in the SYSSTAT schema. The updatable views contain statistical information that is used by the optimizer. The values in some columns in these views can be changed to test performance. (Before changing any statistics, it is recommended that the RUNSTATS command be invoked so that all the statistics reflect the current state.)
Applications should be written to the SYSCAT and SYSSTAT views rather than the base catalog tables.
All the catalog views are created at database creation time. The catalog views cannot be explicitly created or dropped. In a Unicode database, the catalog views are created with IDENTITY collation. In non-Unicode databases, the catalog views are created with the database collation. The views are updated during normal operation in response to SQL data definition statements, environment routines, and certain utilities. Data in the catalog views is available through normal SQL query facilities. The catalog views (with the exception of some updatable catalog views) cannot be modified using normal SQL data manipulation statements.
An object table, statistical view, column, or index object appears in a user's updatable SYSSTAT catalog view only if that user holds explicit CONTROL privilege on the object, or holds explicit DATAACCESS authority. An object table or statistical view also appears in a user's updatable SYSSTAT.TABLES catalog view if the user is a direct or indirect member of a role that has CONTROL privilege on the object, or a role that has DATAACCESS authority. Role privileges and authorities are not considered when determining the objects that appear in the other SYSSTAT catalog views. A routine object appears in a user's updatable SYSSTAT.ROUTINES catalog view if that user owns the routine or holds explicit SQLADM authority. Group privileges and authorities are not considered when determining the objects that appear in a user's updatable SYSSTAT catalog views.
The order of columns in the views may change from release
to release. To prevent this from affecting programming logic, specify
the columns in a select list explicitly, and avoid using SELECT
*
. Columns have consistent names based on the types of objects
that they describe.
Described Object | Column Names |
---|---|
Table | TABSCHEMA, TABNAME |
Index | INDSCHEMA, INDNAME |
Index extension | IESCHEMA, IENAME |
View | VIEWSCHEMA, VIEWNAME |
Constraint | CONSTSCHEMA, CONSTNAME |
Control | CONTROLSCHEMA, CONTROLNAME, CONTROLID |
Trigger | TRIGSCHEMA, TRIGNAME |
Package | PKGSCHEMA, PKGNAME |
Type | TYPESCHEMA, TYPENAME, TYPEID |
Function | ROUTINESCHEMA, ROUTINEMODULENAME, ROUTINENAME, ROUTINEID |
Method | ROUTINESCHEMA, ROUTINEMODULENAME, ROUTINENAME, ROUTINEID |
Procedure | ROUTINESCHEMA, ROUTINEMODULENAME, ROUTINENAME, ROUTINEID |
Column | COLNAME |
Schema | SCHEMANAME |
Table Space | TBSPACE |
Database partition group | DBPGNAME |
Audit policy | AUDITPOLICYNAME, AUDITPOLICYID |
Buffer pool | BPNAME |
Event Monitor | EVMONNAME |
Condition | CONDSCHEMA, CONDMODULENAME, CONDNAME, CONDMODULEID |
Data source | SERVERNAME, SERVERTYPE, SERVERVERSION |
Global variable | VARSCHEMA, VARMODULENAME, VARNAME, VARMODULEID |
Histogram template | TEMPLATENAME, TEMPLATEID |
Module | MODULESCHEMA, MODULENAME, MODULEID |
Period | PERIODNAME |
Role | ROLENAME, ROLEID |
Security label | SECLABELNAME, SECLABELID |
Security policy | SECPOLICYNAME, SECPOLICYID |
Sequence | SEQSCHEMA, SEQNAME |
Threshold | THRESHOLDNAME, THRESHOLDID |
Trusted context | CONTEXTNAME, CONTEXTID |
Usage list | USAGELISTSCHEMA, USAGELISTNAME, USAGELISTID |
Work action | ACTIONNAME, ACTIONID |
Work action set | ACTIONSETNAME, ACTIONSETID |
Work class | WORKCLASSNAME, WORKCLASSID |
Work class set | WORKCLASSSETNAME, WORKCLASSSETID |
Workload | WORKLOADID, WORKLOADNAME |
Wrapper | WRAPNAME |
Alteration Timestamp | ALTER_TIME |
Creation Timestamp | CREATE_TIME |