SQL Workbench

The SQL Workbench page enables users to interact with their multiple databases using SQL tools for querying, data modeling, and data manipulation. The SQL Workbench is truly multi-database enabled, where users can browse objects across multiple databases and perform SQL operations. You can use the SQL Editor mode to write and run SQL statements. Or you can use the SQL Builder mode to visually build SQL by dragging and dropping objects and linking them with intuitive UI.

The SQL Workbench provides the following features:

  • Data Explorer: A tree-based view to navigate schemas, tables, views, and indexes.
  • SQL Editor: Advanced code editor with syntax highlighting, auto-completion, and error checking.
  • No-code Data Modeler: Graphical interface to design and visualize database schemas. This feature will build off the Data Modeler in IDA and Data Studio.
  • SQL Tuning: Built-in capabilities for optimizing SQL queries.
  • Explain Plans: Visual representation of query execution plans for performance analysis.
  • Output Visualization: Generate charts or data visualizations from query results.

Adding database connections to SQL objects pane

SQL Objects pane provides a tree-based navigation to all the schemas, tables, views and indexes under your database connections.

  1. Log in to IBM® Db2® Intelligence Center.
  2. Click SQL Workbench and select SQL objects tab.
  3. Click Select connections. From the Select connections dialog, select the database. You can select multiple databases from the Select connections dialog and add to the SQL objects pane. The SQL objects pane displays all the database connections and schemas in each.
    Note: Click each schema to expand it and browse the tables, views, MQTs, aliases and nicknames.
    Click each object to view and edit the details in editor window. For example, if you click Tables, all the tables with their details will be displayed. If you select a table, the following options displays:
    • Add column: Add a column to the selected table.
    • Generate DDL: Modify the table.
    • Access control: Manage access permissions for the selected table.
    • Update statistics: Fetch up-to-date data.
    • Drop: Delete the table.

Managing database connections in SQL objects pane

From SQL objects pane, you can manage database connections using the following options:
  • Search objects: Search objects across schemas in all the database connections in the SQL objects pane.
  • Options menu: Expand the database to view all the schemas. Click Options menu near the database to view four options.
    • Hide system schemas: Hide all the schemas in a database. You can click Show system schemas to view all the schemas.
    • Search objects: Search objects in the specific database connection. In the Search for objects in QueryTuner dialog, search for the object. For example, if you search employee, all the objects with the name employee will be filtered and displayed under the selected database. Click Clear filter to delete the filtered results.
    • Reload objects: Reload objects in a database.
    • Create schema: Click Create schema to create schema in a database. Enter Schema name and Authorization fields, and click Create to create the schema.

    Managing objects in the schema

    Expand schema to display objects such as tables, indexes, views, and aliases. Right-click the object to display menu with the following options:
    • Generate DML: Click Generate DML to Select, Insert, Update, or Delete records in a database. For example, if you click Select, a new SQL Editor worksheet opens with the Select query. For each operation, new SQL editor worksheet opens.
    • View details: Click View Details to view the object details in the Results window .
    When you expand the schema and click on the object, the object details will be displayed in the SQL Editor. For example, if you click Tables under your schema, all the tables with their details will be populated in the SQL Editor worksheet.

Using SQL Builder

You can add any objects such as tables, views, index and aliases in a database to the SQL Builder and generate corresponding SQL query in Editor

  1. Log in to IBM Db2 Intelligence Center.
  2. Click the plus icon to open a new editor worksheet.

    Select Builder tab. You can drag any table, view, MQT, alias or nickname onto the canvas from the SQL objects pane and select that object in your query. Alternatively, you can select the objects by double-clicking or right-clicking the object in the SQL objects pane and selecting the Add to canvas option.

    When you select Builder, Summary tab displays in the results window with details such as joins, select list and filter.

    Every data object that you drag onto the canvas is added to the select statement that is generated for the visual SQL build. For example, if you drag CUSTOMER_ADDRESS table onto the canvas, the CUSTOMER_ADDRESS object is added to the select query.

  3. Click the menu icon on the object in Builder tab, and select Configure. Alternatively, you can right-click the canvas and select Configure.
  4. Select the columns you want to display in the SQL query from the Select list tab. if you want to change the column name, add an alias. To set filters for the table you drop in the canvas, click the Filter tab in the Configure table page. Enter a value for table, column, operator, value, condition and click Save.
    Note: You can join multiple objects in a database to generate SQL queries. To know more, see configuring joins.

Using SQL Editor

You can use SQL Editor to write and run SQL statements.

  1. Write your query in SQL Editor. You can also drag and drop the required objects to Builder and switch to Editor to see the generated SQL query. With this editor, you can create individual SQL worksheets for different database connections and run them simultaneously.
  2. Click Run all to run the SQL query. The results displays in the Results window. Click Run Selected if you want to run the selected query from the worksheet.
  3. Click the corresponding tab in the Results window to view the results. Db2 IC features an enhanced Results window that displays the results for the executed queries in separate tabs.
    Important: Click Execution log to see all your executed scripts. Click the specific script if you want to see the results in the Results window.

  4. Click Settings icon and select Save as to save your script.
  5. Click My Work tab. You can see all your saved scripts listed there.

Tuning the queries

Tune your queries using following steps to optimize efficiency:

  1. Write your query using SQL Editor.
  2. Click Tune the statements icon in SQL Editor. See tuning queries to know how to tune SQL queries.
  3. Optimize your query using the suggested recommendations in the tuning logs.

Managing tuning logs

To manage the tuning logs, do the following steps:

  1. Click Query tuning log icon in the top-right corner. The tuning logs displays.
  2. Select the check box near the query.
    • View result: See the advisor recommendations and report.
    • View log: See the log status.
    • View options: See the tuning options
    • Retune: Retune the query to optimize the efficiency.
    • Delete: Delete the log.