Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Oct 19, 2007

DB2 Viper II and the IBM Data Studio Developer Workbench

By Paul Zikopoulos

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.

DB2 Archives