Contents


Use database catalog filtering in IBM Data Studio to view, manage, and work with database objects efficiently

Comments

Once connected to a database, IBM Data Studio begins retrieving catalog metadata and caches it in memory. Proactively fetching metadata allows for optimized access by many features that consume metadata. These features, such as object management, generation of DDL, and explorer display or navigation (just to name a few), are at the heart of IBM Data Studio, and their wait-time per action is the cornerstone of a positive experience.

Along with this, an internal caching architecture efficiently manages a consistent set of sharable data objects while keeping an optimized footprint on database load and network bandwidth load.

However, as in many systems, an unfiltered amount of production data can be overwhelming, not only for the user, but also the tool. In these cases, retrieving only the metadata of interest is critical and can be accomplished from within the Administration Explorer by setting a new database catalog filter.

IBM Data Studio database catalog filter

Setting a database catalog filter consists of two parts:

  • A set of conditions to satisfy (WHERE predicates): These conditions are used to reduce the number of objects to load.
  • An optional set of dependent objects to include: These objects provide additional consistency on internal dependencies. For instance, when defining a table filter, you may also be interested in the related indexes. The related indexes will then complete the indexes filter that may be in place already.

This filtering mechanism allows you to limit the number of objects that are returned from the database catalog, while still maintaining a consistent set. As a result, the enablement of a catalog filter has a direct impact on tooling and network performance. With fewer objects to retrieve, manage, and display, the Administration Explorer offers a faster and simpler user experience.

Setting a database catalog filter

When successfully connected to a database, the Administration Explorer displays a set of pre-defined folder types. In order to enable the catalog filter, you can right click on any of those folders and select Database Catalog Filter (shown in Figure 1).

Figure 1. Database Catalog Filter
A screen capture of Database Catalog Filter menu option on a SQL Object Folder in                       Administration Explorer.
A screen capture of Database Catalog Filter menu option on a SQL Object Folder in Administration Explorer.

The Administration Explorer provides filtering support for DB2® for Linux®, UNIX®, and Windows®, and for DB2 for z/OS® connections. Table 1 depicts a more in-depth description of the folders currently available for filtering.

Table 1. Supported databases and SQL object folders
DB2 for Linux, UNIX, and WindowsDB2 for z/OS
TablesTables
ViewsViews
AliasesAliases
MQTMQT
PackagesPackages
SequencesSequences
Stored ProceduresStored Procedures
User Defined FunctionsUser Defined Functions
User Defined TypesUser Defined Types
Synonyms
Auxiliary Tables

After you select the context menu, a dialog is displayed. In this dialog, you define the filter by specifying the filtering conditions along with the types of dependent objects to include (shown in Figure 2). Note that the dependent objects section is enabled only when a condition has been specified.

Figure 2. Database catalog filter dialog
Screen capture of a Database Catalog Filter Dialog with field                      for specifying the predicate and pane for selecting dependent objects
Screen capture of a Database Catalog Filter Dialog with field for specifying the predicate and pane for selecting dependent objects

Conditions

Specify a predicate and value for each condition. You can include or exclude data objects based on known naming patterns. Values specified are case-sensitive.

Figure 3. Conditions
Screen capture of Conditions section in the Database Catalog                      Filter dialog
Screen capture of Conditions section in the Database Catalog Filter dialog

Dependent objects

Specify the types of dependent objects to return from the database catalog. These objects are returned from the database catalog if they are dependent on an object that meets one of the specified filtering conditions.

Figure 4. Dependent objects
A screen capture of Dependent objects section Database Catalog Filter Dialog.
A screen capture of Dependent objects section Database Catalog Filter Dialog.

Once activated, a filter is associated with a few visual cues that help remind you that a filter is in place:

  • [FILTERED] is displayed in front of the name of the database where one or several filters have been specified.
  • [Filtered] is displayed after each respective folder's label, along with a modified folder icon.
Figure 5. Filtered folder
A screen capture of Filtered Folder in Administration Explorer.

To disable filtering, you must delete all the conditions in the database catalog filter.

Use cases associated with the filter dialog

Note: In order to better illustrate each use case and their related concepts, only tables and views have been used.

Use case 1

In our first example, a user applies a filter on "Tables" and set an arbitrary condition, such as "Starts with the characters" as "T". As expected, all the tables that start with "T" will be displayed in the Object List View associated with the Administration Explorer.

In this example, a faster response time will be a factor proportional to the number of matching objects in comparison to the complete set.

Figure 6 shows the screen where you apply the filter and specify the value.

Figure 6. Apply filter on table folder
A screen capture of Database Catalog Filter Dialog for Tables
A screen capture of Database Catalog Filter Dialog for Tables

In Figure 7, all tables that start with "T" are listed in object list view.

Figure 7. Object list view
A screen capture of filtered set of Tables which start with 'T' listed in object list view .
A screen capture of filtered set of Tables which start with 'T' listed in object list view .

Use case 2

In the second example, a user applies a filter on "Tables" and set again a couple of arbitrary conditions, such as "Starts with the characters" as "T" and "Ends with character" as "3", though, this time, the "Tables" dependencies are also selected.

The filter now includes:

  • All tables that start with "T" and end with "3"
  • Any tables dependent on the filtered tables: These extra tables will appear among their filtered peers within the Object List View.
Figure 8. Applying a catalog filter on the table folder
A screen capture of Database Catalog Filter Dialog for Tables.
A screen capture of Database Catalog Filter Dialog for Tables.
Figure 9. All tables that start with "T", end with "3" are listed in the Object List View.
A screen capture of filtered set of Tables which start with 'T' and end with '3' listed in object list view, with related dependent objects.
A screen capture of filtered set of Tables which start with 'T' and end with '3' listed in object list view, with related dependent objects.

Notice that the two extra tables "OFFICE" and "STUDENT" have now been displayed. This is consistent with the dependent objects criteria as "OFFICE" and "STUDENT" have dependencies on table "TEMP3" and "T103" respectively.

Technical details: The following query executed on DB2 for LUW retrieves table dependencies.

 SELECT REFTABSCHEMA, REFTABNAME, TABSCHEMA, TABNAME
 FROM SYSCAT.REFERENCES
GROUP BY REFTABSCHEMA, REFTABNAME, TABSCHEMA, TABNAME

Use case 3

A user applies a filter on "Tables" and sets a couple of arbitrary conditions, such as "Starts with the characters" as "T" and "Ends with character" as "3", while both "Tables" and "Views" dependencies are selected.

The filter now includes:

  • All tables that start with "T" and end with "3"
  • Any tables dependent on the filtered tables
  • Any views dependent on the filtered tables: These extra views will appear among their filtered peers within the Object List View. Note that for consistency, if you create a filter on the "Views" folder, dependent views will continue to appear in that list.
Figure 10. Apply filter on Table folder
A screen capture of Database Catalog Filter Dialog for Tables.
A screen capture of Database Catalog Filter Dialog for Tables.
Figure 11. All the tables that start with "T", end with "3" are listed in the Object List View
A screen capture of filtered set of Tables that start with 'T' and end with '3' listed in object list view, with related dependent objects.
A screen capture of filtered set of Tables that start with 'T' and end with '3' listed in object list view, with related dependent objects.

Notice that the two extra tables "OFFICE" and "STUDENT" are now displayed. This is consistent with the dependent objects criteria, since "OFFICE" and "STUDENT" have dependencies on table "TEMP3" and "T103" respectively.

Technical details: The following query executed on DB2 for LUW retrieves table dependencies.

SELECT REFTABSCHEMA, REFTABNAME, TABSCHEMA, TABNAME
FROM SYSCAT.REFERENCES
GROUP BY REFTABSCHEMA, REFTABNAME, TABSCHEMA, TABNAME
Figure 12. Apply filter on the Views folder
A screen capture of Database Catalog Filter Dialog for Views.
A screen capture of Database Catalog Filter Dialog for Views.
Figure 13. All the views that starts with "V" are listed in the object list view. "ADVANCE" is shown in the list because this is the view that is dependent on the table "TEMP3" shown in the Tables object list view.
A screen capture of filtered set of Views which start with 'V' listed in object list view, with related dependent objects.
A screen capture of filtered set of Views which start with 'V' listed in object list view, with related dependent objects.

Technical details: The following query executed on DB2 for LUW retrieves view dependencies.

SELECT TABSCHEMA, TABNAME, BSCHEMA, BNAME
FROM SYSCAT.TABDEP WHERE BTYPE= 'T' and DTYPE = 'V'
GROUP BY TABSCHEMA, TABNAME, BSCHEMA, BNAME

Use case 4

Tracking dependencies can help you understand which packages depend on an object. For instance, in order to list the packages to rebind after dropping an object, you could consider the following setup:

  • Define a filter on indexes (Indexes folder) while selecting "Packages" as dependencies.
  • Define a filter on packages (Packages folder) to exclude all packages.

Following a concept explained earlier where dependencies will always be displayed on top of a filtered folder, the Packages folder will now only list the packages that depend on the filtered indexes.

Tracking dependencies

Tracking dependent objects is essential when working with various components in the tool.

Impact analysis

When using impact analysis to display object dependencies, it is important to use the new database catalog filter to keep dependent data objects loaded in the tool. If you do not select any dependent objects when creating a filter, related objects excluded by the filter will not appear in the impact analysis graph.

For instance, consider the following example.

  1. The following view definition for VACT includes a table ACT.
    CREATE VIEW VACT (ACTNO) AS SELECT ACTNO FROM DSN8A10.ACT
  2. Define a view filter that includes VACT, and without checking the "Table" dependency option. VACT appears in the Object List View.
    Figure 14. VCAT displayed inside the Object List View
    A screen capture of filtered set of View with name 'VACT' listed in object list view
    A screen capture of filtered set of View with name 'VACT' listed in object list view
  3. Define a table filter that excludes ACT. Consequently, ACT does not appear in the Object List View.
    Figure 15. Object List View does not display ACT
    A screen capture of filtered set of Tables which excludes name 'ACT'.
    A screen capture of filtered set of Tables which excludes name 'ACT'.
  4. When starting Analyze Impact for VACT, ACT will not be displayed in the resulting graph because of the filter exclusion. In fact, no dependent objects are displayed.
    Figure 16. Analyze Impact View that does not report any dependencies
    A screen capture of Analyze Impact on View name 'VACT'.
    A screen capture of Analyze Impact on View name 'VACT'.
  5. To address this problem, select the "Table" dependency option while creating the filter.
    Figure 17. Database Catalog Filter dialog for Views
    A screen capture of Database Catalog Filter Dialog for Views.
    A screen capture of Database Catalog Filter Dialog for Views.
    Figure 18. Analyze Impact for VACT
    A screen capture of Analyze Impact on View name 'VACT'.

Note: Enabling dependencies for all objects ensures a consistent view of the underlying database. However, such information may not always be necessary and in some cases could be more costly to generate.

Generating DDL

When generating DDL, it is important to control the set of data objects used to generate the statements. The example below describes generating DDL statements for a table and its related objects.

  1. Define a filter for the table, and select all dependent objects.
    Figure 19. Database Catalog Filter dialog for Tables
    A screen capture of Database Catalog Filter Dialog for Tables.
    A screen capture of Database Catalog Filter Dialog for Tables.
  2. Define excluding filters for each of the dependent objects types. An excluding filter is a filter that excludes all objects for that type.) This reduces the data objects set to only objects that are dependent on the table.
  3. Right-click on the database and select "Generate DDL".
    Figure 20. Generate DDL wizard page with object options
    A screen capture of Generate DDL wizard with Objects page.
    A screen capture of Generate DDL wizard with Objects page.
  4. The result is that DDL statements are generated only for the table and its dependent data objects.
    Figure 21. Generate DDL wizard page with generated DDL script
    A screen capture of Generate DDL wizard with Save and Run DDL page.
    A screen capture of Generate DDL wizard with Save and Run DDL page.

Setting a threshold for the Object List View

Finally, to improve performance, a threshold for the number of objects to retrieve and display in the Object List View can be assigned.

Set that threshold in Preferences, under Window > Preferences > Data Management > Administration Explorer.

Figure 22. Preference dialog page for the Administration Explorer
A screen capture of Administration Explorer preference page dialog.
A screen capture of Administration Explorer preference page dialog.

As a result, whenever the Administration Explorer is about to retrieve and display a number of objects exceeding the threshold, it automatically displays the database catalog filter dialog box. This provides another opportunity to enable a filter without loading the full list of data objects from the database. (Figure 23)

Figure 23. Database Catalog Filter dialog
A screen capture of Database Catalog Filter Dialog for Tables.
A screen capture of Database Catalog Filter Dialog for Tables.

Such a threshold, acting as a safeguard, may need to be adjusted over time based on usage patterns, optimal number of objects to display, as well as overall performance of the tool.

Conclusion

As described in this article, database catalog filters in IBM Data Studio are helpful not only for improved performance, but also for better managing the database objects or a subset of database objects that a DBA or developer uses as part of day-to-day tasks. The set of objects can be changed at any time simply by changing the type of filter applied and the type of dependent objects to include. With this new capability, IBM Data Studio improves the overall user experience in its key core features, such as generation of DDL, object management, and explorer display and navigation.


Downloadable resources


Related topics


Comments

Sign in or register to add and subscribe to comments.

static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=761158
ArticleTitle=Use database catalog filtering in IBM Data Studio to view, manage, and work with database objects efficiently
publish-date=09292011