Exploring the Db2 for z/OS Catalog
WRITTEN BY SOWMYA KAMESWARAN
Navigating and exploring the Db2 catalog is a very common activity for anyone who interacts with the database engine. Database administrators and application developers browse the object catalog for a multitude of reasons. Whether they want to view all objects, check the status of an object, locate statistics, browse the data, generate DDL, or any other action that involves working with catalog objects, having an intuitive and easy-to-use interface is a key component of working productively and efficiently.
Most Db2 users are familiar with the object-type-based navigation provided by IBM Data Studio and IBM Data Server Manager. After you connect to the Db2 for z/OS database, a menu of the available object types is displayed. You then select the type of object that you’re interested in and drill down to the details.
IBM Db2 Administration Foundation for z/OS, or Admin Foundation, is the strategic administration platform for Db2 for z/OS. It changes the way you interact with the catalog by making it easier to locate objects through the use of a nearly freestyle search-based navigation.
With the ability to automatically discover all the Db2 subsystems in the sysplex, Admin Foundation is able to locate and work with all types of database objects across all of your discovered subsystems.
Figure 1: Landing page for object navigation
It provides the flexibility to limit the search to certain object types and also to specific subsystems. For example, a search for tables, aliases, packages, or stored procedures that contain the string “EMP” returns results similar to those shown in the following figure:
Figure 2: Exploring objects that contain the string “EMP”
From here, you can click on the object names to display the dashboard of that object. Let’s take a look at the dashboard for the EMP table in the DSNB1210 schema.
The dashboard for a table object displays key table information organized under six different tabs. The Overview tab shows high-level information about the table object, as shown in the following figure. You can look into specifics either by clicking on the cards in the Overview tab or by clicking the respective tabs (Structure, DDL, Relationship, Status, and Data). For ease of use, the links to the Database and Tablespace in the Key information card display the respective object dashboard.
At any time, you can also use the Breadcrumb at the top of the page to navigate back to the previously browsed pages. This feature is especially useful when drilling down to review details of other dependent objects from an existing object dashboard (for example, navigating to the Database, Tablespace, or Index dashboard from the Table dashboard).
Figure 3: Table dashboard: Overview
Click the Structure tab to display details about each of the columns in the table.
Figure 4: Table dashboard: Structure
The DDL tab displays the DDL for table creation, which can be copied and executed in the integrated Admin Foundation SQL editor. This capability is very helpful in scenarios where a copy of the table needs to be created in a different schema or even on other Db2 systems.
The information presented in the Relationship tab is helpful in understanding the table hierarchy in the context of the subsystem and the related referential integrity constraints (child, parent, and sibling).
Figure 5: Table dashboard: Relationship
The Status tab displays information about any claims or locks that might be held on the table.
The Data tab displays all the table data. To reduce performance issues when dealing with tables that host large amounts of data, the data is fetched in smaller portions as you page through the grid.
Now let’s look at the dashboard for one of the stored procedures that’s displayed in Figure 2: GET_EMP_NSP. The information is organized in three tabs: Overview, Structure, and DDL.
The Overview tab, again, displays high-level information about the stored procedure.
Figure 6: Stored procedure dashboard: Overview
The Structure tab displays version and dependency information, and the DDL tab displays the CREATE DDL for the stored procedure, which can be copied and executed from the SQL editor.
Figure 7: Stored procedure dashboard: Structure (Versions)
Figure 8: Stored procedure dashboard : DDL
Now let’s look into the dashboard of a Db2 package by selecting the GET_EMP_NSP package from the explore search results shown in Figure 2.
The Overview tab provides valuable information about the Db2 package and versions.
Figure 9: Db2 Package dashboard: Overview
The Structure tab displays the versions and dependencies for the package. The dependencies are listed as links that, when clicked, open the respective object dashboards.
Figure 10: Db2 Package dashboard: Structure (Dependencies)
The Relationship tab displays the hierarchy of the package in the context of the subsystem and other dependencies.
The Bind tab enables you to BIND, REBIND, and FREE packages for an application, with a very easy to use interface. In the following figure, you can see how the Diff view makes it easy to review the changes before executing them(?). The dropdown menu lists all supported actions. Clicking Apply changes executes the command and displays the results in place.
Figure 11: Db2 Package dashboard: Bind
Finally, the SQL tab displays all the SQL statements that are bound to this package. From here, you can tune the SQL statements or you can copy them over to the SQL editor for review.
In this blog, we reviewed the catalog object exploration features and navigation flow on IBM Db2 Administration Foundation for z/OS by learning about the dashboard view for tables, stored procedures and packages and the various possible information and actions provided by each one of them.
Currently, the platform supports exploration of the following 16 key object types:
- Accelerators
- Aliases
- Databases
- Functions
- Indexes
- Packages
- Package collections
- Plans
- Sequences
- Stored procedures
- Subsystems
- Synonyms
- Tables
- Table spaces
- Triggers
- Views
For more information on Admin Foundation install and updates –