Navigating catalogs

To use the catalog navigation function your database logon ID must have 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 you already have these privileges. If not, then the database administrator can grant you access by issuing the following statements. In these sample statements, replace {user ID} with the logon ID that needs read access.

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};

In the Catalog folder you can:

• Browse Db2 objects such as databases, schemas, storage groups, tables, and more.
• Open these objects in the Remote System Details view and use the view's toolbar to sort, filter, and navigate the objects.
• Display the properties of selected objects in the Properties view, and edit the Query Filter data.
• Add database objects to the Favorites folder.
• Open and edit table data in the Remote System Details view.

You can filter the database objects that show in the Db2 for z/OS database connection Catalog folder.

1. Select the object folder you want to filter.
2. Right-click and select Properties. The Query Filter properties page opens. The properties of folders directly under the Catalog folder include a section named Column Filters. In this section, you can add one or more column filters to limit which objects are displayed as children of the folder.
3. To add a column filter, click the Add column Filter option and select a column that is valid for the selected folder type.
   
4. Depending on the type of the Column filter added, a set of operators will be available such as Starts with, Does not start with, Ends with, Does not end with, Contains, Does not contain, Equal to or Not equal to. Select the one you wish to apply.
5. Depending on the type of Column filter added, you can either enter a value manually or select one from a drop-down list. Enter the value you with to apply.
6. To remove a Column filter, click the Delete next to the filter.
7. Use the Match All radio button to return only objects that meet all column filter criteria. Otherwise, use Match Any to return objects that meet at least one of the criteria. For more information about the catalog tables and their columns, see Db2 catalog tables.

   Use the Max rows field to limit the number of matches that are returned.

   Select Include implicit object to include implicitly created objects among the displayed children of the filter. When this option is enabled, the filter name includes the label Include Implicit in the Catalog list and Properties view. For more information about implicitly created database objects, see Implicitly defined table spaces.
   Tip: You can also edit the filters in the Properties view:

   1. Select a filter in the Catalog folder.
   2. In the Properties view, expand Query Filter, and then double-click the cell that contains the value you want to edit.
   3. If there is one filter for a column, you can directly edit the value as before.
   4. If there are two or more filters for a column, use the Ellipsis (…) option or press Shift+Enter to open the edit dialog and configure the filters.
   Important: Only fully completed filters will be saved when the dialog is closed. Incomplete filters will be discarded automatically.
   Note: There is no migration step required for old work spaces. Any incomplete filter values from previous releases will not be displayed or affect behavior going forward.
8. Click Apply and Close. Folders that have filters applied to them are flagged with the filter string.
   

Use the Open Statements in Editor menu action to open an SQL statement in the SQL editor. This menu action is available on the following Db2 objects in the Catalog folder:

• The Packages > package_name > Statements folder, and individual statements in the Statements folder.
• The Stored Procedures > procedure_name > DDL folder, and individual statements in the DDL folder.
• The Views > view_name > DDL folder, and individual statements in the DDL folder.

This screen capture shows the menu action on the Statements folder:

To open SQL statements from the Catalog folder:

1. Select one of the objects that supports the menu action.
2. Right-click and select Open Statements in Editor. The SQL editor opens with the content of the SQL statements. You can use all of the editor's functions on the content. For more information about the SQL editor, see Creating and running SQL scripts.

Follow these steps to Run or Debug a routine from the Catalog:

1. Select a stored procedure object under the Catalog folder.
2. Right-click and select Call Routine or Debug Routine.