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 4

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

Put the Views to Use

This section describes some common tasks that can be accomplished by using the catalog views described in the previous section. These examples will familiarize you with the uses of catalog views. You can explore catalog views further by referring to the DB2 documentation.
  1. List all the tables in a database (Excluding system-defined tables)
    In DB2, system-defined tables are created in schemas that start with 'SYS'. SYSIBM, SYSCAT, SYSSTAT, and SYSTOOLS are examples of system-defined schemas. The following SQL statement displays all the tables from schemas (other than system-defined ones):

    SELECT TABSCHEMA, TABNAME, TYPE, COLCOUNT, KEYCOLUMNS, KEYINDEXID, KEYUNIQUE FROM SYSCAT.TABLES WHERE TABSCHEMA NOT LIKE 'SYS%' AND TYPE = 'T'

    Having the type filter as 'T' will return only information about tables in the given database. You can also filter by other database objects like views, nicknames, and aliases by providing the corresponding type values.



  2. Check whether a given table exists in the database
    Use the SYSTCAT.TABLES catalog view to accomplish this. You can do a select on the catalog view and filter the result by the table name you want to check:

    SELECT TABSCHEMA, TABNAME, TYPE, COLCOUNT, KEYCOLUMNS, KEYINDEXID, KEYUNIQUE FROM SYSCAT.TABLES WHERE TABSCHEMA NOT LIKE 'SYS%' AND TYPE = 'T' AND TABNAME = 'TABLE_NAME'

    Among the selected columns, COLCOUNT gives the number of columns in the tables; KEYCOLUMNS gives the number of columns that constitute the primary key; KEYINDEXID gives the index ID of the primary index; and KEYUNIQUE gives the number of unique constraints in a table.

  3. List all the columns in a given table (Like the describe command in Oracle)
    To get all the columns in a given table, you can use the SYSCAT.COLUMNS catalog view. The following query will display all the columns in the table provided, as well as the data type of the column, the maximum length of data that can be stored in the column, the default value (if any) for the column, whether the column is in an identity column or not, and whether the column is hidden or not:

    SELECT TABSCHEMA, TABNAME, COLNO, COLNAME, TYPENAME, LENGTH, DEFAULT, IDENTITY, HIDDEN FROM SYSCAT.COLUMNS WHERE TABSCHEMA NOT LIKE 'SYS%' AND TABNAME = 'TABLE_NAME' ORDER BY COLNO

  4. List all the indexes available for a given table
    To get all indexes available in a given table, you can use the SYSCAT.INDEXES catalog view. The following query will display the index name, the user who created the index, the names of the columns included in an index, the number of columns in an index, whether an index is primary or not, whether an index is unique or duplicate values are allowed, the index type, etc.:

    SELECT INDNAME, DEFINER, TABSCHEMA, TABNAME, COLNAMES, COLCOUNT, UNIQUERULE, INDEXTYPE FROM SYSCAT.INDEXES WHERE TABSCHEMA NOT LIKE 'SYS%' AND TABNAME = 'TABLE_NAME'

    When you write queries to retrieve data from a table, use all the available indexes to improve performance. For that, you need to know all the columns in an index, the order in which they appear, and the sort order for each of the columns. To get all this information, you can use the SYSCAT.INDEXES and SYSCAT.INDEXCOLUSE catalog views as follows:

    SELECT A.INDNAME, A.TABSCHEMA, A.TABNAME, B.COLNAME, B.COLSEQ, B.COLORDER FROM SYSCAT.INDEXES AS A, SYSCAT.INDEXCOLUSE AS B WHERE A.INDSCHEMA = B.INDSCHEMA AND A.INDNAME = B.INDNAME AND A.TABSCHEMA NOT LIKE 'SYS%' AND A.TABNAME = 'TABLE_NAME' ORDER BY A.INDNAME, B.COLSEQ

  5. List all the triggers for a given table and the trigger definition statements
    Information about triggers is stored in the SYSCAT.TRIGGERS catalog view. By querying this catalog view, you can retrieve the necessary information about a trigger, including its name, granularity, validity, creation time, trigger event, trigger time, and trigger definition statement text. The following SQL shows one such example:

    SELECT TRIGNAME, TABNAME, TRIGTIME, TRIGEVENT, GRANULARITY, VALID, CREATE_TIME, TEXT FROM SYSCAT.TRIGGERS WHERE TABSCHEMA NOT LIKE 'SYS%' AND TABNAME = 'TABLE_NAME' ORDER BY TABNAME

  6. List all the views and view definition statements
    The information about views is stored in the SYSCAT.VIEWS catalog view. By querying this catalog view, you can retrieve all the information pertaining to a view. The following SQL shows how to retrieve the view name, the user who created the view, the check option for the view, the read and write properties of the view, as well as the validity and the view creation SQL:

    SELECT VIEWSCHEMA, VIEWNAME, VIEWCHECK, READONLY, VALID, TEXT FROM SYSCAT.VIEWS WHERE VIEWSCHEMA NOT LIKE 'SYS%' AND VIEWBAME = 'VIEW_NAME'

The preceding examples described how to retrieve and use metadata information about database objects such as tables, indexes, views, columns, and triggers.

Easy Database Development

Knowledge of these catalog views provides a wealth of information about database objects, their logical and physical structures, and their states, which makes database development easier and more efficient.



Shibu Kalluvila Raj is an associate consultant for US Technology Resources LLC, a global information technology and BPO service provider. He is also a graduate research student in computer science at DePaul University in Chicago, studying database systems implementation, software architecture, and agile development.
Comment and Contribute

 

 

 

 

 


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

 

 

Sitemap