Toad^® for IBM DB2

Version 3.1.1

Release Notes

April 2008

Contents

Welcome to Toad for DB2

Toad for DB2 is a database development and administration solution that provides productivity features for rapidly creating and executing queries, automating database object management, developing SQL code, and instant access to DB2 expertise.

Developers and administrators have access to a community of users, peers, and experts to help solve development and administration issues and assist in developing applications and feedback for improving Quests products.

All of this comes with the backing of Quest Software, a healthy and growing corporate entity that can provide the necessary training, support, and superior customer service demanded by IT management.

Back to Top

New in This Release

Toad for DB2 3.1.1 provides fixes to customer issues. For a description of these issues, see Resolved Issues and Enhancements in 3.1.1.

For a description of the updates and new features available in the previous Toad for DB2 3.1 release, see the following sections:

• Updates to Toad for DB2 3.1

• New Features in Toad for DB2 3.1

For a description of the updates and new features available in the previous Toad for DB2 3.0 and 3.0.1 releases, see the following sections:

• Updates to Toad for DB2 3.0

• New Features in Toad for DB2 3.0

Updates in Toad for DB2 3.1

This release includes updates in the following areas:

• General

• Database Administration

• Data Management

• Query Development

For a description of the updates in Toad for DB2 3.0 and 3.0.1, click here.

General

• Default connection document now set in Configuration wizard—Toad no longer prompts you in a separate window at startup to specify which document should open by default whenever you open a connection. You now specify this default using the Configuration wizard.

Database Administration

• General in Toad:
  • Drop Script option added to SQL Generation feature—The Generate SQL right-click menu in the Database Browser or Object Palette offers the option to generate a DROP script.
  • Formatter options applied to Generate SQL scripts—When you generate SQL using the Generate SQL feature in the Database Browser or Object Palette, Toad formats the SQL using the options defined on the Editor - Formatter page in the Options window.

• Specific to DB2 (LUW and z/OS platforms)
  • Schema Migration wizard enhancements—The following updates have been implemented:
    • The Migration wizard now automatically sends migration scripts directly to Toad's Editor, allowing you to review the scripts before executing the migration.
    • If a dependent tablespace listed on the Objects Selected for Migration page is the result of your selecting the Include dependencies option, the tablespace is initially shown as unselected. From this page, you can then select it for migration if that is your intention.
    • The wizard now automatically detects and displays any conflicts upon opening the Define Transformation Rules and Migration Options page. (You no longer have to perform an extra button-click to generate the list of conflicts.)
  Specific to DB2 for LUW:
  • Table editor enhancements—The Create/Alter Table window layout and controls are easier to understand and navigate. Additionally, the new interface supports data partitioning (a new feature in DB2 for LUW 9).
  • Details for event monitor tables—In the Database Browser or Object Palette for an event monitor, select the Target Tables tab to view the list of tables to which an event monitor writes data. (The tab is available if the event monitor definition includes WRITE TO TABLE.) Double-click a table entry to view the details for that table.
  • Statistics profile support during advanced ALTERs and migrations—During advanced ALTERs, Toad preserves statistics profiles for tables. During migrations, Toad generates statements to migrate statistics profiles.
  • Index RUNSTATS execution during advanced ALTERs and migrations—During advanced ALTERs and migrations, Toad automatically generates statements to update statistics on indexes.

• Specific to DB2 for z/OS

  • CHECK DATA execution during advanced ALTERs and migrations—During advanced ALTERs and migrations, Toad generates statements to run CHECK DATA as needed to remove the CHECK PENDING status from objects. The most common case in which the CHECK PENDING status is applied occurs when the generated script loads data in an the object that has foreign keys or check constraints.

Data Management

• Significant memory enhancements—This release implements major memory improvements for queries that return large result sets. When retrieving results, Toad uses a few megabytes of memory initially regardless of the size of the result set. Subsequent scrolling of the result set adjusts the memory usage to only the amount needed to display the results.

• Grid customization—You can now customize the grid style when a skin is applied to Toad. Define the grid style in the Configuration wizard (Tools | Configuration Wizard) or in Interface Options (Tools | Options | Environment | Interface).
• Read-only data—You can make all data grids read-only by default. Go to Tools | Options | Environment | Grid, and select Show data as read-only.
• Search data-Word or phrase searches are now available for data grids. Simply press Ctrl+F to set the search criteria. You can select Mark All in the Find window to highlight all cells in the grid that match the criteria.

Query Development

• Editor:
  • XQuery support—You can now execute XQuery statements from the Editor.
  • View scripts in a full-size window—Use docking controls to auto-hide or group the Editor panel, Code Navigator, and result sets. To display the Editor and Results panels in a separate, full-size window, drag and drop the Results tab into the Editor window. (Press F6 to switch between the two panels.)
  • Error line highlighting—Now you can highlight the line in the Editor that caused an execution error. To do this, double-click the error message in the Script Output tab.
  • Change case and add comments—You can now easily apply upper or lower case to text or comment and uncomment lines by selecting options from the Editor | Advanced menu or the Editor's right-click menu.
• Query Builder:

  • Date ranges—When you add a WHERE condition for a column that uses a date or time data type, a new Date Range tab displays in the Where Condition window. The Date Range tab allows you to select a plain text tag, such as Today, Last week, Last 30 days, or Current year. The tab is added to the WHERE condition of your query and, when executed, is substituted with a BETWEEN command using the appropriate date range. Date Range tags are also used in the Editor so that you can create queries manually.

New Features in Toad for DB2 3.1

This release offers new features in the following areas:

• General

• Database Administration

• Query Development

• Version Control

For a description of new features in Toad for DB2 3.0 and 3.0.1, click here.

General

• DB2 9 FixPak 3 modules installed—Toad for DB2 now ships and installs IBM DB2 9 FixPak 3 modules.
• Spotlight on DB2 launch point—Within the context of the current DB2 connection, select Tools | Spotlight on DB2 (or Spotlight on DB2 z/OS) to open Spotlight on DB2 to monitor activity and performance your database or subsystem.

Database Administration

• Specific to DB2 (LUW and z/OS platforms):
  • Data migration available in Schema Migration—Schema Migration now provides the option to migrate data from source to target objects. The migration process uses DB2 utilities and commands to offload the data at the source and reload it on the target. The process produces two scripts that can be ported to machines of your choice. The first script runs on the source database and exports the source data. The second script runs on the target database and contains the DDL to create objects at the target database and IMPORT statements to load those objects with data.
  • Option to define the data-preservation method for advanced ALTERs—You can specify that advanced ALTERs use either temporary tables or DB2 utilities and commands to offload and reload data when preserving it. Go to Tools | Options | Databases | DB2 | General to define the default method for all advanced ALTERs. You can override this default on the Alter window when altering individual objects.
  • New advanced ALTER options—You can globally set default behavior for the utilities that advanced ALTERs use to preserve data. Go to the Tools | Options | Database | DB2 pages.
• Specific to DB2 for LUW:
  • Native IMPORT, EXPORT, and LOAD utilities available—Toad lets you run the DB2 IMPORT, EXPORT, or LOAD utility on one or more tables to move data in IXF, ASCII, or worksheet formats. You can customize command options and generate, execute, and save the utility scripts. Toad's version of these utilities is compliant with DB2 for LUW 9.
  • Statistical views—Toad supports statistical views. You can now optimize and collect statistics on views so that the SQL optimizer can use them in access paths.
  • Utilities menu—Toad has a new Utilities sub-menu for easy right-click access to utilities that you can perform on a specific object.
  • Ability to start and stop event monitors—You can now start or stop an event monitor listed in the Database Browser or Object Palette. Simply right-click the event monitor and select the appropriate option (or click the appropriate option from object editor toolbar).

• Specific to DB2 for z/OS:
  • Native BIND, BIND LIKE, REBIND, DROP (or FREE) utilities available—Toad provides these DB2 utilities to manage your packages and plans. You can customize command options and generate, execute, and save utility scripts.
  • Quick REBIND for packages and plans—This feature lets you run a simple REBIND command (using system defaults) on multiple plans or packages. Simply right-click multiple plans or packages, and select Rebind... .

Query Development

• Query Builder:
  • FROM clause subquery support—You can now add a subquery to a FROM clause using the Query Builder toolbar or the right-click menu. A Queries panel displays on the left side of the window showing the From Clause Subquery element in the query tree. You can add tables and views and create joins for the subquery in the same way that you normally use the Query Builder to create queries.

    After creating the subquery, click the parent query in the Queries panel to return to the original query. A From Clause Subquery table now displays in the Diagram panel with all the columns in the subquery. Use this new table as you would any other database object.

Version Control

• New provider support—In addition to Visual SourceSafe (server and client version 6.0d), Toad Version Control now supports the following providers:
  • CVS (server version 1.11.22 on Solaris and 2.5.03 on Windows; client version 2.5.03)
  • Subversion (server and client version 1.4.2)

Updates in Toad for DB2  3.0

Toad for DB2 3.0 and 3.0.1 included updates in the following areas:

• General

• Connection Management

• Customization

• Database Administration

• Data Management

• Query Development

• Database Development

• Reporting

• Project Management

General

• Tab renaming—Rename tabs in document windows by pressing F2 or by right-clicking the tab and selecting Rename Document.

Connection Management

• Connection organizing—Use drag-and-drop in the Connection Manager to reorganize connections and connection groups.

• Connection renaming—Rename connections and connection groups in the Connection Manager by pressing F2 or by right-clicking an item and selecting Rename.

• Service management—In the Connection Manager, start, stop, or pause database services by right-clicking a connection.

• Redesigned connection toolbar—Perform connection-related actions formerly accessed from the standard Toad toolbar. These actions include Open Connections (used for creating and saving new connections), Commit, and Rollback. The toolbar also contains a drop-down list of available connections, allowing you to switch easily between connections. You can right-click the current connection in the drop-down list to access functionality associated with the connection, including options to disconnect from the connection, disconnect from all connections, view connection properties, or open documents associated with the connection.

• Redesigned Connections window—Perform these functions and tasks easily from the Connections window (formerly the New Connections window):

  • Click Save to save connection information for later use without having to connect.

  • Edit connection properties in the right pane (including the default DB2 schema or authorization ID and certain DB2 registry settings for the connection), and then save or undo your edits.

  • Create a new connection.

  • Import connections to Toad or export your Toad connections to a file.

Customization

• Options feature:

  • Tree view—Now use a standard tree view to quickly navigate between pages in the Options window.

  • Modeling options—Go to the Database | Diagramming and Query Builder pages to modify options formerly on the Modeling page.

  • New DB2 options—On the Database | DB2 page, specify object-editor options for handling advanced alterations, creating object-editor templates, and removing comments in SQL before execution.

  • Updated keyboard options—Use the enhanced Environment | Keyboard page to assign keyboard shortcuts more easily:

    • You can now edit the Default keyboard scheme.

    • You can click Default beside the Shortcut currently used by field to revert back to the default shortcuts for the selected keyboard command.

    • You no longer have to click Assign for keyboard shortcuts to be saved. They are now applied when you move the cursor focus to another command.

  • Network Share option—Specify a location where you can share reports from the Report Manager or files and directories from the Project Manager with your colleagues.

Database Administration

• Primary key and Not Null indicators—Easily identify key columns and columns that do not allow null values. A key icon next to a column name indicates a primary key; and an asterisk next to the column name indicates a Not Null column. The Not Null indicator is also available for query result sets in the Editor. (The primary key indicator is not available in the Editor.)

• New targets for generated SQL—Use the Generate SQL option to send a generated SQL script to the Editor or a clipboard or to save it to a file.

• New code page support for Log Reader—Log Reader for DB2 now supports code page 923.

Data Management

• Side-by-side grid view—Now display the Card view side-by-side with the Grid view on Data tab.

• Edit and read-only toggle—Right-click the green (or red) button in the lower left of the Data grid to toggle between edit (Grid has custom editing) mode and read-only (Grid is read-only...) mode.

• Row copy support—Copy multiple cells of data. Press Ctrl and click each cell to select the cells to copy; then right-click and select Copy Rows.

• Row flagging—Flag rows on the Data tab in the Database Browser by clicking within the margin of the row. Review the following guidelines for using this feature:

  • This option does not apply to data queried from the Editor.

  • The table you are browsing must have a primary key.

  • The flag is tied to values of the primary key. Therefore, if the value changes, the row is no longer flagged. Also, if you alter the table and change the primary key, all flagged rows are removed.

• Line numbers for data—Add a line number to the margin in the data grid to locate or reference a row quickly when working with colleagues. This feature is enabled in Tools | Options | Environment | Grid.

• Merge results—Display results in a single, stacked data grid if a query returns multiple results. This feature is enabled in Tools | Options | Database | Script Result.

Query Development

• Editor:

  • Ability to switch connections—Change the connection associated with the current window by right-clicking the tab, selecting Change Connection, and selecting an available connection.

  • Line comments—Easily comment or uncomment lines of code by selecting the code text, right-clicking, and selecting Comment Lines or Uncomment Lines. You can also use the keyboard shortcuts Ctrl+ - (minus) to comment lines and Ctrl+Shift+ - (minus) to uncomment lines.

  • Query execution enhancements—Display query results using these new enhancements:

    • A collapsed results pane automatically expands when you execute a script (F5) or an SQL statement (F9) or when you verify SQL.

    • Multiple result sets are now merged automatically by default, instead of displaying in separate tabs. A navigation panel displays to the left of the result sets, enabling you to navigate quickly to each set. The number of columns and rows (shown as columns number X rows number) also displays in the navigation panel.

      To specify the default format for displaying results sets (as either merged sets or individual tabs), use Tools | Options | Database | Script Result. You can also modify the maximum number of result sets to display for a query. (The default is 100.)

  • Intellisense—Add an asterisk as the first entry in the auto-completion list for columns. If you specify the asterisk, all columns for the table are added to the list. This feature is enabled in Tools | Options | Editor | Toad Insight.

• SQL Modeler (now Query Builder):
  • SQL Modeler renamed—Note that the SQL Modeler has been renamed to Query Builder for this release.
  • Column enhancements—When adding a column to a query, select the Add All Columns checkbox to add the actual name of each column to the statement. You can also select the Wildcard checkbox to generate SELECT * in the statement.
  • Lookup table—Quickly map a descriptive column to a column that contains an ID number, so that the description is included in the generated query even if the table that contains the description is not included.

    To access this feature, in the Diagram pane, right-click the ID column for which you want to find descriptive information, and select Find Lookup Table. The Find Lookup Table window is displayed, showing a Tables pane that lists all tables defined with a column with the selected column name.

  • Options integration—Open the Database - Query Builder options page directly from the Query Builder toolbar.

Database Development

• ER Diagrammer:

  • Add notes—Right-click a pane, table, or relationship to add notes.

  • Manual relationships—Create manual relationships between objects.

  • Diagram schema—Quickly diagram all tables in a schema.

  • Query Builder integration—Send a diagram to the Query Builder.

Reporting

• New launch points for Toad Data Report wizard—You can now start the Toad Data Report wizard from the Tools menu (select Tools | Data Report Designer) and from within the Reports Manager (right-click a folder and select Create Data Report).

Project Management

• Project Manager:

  • New toolbar—Use the toolbar to start the project creation process quickly or to navigate to other projects easily.

  • To-do items—Keep up with project or daily tasks using the new To-Do Items feature. To create a To-Do list, right-click a resource node in the Project Manager, and select New | To Do Item. Then double-click the clipboard beside the To-Do item to assign a due date and to add any notes. With an assigned due date, the item's text changes to red when it is past due. The notes display as a tool tip when you hover the cursor over the item.

New Features in Toad for DB2 3.0

Toad for DB2 3.0 and 3.0.1 offered new features in the following areas:

• Installation

• Connection Management

• Database Administration

• Data Management

• Query Development

• Reporting

• Troubleshooting

• Version Control

• Project Management

Installation

• DB2 for LUW Version 9 FixPak 1 load modules—Toad for DB2 Version 3.0 ships with the IBM DB2 for LUW Version 9 FixPak 1 modules.

• New Quest IBM DB2 for z/OS component—The new Quest IBM DB2 z/OS component is available with this release of Toad for DB2. You install this component on each DB2 for z/OS subsystem that you want Toad to manage. In addition to the object-management functionality already provided with Toad, this component enables you to perform the following in Toad:

  • View the list of volumes available for defining a storage group in the subsystem

  • View the information about the current threads connected to the subsystem

  • Perform future product features

  For complete information about installing this component, refer to the Quest IBM DB2 z/OS Component Installation Guide, available from the Windows Start menu under Program Files | Quest Software | Toad for DB2 | Documentation.

Connection Management

• Special register editing—Toad now supports editing DB2 special register values at the connection level and at the Editor level. When you set up a Toad connection to a DB2 database or subsystem, you can define certain special register values that are then in effect each time Toad connects to the database or subsystem.

  Additionally, you can override special register values for the duration of a specific Editor session. To do so, click the Set Register Value toolbar button in the Editor to display the Alter Special Registers window. Then make changes to the listed special register values.

  To be able to override special register values in the Editor, make sure that you use the following settings on the Tools | Options | Editor | General page:

  • The Use specific connection for each open editor checkbox is selected.
  • The Allow changing executing connection in editor checkbox is cleared.

Database Administration

• General to Toad:
  • Diff Viewer feature—Compare differences in DDL or in files using the new Diff Viewer. Access the Diff Viewer using one of the following methods:

    • Select Tools | Diff Viewer. You can then select any two files to compare.

    • Right-click an object or data grid, and select Send To | Diff Viewer.

    Note: You cannot make updates to files in the current database.

• Specific to DB2 (both platforms):
  • Object migration—The Toad for DB2 Schema Migration wizard migrates objects from a source DB2 database or subsystem to a target database or subsystem. The migration process runs in the background so you can continue to work on other tasks.  To start the wizard, select one or more objects to migrate, right-click, and select Migrate Object(s). You can also select Advanced | Migration.

    Using the Schema Migration wizard, you can do the following:

    • Use a powerful filter-building tool to select the objects to migrate.
    • Select to migrate objects from several schemas at once.
    • Select to migrate DDL or statistics or both for a given object.

      Note: Currently Schema Migration does not migrate object data.

    • Resolve potential conflicts by defining transformation rules that change schema and other attributes on the target objects.
    • Customize the migration execution. For example, you can choose to run the migration script immediately or within a Toad Editor session, save the migration script to a file, save the current Schema Migration project to a file, have the wizard run in the background during the migration, or have a report generated when the migration completes.

  • Object lists—Group specific objects into named object lists. You can run remedial utilities and object migration in Toad against an object list, without having to select the objects individually. When you run one of these functions against an object list, you can be certain that the generated script does not reference objects that could halt the execution. To run a utility or a migration against an object list, right-click the object list name, and select the option for the appropriate function.

  • Object-editor templates—Save and reuse specific object attributes as templates for creating other objects. You can create both regular templates, which you manually load in the object editors, and default templates, which are automatically loaded each time you open object editors. When you define a default template for an object type, Toad gives you the option to designate it as the default for the database (or subsystem) or for the user connection to that database (or subsystem).

    The  toolbar located on each Create or Create Like window provides options to load an existing template or to create a regular or default template from the current object attributes.

  • Advanced ALTERs—Perform database-object alterations that cannot be performed by ALTER SQL statements. Toad for DB2 analyzes the changes you want to make to objects and generates scripts that contain either ALTER statements or, if advanced alteration is needed, statements that drop and recreate the objects.

• Specific to DB2 for LUW:

  • DB2 for LUW 9 support for objects—Manage objects using version 9 enhancements and updates now included in Toad object editors.

  • New object editors—Manage buffer pools and database partition groups (including stripe sets) using Toad object editors.

  • Database and database-manager configuration parameter management—Review and edit current database and database configuration parameters values.

  • Support for Transfer Ownership—Change ownership of objects using the DB2 TRANSFER OWNERSHIP command.

  • Enhanced table and index REORG utility—Run this utility on one or more tables or indexes at the table, index, or tablespace level. The newly designed Reorg window lets you specify reorganization options for the individual tables or indexes and for the REORG command in general. Additionally, the utility now supports both REORG INDEXES ALL and REORG INDEX commands.

  • Native RUNSTATS utility available—Execute this utility on one or more tables at the table or tablespace level. The Runstats-Tables window allows you to specify RUNSTATS options for individual tables and for the command in general.

• Specific to DB2 for z/OS:

  • New object editors—Manage auxiliary tables and storage groups using Toad object editors.

  • Thread activity details—View details about current threads connected to your DB2 database.

Data Management

• One-click export—Easily export data using default settings from the Editor, Query Builder, and Database Browser.

  Note: When using this feature with Database Browser, first select the Data tab before selecting any of the one-click export actions to avoid hanging Toad.

Query Development

• Debug support for DB2 for z/OS SQL stored procedures—Now compile and debug stored SQL procedures in DB2 for z/OS. Refer to the Toad for DB2 Getting Started Guide for a description of the system requirements for running the compile and debug components.
• DB2 for LUW CLP command execution—Execute DB2 for LUW CLP commands using the Editor.

Reporting

• New Reports Manager feature—The new Reports Manager lets you quickly manage reports or use canned reports provided by Toad. To access this feature, select View | Reports Manager.

• Microsoft Excel integration—You can now open existing reports or spreadsheets without leaving Toad.

  Note: When using this feature, have only one Excel file open at a time. If more than one file is open, the Excel toolbar is disabled for the additional files.

Troubleshooting

• SQL trace support—Log information for all executed SQL to a file or to the Output window by enabling SQL Trace. If you select to log information to a file, this log file is stored in C:\Documents and Settings\username\Local Settings\Application Data\Quest Software\Toad for database version\ConnectionTrace.log. You can view the log file in the Editor by selecting Tools | SQL Trace | Show Logfile.
• Online troubleshooting help—Use Troubleshooting topics in the online help to resolve connection and debugging problems and other issues.

• Web search—Use the new Web Search feature to Google (or perform another type of web search) without leaving Toad. From the toolbar in the main window, select the search engine type (such as Google), enter the search criteria, and click Search.

Version Control

• Robust integration with VCS vendors—Take advantage of Toad's more in-depth integration with popular VCS vendors. Currently, Toad has integrated version control with Visual Source Safe.
• Object and SQL script support—In addition to files, archive revisions to database objects and SQL scripts from the Database Browser.
• Version Control Setup wizard—Run a user-friendly wizard to configure version control to work with Toad.

Project Management

• Object notes—Add notes to objects in the Database Browser, Object Palette, ER Diagram, and Query Builder.

Back to Top

Resolved Issues and Enhancements

Click the following links to view issues addressed and enhancements implemented in the Toad for DB2 3.x releases, including this latest release 3.1.1:

• 3.1.1

• 3.1

• 3.0.1

• 3.0

Resolved Issues and Enhancements in 3.1.1

Resolved Issues and Enhancements in 3.1

Resolved Issues and Enhancements in 3.0.1

Resolved Issues and Enhancements in 3.0

Back to Top

Known Issues

The following is a list of issues known to exist at the time of the Toad for DB2 release.