Toad for DB2 v6 – What’s New

New/Updated Features common to LUW and z/OS

A number of the changes to v6 affect both DB2 LUW and z/OS. We’ll cover those first.

Removal of installing our own DB2 client (use customers DB2 client)

In prior releases of DB2, Toad shipped its own DB2 client. This was necessary in the early days of Toad for DB2 when .Net clients weren't readily shipped and available.

This eliminates the need to migrate the DB2 catalog to a Toad Specific DB2 catalog. This also streamlines and simplifies the installation process. Toad for DB2 v6 will work with DB2 clients 9.7.x through 10.5.x

On the first start, Toad will inform us that it's found and is using our default DB2 client.

We click OK to continue.

Next Toad will notify us that it doesn't have any connections defined and ask if we want to auto create connection profiles.

If we click this check box, Toad will create the connections in a hierachical, host-instance layout. If you don't check this box, you can convert to this layout after Toad creates the connections

So, we click OK and Toad creates our connections.

UI Refresh – updated look and feel and enhancements

If you're familiar with the previous releases of Toad you'll immediately notice that the interface has a fresh, more modern look to it with the first most obvious item being the large icons on the toolbar.

If, however, you prefer the prior, small icon look, you can easily get back to this look and feel by right-clicking on the toolbar and unchecking the "Use Large Icons" option.

Another change that you may have also noticed with the icons is the lack of them. That's because Toad used to keep all of its icon toolbars displayed at the top of the application even when you weren't using them. We heard from new users that this could make it difficult to find the correct toolbar and know what options were available when working with a specific Toad feature.

Now, toolbars open in context to the feature you're using. Here you can see that the SQL Editor toolbar options now display within the SQL Editor window making it much easier to find all of the SQL editor options:

Another refinement to the UI is the ability to perform some tasks more quickly. For example, in the database explorer, we’ve added quick launch buttons and right-click options. In the example below, on the Columns tab you can multi-select objects and user either the in-context icon or right-click menu to quickly create add an index:

Similarly, the controls that display when an action is chosen have been updated, too, with a fresh, clean appearance:

Another small change to the UI was to increase the web URL input area. Before it was difficult to use the built-in web browser because you couldn't easily edit or view the full URL without scrolling left or right.

The last UI improvement is the expanded window size for command and utility output. In prior versions the results were shown in a small window that required the user to scroll to view the full results. So, if we perform a RUNSTATS on a table we now see the results displayed in a larger separate window:

SQL Editor – explain plan tab enhancement

Previously if you had a SQL Editor with multiple SQL statements and you were viewing the explain plan it could be difficult to correlate the explain plan with the statement in the editor. In v6, we’ve fixed this by scrolling and highlighting the associated statement in the SQL Editor pane to the explain plan output being viewed:

SQL Editor – Profiling Tab

New to v6 is the addition of a data profiling tab to your SQL results window. The profiling tab displays information about the first 1000 rows and provides insights into unique values, duplicate values, null values and data distribution. If it’s a character-based column, Toad will also display the top patterns in the data.

Predictive Space Growth

Toad for DB2 v6 has added predictive and future growth features to our space manager for both DB2 LUW and z/OS. So, if you’ve been capturing historical RUNSTATS information, we can now predict into the future how much size and space your objects will need. A new check-box option has been added that when checked will display a future-looking trend. This feature is available only when viewing a single object. The user can control this view by date or by size limit such that you can determine how large an object will be on a given date or the date on which an object will get to a certain size. In the example below we show the actual data in red and the predicted growth line in green.       

Multi-Database Query

The multi-database query has been around for several releases of Toad for DB2, but in v6 it has been enhanced with a conditional mode option to support running different queries based on the database version. This allows users to set up a dashboard display where the underlying query would need to be different because it’s executing against different database catalog versions. For example, you might want to run a query against a DB2 z/OS v8 and v9 system and the DB2 catalog has changed between these versions:

Display Only Explainable Statements

V6 adds the ability to filter out and only display explainable SQL statements for plans and packages. This makes it easier to identify them for potential tuning. Just click the filter icon (highlighted below) and only those statements that can be explained are displayed. Once filtered, it’s then easy to select a statement(s) and click the tuning fork icon to launch SQL Optimizer to auto-tune the SQL.

Object Compare Enhancements

Toad for DB2’s Object Compare is now way more powerful than Schema Compare ever was. Toad for DB2 v6’s object compare replaces the old Schema Compare. You can still do a full compare at the schema level but you will use Toad’s object compare instead. Toad for DB2 v6 adds a lot more flexibility in what can be compared and what to ignore when doing the comparison. Here are the new items that can now be ignored when doing a compare:

• CCSID differences (now applies to z/OS Databases)
• Tablespace differences
• Range Partition value differences
• COMPRESS differences
• Index Collect Statistics (LUW)

LUW specific changes

While the changes listed above applied to both LUW and z/OS, v6.0 also adds a number of LUW-specific improvements.

LUW ‘Real Time Statistics’ Report

V6 adds a new tab to the database viewer that provides live statistics for tables, indexes, and tablespaces. For example, for tables, Toad will show how many rows have been read and/or updated. This provides DBAs a quick monitor view of database activity:

LUW pureScale

We’ve added a pureScale display to v6. PureScale is IBM’s answer to Oracle RAC and is a multi-node, scalable environment. Toad now provides a view of the health of your multiple nodes:

Space Usage

We’ve updated Toad’s space usage display to include a comparison graph overlaid in the table used percentage data:

Table Partitioning Key Values

A new partitioning key values tab has been added for partitioned tables. In prior releases the only way a user could see the partition key values was to view the script. Toad now displays the key values for each partition:

LUW Table Locks

Similar to the partition key values tab, Toad has also added new tabs to show lock information at the table and database level.

At the table level, the locks tab will show if any locks are being held:

At the database level, if any table is holding a lock or an application is waiting on a lock it will be displayed in the new Lock Waits tab:

New Dependencies Displayed

We’ve added a dependency tree for Array and Row Type Variables:

LUW Remote Scripts

Prior to v6, Toad generated scripts that contained Toad-specific syntax. Toad now generates scripts that can be run natively in the CLP (Command Line Processor):

LUW Index Creation

New with v6, if COLLECT STATISTICS is specified when creating an index, then Toad will not generate a RUNSTATS statement as it would be redundant:

z/OS specific changes

This last section will cover some changes made in v6 specific for DB2 z/OS.

Check Index Utility

V6 adds the ability to right-click on a z/OS index and run a CHECK INDEX utility:

Cataloged Global Variable Support

Global variables were added with z/OS v11 and allow the variable to be used throughout z/OS stored procedures and functions. Toad v6 now fully supports these in the object browser with features such as CREATE, CREATE LIKE, ALTER and DROP:

Integrated Components Install Wizard

Prior to v6, the Toad z/OS components install was a separate application. We’ve now built it into Toad which greatly simplifies the task. To launch, you simply right click on the z/OS subsystem and select “Install Toad Components …”.

The wizard that gets displayed looks very similar to the prior wizard but because it gets launched from the context of a subsystem, Toad can pre-fill much of the dialog information for you:

One item that is new with the wizard is that when it completes, Toad will launch two SQL Editor windows. The first will contain the SQL to create all of the objects needed by Toad for utility and command execution and the second will contain all the needed grant statements.

Summary

Toad for DB2 v6 has a lot of great improvements. The list above covers most of the changes but be sure to check out the release notes for a complete list. If you have feedback on v6 or ideas for future releases, we’d love to hear from you.