You are currently on IBM Systems Media’s archival website. Click here to view our new website.


Studying Data Studio

Studying Data Studio

Back in late April, when IBM announced the upcoming availability of Rational Developer for i, V9.0 (or RDi, as it will undoubtedly be known) they mentioned that the new version would include IBM Data Studio. As we noted when we blogged about this at the time, Data Studio has been around for a while now and could be run as a standalone tool or plugged into RDP, but few bothered.

Why? Because there was little or no support for IBM i in the product, and most of us are familiar with using Navigator (formerly known as Operations Navigator) to perform the kind of database tasks that Data Studio is designed for. However, we’ve never been huge fans of Navigator. Partly that’s because IBM hasn’t significantly updated it in many years, resulting in a look and feel that’s sadly dated, to be polite. Couple that with Navigator’s sluggish performance, and we were ready to take a serious look at any potential replacement. So even though we knew the current version of Data Studio was probably not “IBM i ready,” we decided to take a look and see what it had to offer.

Our explorations have shown us that the product offers some very nice features that make it much easier to code and run SQL statements than with Navigator. The performance is also far snappier, but to get it set up for use required a certain amount of, shall we say, determination. We’re hopeful that IBM will be addressing these issues in the new integrated version. Even with the challenges, we felt it was worth the effort of downloading and installing the product and we’re looking forward to seeing the integrated version.

That said, let’s take a closer look.

Downloading and Installing

You can download the latest version of Data Studio here. Look toward the bottom of the page to the “Optional Downloads” section and select the one identified as Data Studio client -Red Hat Linux, SUSE Linux, Windows. Sadly, there is no Mac option—but we can hope.

This page also includes links to the support pages and documentation for the product. However, we found the most useful document to be the free ebook, “Getting Started with IBM Data Studio for DB2.” The installation instructions in chapter 1 of the guide make it easy to get the product up and running and are far easier to understand than the QuickStart Guide. You can ignore the section at the end of part 1 about setting up the Web console unless you plan to use the product with DB2 on a non-IBM i system as that part of the product is not currently supported on IBM i.

In part 2, “Managing your database environment,” you’ll encounter your first hitch. The default Administration Explorer perspective doesn’t support operations on IBM i, so if you try to set up a connection according to the guide, you’ll be greeted by the message in Figure 1.

So, to create a working database connection, we first need to open the Database Development perspective. To do this, simply follow the instructions in part 1 that showed you how to switch to the Java perspective, but this time select the Database Development option. We don’t know what the Java perspective has to do with anything but that’s the example the guide uses. Once you’re in the Database Development perspective, you’re all set to go.

On the left side of this perspective, you’ll see the Data Source Explorer view. You can either click the New Connection Profile button highlighted in Figure 2 or select “New...” from the right-click context menu of the Database Connections item. Once you do that, you’ll be greeted by the New Connection dialog as shown in Figure 3.

Selecting the DB2 for i entry (Hey! We’re top of the list!) selects the JDBC driver that will be used to communicate with IBM i. You then need to enter your connection (i.e., system name) and sign on details. Once you’ve done this, test your connection using the marked Test button.

If all goes well, click “Next” to get to the filter dialog. This isn’t essential, but because Data Studio doesn’t understand the concept of a library list, unless we take steps to prevent it, expanding our connection will result in a list of every single library on the system. Since that includes the system libraries and all of the S/36 environment #name style libraries, it’s a good thing that the filter option provides us with a simple and flexible way to avoid this. You can see what it looks like in Figure 4.

Two basic filtering options are available. In the upper part of the dialog, you can select to include or exclude items based on characters in their names. It’s possible to specify that you only want to include items that begin with certain characters. In fact, you can chose to select or omit based on a number of character strings including the use of wild cards. On one of our connections, we used this approach to exclude all of the Q* and #* libraries.

The alternative, and the one shown in Figure 4, is the selection list. You can select items from the complete list that’s presented and either include or exclude them. This is the approach we’ve used as it keeps the list manageable.

The Data Source Explorer

Once your connection details are complete, click “OK” and your new connection will appear in the Data Source Explorer view. Clicking on the elements in the connection will expand them as you can see in Figure 5.

Since this is a database tool, the term “schemas” is used where many of us would still use “libraries.” As you can see in the image, the schemas folder in the tree has been annotated to indicate that it’s a filtered view, which is a nice touch. In the lower part of the image you can see that drilling down into a table reveals the basic details of the columns within it. Under each table entry, you’ll also find individual folders for Constraints, Indexes and Triggers. It’s a really convenient way of finding all of the information related to a table in one place. Not only that but the context menus for each element allow you to perform tasks such as dropping an index or generating the DDL for a table.

One feature that we really like is the ability from the context menu of a table or view to select one of a number of options to display the data. In the case of tables, there’s even an option to edit the data. The highlighted area in Figure 6 includes this option.

Notice also that there’s the option to generate a new SELECT statement for the table. This new SELECT will be generated and placed in the SQL editor as you can see in Figure 7. It includes all of the columns in the table and can be edited to remove columns, add selection criteria, group clauses, etc. You can also add additional SQL statements to the script, such as a SELECT SCHEMA directive to ensure your unqualified table names can be located. Once you’ve edited the SQL, you can run it from within the editor by clicking the highlighted button.

The results (or errors if the SQL is less than perfect) are displayed in the SQL Results view at the bottom of the screen. In most cases, you’re going to want to double-click the view’s tab in order to bring it up to full screen; that’s how we captured the image in Figure 8.

This view will display with contents of the Status tab (highlighted) visible. To see the results as shown, you must first click on the Results tab. If there were any errors in the SQL, they will have appeared in the status window. While we originally thought that the history list on the left side was overkill, that was before we realized it really was a history and that, by clicking on an entry, you can review the results of that query. Useful.

The SQL statement you’ve been working with in the editor is automatically saved—but we’re not quite sure exactly where! There must be a way of retrieving it after you close the view, but the only place we can find it is at the bottom of the File menu. However this isn’t a problem as you can use File>Save As... to save the SQL to a Project, and Projects offer a far more interesting way of building and editing SQL statements as you’ll see in a moment.

Jon Paris is a technical editor with IBM Systems Magazine and co-owner of Partner400.

Susan Gantner is a technical editor with IBM Systems Magazine and co-owner of Partner400.



2019 Solutions Edition

A Comprehensive Online Buyer's Guide to Solutions, Services and Education.

Getting Started With RDP 8, Part 2

Obtaining and installing the new Rational Developer for Power


Getting Started With RDP 8, Part 1

Obtaining and installing the new Rational Developer for Power


Less Talk, More Action

Rich UI enhancements put the B in RBD 8, Part 2

IBM Systems Magazine Subscribe Box Read Now Link Subscribe Now Link iPad App Google Play Store
IBMi News Sign Up Today! Past News Letters