Login | Register   
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


advertisement
 

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

Find out how DB2 stores metadata information about database instances. You can use this information to learn more about your database objects, making database development easier and more efficient.


advertisement
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



    Comment and Contribute

     

     

     

     

     


    (Maximum characters: 1200). You have 1200 characters left.

     

     

    Sitemap