A Toad without Warts: Quest's Toad Product for DB2 Hits the Streets - Part 1

October 17, 2005

by Paul C. Zikopoulos

The Toad product from Quest Software is a well-known database tool that helps to improve the productivity of application developers and database administrators (DBAs) working with some of the world's most popular relational databases. In September 2005, Quest Software made some serious news in the database industry by announcing an open beta of their new Toad for DB2 product. Toad was previously available for other databases such as Oracle and Microsoft SQL Server, and has now made its features available for application developers and DBAs that work with the IBM DB2 Universal Database for Linux, UNIX, and Windows product (DB2).

Developers and DBAs have a familiar and proven tool set with Toad for DB2 that they can use to manage their database environment. If that environment has heterogeneous characteristics (such as a mix of Oracle and DB2 databases), IT personnel can leverage the common Toad interface for their entire database environment since editions of this product are available for today's most popular database engines.

This is the first of a two-part series in which I introduce you to the Toad for DB2 product, give you a feel for its capabilities, and ensure that you know this tool is available for your DB2 databases. (At the time of writing, Toad for DB2 was in open beta – more on this in Part 2.) This is truly an amazing product, and yes, it even "ribbits" when you start it, which I think is really cool!

What does Toad for DB2 do?

Perhaps this section should be called "What doesn't Toad for DB2 do?", because it would be easier to describe. Toad for DB2, like the other Toad offerings, provides an intuitive graphical user interface (GUI) to a DB2 database server. It is a powerful tool with minimal overhead (just look at the "What does Toad for DB2 run on" section in Part 2 of this article) that makes DB2 development faster and easier, while simplifying DB2 object maintenance for DBAs. At a high level, Toad for DB2 includes:

• An SQL editor
• An SQL modeler
• A database schema browser
• Export and import utilities
• Project management (see Part 2)
• A connection management utility (see Part 2)
• Customizable interface views (see Part 2)
• Object filtering and search capabilities (see Part 2)
• SQL Recall (see Part 2)

An example of the Toad for DB2 product is shown below:

You can see by looking at the tabs in the middle of the figure that there is a lot you can do with this product.

The SQL Editor

Toad for DB2 comes with an SQL editor that has three basic parts to it. The first is the Code Navigator pane, which you can use to edit your SQL text. The code navigator displays an outline view of the editor's contents and allows efficient navigation of large files.

The Text Edit pane of the SQL Editor supports rapid development with support for features such as automatic statement completion (called Intellisense in the Visual Studio .NET world, and Assist in the Eclipse world). This allows for automatic completion of statements based on a cached schema of your database.

For example, if you are not familiar with the database schema you are working on, you can create an SQL statement and auto-populate the list of corresponding objects you reference in your SQL statement. In the following example, you can see that the Toad for DB2 product lists all of the tables under the PAULZ schema the moment I hit the . separator:

Note that in the previous figure the BOOKCHAPTER object I am referencing is actually a view and not a table. This is illustrated by the glasses () beside the object.

The text editor also supports DB2 syntax colorization such that keywords that correspond to the DB2 SQL API are highlighted as with normal integrated development environments:

The SQL Editor also includes uppercase and lowercase classification, alignment and indenting of code blocks, and more.

I also like the fact that I can highlight a statement in my Text Edit window and just run it selectively. (Note that the running of statements is asynchronous, so you are not blocked from the user interface if the query takes a long time to run.)

Other features in the Text Edit window include bubble hover help that includes a description of the selected object, code snippets (shown below), templates, and more.

The Data Grid feature of the SQL Editor is a visual control where the DB2 results of executed statements are automatically bound. For example, running the SELECT * FROM PAULZ.STAFF query results in the following data being bound to a data grid:

Note in the preceding figure that you can see the time the query was executed, how long it took to run, and the data itself. The data is buffered, and you are given controls (), which allow you to not only browse the result set, but also perform INSERT and DELETE operations (assuming you have the authorization to do so).

From any results set, you can even generate the explain plan that represents the decision of the DB2 optimizer by clicking on the Explain Plan tab. The corresponding explain plan for the query in the previous example is shown below:

Another feature I really like about this product is the ability to have multiple results sets for different queries. The following example shows the results of running two queries in their own transaction scope – note the generation of the two query result sets:

You can change the display of a data grid such that it is rendered in a 'card' view, as shown in the figure below:

Finally, you can export the contents for a data grid to various file formats, as shown below:

There is a lot more you can do with the SQL Editor, but hopefully I have given you a quick overview of how rich this feature set is in Toad for DB2.