Rational Data Architect and DB2 9: The Database Explorer

April 17, 2007

In a previous series of articles, I’ve written about the tight integration of the IBM DB2 9 for Linux, UNIX, and Windows data server and its impressive integration with some of the world’s most popular integrated development environments (IDEs) such as IBM Rational Application Developer and Microsoft Visual Studio .NET. One area I’ve not covered is integration with an IDE for data architects--the IBM Rational Data Architect (Rational DA) product.

Rational DA is an enterprise data modeling and integration design tool that can simplify the role of data architects who work with diverse and distributed data assets. For example, using Rational DA, you can create logical and physical data models. You can discover, explore, and visualize the structure of data sources. You can discover potential relationships and identify relationships (through mapping) between disparate data sources. You can compare and synchronize the structure of two data sources and targets. You can analyze models and data sources for conformance to enterprise standards, generate SQL/XML statements, and lots more.

If you looked at all the capabilities provided by Rational DA, you could logically group them into the following categories:

• Database Development – Enables DB2 architects to generate, edit, import, test, debug, deploy, compare, export, and batch deploy SQL code (including stored procedures and user defined functions). In addition, you can model, build, test, and deploy DB2 databases all from a single interface.

• Model and Database Compliance Analysis – Enables database administrators (DBAs) to run checks and balance controls against their database models or actual databases to ensure schema conformance to enterprise standards. Not only can Rational DA be used to analyze the schema, but it can also advise and enforce enterprise standards.

• Data Modeling – Enables architects and designers to easily create logical, physical, and domain models. Physical data models can be created from scratch, from logical models using transformations, or from a database using reverse engineering.

• Mapping - Helps uncover and define relationships across data sources and targets that are critical. The unique mapping capabilities provided in Rational DA include the ability to perform discovery mapping, which generates possible relationships and visualization diagrams to aid in the understanding of these relationships.

• Information Integration Design - Simplifies the design, creation, and deployment of federated databases. Rational DA enables IBM WebSphere Federation Server, IBM Information Server, DB2 9 (so long as you’ve purchased the appropriate feature pack), and DB2 Connect servers to easily access, visualize, generate, and deploy federated databases.

• Comparison and Synchronization – Helps architects to compare models to models, models to databases, and databases to databases with transparent synchronization capabilities. For example, a DBA can generate a schema model from a deployed database, synchronize it with an enhanced model from development, and then forward engineer the schema into production.

• Impact Analysis – Used to help identify the impact of a change before the change is actually implemented. Impact analysis can visually list and report on the dependencies of specific elements that make it easier to see the impact of a change.

• Life Cycle Integration – Provides full support for team development environments. Rational DA enhances team collaboration with seamless versioning, branching, and synchronization of changes using source control programs such as Rational ClearCase, CVS, and more.

In this series, I want to take you through the various interfaces of the Rational DA IDE as they relate to DB2 9 data servers and tasks that you can accomplish with this wonderful tool, such as the generation of a database glossary for conformance purposes.

Because Rational DA is based on the Eclipse 3.2 framework, if you’ve read my articles about Rational Application Developer, you’ll find yourself off to a good start, but it’s not a prerequisite for this series.

Getting started...

In my coverage of Rational DA, I assume that you’re working within the Data perspective unless I explicitly name a different perspective. The Data perspective is shown below:

If you don’t see this perspective in the top-right corner of the IDE, click Open Perspective () --> Other, and select the Data perspective (you may need to select Show All to see this perspective):

This series also assumes that you have the SAMPLE database created on your data server with the XML extensions. If you don’t, you can create it by entering the db2sampl -xml command from your operating system’s command prompt.

If you don’t have a copy of DB2 9, you can download a free trial copy of DB2 Enterprise 9 or the free to use, build, test, and deploy version, DB2 Express-C 9. (A free trial copy has an expiration date on the code and can’t be used for production, whereas DB2 Express-C 9 you can actually use indefintately and for production purposes.)

You can see in the following figure that I’ve already added a number of data server connections to my Database Explorer view in Rational DA:

You can also see that I have a current connection to a SAMPLE database located in an Apache Derby 10.0 data server (noted by the green icon); however, this is not the SAMPLE database in DB2 9 created by the prerequisite db2sampl -xml command for this article.