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

Monday Oct 17th 2005 by DatabaseJournal.com Staff

In September 2005, Quest Software announced an open beta of their new Toad for DB2 product. In this two-part series, Paul Zikopoulos introduces you to the Toad for DB2 product, giving you a feel for its capabilities.

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.

by Paul C. Zikopoulos

The SQL Modeler

The SQL Modeler in Toad for DB2 gives users a fast way to create the framework for SELECT, INSERT, UPDATE, and DELETE statements against tables, views, and aliases (sometimes referred to as synonyms). You can use this tool to also create data manipulation language statements (it will launch the SQL Editor) and joins. An example of the SQL Modeler is shown below. Note that you can drag-and-drop objects from the Object Palette to the SQL Modeler workspace:

Click for larger image

The SQL Modeler can be used for automatic SELECT and JOIN syntax generation. You can see in the following figure that, by simply dragging a connection from the EMPLOYEE table to the DEPARTMENT table, you are generating the join syntax for this SQL statement at the same time:

Click for larger image

When dragging and dropping objects from the Object Palette to the SQL Modeler, you can select multiple items by holding down the Ctrl key while clicking on the objects you want to work with in the palette. You can exclude system objects (using a filter) for easier navigation of the schema.

The SQL Modeler also lets you view the explain data of the DB2 optimizer's execution plan in the same way as the SQL Editor, and with a single click you can launch the SQL Editor with the statement you modeled for edit.

The Database Browser

The Database Browser acts as your typical object explorer in that it provides catalog information for specific objects in your DB2 database. The left pane of this feature contains a list of the objects for the selected tab, while the right pane displays catalog information for the object. An example of the Database Browser is shown below:

Note that when you select an object in your database, details of it are automatically shown in the right pane. In the preceding figure, you can see the column definitions for the table (since the default selected tab is the Columns tab) when you select the STAFF table. You can view lots of properties of the selected object. Specifically, you can tab to information on the following topics from the Database Browser:

  • Columns
  • Properties
  • Indexes
  • Constraints
  • Aliases
  • Views
  • Triggers
  • Used by
  • Uses
  • Data
  • Grants
  • Statistics
  • Scripts

The figure to the right of this list shows all the actions you can perform from the Database Browser.

The following figure shows some of the tabs that are available. DBAs simply love the creation scripts in the Script tab that are automatically generated and maintained for you:

Of course, you can display the properties for, and work with, any of the following objects from the Database Explorer:

The Export and Import Utilities

Toad for DB2 comes with an export utility that is fronted by an intuitive wizard, which provides a single common interface to select file formats (including database-neutral formats such as .CSV files). The Toad for DB2 Export wizard also supports exporting a chosen format to a clipboard, which lets you leverage the OLE copy and paste features of your operating system. The Export wizard is shown below:

Of course, there is also a corresponding Import utility that you can use to populate your DB2 tables:

Jumping to Part 2...

In this first part, I have provided you with an introduction to some of the more notable features in the up-and-coming Toad for DB2 product. In Part 2 I will introduce you to even more capabilities. You can also check out a flash demo of Toad for DB2 at http://www.toadsoft.com/toaddb2/.

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 ten years of experience with DB2 UDB and has written over sixty magazine articles and several books about it. Paul has co-authored the books: 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, running with his dog Chachi, and trying to figure out the world according to Chloë – his new daughter. You can reach him at: paulz_ibm@msn.com.


IBM, DB2, and DB2 Universal Database 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.

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

Linux is a trademark of Linus Torvalds in the United States, other countries, or both.

Other company, product, and service names may be trademarks or service marks of others.

Copyright International Business Machines Corporation, 2005. All rights reserved.


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.

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