DB2 System Catalog Tables


Each DB2 database has a set of tables called the system catalog tables. DB2 creates these tables when a database is created. They contain definitions of database objects (for example, tables, views, indexes, and packages), security information about the type of access that users have to these objects, and statistical information about the tables in the database.

DB2 automatically updates these tables when SQL Data Definition Language (DDL) statements are issued. For example, when you create a table, DB2 enters the definition of the table into the SYSIBM.SYSTABLES table. When you create an index, DB2 enters the index definition into the SYSIBM.SYSINDEXES table. DB2 uses these catalog tables to keep track of what exists in the database and their statistics. You cannot explicitly create or drop these tables, but you can query and view their contents.

DB2 creates the system catalog base tables under the SYSIBM schema. All of the table names have the prefix SYS, for example, SYSTABLES, SYSVIEWS, and SYSTABLESPACES. DB2 also creates a set of read-only views for the SYSIBM tables under the SYSCAT schema. These views contain the same or a subset of the information in the SYSIBM base tables, but the view names do not have the SYS prefix. For example, SYSCAT.TABLES is a view defined for the SYSIBM.SYSTABLES table, SYSCAT.VIEWS is a view defined for the SYSIBM.SYSVIEWS table, and SYSCAT.TABLESPACES is a view defined for the SYSIBM.SYSTABLESPACES table.

In addition to the SYSIBM tables and the SYSCAT views, DB2 defines the following database objects in the system catalog.

  • A set of routines (functions and procedures) in the schemas SYSFUN and SYSPROC.

  • A set of updatable catalog views 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 query performance (see section D.3, How to Use the SYSSTAT Tables to Perform What-if Modeling and Analysis).

To display the names of all the system catalog tables and views along with their creation time, use the LIST TABLES FOR SYSTEM command. To display tables or views by their schema names, use the LIST TABLES FOR SCHEMA schemaname command, where schemaname is any valid schema, for example, SYSIBM, SYSCAT, SYSFUN, SYSPROC, or SYSSTAT.



Understanding DB2(R. Learning Visually with Examples)
Understanding DB2: Learning Visually with Examples (2nd Edition)
ISBN: 0131580183
EAN: 2147483647
Year: 2004
Pages: 313

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net