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

This article describes a new filtering capability made available in IBM® Data Studio. By filtering directly from the underlying database catalog, this function allows Data Studio to provide an overall improved experience with less information to process and manage, more relevant information, and better performance.

Loic Julien (lj@us.ibm.com), Software Architect, IBM

Loic Julien photoLoic Julien is a senior software engineer at IBM and is currently working on the architecture and the development of all core components within IBM Data Studio. Loic has historically contributed to the development of Infosphere Data Architect, Rational XDE and Rational Rose. Loic has an M.S. from San Jose State University in client/server computing.



Salvador Ledezma (ledezma@us.ibm.com), Software Engineer, IBM

Salvador LedezmaSalvador Ledezma has been working at IBM since 2002 at the Silicon Valley Lab, in San Jose, CA, initially developing Java-based workloads and applications for DB2 z/OS. Salvador currently spends his time working on runtime and tooling technologies for IBM's data server products using the Eclipse platform.



Hardik Patel (hardikpa@us.ibm.com), Software Engineer, IBM

Hardik Patel photoHardik Patel is a Software Engineer at the IBM lab in Lenexa, Kansas. He has worked on the Optim Development Studio and the IBM Migration Toolkit (MTK) teams. Currently, he is responsible for the SQL and XQuery editor component of the Data Studio products.



Gary Gong (ggong@us.ibm.com), Software Engineer, IBM

Gary Gong photoGary Gong is a software engineer from the Silicon Valley Lab in San Jose. He is currently working on contributions to Eclipse-based data tools components that are used in Data Studio. Previously, he was part of the DB2 for z/OS query processing team, building enhancements into the query optimizer.



29 September 2011

Introduction

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.

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

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

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.

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

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 .

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.
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.

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.
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.

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.
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.

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
  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'.
  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'.
  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.
    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.
  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.
  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.

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.

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.

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.

Resources

Learn

Get products and technologies

  • Download IBM Data Studio for free.
  • Evaluate IBM products in the way that suits you best: Download a product trial, try a product online, use a product in a cloud environment, or spend a few hours in the SOA Sandbox learning how to implement Service Oriented Architecture efficiently.
  • Build your next development project with IBM trial software, available for download directly from developerWorks.

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


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