DB2 9.5 and the IBM Data Studio - Part 2

Monday Nov 5th 2007 by Paul Zikopoulos

Part 2 of this series delves into some of the enhanced and new features of the Database Explorer View in DB2 9.5, focusing on the options available from a live database connection object.

Connected Database Object Options from the Database Explorer View

In this new series, we are exploring the integrated development environment (IDE) of IBM Data Studio (formerly known as IBM Data Server Developer Workbench during the open beta), which is new with DB2 9.5 (formerly known as DB2 Viper 2). In Part 1 of this series, we looked at how to add a database connection to the Database Explorer view. In this article, we’ll start to delve into some of the enhanced and new features from this view in DB2 9.5: specifically, the options available to you from a live database connection object.

Note: In Part 1, I referred to this toolset by its name as of the DB2 Viper 2 Beta 2 version. As you’ll see, this has now changed as DB2 Viper 2 has been officially named DB2 9.5, and the IDE has been officially dubbed IBM Data Studio. In Beta 1, it was referred to as Viper Studio. In this article, I’ll start using the newly announced names for both DB2 and the IDE, however if you’re referring to past articles in this series, you can interchange the names (they all represent the same thing).

Assumptions if you’re starting here...

I recommend that you start with Part 1 because I build on the concepts introduced and the objects created there. For example, in this part, I’ll leverage the database connection to the DB2 9 SAMPLE database from Part 1 and that database was created with both relational and XML data. Since IBM Data Studio automatically detects local database connections and adds them to the Database Explorer view, you can start here in Part 2 if you want so long as you’ve run the db2sampl –xml –sql command from your operating system’s command prompt. To get a copy of the latest DB2 9 .5 beta, visit: www-306.ibm.com/software/data/db2/9/download.html. (Be sure to bookmark the following URL to download copies of DB2 Information Management software as it becomes generally available: http://www.ibm.com/software/data/db2/9/download.html.)

Database options in IBM Data Studio Database Explorer view

After working through Part 1 in this series, the Database Explorer view should look similar to the following figure:

Connecting to a database

Before you can work with a database from the Database Explorer view, you need to initiate a connection to the database (which assumes the DB2 instance where the database resides has been started). In the previous figure, you can see that IBM Data Studio shows you the connection status of a selected database from the Database Explorer view. (See the highlight in the previous figure.) In addition, icons beside the database names serve as quick connection status identifiers. For example, since I don’t have an active database connection in the previous figure, the connection icon beside the SAMPLE database is gray () instead of green .

To connect to a selected database, perform the following steps:

1.  Select the database that you want to connect to. (For this example, select the SAMPLE database.)

2.  Right-click the selected database and select the Reconnect option. The Database Authorization dialog box opens.

3.  Enter a user account that has the correct authorization for this database, and click OK:

If the connection is successful, the icon beside the corresponding database turns green (), and the status message referred to earlier indicates a live database connection.

You can also see details about your database connection in the Properties view:

Make note of this tab because it contains valuable information that can be very useful for your day-to-day work. Specifically, I find being able to copy and paste the Connection URL field beneficial when working with certain applications, or when building my own.

If the connection fails (such as when the instance is stopped or incorrect credentials were provided) IBM Data Studio displays a message:

The previous message is what I got when I stopped the instance and tried to connect to the SAMPLE database. Granted, this message might not tell you what you want to know, but it might change after beta code. You will notice at the bottom the choice to work in offline mode. I’ll discuss this very valuable concept later in this article since you can enable this option. If offline operation is not enabled and you click Yes in the previous window, you receive a message indicating this status, as shown below:

Once you have a database connection, you can perform a number of operations at the database level, as shown by the pop-up context menu (which you get by right-clicking a connected database):

The available options for a connected database are the focus for the remainder of this article.

The basics: editing, deleting, disconnecting, and refreshing connections

You can perform a number of basic operations from a live database connection so I decided to lump them all together in this one section.

The Edit Connection option

You can edit a database connection whether its object is connected or not. When you select Edit Connection, the Edit Connection window opens:

Click for larger image

You may want to edit a database connection for several reasons, such as to use a different user account that has different privileges on the data server, or to modify a filter created on the original database connection object. I’m not going to detail all the things you can do with this option because they are very similar to those available when adding a new database connection, which were outlined in Part 1 of this series.

Tip: If you frequently work with different user IDs or focus on different schemas, I recommend that, instead of editing the database connection object, you simply create multiple connections to the same database with different credentials or schema filters. For example, in the following figure, you can see that I have multiple database connection objects that are bound to the SAMPLE database. One of the connections has a filter that applies a PAULZ-only schema filter and another object doesn’t have any filter at all yet both use the same user account to connect to the data server. A third database connection object uses a different user account (SECADM) to connect to the database:

The Delete option

The Delete option in this context only deletes the database connection object not the database itself. (I can hear the sighs of relief already.) The only other thing I’ll mention about this option is that you don’t get a confirmation dialog box when you select it (at least in the beta version) – so ensure that this is what you want to do.

The Disconnect option

As you might expect, you use this option to disconnect a database connection object from the database for which it was created. Note that the disconnection happens only on the selected database connection object. For example, if you had two database connection objects with live connections to the same database, disconnecting one object will not affect the other, as shown below:

Note: The Disconnect option’s icon can be a bit misleading because it’s the same as the disconnected server icon. The purpose of the icon is to represent the final state, but because of its color, it may appear as if it’s not an available option.

The Refresh option

As you change things in the Database Explorer view, those changes aren’t always immediately reflected in this view. Several reasons for the delay are possible: you are working in offline mode, the cache hasn’t been updated, and so on. If you ever create an object and it doesn’t show up, or connect to a database and the status still shows disconnected, select the Refresh option. (If you’ve ever worked with the Windows Explorer in Microsoft Windows then you’re already familiar with this ‘clean-up’ task; it’s the same concept here).

Add to Overview Diagram

When you are connected to a database, you can add the database object to an overview diagram. Overview diagrams automatically include all or a selected set of data objects. If you build overview diagrams using the Information Engineering (IE) or Unified Modeling Language (UML) notation, you can discover and draw implicit foreign key relationships between these objects. I find that overview diagrams help me to understand the interaction between data objects.

When you select the Add to Overview Diagram option, IBM Data Studio gives you the option to select a number of schema objects (or a subset of them) to add to the overview diagram using the Overview Diagram Selection window:

Note, however, that the contents of the Overview Diagram Selection window are entirely dependent on any filters applied to the database connection object. For example, in Part 1 of this series, I applied a schema filter to show only the PAULZ schema, which is why that is the only option returned in the previous window.

If you disable this filter by editing the database connection or changing an object-specific filter, then the results of such actions would be taken into consideration when you select the Add to Overview Diagram option, as shown below:

In the previous figure, you can see that I disabled the PAULZ-only schema filter by selecting the Disable filter option. You can change a database-level filter or any database connection information by selecting the Edit Connection option from the pop-up menu for a database object.

The following overview diagram was created in my IDE when I simply selected the PAULZ schema from my filtered database connection object:

This doesn’t look very helpful, does it? (Rest assured: it will be.) The IDE doesn’t automatically adjust or zoom into this diagram because it has the potential to be huge. To enlarge this diagram simply double-click the title bar () and it will expand to fill the entire IDE. It should now look like this:

In this example, it’s still not that helpful because there are a lot of relationships in the SAMPLE database. IBM Data Studio provides an Outline view, which you can use to navigate large diagrams:

In the previous figure, you can see the linkage between the Outline view on the right, and the corresponding magnified view in the designer palette. There is more to the Outline view, but I’ll cover all of its capabilities in a future article.

You can further customize how the storage diagram looks by using the Properties tab, or by adding meta-data shapes and notes (such as grouping a set of related tables in a polygon or adding a note to an object describing its business use). For example, in the following figure, I used the Properties tab to include column names, types, and so on:

There are lots of features associated with overview diagrams, and I’ll cover those in my next article.

New SQL Statement

You use this option to generate a new SQL statement to run against the database you are connected to. IBM Data Studio gives you the option to use the SQL Builder (which has drag-and-drop query-building components) or the SQL Editor to build your SQL statements. I’ll cover all the enhancements and capabilities of the SQL assistance that’s part of this IDE in a future article. I typically don’t use this option from the Database Explorer view because you can’t save this SQL statement into a project; it’s more for ad hoc querying of the database and, therefore, not as useful as the case when you build an SQL statement as part of a database development project, where it can be persisted, subsequently turned into a Web service, and more.

Save Offline

In the “Connecting to a database” section, I briefly commented on the option to work offline. This, for me, is one of the coolest features of IBM Data Studio. To best illustrate the use of this nifty feature, I’ll give you an example. While traveling on a plane, I often build SQL statements for future demonstrations. Without Internet access on a plane, I can’t connect to my remote DB2 for z/OS data servers and use the SQL facilities in this IDE to build my SQL statements. Of course, since DB2 family members have 95% of the SQL API in common between them, I can move the SQL code to any project despite what platform the DB2 data server is running on (distributed or mainframe); but that isn’t what I want to do. What happens if the DB2 for Windows data server for which I’m building the SQL statement isn’t local but is rather on a domain somewhere in my network? By creating an offline copy of the database, IBM Data Studio creates a schema cache that allows me to perform many operations (especially for building SQL statements) even when I don’t have a connection to the database. That’s cool!

When working in offline mode, you can:

  • View the objects in a database (for example, schemas, tables, views, user-defined functions, stored procedures, and so on)
  • View database object properties
  • Create and view an overview diagram as discussed in the previous section
  • Create objects in a data development project

Of course, you can’t do everything when you are working in offline mode. Some of the actions that you can’t perform with an offline database connection object are:

  • Filtering or refreshing the Database Explorer view (which requires access to the data server); however, you can apply sub-filters to filter out tables within a schema that was part of the database connection object filter.
  • Sampling the contents of a database (since you have to pull the contents of the data from a table)
  • Dropping data objects from the Database Explorer
  • Extracting, loading, or editing data
  • Running routines
  • Deploying routines to the same server (the routines need to be built, of course)
  • Running SQL statements from the SQL builder or the SQL editor (since you expect a result set); however, IBM Data Studio will still parse and assist you in the creation of the SQL statement
  • Viewing Visual Explain information from an SQL statement

To enable a database connection object for offline mode, perform the following steps:

1.  Ensure that you have a live connection to the database object for the selected database connection object (because you can’t save the contents of a database if you’re not connected to it).

2.  Right-click the connected database connection object, and select Save Offline:

IBM Data Studio will go through the entire schema that is included in the selected database connection object. (For example, if a schema is filtered out, it won’t be saved for offline operations.)

For a database with a large schema, this could take a long time. For example, SAP with all its modules fully implemented has about 60,000 tables! If you want the IDE to work in the background to complete this task, select Run in Background. In the bottom-right corner, you’ll see the status of this operation if you run this task in the background:

In this step, you can see that I enabled the SAMPLE NO FILTER database connection object for offline operation.

3.  Disconnect from the database by selecting Disconnect from the pop-up menu (so you can test working in an offline mode)

4.  Right-click the database connection object that you enabled for offline work, and select Work Offline. IBM Data Studio loads in the cached schema to enable offline work:

Tip: Running this task in the background gives you the opportunity to work with another database connection (perhaps one that is local) while the schema is being loaded for this connection.

When the schema cache has been loaded, a toggle appears beside the database connection object, allowing you to expand its contents on offline mode:

In the previous figure, you can see I have three states of database connections. The PAULZ-only FILTER object has a live connection (since it has a green icon). The SAMPLE and TOOLSDB objects have no connection (they have gray icons, but more obvious, you can’t expand them), and the SAMPLE NO FILTER object is disconnected but enabled for offline work (since it is expandable, but the icon is gray). Finally, you see that the connection status at the bottom of the Database Explorer view shows that you are working with a cached database connection object.

In the following figure, I’ve expanded the SAMPLE NO FILTER database connection object. You can see that I can’t sample the contents of the data since I’m working in an offline mode:

However, I can work with the table since its definition is part of the schema cache, (you can see that I expanded the table to see its columns and their data types):

Wrapping it up...

In this article, I took you through the rest of the operations that you can perform on a database connection object. We’ve barely scratched the surface of IBM Data Studio, but as you can see, there’s a lot of capability here. In my next article, I’m going to delve into all the things you can do with overview diagrams, which I lightly touched on in this article.

» 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 thirteen years of experience with DB2 and has written more than one hundred-fifty magazine articles and is currently working on book number twelve. Paul has authored the books Information on Demand: Introduction to DB2 9.5 New Features, DB2 9 Database Administration Certification Guide and Reference (6th Edition), DB2 9: New Features, Information on Demand: Introduction to 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, including running with his dog Chachi, avoiding punches in his MMA class, and trying to figure out the world according to Chloë – his daughter. You can reach him at: paulz_ibm@msn.com.


IBM, DB2, and z/OS are trademarks or registered trademarks of International Business Machines Corporation in the United States, other countries, or both.

Microsoft and Windows are trademarks of Microsoft Corporation in the United States, other countries, or both.

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