Paul Zikopoulos explains how to use the most common
features of the Database Explorer in the Rational Application Developer IDE, in
preparation for a discussion on Database Projects and how to create schema
objects such as tables and views, within RAD.
by Paul C. Zikopoulos, IBM Canada
In a previous series of articles, I covered the rich application development integration between the Microsoft Visual Studio .NET integrated development environment (IDE), the native IBM DB2 Universal Database (DB2 UDB) managed provider for .NET, and the .NET framework.
I have received a lot of feedback from readers who are surprised at just how productive application developers in the .NET world can be when developing to the DB2 UDB database platform. I have also received inquiries as to the level of integration between DB2 and Java technology. In this series of articles, I have decided to change the application programming interface (API) to Java (JDBC or SQLJ), and the IDE to IBM Rational Application Developer for WebSphere Software, and to take these developers through the integration that the DB2 UDB database provides them.
In Part 1, I start with the Database Explorer. Subsequent parts in this series will include:
- Data definition
- Schema wizards for object creation.
- Entity modeling
- Application deployment.
- Java routines.
- and more...
This article assumes that you are working with the Rational Application Developer IDE in the Data Perspective, as shown below:
If you don't see this perspective in the top-right corner of the IDE, click the Open Perspective button (it looks like ), then click Other, and select the Data perspective from the Select Perspective dialog box, as shown below. (You may need to select the Show all check box to see this perspective, depending on how your IDE has been configured.)
This series also assumes that you have the SAMPLE database created on your local machine. If you don't, you can create it by entering the db2sampl command from your operating system's command prompt. In addition to this, create another database called NEWSMPL (which we will use throughout this series when warranted) using the DB2 CREATE DATABASE NEWSMPL statement. If you don't have a copy of DB2 UDB, you can download a free trial copy at: http://www.ibm.com/developerworks/downloads/im/udb/ or a free licensed copy for personal development purposes at: http://www.software.ibm.com/webapp/download/search.jsp?go=y&rs=db2udbpde.
The Database Explorer
Most application developers are familiar with explorer-style environments in an IDE. Even if you are not an application developer, think Windows Explorer, and you will get the point.
The Database Explorer window in Rational Application Developer is shown below:
You can see in the previous figure that I have already added the SAMPLE database to this window.
You can perform all sorts of functions from the Database Explorer such as generating SQL to run against target databases, adding database connections, importing the database schema into the Database Definition workbench, which houses your projects, generating the Data Definition Language (DDL) for a corresponding object, visualizing the schema, and more.
by Paul C. Zikopoulos, IBM Canada
Adding database connections to the Database Explorer
The Database Explorer window allows you to explore the schema within your databases. These databases do not have to be only DB2 UDB databases either. In fact, almost any database that you can access with a JDBC driver, you can access in this window, for example, SQL Server, Oracle, Apache Derby, IBM Cloudscape, Sybase, and of course, the DB2 UDB family.
To add a database connection to the Database Explorer window, perform the following steps:
1. Right-click anywhere in the Database Explorer window and select New Connection:
2. Select the Choose a DB2 alias radio button, type NEWSMPL in the Connection name field, and click Next, as shown below:
Select Choose a DB2 alias when you want to connect to a DB2 UDB database that is cataloged on your local DB2 client. You can select a JDBC driver supported by your DB2 client; however, you do not need to know the platform and version of your DB2 alias with this option because this information is determined at connection time.
If you select Choose a database manager and JDBC driver, you have to know the database manager, platform, and version of the local or remote database you want to connect to. You can select a JDBC driver supported by your database manager and identify all required connection information for that driver. This is the option you would select if you wanted to connect to a remote DB2 UDB database for which you do not have a cataloged connection, or if you wanted to connect to a non-DB2 UDB database, as shown below:
Note that the Finish button is now enabled. Rational Application Developer has enough information about your database connection to add it to the Database Explorer. Clicking Next allows you to further customize this database connection. For example, you can work with filters, define a default schema, and more.
3. Select the database alias of the database you want to connect to from the Alias drop-down box, and click Next. The rest of the fields are automatically filled in for you since the information can be retrieved from the local catalog directory.
The following figure shows how Rational Application Developer automatically discovered the databases on my system. Since it knows these are DB2 UDB databases, it is also able to generate the JDBC driver class, and knows the location of the JDBC driver. From here, Rational Application Developer subsequently builds the connection URL for you. This is all done without your intervention. You can click Browse to select another JDBC driver if you want.
The Specify connection parameters page also gives you the opportunity to test the database connection by clicking Test Connection. If the Use your operating system user ID and password check box is selected (and the user account you used to log on to your system is authorized to access the database), you do not have to specify a user ID and password for this connection.
4. Specify the filters that you want to apply to this database connection, and click Next. (For this series, you can accept the defaults.)
There are two phases to the Rational Application Developer filtering. For example, you can filter on any system objects and only include objects that reside in your specific schema, and then filter out objects from that qualifying set. This feature is very useful in large enterprise environments, especially those running popular enterprise resource planning (ERP) applications such as SAP, which have thousands of tables. Although Rational Application Developer maintains a cache of the schema under the Database Explorer window after a successful connection is made to the database, it does not make sense to populate it with objects that you do not need or have privileges for.
Of course, you can always modify the filter or remove it by altering the properties of the database connection when it is created. Furthermore, you can enable filters and disable them by selecting the Enabled check box. This allows you to save filters and apply them when needed perhaps when you are working on different projects. Finally, you can have the result schema qualify for enumeration based on all the filters defined, or conditions within multiple filters by selecting the appropriate radio button.
5. Specify the Java home directory for building Java routines and a default SQL schema (the defaults are all fine for this series), and click Next, as shown below:
If you are connecting to a DB2 UDB for z/OS database, you can additionally specify the package and build owners for this database connection. If you are not connecting to a DB2 UDB for z/OS database, these fields are unavailable, as shown in the preceding figure.
6. A summary of the database connection is presented. Review it, and click Finish.
When you click Finish, Rational Application Developer adds the database connection to the IDE, loads the JDBC meta data, and adds the database schema. During this time, a progress bar is displayed at the bottom of the Summary window. (Obviously, the amount of time it takes to create the connection and populate the cache depends on the size of the database and the number of objects that pass through the filter.)
7. When the connection is built, the Copy to Project window automatically opens. You can use this window to copy the schema into a project. For now, just click Cancel. I will cover this in a subsequent article.
Once you have finished these steps, you will see the new database connection added to the Database Explorer window, as shown below:
Repeat the steps in this section to add the SAMPLE database, if it is not already added, so your Database Explorer looks like the preceding figure.
Working with connections in the Database Explorer
You can work with the databases in the Database Explorer to perform all sorts of tasks. In this section, we will work with the SAMPLEDATABASE connection (which links to the SAMPLE database created using the db2sampl command). If you are following along, select the database connection that you specified in the previous section to link to the SAMPLE database.
When you are not connected to a database, you cannot work with its objects. You can right-click a database connection to connect to the database. This is how the Database Explorer looks when you are not connected to the database:
To connect to a database, highlight it, right-click, and select Reconnect, as shown below:
When you are connected to a database in the Database Explorer, the window has a toggle beside that database, which you can expand to explore that database's schema:
Note that during the reconnection process, Rational Application Developer has to repopulate the cache of the schema, so, depending on the size of the database, this could take a while. (Hint: use your filters!)
You can also edit a database connection by selecting Edit Connection from the pop-up window. This allows you to work with filters by turning them off, adding new filters, or removing them, and so on. Of course, you can delete a database connection by clicking Delete.
When you are connected to a database in the Database Explorer, you can choose to refresh the cache (for example, if you have added database objects since the last connection) by clicking Refresh, or terminate the connection by clicking Disconnect, as shown below:
From a connected database tree, you can perform all sorts of actions in the Database Explorer. Some of these I will cover in future articles, but I will briefly mention the most popular ones here.
With a connected database, you can browse data that resides in tables by right-clicking a table and selecting Sample contents:
You can also generate the DDL for an object by selecting Generate DDL. (I have shown how this feature works here, but it relies on a project that I will cover in a subsequent part of this series.)
Wrapping it all up
In this article, I showed you how to use the most common features of the Database Explorer in the Rational Application Developer IDE. In the next article, I will talk about Database Projects and how to create schema objects such as tables and views, within RAD.
About the Author
Paul C. Zikopoulos, BA, MBA, is an award-winning writer and speaker with the IBM Database Competitive Technologies team. He has more than ten years of experience with DB2 products and has written numerous magazine articles and books about it. Paul has co-authored the books: 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). Currently he is writing a book on the Apache Derby/IBM Derby database. You can reach him at: firstname.lastname@example.org.
IBM, Cloudscape, DB2, DB2 Universal Database, Rational, WebSphere, and z/OS are trademarks or registered trademarks of International Business Machines Corporation in the United States, other countries, or both.
Java and all Java-based trademarks are trademarks of Sun Microsystems, Inc. in the United States, other countries, or both.
Microsoft and Windows are trademarks 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 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, 2005. 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.