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 3

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.INDEXES
The SYSCAT.INDEXES catalog view is used to store information about the indexes defined for a given table. If there is an index defined for a table in the database, then a row will be created in this catalog view for each of those indexes. Database developers can use this catalog view to find information about the indexes for a given table. Table 4 below lists some of the columns available in this catalog view.

Column Name Data Type Description
INDSCHEMA VARCHAR(128) Name of the schema on which the index is defined
INDNAME VARCHAR(18) Index name
DEFINER VARCHAR(128) User who created the index
TABSCHEMA VARCHAR(128) Stores the schema name of the table on which the index is defined
TABNAME VARCHAR(128) Stores the name of the table for which index is defined
COLNAMES VARCHAR(640) List of columns in the index
UNIQUERULE CHAR(1) Determines whether the index is unique or not:
  • D = means duplicate allowed
  • P = means primary index
  • U = means unique index
  • INDEXTYPE CHAR(4)
  • CLUS = means clustered index
  • REG = means regular index
  • DIM = means dimension block index
  • BLOK = means block index
  • (* 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 4. Columns Available in SYSCAT.INDEXES Catalog View

    SYSCAT.INDEXCOLUSE
    The SYSCAT.INDEXCOLUSE catalog view is used to store information about all the columns that participate in an index. If you know the index name, you can use this catalog view to find out its participating columns, its sequence, and its sort order. This catalog view is different from the SYSCAT.INDEXES in the sense that it contains information about only the columns in the index, its sequence, and the order of columns in the index, while the former carries more information such as the type of index, index rules, etc.



    Table 5 lists some of the columns available in this catalog view.

    Column Name Data Type Description
    INDSCHEMA VARCHAR(128) Name of the schema on which the index is defined
    INDNAME VARCHAR(18) Index name
    COLNAME VARCHAR(128) User who created the index
    COLSEQ SMALLINT Stores the schema name of the table on which the index is defined
    COLORDER CHAR(1) Order of value in the column:
  • A = means ascending
  • D = mean descending
  • I = means include – ordering ignored
  • (* 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 5. Columns Available in SYSCAT.COLUSE Catalog View

    SYSCAT.TRIGGERS
    The SYSCAT.TRIGGERS catalog view is used to store information about the triggers defined in a database. If you know the trigger name, you can query this catalog view to find information about that trigger.

    Table 6 lists some of the columns available in this catalog view.

    Column Name Data Type Description
    TRIGSCHEMA VARCHAR(128) Name of the schema on which the trigger is defined
    TRIGNAME VARCHAR(18) Trigger name
    DEFINER VARCHAR(128) User who created the index
    TABSCHEMA VARCHAR(128) Stores the schema name of the table for which the trigger is defined
    TABNAME VARCHAR(128) Name of table for which the trigger is defined
    TRIGTIME CHAR(1)
  • A = means after trigger
  • B = means before trigger
  • I = means instead of trigger
  • TRIGEVENET CHAR(1) Event for which the trigger is defined:
  • I = means INSERT
  • D = means DELETE
  • U = means UPDATE
  • GRANULARITY CHAR(1) Determines whether the trigger is executed per statement or per row:
  • S = means once per statement
  • R = means once per row
  • TEXT CLOB(64K) Full text of the trigger statement
    (* 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 6. Columns Available in SYSCAT.TRIGGERS Catalog View

    Apart from the above list, other catalog views that carry useful information for database developers include the following:

    • SYSCAT.CHECKS – contains information about the check constraints in a table
    • SYSCAT.COLAUTH – contains information about users' access rights for a particular column
    • SYSCAT.DATATYPE – contains information about the built-in and user-defined data types available in the database
    • SYSCAT.DBAUTH – user authorization information for a particular database instance
    • SYSCAT.INDEXAUTH – privileges for users on a particular index



    Comment and Contribute

     

     

     

     

     


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

     

     

    Sitemap