An Introduction to IBM Data Studio

David Simpson

IBM Data Studio is a no-charge product provided by IBM to allow users, developers and administrators to interact with databases from a client machine. IBM Data Studio may be used on a variety of platforms, but is most commonly used with the DB2 family of products. Most of the functionality from previous client tools such as Visual Explain, Control Center and Stored Procedure Builder (aka Development Center & DB2 Developer Workbench) has migrated or is in the process of being migrated to this new platform. The Eclipse IDE was selected as the environment to be used.

IBM Data Studio may be used to accomplish many different tasks and there are also optional features that may be enabled for a charge that will extend this functionality. While there are many uses for this product, I find the most common ones are:

Stored procedure development, testing and debugging

Query tuning with Visual Explain and the Query Tuner

Developing and testing SQL statements and scripts

An easy interface to the DB2 catalog for distributed developers

Java developers are generally familiar with the Eclipse interface since it has been used as a Java development environment for many years. Many products in IBM’s Rational line are built on the Eclipse framework. Others are sometimes less familiar with this environment.

Terminology

Like any complex environment it takes a while to become familiar with the terminology and logistics of navigation in an Eclipse environment. I find it is particularly frustrating for people with a z/OS background. In many ways it is like learning to use an ISPF-based application locally on z/OS. There are things about the infrastructure that one just needs to know before the actual application makes any sense.

Here is some helpful terminology:

Workspace – When you first launch an Eclipse based application like IBM Data Studio, you will be asked which workspace you would like to use. In Eclipse terminology a workspace is a folder on your client machine where your work will be stored. This work will include metadata (such as how to connect to various databases), projects (stay tuned for more detail) and settings about how Eclipse will behave. When you first enter IBM Data Studio, you will be using the default workspace. You can change your workspace at any time by clicking on File -> Switch Workspace. You will then see the folder that Eclipse is using.

Views - When IBM Data Studio is launched the first thing you will see is the workbench. The workbench is made up of one or usually more views where work will be accomplished. Each view may be closed, minimized or resized as needed. The default views and positions will depend on which perspective you are using. Views may also be expanded to consume the entire workbench by double clicking on the tab for the view. Double clicking on the tab again will place it back where it belongs on the workbench. This will become especially important when you are working on something exclusively (such as a query script or stored procedure) and the other views on the screen become “noise.” Whenever you want to return everything to its normal place in your perspective, you can click “Window” then “Reset Perspective.” This will retrieve any “lost” views. When using an editor you can get context sensitive help by pressing CTRL-space.

Perspective - In Eclipse, a perspective is a default collection of views and actions along with the size and position of the views on the screen. When using IBM Data Studio it is often helpful to use the “Data” perspective, but there are other perspectives for Administration, Tuning, Debugging and many more. You can change your perspective using the button on the top right. You can also customize or create your own perspectives. You can add views by clicking Window -> Show View.

Project - All work in Eclipse is created and stored in a project. You can think of a project as a subfolder in your workspace stored on the local drive of your workstation. A project is a place to put the things you create such as SQL scripts, stored procedures, or the results of explains and query tuning. There are different kinds of projects that can hold different kinds of work. In IBM Data Studio, many kinds of work will require a Data Development Project. You can create as many projects as are necessary. Each project will have a default database connection that it uses.

Common Uses

Let’s now take a look at some common uses for this product.

Working with stored procedures is probably the most common need that drives us to IBM Data Studio. We can develop stored procedures across the DB2 family of products using the SQL Procedure Language (SQL PL) or Java. SQL PL is increasingly being used to code stored procedures and user-defined functions because of the ease of development. With the advent of “native” stored procedures on the z/OS environment there is the added benefit of System z Integrated Information Processor (zIIP) offload (read cheaper CPU) when the stored procedure is called from a distributed environment. The Eclipse debugger may also be used to step through the execution of a SQL PL or Java stored procedure right inside IBM Data Studio.

While external procedures such as those written in COBOL cannot be developed and debugged using IBM Data Studio they may still be executed for purposes of a single unit test. You can locate your stored procedure in the Data Source Explorer view and right click on it to execute. This provides a handy alternative to the coding of “stub” programs to call a procedure for unit testing.

IBM Data Studio is also frequently used for tuning queries. The functionality of Visual Explain and Optimization Service Center has been ported into this environment. For a more detailed look at these components you can view a Tech Talk on the topic here.

Summary

If you plan on working with DB2 on any platform for the foreseeable future then you will want to become familiar with IBM Data Studio. We have seen this product evolve a great deal over the last few years and we can expect new and important functionality to continue to end up there in the coming years. Much of this functionality will make DBAs and DB2 developers much more productive as we can automate tasks that would otherwise require very expensive toolsets or time consuming home grown solutions. Stay tuned…