Navigating the Db2 catalog

As you’re writing code to interact with Db2 for z/OS data, you often need to explore the various objects that are associated with the databases that contain that data. Db2 Developer Extension makes it easy to explore the Db2 catalog through its catalog navigation support.

Enabling catalog navigation

Using the catalog navigation feature requires read access to several Db2 system catalog tables. If you’re already working with another tool that provides you with access to the Db2 catalog, it’s likely that these privileges have already been granted. If not, the following statements can be used to grant these privileges:

GRANT SELECT ON SYSIBM.SYSCHECKDEP TO {user ID};
GRANT SELECT ON SYSIBM.SYSCOLUMNS TO {user ID};
GRANT SELECT ON SYSIBM.SYSCONTROLS TO {user ID};
GRANT SELECT ON SYSIBM.SYSDATABASE TO {user ID};
GRANT SELECT ON SYSIBM.SYSDATATYPES TO {user ID};
GRANT SELECT ON SYSIBM.SYSDEPENDENCIES TO {user ID};
GRANT SELECT ON SYSIBM.SYSFOREIGNKEYS TO {user ID};
GRANT SELECT ON SYSIBM.SYSINDEXES TO {user ID};
GRANT SELECT ON SYSIBM.SYSJAROBJECTS TO {user ID};
GRANT SELECT ON SYSIBM.SYSKEYCOLUSE TO {user ID};
GRANT SELECT ON SYSIBM.SYSKEYS TO {user ID};
GRANT SELECT ON SYSIBM.SYSPACKAGE TO {user ID};
GRANT SELECT ON SYSIBM.SYSPACKDEP TO {user ID};
GRANT SELECT ON SYSIBM.SYSPACKLIST TO {user ID};
GRANT SELECT ON SYSIBM.SYSPACKSTMT TO {user ID};
GRANT SELECT ON SYSIBM.SYSPARMS TO {user ID};
GRANT SELECT ON SYSIBM.SYSPLAN TO {user ID};
GRANT SELECT ON SYSIBM.SYSROUTINES TO {user ID};
GRANT SELECT ON SYSIBM.SYSSEQUENCES TO {user ID};
GRANT SELECT ON SYSIBM.SYSSEQUENCESDEP TO {user ID};
GRANT SELECT ON SYSIBM.SYSSTOGROUP TO {user ID};
GRANT SELECT ON SYSIBM.SYSTABCONST TO {user ID};
GRANT SELECT ON SYSIBM.SYSTABLEPART TO {user ID};
GRANT SELECT ON SYSIBM.SYSTABLES TO {user ID};
GRANT SELECT ON SYSIBM.SYSTABLESPACE TO {user ID};
GRANT SELECT ON SYSIBM.SYSTRIGGERS TO {user ID};
GRANT SELECT ON SYSIBM.SYSVARIABLES TO {user ID};
GRANT SELECT ON SYSIBM.SYSVIEWDEP TO {user ID};
GRANT SELECT ON SYSIBM.SYSVIEWS TO {user ID};
GRANT SELECT ON SYSIBM.SYSVOLUMES TO {user ID};

where {user ID} is the authorization ID or role that you want to grant read access to.

Viewing REST services

Viewing REST services requires some additional configuration.

  1. Ensure that REST services are enabled on Db2 for z/OS. See Enabling Db2 REST services for instructions.
  2. Use either of the following methods to authorize access to REST services:

Exploring Db2 objects

After catalog navigation has been enabled by granting read access to Db2 system catalog tables, when you create a database connection, the following object types that are associated with that connection are listed under the connection name:

  • Databases
  • Global variables
  • Indexes
  • Packages
  • Plans
  • REST services
  • Schemas
  • Sequences (including sequence aliases)
  • Storage groups
  • Stored procedures
  • Tables (including table aliases)
  • Table spaces
  • Triggers
  • User-defined functions
  • User-defined types
  • Views (including view aliases)

You can expand an object to see detailed information about that object (columns, constraints, indexes, and so on).

When browsing a list of objects, you can use the the toolbar to change the rows returned and get numerical information about the objects.

Catalog navigation toolbar

  • Click the search icon (Search icon) to search within the displayed rows.

  • Click the filter icon (Filter icon) to filter the rows by one or more criteria. Specify a valid pattern expression based on the LIKE predicate, and use the exact case (the filter feature is case-sensitive). For more information, see Filtering by multiple columns.

  • For object types that allow implicitly created objects, click the view icon (View icon) to view or hide these objects.

  • Display n rows shows the number of rows fetched.

  • Click Max rows: n to change the maximum number of rows that can be fetched.

  • Click Total rows: n to get the total number of objects for the object type in the Db2 catalog.

For an individual storage group, database, and schema, you can view objects within the object in the Objects tab.

Objects tab

For an individual table, you can view a table’s columns, contraints, indexes, and data.

Table objects

Catalog object reference

The following list shows which characteristics are provided for each object type:

  • Databases
    • Properties
    • Objects
  • Global variables
    • Properties
    • References
  • Indexes
    • Properties
    • Columns
  • Packages
    • Properties
    • Statements
    • Dependencies
  • Plans
    • Properties
    • Packages
  • REST services
    • Properties
    • Request body
    • Response body
  • Schemas
    • Properties
    • Objects
  • Sequences
    • Properties
    • References
  • Storage groups
    • Properties
    • Objects
  • Stored procedures
    • Properties (differs depending on the type of procedure)
    • Options (differs depending on the type of procedure)
    • Parameters
    • Packages
    • DDL
    • JAR dependencies (applies only to external Java stored procedures)
    • Source (applies only to external Java stored procedures)
  • Tables
    • Properties
    • Columns
    • Constraints
    • Indexes
    • Data
  • Table spaces
    • Properties
    • Partitions
    • Tables
  • Triggers
    • Properties (differs depending on the type of trigger)
    • DDL
  • User-defined functions
    • Properties (differs depending on the type of user-defined function)
    • Options (differs depending on the type of user-defined function)
    • Parameters
    • Packages
    • DDL
  • User-defined types
    • Properties (differs depending on the type of user-defined type)
    • References
  • Views
    • Properties
    • Columns
    • Data
    • Dependencies
    • DDL