Paul Zikopoulos concludes a discussion of the features in the Database Explorer
In my latest articles, Ive
been writing about the IBM Rational Data Architect (Rational DA) integrated
development environment (IDE) thats specifically designed for those involved
in data server design and schema evolution. So far, Ive 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. Ive
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
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 systems 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 connections 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 Ive instructed Rational DA to generate the DDL with fully qualified
names for the database and its existing tables. Ive 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 projects 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
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 isnt 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 folders 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 databases 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, isnt 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 tables 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 ().
Its 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 thats 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 youve 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:
Its outside the scope of this article to go into more details about this feature; however, in a future article, Ill 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 Ive 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, Ill 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:email@example.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 authors 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 authors knowledge at the time of writing.