DB2 System Catalog Views: Everything You Need to Know About Your DB Objects : Page 2

SYSCAT.TABLES

The SYSCAT.TABLES catalog view stores information about database objects created in a database, such as tables, views, nicknames, and aliases. Anytime these database objects are created, the database manager inserts a row in this catalog view. Database developers can use this information to determine the existence of a database object, its status, the number of columns in the table/view, the number of columns in the primary key, the primary index ID, the number of unique indices, and much more.

Table 1 provides some of the columns available in this catalog view.

Column Name	Data Type	Description
TABSCHEMA	VARCHAR(128)	Stores the schema name on which the database object is defined
TABNAME	VARCHAR(128)	Stores the name of the database object, such as table, view, nickname, or an alias
TYPE	CHAR(1)	Identifies the database object as a table, view, alias, or a nickname (The type value 'T' means table; 'V' means view; 'N' means nickname; and 'A' means alias.)
COLCOUNT	SMALLINT	Number of columns in the table or view
KEYCOLUMNS	SMALLINT	Number of columns that constitute the primary key
KEYINDEXID	SMALLINT	Index ID for the primary key
KEYUNIQUE	SMALLINT	Number of unique constraints in the table or view

(* This table is only a partial list of columns, which are of interest to this article. For the complete list, refer the IBM DB2 UDB Ver. 8.2 SQL Reference Volume 1.)

Table 1. Columns Available in SYSCAT.TABLES Catalog View

SYSCAT.VIEWS

The SYSCAT.VIEWS catalog view stores one or more rows per view defined in a database instance. Developers can use this catalog view to find out more about the views. The information stored in this view includes view name, view DDL text, and the validity of the view. Table 2 provides a partial list of the columns (which are of interest to this article) available in this catalog view.

Column Name	Data Type	Description
VIEWSCHEMA	VARCHAR(128)	Schema name for the view
VIEWNAME	VARCHAR(128)	Name of the view
DEFINER	VARCHAR(128)	User who created the view
VIEWCHECK	CHAR(1)	Type of view checking defined for this view: N = means no check option L = means local check option C = means cascaded check option
READONLY	CHAR(1)	Defines whether the view is read only or not: Y = means read only N = means view is not read only
VALID	CHAR(1)	Determines the validity of the view: Y = means view is valid X = means view is invalid
TEXT	CLOB(64K)	DDL text for view

(* This table is only a partial list of columns, which are of interest to this article. For the complete list, refer the IBM DB2 UDB Ver. 8.2 SQL Reference Volume 1.)

Table 2. Columns Available in SYSCAT.VIEWS Catalog View

SYSCAT.COLUMNS

The SYSCAT.COLUMNS catalog view is used to store information about columns in a table. For each table defined in the database, database manager inserts a row in this catalog view for each column defined in the new table. Table 3 provides a partial list of the columns (which are of interest to this article) available in this catalog view.

Column Name	Data Type	Description
TABSCHEMA	VARCHAR(128)	Stores the schema name of the table on which the column is defined
TABNAME	VARCHAR(128)	Stores the name of the table on which the column is defined
COLNAME	VARCHAR(128)	Name of the column
COLNO	SMALLINT	Position of the column in the table
TYPENAME	VARCHAR(18)	Data type of the column
LENGTH	INTEGER	Size of the column
DEFAULT	VARCHAR(254)	Default value for the column, if defined
INDENTITY	CHAR(1)	'Y' – indicates the column as an identity column 'N' – indicates the column as not an identity column
HIDDEN	CHAR(1)	Type of the hidden column: 'S' – means system managed hidden column; blank if the column is not hidden

(* This table is only a partial list of columns, which are of interest to this article. For the complete list, refer the IBM DB2 UDB Ver. 8.2 SQL Reference Volume 1.)

Table 3. Columns Available in SYSCAT.COLUMNS Catalog View

Previous Page

Next Page