DB2 Viper II and the IBM Data Studio Developer Workbench

Part 1: An Introduction to the Database Explorer

If you use IBM DB2 9 (DB2 9) to power up your business,
you’re likely aware of the Eclipse-based DB2 Developer Workbench (DB2
DWB). This toolset delivered enormous productivity to the application
development life cycle. About this tool, builder.au
(an organization that pitches themselves as “by developers for developers….”) had
this
to say
:

DB2
9 has really raised the bar for database development. Besides, who can go past
the slick new Developer Workbench? With the workbench IBM should be able to
draw back some ground from developers who in the past may have looked past DB2
for a platform that was easier to develop on.”

Essentially, the DB2 DWB replaced the DB2 Universal Database
Version 8 Development Center (DB2 Development Center) and included project
migration features from the DB2 Development Center, pureXML integration, a
centralized place to create routines for DB2 for Linux, UNIX, and Windows data
servers (as well as DB2 for z/OS data servers), a unified debugger for routine
development (stored procedures and user-defined functions), and many other
features that enhanced the deployment, packaging, and development for your DB2
data server.

For all its strengths, the DB2 DWB left one area unaddressed:
different workers still needed different DB2 tools to do their jobs. For
example, an application database administrator (DBA) would surely use the DB2
DWB to build application logic such as stored procedures or user-defined
functions (UDFs). If you were building this logic as part of a service-oriented
architecture (SOA) foundation, you’d have to implement the Web Services Object
Runtime Framework (WORF) to have DB2 provision this logic as a Web Service. (Now
you’re looking at two technologies to simply expose a routine as a Web Service.)
If you wanted graphical assistance while building your database schema, an
operational DBA would likely leverage the DB2 Control Center, which isn’t Eclipse-based
at all. A DBA focused on business intelligence (BI) data servers would likely
turn to the DB2 Design Studio, which is Eclipse-based. A security steward
really didn’t have any tool infrastructure so had to use a creative mix of the
command line processor (CLP) and scripts. The story continues, but you get the
point.

In DB2 Viper II (the beta version of the next point
release of DB2 9, which was formerly codenamed DB2 Viper), the new IBM
Data Studio Developer Workbench (IBM DS DWB) now appeals not just to application
developers but also to application DBAs, operational DBAs, security stewards,
SOA architects, and more. While the IBM DS DWB becomes the central tooling
infrastructure that you can use to manage your DB2 for Linux, UNIX, and Windows
data servers, it’s also the tooling infrastructure for Informix IDS and DB2 for
z/OS data servers! Think of this as your all-roles-in-one and all-data-servers-in-one
toolset. It’s poised to drastically reduce the total cost of ownership of your
IBM data server solutions as well as flatten the time-to-value curves
associated with them.

Note: I refer to this
toolset by its name as of the DB2 Viper II Beta 2 drop. Expect this to change.
It used to be referred to as Viper Studio. As DB2 Viper II becomes generally
available (and renamed with a point release number) I will switch to the final
name.

If you’ve read some of my other
toolset articles
(such as the series on Visual Studio 2003, Visual Studio
2005, IBM Rational Application Developer, and IBM Rational Developer Architect)
you may have noticed that I detail a toolset’s features by its functions as
opposed to how it would be used by a specific individual performing a specific
role. I do this because we often wear different hats; and although our roles may
have different names in different organizations, we often do the same job. For
example, you often find operational DBAs building application logic, or DBAs responsible
as much for security and audit as for honoring a service level agreement (SLA).
I intend to take the same approach in this series. The first few articles are devoted
to all the things you can do with the Database Explorer.

How to get started with this series

This series assumes that you have the SAMPLE database created on your local
machine. If you don’t, you can create it by entering the db2sampl –xml –sql command
from your operating system’s command prompt. To get a copy of the latest DB2 9
Viper II beta, visit: http://www-306.ibm.com/software/data/db2/9/openbeta.html.
(But be sure to bookmark the following URL to download copies of DB2
Information Management software as it becomes generally available: http://www-306.ibm.com/software/data/db2/9/download.html.)

The IBM Data Studio Developer Workbench Data Perspective

Eclipse-based toolsets use the concept of perspectives.
Perspectives define the initial set and layout of views in any Eclipse-based
toolset, and the IBM DS DWB is no exception. Perspectives provide a set of
functionality aimed at accomplishing a specific type of task or working with
specific types of resources. For example, if you’re building a data server
project, you’ll find yourself in the Data perspective; if you’re doing
stuff with Java, then chances are you’ll be working in the Java perspective;
if you are debugging application logic, then you’ll want to switch to the Debug
perspective, and so on.

Within a window, each perspective shares the same set of
editors – though they may have different tweaks and things suited for that
perspective’s tasks. For example, if you had to edit some SQL in the Data perspective,
you would use the same style editor that would be used for editing Java code in
the Java perspective. This concept (reusability of components) is what
really started the whole Eclipse initiative: a single toolset for all facets of
a solution yields enormous productivity benefits. Perspectives also control
what appears in certain menus and toolbars. They define visible action sets,
which you can change to customize a perspective. You can save a perspective
that you build in this manner, making your own custom perspective that you can
open again later. As you work through a solution, you can even switch perspectives
easily and frequently. This article assumes that you are working in the IBM DS
DWB integrated development environment (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 Open Perspective
dialog box, as shown below. (You may need to select the Show all check
box – it only appears if all the perspectives can’t be displayed in the Open
Perspective window) to see this perspective, depending on how your IDE has been
configured: you can see in the lower-left corner of the following figure that the
Data perspective appears in the Change Perspective quick list).

The Database Explorer

Most application developers are
familiar with explorer-style environments in an IDE. Even if you are not an
application developer, think of Windows Explorer and you’ll get the point.

The Database Explorer window in
the IBM DS DWB is shown below:

You can see in the previous figure
that I’ve already added the SAMPLE database to this window. In the Database Explorer,
you can connect to existing databases and view their designs and create or alter database objects. The
key differentiator for the Database Explorer is that it’s a live connection
to the data source.

You browse data from the Data Project Explorer view, see
data distributions for quick quality checks, run stored procedures and
user-defined functions, and lots more. Specifically, using the Database
Explorer, you can:

  • Create and manage database connections, and browse data objects
    in a connection
  • Define connection filters
  • Connect to existing databases and view their designs
  • Reconnect to a database if your database connection was
    disconnected
  • Disconnect from a database connection if you are connected
  • Use saved connection information to view objects in a database
    even if you are not actively connected
  • Refresh the database objects that are defined for a database
    connection
  • Delete a database connection
  • Import or export database connection information to an XML file
  • Highlight an object in a connection, and view its properties in
    the Properties view
  • Modify data objects, and manage changes
  • Create new data objects using the SQL editor
  • Create or alter data objects using the Database Administration
    editor
  • Drop data objects
  • Compare and merge two data objects
  • Analyze the impact and dependency of data objects
  • Export data object metadata to data projects, where you can
    modify and redeploy the objects
  • Drag and drop stored procedures or user-defined functions into a
    data development project, where you can modify and deploy the objects
  • Create, run, and tune SQL queries and routines
  • Create and run SQL scripts using the SQL editor
  • Create routines with the SQL editor or the routine editor
  • Debug stored procedures using the integrated debugger.

I’ll cover many of the capabilities outlined in this list in
this series.

Paul Zikopoulos
Paul Zikopoulos
Paul C. Zikopoulos, BA, MBA is the Program Director for the DB2 Evangelist team at IBM. He is an award-winning writer and speaker with more than 14 years of experience with DB2. Paul has written more than 230 magazine articles and 11 books on DB2 including, 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 Clusters) and a DB2 Certified Solutions Expert (BI and DBA). In his spare time, he enjoys all sorts of sporting activities, including running with his dog Chachi, avoiding punches in his MMA training, and trying to figure out the world according to Chloë - his daughter.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles