Get the Jump on Toad for DB2

On October 25, 2005, Quest Software launched the release version of Toad for DB2, a "database development and administration solution." Part of a line of Toad database tools, Toad for DB2 works specifically with the IBM DB2 family, including IBM DB2 Universal Database and DB2 Express.

As one might expect from a database tool, Toad puts a very nice UI on a very powerful database. But what, exactly, is it capable of? Oracle developers already know the answer; the original TOAD stood for Tool for Oracle Application Development. In fact, half a million Toad for Oracle users should see a clean transition to Toad for DB2, should they decide to migrate, and will be quite familiar with its capabilities.

Figure 1. New Connection Screen

The true test of any database UI is its SQL functionality, and as expected Toad delivers robust tools for creating and managing SQL code. But it also gives the user several other nice features, such as object filtering and search, code formatting, and a line-by-line debugger for SQL, stored procedures, and triggers.

To take Toad for DB2 on a test drive while it was still in its beta phase, I created a very simple goal. Create a sample database, populate it with data, and run a few complex queries off of it. Below I'll go over some of the things I discovered, a couple of small hiccups, and some particularly nice features of which you should be aware.

The first thing to know is that Toad does not create databases, it manages databases you've already created. So for this test drive, I created an empty database in DB2 Express called FROGGER. After that, all database management was done strictly in Toad.

That includes managing the connection to the database. When Toad first starts, you'll see a New Connection screen, shown in Figure 1. This allows you to connect to an existing database, as well as flag a particular connection to start automatically.

Figure 2. Connection Established

An icon next to the "Database Alias" launches DB2's Configuration Assistant, so you can create or configure a database if need be. Be forewarned, though. Doing this from within Toad can slow down your DB2 process. At least on my machine, I found a 230MB process hogging my resources, though this eventually slimmed back down to a more manageable 50MB after I closed the Assistant.

Once the connection has been made, you'll see your primary management icons, including tabs for Tables, Views, Triggers, etc.

Creating a table should be simple and quick. And indeed, with Toad it is. I quickly created a simple table with four fields, including one primary key, which I defined on the Dimensions tab.

Figure 3. Creating the FrogTypes table

Once a table is created, you can get at all sorts of information on it using the tabs in the detail window, on the right side of the workspace.

• Columns: Column information for the table, such as Name and Position, Data Type, Null acceptance, Identity, Default value, etc.
• Properties: Table metadata, including Table Name, Table Schema, Tablespace, and creation information.
• Indexes: Index metadata: Index Name, Table Schema, Columns included in the index, Unique Rule, Index Type.
• Constraints: Descriptions of any data constraints you've added to the table.
• Triggers: Any triggers you've defined for the table, including the event that launches it (INSERT, UPDATE, or DELETE operation) and the code for the trigger. You can add new triggers by right-clicking and selecting "Add Trigger" or by choose the Triggers tab in the object palette on the left.
• Aliases: Alternate name for the table.
• Views: Any views you've based on the table.
• Used By: Other objects that depend on this table.
• Uses: Dependencies this table has on other objects.
• Data: Actual data contained in the table. According to Help, data can be added or edited here using the grid view, but I was never able to do that.
• Grants: Individual and group permissions and the specific privileges those users have.
• Statistics: Huge amounts of statistical data for the table.
• Script: Auto-generated table creation SQL script.

With the table created, I would expect to be able to add data directly using the UI. But when I looked for a way to do this, the only option I could find was the Data detail for the Table object. As mentioned, this would not allow me to edit the data of the table in any way, indicating that it was read-only. I couldn't find anything in the Help to explain why this was the case.

Figure 4. Generate SQL

So I tried a different tactic.

One feature I particularly like is the ability to general SQL on the fly for common queries. In this case, I wanted some quickie code for an INSERT query. Right-clicking on the table and choosing "Generate SQL" gives you options for SELECT, INSERT, UPDATE, and DELETE statements, as well as table generation code. After selecting an option, the SQL code is copied to the clipboard so you can paste it into whatever you're working on.

In this case, I opened up the SQL Editor (Tools -> SQL Editor or the SQL Editor icon on the toolbar) and posted the SQL code there. Be sure to remove references to the ID fields as those are generated automatically. By substituting the generic values with my frog information, I quickly populated the FROGTYPES table. Not quite as easy as editing a data grid, but still pretty fast.

Figure 5. SQL Editor

This just scratches the surface of the SQL Editor. With SQL Recall (F8), you can drag and drop any of your recent queries into the workspace. You can create code snippets and custom code completion templates. You can use hotkeys, a clipboard ring, position indicators, and bookmarks. Handy features, all.

Another handy feature is the ability to search the SQL code you've written. Using the Object Search icon (or Tools -> Object Search), you can quickly find a database object, either by name or by part of its source code. You can even search for a fragment of a column name found in your Views or Tables.

Figure 6. Object Search

After creating a couple more tables, a lookup for Cars and a many-to-many for Car-to-Frog impacts (see Figure 11 for a data dump), I decided to try out the SQL Modeler. This is perfect for creating complex, multi-table SQL queries.

To open the SQL Modeler window, click on its icon, which says "Build SQL Statements" on flyover, or select Tools -> SQL Modeler. You'll see a blank modeler canvas and some criteria fields at the bottom, which will be used when you select your fields (Figure 7).

Figure 7. SQL Modeler

You can add tables and views to the Modeler a couple of different ways. Any time you see a list of objects, such as in the Object Search described above or in the object browser, you can drag and drop them to the Modeler window. I had to do a little rearranging to make the browser visible while the Modeler was open. I used Window -> New Vertical Tab Group and then resized a few things. You can also use View -> Object Palette to create a flyout side tab if you don't already have one on the workspace.

The other technique is to right-click on the object you want to include and simply choose "Add to SQL Modeler". This technique is easier, but unfortunately I ran into a small glitch at first. First, dragging and dropping added no table to the modeler. Then right-clicking caused Toad to croak altogether. But after shutting it down and restarting it, everything worked as expected.

Once I was up and running, I was able to quickly build a complex join query using the wide range of criteria options.

Figure 8. SQL Modeler

After visually building the query, hit F9 or the green arrow on the toolbar to execute it. Results show up in the Result Sets tab. You can also go to the "Generated Query" tab to copy the SQL code for pasting into SQL Editor or another utility.

But alas, the Modeler is also versatile enough to allow the user to create illegal queries. As the night wore on and the caffeine wore off, it took me several tries to get my join query working. At one point, I got this helpful DB2 error message: An unexpected token ")" was found following "OGCARIMPACT"."CARID"". Expected tokens may include: ")". In the end, though, it worked. And it would have taken much, much longer to debug without a visual modeler.

Before moving on to the rest of the database features, one in particular deserves a quick walkthrough. Stored procedures play a strong role in Toad for DB2. Using the tools here, you can build and debug them with relatively little effort.

Figure 9. New Procedure

To start a stored procedure, either go to the Procedure tab in the Object palette and click "Create a New Procedure" or select Create -> Procedure from the main menu bar. You should see a procedure template, as in Figure 9.

For this example, I wanted to do a simple INSERT procedure into the FROGCARIMPACT table. As mentioned before, you can generate SQL code on the fly by right-clicking the table and selecting Generate SQL -> Insert Statement. This copies the code to the clipboard, which you can then paste in your procedure template. A few minor changes to add IN parameters and I ended up with some simple SQL code:

CREATE PROCEDURE AddSquish ( IN FID INTEGER, IN CID INTEGER )
	RESULT SETS 0
	LANGUAGE SQL
	BEGIN
		INSERT INTO NULLID.FROGCARIMPACT (FROGID, CARID)
		VALUES (FID, CID);
	END;

To take advantage of debugging options, select Debug -> Toggle Compiling with Debug from the main menu. Choose "Execute the SQL Statement (F9)" to compile the procedure and add it to the database.

If you go back to your object palette and go to the Procedures tab, you should see the new procedure. With Toad, you can debug the procedure from the context menu, as in Figure 10. Even if you didn't set the Debug toggle, Toad will still execute the procedure so you can check the results.

Figure 10. Debugging a Procedure

At this point, I have a usable database, filled with sample data. So what else can Toad to? Lots. Here's a brief rundown of some additional tools and features:

• Web Browser: Add a basic Web browser to your workspace. The home page includes a ton of support links for the various Toad products, including whitepapers, discussion forums, and tips of the month.
• Knowledge Xpert: There is also a surprisingly robust Help utility and browse, search, bookmark favorites, etc. I especially like the error code catalog, though I couldn't find any SQLSTATE codes.
• Report Generator: Generate HTML documentation for any and all database objects, including columns, data, metadata, and linked Table of Contents for entire schemas, users, tablespaces, etc. Figure 11 shows a straight data dump, generated as an HTML page, of the three tables used in my sample FROGGER database.

  Figure 11. Frogger Data

• Import / Export Wizards: Exchange or save data in a variety of formats, including CSV, XML, and HTML.
• DDL Script Wizard: Generate scripts for the entire database or for specific objects.

In all, Toad allows you to create tables, indices, views, aliases, materialized query tables (physically stores query results as static data), triggers, sequences (ordered set of values that can be used to create unique identifiers), tablespaces, buffer pools (areas of database memory used for caching data pages so as to optimize disk reads), structured datatypes, schemas, users, groups, procedures, and functions.

In a word: yes. A command line processor is not without its charms. But when you have to work fast and the nostalgia of typing line by line has worn off, nothing beats a solid GUI.

But Toad is a prince of an interface. It helps me accomplish everything I might need and a few things I didn't think about. The application runs fast, it's fairly intuitive, it has extensive context-sensitive documentation, and it makes cool croaking sounds when you start it up. What else can I say? I love it, warts and all. I'll kiss this Toad anytime. It leapfrogs over other database tools I've seen. And best of all, with a name like Toad, writers can make cheesy cliches for days.