How to View the Definition of a Table in IBM DB2

Data Tutorial

When using IBM DB2, there may come a time where you wish to view the structure of your database or even of specific tables. In this brief tutorial, we’ll explore two different methods of examining your database tables in more detail to see just what the schema really looks like.

Using SYSIBM Catalogs

The first method for examining your database table structure makes use of the handy SYSIBM catalogs provided by DB2, which contain a wide array of meta information about the database and the tables therein.

Specifically, we can utilize either SYSIBM.SYSTABLES or SYSIBM.SYSCOLUMNS. For generic meta data about the table itself, you’ll likely wish to query SYSIBM.SYSTABLES, but if you’re specifically looking for the schema (column names, data types, etc), you’ll instead need to use SYSIBM.SYSCOLUMNS.

A basic query using .SYSCOLUMNS would look something like this:

SELECT
  NAME,
  COLTYPE,
  LENGTH,
  SCALE
FROM
  SYSIBM.SYSCOLUMNS
WHERE
  TBNAME = 'MyTableName';

The column names we’re selecting are fairly self-explanatory, with perhaps the exception of SCALE which defines the number of fractional digits for DECIMAL column types.

For example, if we wanted to return a query of column information about our books table, we’d just replace MyTableName with books:

SELECT
  NAME,
  COLTYPE,
  LENGTH,
  SCALE
FROM
  SYSIBM.SYSCOLUMNS
WHERE
  TBNAME = 'books';

Using the DESCRIBE Command

For a simpler query that forgoes control over the data returned in the result set, you may also use the DESCRIBE command.

DESCRIBE essentially displays the meta data contained within the assorted SYSIBM catalogs mentioned above, but only does so based on the type of DESCRIBE query you execute. You can ask DB2 to DESCRIBE information about tables, indexes, columns, and even specific SELECT statement queries, if so desired.

For our purposes, we’ll use DESCRIBE to once again retrieve the details of a specific table, which is performed with this simple query:

DESCRIBE TABLE MySchemaName.MyTableName;

Thus, if we want to retrieve information about our books table that is part of our bookstore schema, we’d simply execute the following query:

DESCRIBE TABLE bookstore.books;

The results will look something like this, with similar information to that of our manual SYSIBM.SYSCOLUMNS query:

Column             Data Type   Data Type          Column
name               schema      name               length   Scale    Nulls
------------------ ----------- ------------------ -------- -------- --------
id                 SYSIBM      SMALLINT                  5        0 No
title              SYSIBM      CHARACTER               100        0 No
primary_author     SYSIBM      CHARACTER               100        0 Yes

Viewing Table Indexes

In addition to the basic column information above, you may also wish to view the indices that are part of your table. Thankfully, there is a simple option with the DESCRIBE keyword to query this data quite easily:

DESCRIBE INDEXES FOR
  TABLE MySchemaName.MyTableName;

The result set is a detailed list of all indices connected to that table, like so:

Index           Index                Unique          Number of      Index
schema          name                 rule            columns        type
--------------  ------------------   --------------  -------------  --------------
SYSIBM          SQL070551145212450   D                          -   XML DATA - REGIONS
SYSIBM          SQL070536478525890   U                          1   XML DATA - PATH
USER1           RELIDX1              D                          1   RELATIONAL DATA
[...]

There you have it. With one or two simple queries, you can get the full details of your table schema while using IBM DB2.