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, Ive 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 worlds most popular integrated development environments (IDEs) such as IBM Rational Application Developer and Microsoft Visual Studio .NET. One area Ive 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 youve 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 youve read my articles about Rational Application Developer, youll find yourself off to a good start, but its not a prerequisite for this series.
In my coverage of Rational DA, I assume that youre working within the Data perspective unless I explicitly name a different perspective. The Data perspective is shown below:
If you dont 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 dont, you can create it by entering the db2sampl -xml command from your operating systems command prompt.
If you dont 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 cant 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 Ive 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 Explorers database connections arent 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 its 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 doesnt 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 Im 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, its 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 youve finished entering all of the required information, you can click Finish to add the database connection to the Database Explorer view, but dont 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 doesnt work, you can more easily change some of the configuration settings because you still have the New Connection window open. (Its faster to find out if the database connection youve set up isnt 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, dont 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 youre 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 dont have a connection to a database, its corresponding
icon will be grey ().
Working with disconnected databases
When you dont 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 doesnt drop the database, just the connection from Rational
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
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
You can see in the previous figure that even though the SAMPLE database doesnt
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 couldnt 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:email@example.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.
product, or service names may be trademarks or service marks of others.
International Business Machines Corporation, 2007. All rights reserved.
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.