Views in the global catalog table containing federated information
Most of the catalog views in a federated database are the same as the catalog views in any other Db2® database.
There are several unique views that contain information pertinent to a federated system, such as the SYSCAT.WRAPPERS view.
The SYSCAT views are read-only. You cannot issue an update or insert operation on a view in the SYSCAT schema. Using the SYSSTAT views is the recommended way to update the system catalog. Change applications that reference the SYSCAT view to reference the updatable SYSSTAT view instead.
The following table lists the SYSCAT views which contain federated information. These are read-only views.
| Catalog views | Description |
|---|---|
| SYSCAT.CHECKS | Contains check constraint information that you defined. |
| SYSCAT.COLCHECKS | Contains columns referenced by a check constraint. |
| SYSCAT.COLUMNS | Contains column information about the data source objects (tables and views) that you created nicknames for. |
| SYSCAT.COLOPTIONS | Contains information about column option values that you set for a nickname. |
| SYSCAT.CONSTDEP | Contains the dependency of an informational constraint that you defined. |
| SYSCAT.DATATYPES | Contains data type information about local built-in and user-defined Db2 data types. |
| SYSCAT.DBAUTH | Contains the database authorities held by individual users and groups. |
| SYSCAT.FUNCMAPOPTIONS | Contains information about option values that you have set for a function mapping. |
| SYSCAT.FUNCMAPPINGS | Contains the function mappings between the federated database and the data source objects. |
| SYSCAT.INDEXCOLUSE | Contains columns that participate in an index. |
| SYSCAT.INDEXES | Contains index specifications for data source objects. |
| SYSCAT.INDEXOPTIONS | Contains information about index options. |
| SYSCAT.KEYCOLUSE | Contains columns that participate in a key defined by a unique key, primary key, or foreign key constraint. |
| SYSCAT.NICKNAMES | Contains information about nicknames that you created. |
| SYSCAT.REFERENCES | Contains information about referential constraints that you defined. |
| SYSCAT.ROUTINES | Contains local Db2 user-defined functions, or function templates. Function templates are used to map to a data source function. |
| SYSCAT.REVTYPEMAPPINGS | This view is not used. All data type mappings are recorded in the SYSCAT.TYPEMAPPINGS view. |
| SYSCAT.ROUTINEOPTIONS | Contains information about federated routine option values. |
| SYSCAT.ROUTINEPARMOPTIONS | Contains information about federated routine parameter option values. |
| SYSCAT.ROUTINEPARMS | Contains a parameter or the result of a routine defined in SYSCAT.ROUTINES. |
| SYSCAT.ROUTINESFEDERATED | Contains information about federated routines that you defined. |
| SYSCAT.SERVERS | Contains server definitions that you create for data source servers. |
| SYSCAT.TABCONST | Each row represents a table and nickname constraints of type CHECK, UNIQUE, PRIMARY KEY, or FOREIGN KEY. |
| SYSCAT.TABLES | Contains information about each local Db2 table, federated view, and nickname that you create. |
| SYSCAT.TYPEMAPPINGS | Contains forward data type mappings and reverse data type mappings. The mapping is to local Db2 data types from data source data types. These mappings are used when you create a nickname on a data source object. |
| SYSCAT.USEROPTIONS | Contains user authorization information that you set when you create user mappings between the federated database and the data source servers. |
| SYSCAT.VIEWS | Contains information about local federated views that you create. |
| SYSCAT.WRAPOPTIONS | Contains information about option values that you have set for a wrapper. |
| SYSCAT.WRAPPERS | Contains the name of the wrapper and library file for each data source that you create a wrapper for. |
The following table lists the SYSSTAT views which contain federated information. These are read-write views that contain statistics you can update.
| Catalog views | Description |
|---|---|
| SYSSTAT.COLUMNS | Contains statistical information about each column in the data source objects (tables and views) that you have created nicknames for. Statistics are not recorded for inherited columns of typed tables. |
| SYSSTAT.INDEXES | Contains statistical information about each index specification for data source objects. |
| SYSSTAT.ROUTINES | Contains statistical information about each user-defined function. Does not include built-in functions. Statistics are not recorded for inherited columns of typed tables. |
| SYSSTAT.TABLES | Contains information about each base table. View, synonym, and alias information is not included in this view. For typed tables, only the root table of a table hierarchy is included in the view. Statistics are not recorded for inherited columns of typed tables. |