Toad DB2 Tips & Tricks

Want to get the most from Toad? This technical brief will walk you through some of its most useful features. You'll see how to set the default layout that's best for you, create and optimize SQL statements, view and analyze results sets, create professional reports, and more.

General Setup Configuration and Startup

Toad for DB2 enables you to control the look and feel of Toad as well as default Toad behaviors.

Choosing Your Preferred Layout and Configuration Settings

The Configuration Wizard (shown in Figure 1) will start up the first time you run Toad. It will walk you through choosing your preferred layout and configuration settings.

Figure 1. The Configuration Wizard helps you establish your preferred layout and configuration.

Later, you can invoke the Configuration Wizard within Toad using the Tools | Configuration Wizard menu item (see Figure 2).

Figure 2. You can invoke the Configuration Wizard from within Toad at any time.

Choosing How Connections Are Displayed at Startup

You can specify whether you want the Connections window or the Connection Manager to open at startup, as shown in Figure 3.

Figure 3. You can specify how connections should be displayed at startup.

Choosing Which Features Open Automatically When a Connection Is Established

You can also specify what features you want to open automatically when a database connection is established (see Figure 4).

Figure 4. You can select which features should open automatically when a database connection is opened.

Once you complete that wizard you can also modify those specific settings using the Tools | Options menu item, as shown in Figure 5.

Figure 5. To change your connections settings later, use the Options menu item.

Choosing What Actions Are Performed at Toad Startup

The highlighted section in Figure 6 controls what actions are performed at Toad startup.

Figure 6. After clicking the Options menu item, you can change what actions are performed at startup.

Controlling Whether Toad Can Access the Internet

If you need to ensure that Toad will not attempt to access the internet (for example to check for product updates), check the checkbox highlighted in Figure 7.

Figure 7. You can prohibit Toad from accessing the internet (for example, to check for product updates).

Cataloguing Your Databases

Toad uses your default DB2 client. When you start Toad for the first time, Toad will notify you that it has found and is using the default client:

If Toad finds that you do not have any databases cataloged, a dialog will open, allowing you to create connections from the database catalog:

You can always catalog your databases using the DB2 Configuration Wizard, as shown in Figure 10.

Figure 8. Toad will let you know that it's selected your default DB2 client.

Figure 9. Easily create a new connection in Toad.

Figure 10. You can always catalog your databases using the DB2 Configuration Wizard.

Specifying Options for Each Database Connection

When you define a database connection, you can specify that it should connect automatically when Toad starts up, as shown in Figure 11.

Figure 11. You can specify whether each database connection should connect automatically at startup.

To specify what features (if any) should be opened automatically when a DB2 connection is established, use the settings highlighted in Figure 12.

Figure 12. You can choose which features open automatically when a new DB2 connection is established.

The Database Explorer

The Database Explorer

The Database Explorer (see Figure 13) allows you to easily navigate through your database objects and view important information about those objects. It contains features that make it very powerful and it is highly customizable.

Figure 13. The Database Explorer enables you to easily navigate through your database objects.

Creating Filters

The filter editor allows you to specify granular filtering criteria and to name and save filters for later use, as shown in Figure 14.

Figure 14. The filter editor allows you to specify granular filtering criteria and save filters for later use.

Using the Right-Click Menus

Many actions are available from right-click menus. Just right-click on an object in the Database Explorer to view all of the object-specific actions and features, as shown in Figure 15.

Figure 15. Right-click on an object in the Database Explorer to see all of the actions available for that object.

Using the Dependencies Tree

The dependencies tree (shown on the Dependencies tab) not only does a great job of displaying related objects, but it is an active tree where you can right-click on those objects and invoke many features as well (see Figure 16).

Figure 16. You can right-click on an object in the dependencies tree to see the actions available for that object.

Generating SQL Quickly

Another slick feature is Toad's ability to generate SQL quickly: just right click on the object and select Generate SQL. You can easily generate Select, Insert, Update, Drop or Create statements and have them either saved to a file, opened in a Toad SQL Editor or saved to the clipboard, as shown in Figure 17.

Figure 17. You can generate SQL quickly just by right-clicking on an object.

Viewing Details About Objects

The Object Details feature comes in handy when you want to view information on multiple objects at once. The feature is easy to invoke: while in a SQL Editor with your cursor on the object name, hit F4. Alternatively, with the object selected, right-click and choose the Send To – Object Details action, as shown in Figure 18.

Figure 18. Right-click on an object in SQL Editor to see details about the object.

Advanced Data Features

Advanced Data Features

Toad for DB2 is chock full of features for working with objects containing data, such as tables, views and MQ table. Probably the first thing you notice when you select of one of those objects in the Database Explorer is the Data tab on the right-hand side.

The grid shown in that tab is a powerful component that has many features to make handling data a cinch (see Figure 19).

Figure 19. The grid in the Data tab of Database Explorer has many powerful features.

Customizing the Data Grid

Right-click on any column header to see additional grid options, as shown in Figure 20.

Figure 20. Right-click on any column header to see additional options.

Editing Data in the Grid

Editing data in the grid is simple: just click on a cell and editor controls specific to that data type will pop up, making editing easy (see Figure 21).

Figure 21. Right-click on a cell to see editor controls appropriate for the data type.

Defining Key Columns on the Fly Using the Data Grid

If you happen to have some tables that are defined without unique key columns, you can define key columns in the grid on the fly so you can edit that data as well (see Figure 22).

Figure 22. You can define key columns on the fly using the grid.

Exporting Data

Toad has many export features, including Quick Export, which allows you to easily export your data into various popular formats, as shown in Figure 23.

Figure 23. Quick Export enables you to easily export your data into various popular formats.

Toad also has an Export Wizard (see Figure 24) that supports more formats and allows for further customization.

Figure 24. Toad's Export Wizard has even more export options.

All Toad Grids Have the Same Advanced Features as The Grid in The Data Tab.

All of the features available in the Data grid are available in any grid in Toad. For example, Figure 25 shows the right-click actions in a SQL Editor Results grid.

Creating Histograms to Analyze Data

Toad DB2 includes a histogram tool that can also be invoked from any grid, as shown in Figure 26.

Figure 26. You can invoke Toad's histogram tool from any grid.

The histogram tool, shown in Figure 27, displays the frequency of columns values within a table or results grid. It is very helpful for understanding and analyzing your data.

Figure 27. The histogram tool displays the frequency of columns values within a table or results grid.

Similarly, Toad offers a Profiling tab that shows data patterns and distribution, as well as insights into uniqueness and distinct values:

Figure 27. Toad's Profiling tab offers detailed insights about your data.

Comparing Data

Toad for DB2 offers several different features for comparing and syncing your DB2 data. In any results grid, you can easily compare and view differences from other grids, as shown in Figure 28.

Figure 28. Right-click on a results set to compare it to another results set.

The Data Diff Viewer highlights the data differences, as shown in Figure 29.

Figure 29. The Data Diff Viewer highlights the differences between two sets of data.

Comparing and Synching Data

Toad also has a Data Compare and Sync feature. You can invoke it by right-click-clicking on a results set, as shown in Figure 30.

Figure 30. To compare or sync data, right-click on a results set and choose Data Compare.

This invokes the Data Compare Wizard, which walks you through specifying comparison and data options and then generates a detailed comparison report, as shown in Figure 31.

Figure 31. From the comparison report, invoke the Synchronization Wizard to sync results sets.

Querying and Reporting Capabilities

Querying and Reporting Capabilities

Toad for DB2 has many advanced query building and reporting capabilities that make SQL developers more productive. The automated code completion feature in the SQL Editor (see Figure 32) simplifies writing SQL.

Figure 32. The automated code completion feature in the SQL Editor simplifies writing SQL.

Query Builder

Toad's Query Builder feature lets you generate SQL by dragging and dropping tables onto its canvas and clicking a few checkboxes, as illustrated in Figure 33.

Figure 33. With Toad's Query Builder, you can generate SQL by dragging and dropping tables and clicking a few checkboxes.

Data Report Designer

Once you are satisfied with your results, it is easy to create professional reports using the Data Report Designer. Begin by right-clicking a cell in the results set, as shown in Figure 34.

Figure 34. To create professional reports, start Toad's Data Report Designer.

The Data Report Wizard walks you through several steps to define your report (see Figure 35).

Figure 35. Use the Data Report Wizard makes it easy to define your report.

Your report will be presented inside Report Designer so you can further customize and format your report, as well as preview and save it (see Figure 36).

Figure 36. Use Report Designer to finalize and save your report.

Automating and Scheduling Reports

You can use Toad's automation feature (see Figure 37) to automate and schedule reports (and other tasks and scripts too).

Figure 37. Use Toad's Automation feature to automate and schedule reports (and other tasks and scripts too).

SQL Tuning and Optimization

Toad has tuning and optimization features to help you deliver the best performing SQL.

Viewing Access Plans

For example, using the Explain Plan tab of SQL Editor, you can see the access plan for your SQL in any of a variety of formats, as shown in Figure 38.

Figure 38. The Explain Plan tab of SQL displays the access plan for your SQL in a variety of formats.

SQL optimization

You can invoke Quest's SQL Optimization from the Toad SQL Editor (see Figure 39).

Figure 39. You can invoke Quest's SQL Optimization from the Toad SQL Editor.

SQL Optimizer has a one-click SQL rewrite button. This invokes its AI engine, which automatically generates as many alternative yet equivalent statements as possible (controlled by settings) to identify alternatives that have unique access plans (see Figure 40).

Figure 40. SQL Optimizer automatically generates alternative yet equivalent statements.

SQL Optimizer enables you to easily compare the costs, stats and executions of those alternatives so you can identify the best solution (see Figure 41).

Figure 41. SQL Optimizer helps you compare the costs, stats and executions of the alternatives so you can choose the best solution.