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, well 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
youll 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, Ill start using the newly
announced names for both DB2 and the IDE, however if youre referring to past
articles in this series, you can interchange the names (they all represent the
Assumptions if youre 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, Ill 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
youve run the db2sampl xml sql command from your operating systems
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 dont 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
the database that you want to connect to. (For this example, select the SAMPLE
the selected database and select the Reconnect option. The Database
Authorization dialog box opens.
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. Ill
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
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
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. Im 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 doesnt 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 Ill mention about this
option is that you dont 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
Note: The Disconnect options icon can
be a bit misleading because its 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 its not an available option.
The Refresh option
As you change things in the Database Explorer view, those
changes arent always immediately reflected in this view. Several reasons for
the delay are possible: you are working in offline mode, the cache hasnt been
updated, and so on. If you ever create an object and it doesnt show up, or
connect to a database and the status still shows disconnected, select the Refresh
option. (If youve ever worked with the Windows Explorer in Microsoft Windows
then youre already familiar with this clean-up task; its the same concept
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
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 doesnt look very helpful, does it? (Rest assured: it
will be.) The IDE doesnt 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, its 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 Ill 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 Ill 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. Ill cover all the enhancements
and capabilities of the SQL assistance thats part of this IDE in a future
article. I typically dont use this option from the Database Explorer view
because you cant save this SQL statement into a project; its 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.
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, Ill give
you an example. While traveling on a plane, I often build SQL statements for future
demonstrations. Without Internet access on a plane, I cant 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
isnt what I want to do. What happens if the DB2 for Windows data server for
which Im building the SQL statement isnt 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 dont have a connection to
the database. Thats 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
Create objects in a data development project
Of course, you cant do everything when you are working in
offline mode. Some of the actions that you cant 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
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
Deploying routines to the same server (the routines need to be built,
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:
that you have a live connection to the database object for the selected
database connection object (because you cant save the contents of a database
if youre not connected to it).
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 wont be saved for offline
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, youll see the status of this operation if you run this task in the
In this step, you can see that I
enabled the SAMPLE NO FILTER database connection object for offline
from the database by selecting Disconnect from the pop-up menu (so you
can test working in an offline mode)
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
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
cant 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, Ive
expanded the SAMPLE NO FILTER database connection object. You can see that
I cant sample the contents of the data since Im 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. Weve barely
scratched the surface of IBM Data Studio, but as you can see, theres a lot of
capability here. In my next article, Im 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: firstname.lastname@example.org.
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 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.