Toad for DB2 7.4 - Installation Guide

Create a Multi-Database Query

Note: This topic focuses on information that may be unfamiliar to you. It does not include all step and field descriptions.

The Multi-Database Query tool allows you to run a SQL query to gather information across a large number of databases. You can use the wizard to select multiple databases, to enter multiple SQL queries, and to format/highlight the results based on criteria you specify. Use Multi-Database Query to compare information from several databases in one time.

To create a Multi-Database Query

1. Select Tools | Query Multiple Databases.

2. Select one of the following:

   • Create your own Custom Query
   • Select a Sample Query from the list
   • Open a previously saved query (*.tsg file)

3. Select the Databases you want to run your query on.

   Tip: You can run your queries against multiple schemas within the database provided that your SQL does not use fully qualified names. To specify schemas select Non Qualified SQL — select Schema(s) and check the schemas in the list of database schemas.

4. Add and edit queries and cell formatting.
   • Click Add Query to create and preview a new query. You can create several queries and execute them simultaneously with Multi-Database Query .
   • Select a query and click Edit\Preview Query to alter it.

   Note: In order to get a fluent report it is recommended that the queries return the same number of rows. Please see the sample report "LUW Table and Index Count" as an example of a report with multiple queries.

   Review the following for additional information:

   SQL	Enter a query.
   Cell Formatting	Click Add to add a new formatting rule. Column Cell Formatting Rule—Specify a criteria to format cells. All matching records will be formatted according to formatting rules set in Format Columns, Text Color, Background. etc. When creating a simple rule, select a column, condition and value (for example, "col1 >= 32") When creating a custom rule, use either column names or aliases as an operands or values in the expression. If the SQL query contains an alias, you must use the alias name (for example, "col1 > col2" or "col1 + col2 >= 100). Click to see the list of supported operators and predefined columns Operator: Description: <= Comparison: Less than or equal to >= Comparison: Greater than or equal to < Comparison: Less than > Comparison: Greater than = Comparison: Equal <> Comparison: Not equal != Comparison: Not equal + Addition - Subtraction / Division * Multiplication % Modulo a % n is equivalent to a - (n * int(a/n)) ! Not ( Grouping ) & Bitwise AND | Bitwise OR AND (case insensitive) Logical AND OR (case insensitive) Logical OR Predefined columns: Description: schema Database schema name for the processed row connection Database connection name now Current date time column Any column that includes values satisfying selected criteria Format Columns—Specify the columns you want to apply formatting to. Specify text and background colors, severity signs and messages.
   Preview	Click Refresh to build a preview based on current query and formatting rules. The preview automatically updates when you add or alter a cell formatting rule. Hide Columns you want to exclude from result set. The retrieved data can be grouped by the column. Select columns from the Group by Col list.

5. Click Finish to run your queries.

6. When reviewing results, you can select one of the following options from the Multi-Database Query toolbar:
   • Create new Multi-DB Query and alter current query.
   • Refresh data and schedule auto- refresh.
   • Send Multi-DB Query to Automation. You will be prompted to save your Multi-DB Query as *.tsg file. Automation pane is invoked automatically and lists your *.tsg as source file. See Use Database Automation Activities for more information.
   • Right-click data and select Show Report to save and print the results.

Conditional Mode for Multiple Database Query

Database version or custom conditions can be specified for queries in Multiple Database Query SQL Editor.

1. Click Add Query to create and preview a new query. You can create several queries and execute them simultaneously with Multi-Database Query .
2. A popup shows after clicking the query where conditions can be specified. In Query Editor check Conditional Mode.
3. When the database fulfils the conditional criteria the query is executed. The conditions are tried in the top-down order, i.e. if the first condition is true then the SQL statement is executed and no other conditions are tried on this connection.

Note: Preview Connection type defines the desired connection for preview as well as for AutoCompletion of SQL queries in the Query Editor.

Related Topics

About Editing SQL