DB2 Viper II and the IBM Data Studio Developer Workbench

Friday Oct 19th 2007 by Paul Zikopoulos
Share:

In part 1 of this new series, Paul Zikopoulos introduces the Database Explorer that's part of the new IBM DS DWB in the DB2 Viper II open beta.

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.

Adding database connections to the Database Explorer

The Database Explorer view allows you to explore and work with schema objects within your databases, as well as a myriad of other functions. The capabilities of the IBM Viper II DS DWB far exceed those of its predecessor. If you’re familiar with the DB2 DWB in DB2 9, these differences will become obvious to you as you work through this series With that said, simply looking at a database connection object in the IBM DS DWB Database Explorer view and comparing it with its predecessor in the DB2 DWB should convince you that the IBM DS DWB is far from a simple upgrade:

Although this article focuses on DB2 9 and DB2 Viper II for Linux, UNIX, and Windows data server connections, you can use the same steps to connect to the following data servers: Apache Derby version 10+ and later, IBM Cloudscape version 5.1 and later, all supported versions of DB2 for Linux, UNIX, and Windows, all supported versions of DB2 for i5/OS, all supported versions of DB2 for z/OS, and Informix IDS versions 9.2, 9.3, 9.4, 10.0 and later, and 11.0 and later.

To add a database connection to the Database Explorer window, perform the following steps:

1.  Right-click anywhere in the Database Explorer window and select New Connection:

The New Connection wizard opens:

Click for larger image

Note: If the IBM DS DWB doesn’t have enough information to connect to a target data server, a status icon () will appear at the top of your window. This is a common error indication method within Eclipse so you should always look at the top of a window before clicking Finish or OK, and so on.

2.  Expand DB2 for Linux, UNIX, and Windows in the Select a database manager list and select All Versions. The Connection URL details box will automatically change and update the required fields with the last database entry added to the System Database Directory.

Since my system only has the TOOLSDB (created during the installation) and the SAMPLE (created after installing DB2 Viper II) databases on it, the IBM DS DWB automatically updated the required connection information fields with the SAMPLE database’s parameters:

You can use the JDBC Driver field from Step 1 to change the driver and the security method (if it is external to DB2) that is used to make the database connection. By default, the IBM DS DWB will use the native security facility with SERVER authentication to make this connection:

Selecting Other allows you to customize the information for this connection as would be required if, for example, you were building a connection to a DB2 data server but using third-party drivers. You can fully customize this process, as shown below. (Note that as soon as you select this option, the error indicator appears since the page requires more information.)

Note: If you select the default driver again, all of the previously displayed information is restored.

You can also change where the native DB2 authentication challenge takes place using this window. For example, to have authentication take place on the client (something I generally don’t recommend for reasons beyond the scope of this article), select the Use client authentication check box:

As you can see in the previous figure, any changes you make to the Connection URL details box dynamically updates the generated connection string in the Connection URL field.

3.  Test the data server connection by entering the appropriate credentials in the User information box and clicking Test Connection. If the connection is successful, click Next.

You may notice that you can also just click Finish to add the connection; however, the IBM DS DWB offers you some further data server customizations that can come in handy (especially in large environments), so for illustration purposes, don’t add the connection just yet.

If the connection isn’t successful, you will see a message indicating so. For example:

Alternatively, when you click Next on the Connection Parameters page, the IBM DS DWB will automatically test the connection for you and display a message if the connection is unsuccessful. If the connection is successful, the Filter window opens.

4.  Specify the filter that you want to apply to this database connection by clearing the Disable filter check box and building the filter for your database connection. For our example, select the Selection radio button; select the schema you used to create the SAMPLE database (in my case it was PAULZ because I was logged onto a Windows system as this user and issued the db2sampl –xml –sql command); select the Include selected items option from the corresponding drop-down list. Then click Finish.

Filtering is a great idea when you work with databases with large schemas. For example, some enterprise resource planning (ERP) applications such as SAP or PeopleSoft can have over 30,000 tables! Filtering provides a convenient way to quickly access only those schema objects you are interested in. What’s more, it provides a security meta-data layer as well. Generally, if you’re working with a large database schema, I strongly recommend using a filter. Finally, your whole development environment will perform faster because the entire schema won’t have to be fetched into the Database Explorer view. As you can see in the previous figure, there are essentially two methods you can use to build a filter: expressions and selections.

You can use the Expression filter to define all sorts of variations of filters that are generated by almost any imaginable sequence that relates to your schema names. The following figure shows the different stemming options for an expression-based schema filter:

Click for larger image

For this example, I just want the tables in the PAULZ schema to show up in the Database Explorer view, so I simply enter that into this field. When you use this approach to filter the Database Explorer view you can end up with tables you didn’t expect t see. For example, using the Starts with the characters option, the Database Explorer view would include those tables within the PAULZ schema, but also within any other schemas that start with these letters, such as PAULZ1 or PAULZ2.

If you want to filter on a specific schema (or schemas) and don’t require the advanced filtering generation capabilities that the expression approach offers, you can simply select the Selection radio button and select all the schemas you want included in the connection object you are creating. For example, in the following figure, I’m defining a filter on my database connection such that only those objects within the PAULZ schema will appear in the Database Explorer view:

Note that you can include or exclude objects on the basis of a schema name using the drop-down list within the Selection box, as shown below:

As you can imagine, if you select Exclude selected items from the drop-down list, all schema objects other than those in the PAULZ schema will be shown in the Database Explorer view.

You can further filter objects underneath a schema name once a connection is added to the Database Explorer view. For example, assuming the previous filter (which included the PAULZ schema) is applied to the database connection, you may want to filter those tables further within the PAULZ schema to start with PROD such that only tables like PRODUCT and PRODUCTSUPPLIER are shown within the PAULZ schema, as shown below:

After you complete the previous steps, the Database Explorer view should now look similar to the following figure:

You can see that a filter has been applied to the Schemas folder. The [Filtered] keyword is added to any folder that is filtered to give you design-time information in regards to why you may not see the objects you are looking for. Of course, if you created a filter using the Exclude selected items option detailed after Step 4, the Database Explorer view would look like:

You can dynamically change a filter at any time by right-clicking a filtered folder and selecting the Filter option.

Wrapping it up...

In this article, I introduced you to the Database Explorer view that’s part of the new IBM DS DWB in the DB2 Viper II open beta. In addition, I showed you how to add a database connection to this view and apply filters to different schema objects to optimize the design-time experience for your database. In the next article, I’ll introduce some of the tasks that you can perform from the Database Explorer view.

» 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 13 years of experience with DB2 and has written more than 150 magazine articles and is currently working on book number 12. 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.

Trademarks

IBM, DB2, DB2 Universal Database, pureXML, z/OS, Informix, Rational, i5/OS, Cloudscape 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.

UNIX is a registered trademark of The Open Group in the United States and other countries.

Java and all Java-based trademarks are trademarks of Sun Microsystems, Inc. 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.

Disclaimer

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.

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved