Rational Data Architect and DB2 9: The Database Explorer

Tuesday Apr 17th 2007 by Paul Zikopoulos

Learn how to add and work with database connection objects in the Rational Data Architect Database Explorer view. This series takes you through the various interfaces of the Rational DA IDE as they relate to DB2 9 data servers and the tasks that you can accomplish with this tool.

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.

Adding database connections to the Database Explorer view

The Database Explorer view allows you to explore schemas within your databases. The Database Explorer’s database connections aren’t limited to just the DB2 family data servers; in fact, Rational AD can work with almost any database that you can access with a JDBC driver. For example, you can work with SQL Server, Oracle, Apache Derby, Sybase, and IBM Cloudscape.

To add a database connection to the Database Explorer view in Rational DA, perform the following steps:

1.  Right-click Connections in the Database Explorer view and select New Connection:

2.  In the New Connection wizard, enter the required information for the DB2 data server connection you want to make.

In the previous figure, you can see that I cleared the Use default naming convention check box. When you select this option, Rational DA will automatically generate sequence names for your database connections and you will have no control over them. For example, below you can see that Rational DA automatically generated the name SAMPLE1 for this database connection because a SAMPLE database connection name already exists (although it’s for a database that resides in an Apache Derby data server):

Use the Select a database manager list to select the target data server where the database you want to connect to resides. The fields you are required to fill out for the connection change according to the data server type you select. For example, if you wanted to connect to a SQL Server database, it would look like this:

Note that the Select a database manager list doesn’t have an entry for DB2 9 connections. Depending on the version or maintenance level of Rational DA you are using, this might be the case for your environment too. You can connect to a DB2 9 data server using either the V8.1 or V8.2 profile. For this example, select V8.2.

The JDBC driver list defaults to a DB2 data server connection when you select DB2 as a target data server. (It can be any member of the DB2 family, so long as you are licensed to connect to it.) If you want to connect to a DB2 data server using a different driver (for example, a third-party driver), you can select the Other option. If you select this option, you need to explicitly define the connection URL, class location, and JDBC driver class. (See how this window changes when you select this option.)

When you are connecting to a DB2 data server, most of the fields in the New Connection window are already filled out for you. When connecting to a database, ensure that you enter the database name (in this case, SAMPLE) in the Database field, as well as the port number this instance listens on for incoming data requests (the default is 50000). If you are connecting to a remote DB2 data server, you should specify the host name of that data server in the Host field. In this example, since I’m making a local connection, I entered localhost.

You can see some of the advanced integration of Rational DA when connecting to DB2 data servers in the various figures in this step. Since Rational DA knows the target data server is DB2 9, it’s able to generate information for the JDBC driver class, and it knows the location of the JDBC driver automatically. From here, Rational DA builds the connection URL for you. This is all done without your intervention – something you have to do manually with other data server connections.

(When you’ve finished entering all of the required information, you can click Finish to add the database connection to the Database Explorer view, but don’t do that yet because I want to show you some other options you have in this wizard when building a database connection.)

3.  Before adding the database connection, or specifying some advanced options covered in the remaining steps, you should test the database connection. Enter a valid user ID and password in the User Information box and click Test Connection. If it is successful, click OK.

The reason I had you test the connection first is that if you click Finish and the connection doesn’t work, you can more easily change some of the configuration settings because you still have the New Connection window open. (It’s faster to find out if the database connection you’ve set up isn’t going to work by testing the connection while you are adding it as opposed to finding out later.) You can also specify some advanced information regarding this database connection, such as the ability to filter the schema and more. For this article, don’t add the database connection just yet and complete the remaining steps.

4.  Clear the Disable filter check box and specify a filter such that only the schema you used to create the SAMPLE database is returned to the Database Explorer view. For example, you may set up a filter that looks like this:

By default, you have a lot of control over the filter. In the previous example, I specified that this database connection should only return objects that belong to the HR and PAULZ schemas. You could use the Selection list to exclude schemas as well.

This is a very important capability, especially if you’re working with mature database schemas or third-party applications such as SAP. Imagine if you had to enumerate all the objects for a database with 100 schemas and 20,000 tables!

You can build your own expression for schema filtering by selecting Expression and one of the options in the Name list, along with a corresponding predicate. For example, to display only schemas that begin with SYS, you would select Expression, the Starts with the characters option, and enter SYS* in the adjacent field:

5.  Click Finish. The database connection is added to the Database Explorer view.

You can see in the previous figure that whenever you add a new database connection to the Database Explorer view, Rational DA automatically connects to it, as shown by the green icon () beside the DB2SAMPLE database that was just added.

For the connection just added (see below), note the [Filtered] keyword beside the Schema folder.

If you want to change any of the attributes you specified for a database connection, highlight the connection, right-click, and select Edit Connection. This will open the same New Connection window that you used to add the database connection in the first place. Simply change any attributes, click Finish, and retest the connection.

Working with database connections from the Database Explorer view

Before you can perform any actions on a database, you need to connect to it. A database connection in this view will have a green icon () beside it. When you don’t have a connection to a database, its corresponding icon will be grey ().

Working with disconnected databases

When you don’t have a connection to a database, you can still perform a number of functions on the database connection object (though not on the database itself). Highlight the connection object, right-click, and select one of the following options:

  • Delete – Removes a database connection from the Database Explorer view. (This action doesn’t drop the database, just the connection from Rational DA.)
  • New SQL Statement - Creates a new SQL statement to run against the target database. This action will automatically attempt a connection to the selected database and start the New SQL Statement wizard (covered later in this series).
  • Refresh – Updates the view in the Database Explorer after, for example, a database was dropped or you changed the filter settings for a database connection.
  • Edit Connection - Opens the New Connection wizard where you can change the configuration settings of an existing database connection.
  • Work Offline - Enables you to work with the database schema offline. This gives you the ability to get assistance for generating SQL statements, visualizing schemas, and more, without having to have a live connection to the database. In order to use this option, you have to enable the database connection for offline work during a connected database session (detailed later in this article).
  • Reconnect – Attempts to reconnect to a database. On each attempt, you will be prompted for a valid user ID and password:

Working with connected databases

When you have a connection to a database, you can perform some additional functions on the database connection object, as well as work with objects within the database itself. (Unlike disconnected connections, you can expand a connected connection object to show the actual database and its contents.)

Instead of the Reconnect option, of course, you have the Disconnect option when working with a live database connection. You can also select the Save Offline option to enable the Work Offline option described in the “Working with disconnected databases” section. This option instructs Rational DA to save a copy of the database schema for use when a database connection is not present. The process to create an offline copy of the database schema can take a long time depending on the size of your database. To have this work performed in the background, click Run in Background.

After you have created an offline work environment, the next time you select the Work Offline option for a disconnected database connection object, you can use the objects within it from the cache, as shown below:

You can see in the previous figure that even though the SAMPLE database doesn’t have a live connection, this object has a sub-tree that contains the actual database and enabled folders so that you can perform actions on its objects. Note that this only provides you access to the schema for design purposes. For example, you couldn’t run an SQL statement from a disconnected database in offline mode, but you could create one.

Wrapping it all up

In this article, I showed you how to add and work with database connection objects in the Rational Data Architect Database Explorer view. In the next two articles, I will focus on solving real business problems with Rational DA, and show you more functions that you can perform. In the next article, I take you on a tour of all operations you can perform against the database objects of a connected database.

» 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 magazine articles and currently working on his tenth book. Paul has co-authoed the books DB2 9: New Features, Information on Demand: Introduction DB2 9 New Features, Off to the Races with Apache Derby, 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, ClearCase, Cloudscape, DB2, Rational, and WebSphere are trademarks or registered trademarks of International Business Machines Corporation in the United States, other countries, or both.

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

UNIX is a registered trademark of The Open Group in the United States and other countries.

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

Other company, product, or 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