Rational Data Architect and DB2 9: The Rest of the Database Explorer View

Tuesday Jun 12th 2007 by Paul Zikopoulos

Paul Zikopoulos concludes a discussion of the features in the Database Explorer view.

In my latest articles, I’ve been writing about the IBM Rational Data Architect (Rational DA) integrated development environment (IDE) that’s specifically designed for those involved in data server design and schema evolution. So far, I’ve shown you how to add a database connection, which becomes the basis from which all of your work is directed. In addition, I've detailed how to build SQL statements using the SQL builder and the SQL editor, and described a bunch of really cool features that minimize design time errors and lead to faster and more robust deployments. I’ve also explained how to visualize your database storage using a storage diagram. In this article, I detail the rest of the features in the Database Explorer view.

To follow along with this present article, you should have a connection to the SAMPLE database (created by entering the db2sample –xml command from your operating system’s command line processor) and have a live connection to it from the Database Explorer view.

Database Explorer view actions from a database object

You can perform a number of actions from a database object in the Database Explorer view. You can see these options by right-clicking your database connection:

The actions that you can perform from a selected database are:

  • Copy – Copies a database connection’s objects into the Data Model folder of a Data Design project located within the Database Project Explorer view. (You will learn more about Rational DA projects in subsequent articles in this series.)

    In the previous figure, you can see that the SAMPLE database connection was copied and pasted into the PZDataDesignProject and can be viewed within the Data Model folder.

  • New SQL Statement – As detailed in a previous article in this series, invokes the SQL editor or SQL builder so you can create an SQL statement.

  • Generate DDL – Generates the data definition language (DDL) required to recreate the selected object. When you select this option from a database, you can choose from among several objects and supporting business rules available to include in the generated DDL.

    In the following example, you can see that I’ve instructed Rational DA to generate the DDL with fully qualified names for the database and its existing tables. I’ve specifically left out other objects in the schema, and business rules such as referential integrity constraints and primary key constraints:

    Rational DA gives you the option to run the generated DDL immediately or place the entire DDL into a designated project as a script file:

    In the previous figure, you can see that I selected a project in which to place the generated DDL script file using Browse and a corresponding Rational DA project. (I used the project I showed you how to create in a previous article.) When you have specified a project and its path, click Next, verify the scripting actions you requested in the Summary window, and click Finish.

    All the generated DDL is placed in the selected project’s SQL Scripts folder as a script file called script1.sql:

  • Visualize Topology Diagram – Creates a read-only visualization diagram of the peer relationships among objects in your data server (for example, schemas and tables). You can drill down into these objects by clicking to expand each object tree:

    You can see in the previous figure that the generated topology diagram includes federated tables as well as regular tables. For example, the Northwind1 object is actually a federated nickname that represents a table that resides in a Microsoft Access database, where two other tables, called ORDERS and EMPLOYEES, reside.

    A dashed line in this diagram shows peer relationships between source and target objects. A peer relationship line can show connections between nicknames (as is the case above), tables, views, materialized query tables (MQTs), and table aliases.

    If you right-click an object in a topology diagram, you see a pop-up menu with the options that you can perform on that object. For example, you can hide a table or schema from the topology diagram or quickly navigate to its location in the Database Explorer view.

    Note that if the topology diagram is very large, you can navigate it using the Outline view, as discussed in my previous article on storage diagrams. In addition, note the filtering icon () in the top-right corner of the database name in this topology diagram. This icon indicates that the database has a filter applied to it. (In a previous article in this series, I showed you how to apply schema filters to database connections in the Data Explorer view.)

    You can use the Preferences button (shown at the top of the diagram in the previous figure) to configure the way the topology diagram looks in Rational DA.

    When you click this button, the following window opens:

    Use the Peer relationships controls to show or hide relationships between objects in the database. For example, Always show peer relationships (selected by default) shows the relationship lines between related objects (as shown in the topology diagram used in this article). Hide peer relationships until the object is selected hides the dashed lines until you select the object.

    Use the Icon text style controls to set how names appear in the topology diagram. Truncate long text, selected by default, shortens long object names, as shown in the sample topology diagram. Wrap long text shows the full names of these objects:

    The rest of the fields in this window can be used to filter the schema (if it isn’t already filtered) and the tables in the diagram.

  • Refresh – Updates the objects shown in a view. For example, when you create a new table in the database, this option refreshes the objects in the Database Explorer view so that the new table appears.

    The Refresh option exists on every folder’s pop-up menu in the Database Explorer view. If you select it from the pop-up menu of a database object, it refreshes all the database’s folders. In contrast, if you select this option from a single folder, only the objects in that folder are refreshed.

  • Compare With – Shows the differences between two objects of the same type from within a database. You select an object first and then this option. However, you cannot compare databases. (Rational DA gives you an error if you select this option when a database is selected.)

    For example, to compare the EMPLOYEEDJ and EMPLOYEE tables within the SAMPLE database, perform the following steps:

    1.  Expand the SAMPLE database and locate the EMPLOYEEDJ table within the Tables folder, right-click, and select Compare With->Another Data Object:

    2.  Rational DA automatically displays a list of the same type of objects within the database. For this example, since I selected a table for the base comparison, Rational DA opens a window that lets me select another table within the database to compare it to, as shown below:

    Notice in the previous figure that the table EMPLOYEEDJ, which serves as the basis for this comparison, isn’t available for selection.

    3.  Click OK. The Compare editor opens:

    Using this editor, you can browse through all the differences and copy highlighted differences between the compared artifacts. For example, in the previous comparison, you can see that a primary key exists on the EMPLOYEE table but not on the EMPLOYEEDJ table. To make a primary key on the EMPLOYEEDJ table, select the EMPLOYEE table’s primary key, and click Copy from Right to Left ():

    Of course, you can copy changes in either direction. If you wanted to copy a change from the EMPLOYEEDJ table to the EMPLOYEE table, you would select Copy from Left to Right ().

    It’s important to note that none of these changes affects the actual objects – in other words you are merely working in a designer. To make the changes permanent, you need to generate the corresponding DDL. The good news is that Rational DA makes this very simple. When you have made your changes, you can generate the corresponding DDL in the same way that you used to copy the changes – only this time select the appropriate Generate Left Delta DDL () or Generate Right Delta DDL () buttons, depending on what you want to script. When you select either of these options, Rational DA opens a window that’s very similar to the Generate DDL option detailed earlier in this article.

    In this example, the Generate Left Delta DDL button generates the DDL for the changes to the left side of the comparison, as shown below:

  • Analyze Model – Analyzes a data model to ensure that it meets certain specifications laid out by rules you’ve set up for your projects. Model analysis helps to ensure model integrity and helps to improve model quality by providing design suggestions and best practices.

    From the Database Explorer view, you can analyze a database or schema from a database connection. This option is also available from the Data Project Explorer view on a selected project; there you can analyze a package in a logical data model in addition to a database or schema in a database connection.

    An example of the window that opens when you select this option is shown below:

    It’s outside the scope of this article to go into more details about this feature; however, in a future article, I’ll show you how to use this feature to analyze a glossary and business rules with respect to table names.

  • Analyze Impact - Helps you find dependencies and analyze the impact of changes to your database. For example, if you want to copy a schema from the Database Explorer to the Data Project Explorer, you can find dependencies on that schema to ensure that all references are resolved. In addition, if you want to modify an object, you can view the list of objects that might be impacted by such a change. This option is available from the Data Project Explorer and the Database Explorer views. The Impact Analysis window looks like this:

    You use the Impact Analysis window to specify impact analysis direction and expansion options. Dependent objects finds objects that the selected object depends on. Impacted objects finds objects that the selected object impacts. Both perform the impact analysis with both dependent and impacted objects. This window also allows you to include recursive objects and those within the containment hierarchy in the analysis.

    The result of an impact analysis is shown in the Model Report tab and in the designer canvas. Here is an example:


    You can perform an impact analysis from the Compare window associated with the Compare With option. You can invoke this feature using the Analyze Left Impact () or Analyze Right Impact () buttons.

Wrapping it all up

In this article, I took you on a whirlwind tour of the rest of the features available in the Database Explorer view. As you can see, Rational DA is a very feature-rich and extensive product. You can explore for yourself the features I’ve presented in this series by selecting various options and playing around in the tool.

In subsequent articles, I will move on to more business-related solutions that are possible with a tool such as Rational DA. In the next article, I’ll show you how you can enforce naming standards in your database based on business rules that you define.

» See All Articles by Columnist Paul C. Zikopoulos

About the Author

Paul C. Zikopoulos, BA, MBA, is an award-winning writer and speaker with the IBM Database Competitive Technology team. He has more than ten years of experience with DB2 and has written more than one hundred articles and several books about it. Paul has co-authored the books: Information on DB2 9 New Features, Demand: Introduction DB2 9 New Features, DB2 Version 8: The Official Guide, DB2: The Complete Reference, DB2 Fundamentals Certification for Dummies, DB2 for Dummies, and A DBA's Guide to Databases on Linux. Paul is a DB2 Certified Advanced Technical Expert (DRDA and Cluster/EEE) and a DB2 Certified Solutions Expert (Business Intelligence and Database Administration). In his spare time, he enjoys all sorts of sporting activities, running with his dog Chachi, and trying to figure out the world according to Chloë – his new daughter. You can reach him at: mailto:paulz_ibm@msn.com.


IBM, DB2, and Rational are registered trademarks of International Business Machines Corporation in the United States, other countries, or both.

Microsoft is a trademark of Microsoft Corporation in the United States, other countries, or both.

Linux is a registered trademark of Linus Torvalds in the United States, other countries, or both.

Other company, product, and service names may be trademarks or service marks of others.

Copyright International Business Machines Corporation, 2007. All rights reserved.


The opinions, solutions, and advice in this article are from the author’s experiences and are not intended to represent official communication from IBM or an endorsement of any products listed within. Neither the author nor IBM is liable for any of the contents in this article. The accuracy of the information in this article is based on the author’s knowledge at the time of writing.

Mobile Site | Full Site