Rational Data Architect and DB2 9: The Database Explorer - Page 2
April 17, 2007
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.