Query Analyzer Window

The most widely used window of Aginity Workbench application is the Query Analyzer window that allows exploring the database schema and executing SQL queries.

Query Analyzer is the main window that allows you to explore your database schema and execute SQL queries.

The Query Analyzer window consists of three major parts: the Object Browser, the SQL Editor, and the Result Set.

If you use multiple monitors, you can move the Query Analyzer window to one of them. On the View menu, click Make Standalone Window and drag the window to another monitor.

Object Browser

The left section of the Query Analyzer window is called the Object Browser. It allows you to browse all the schemas in your dashDB database and view the entities in each schema (tables, views, stored procedures, table-valued functions, and so on).

You can manage the visibility of the Object Browser. Press CTRL+B to hide or display the object tree.

The database entities in the Object Browser are grouped by schemas and have the schema name displayed at the beginning of their names if this is specified in the application options. In dashDB terms, a schema is a logical grouping of related database objects.

To quickly show or hide the schema groupings in the Object Browser, select or clear Group by Schema on the View menu. To show or hide the schema name in the object names, select or clear Entity Schema on the View menu.

Empty schemas are not shown when the Group by Schemas option is selected.

By right-clicking a database, table, or another object, you can access functions specific to the corresponding object type.

The Object menu on the menu bar provides the same functions, as the shortcut menu available by left-clicking a tree node.

The following are some of the available functions, by object type:

Databases

Table

Column

You can create and apply an entity filter to the list of objects in the Object Browser to display the objects that are relevant to you and hide all other objects. You can also turn on a default filter to apply it automatically each time you connect to the target database.

SQL Editor

The section in the upper right of the Query Analyzer window is used for editing SQL scripts.

Adding a New Tab in the SQL Editor

To add a new tab to write a SQL query in, click New tab. You can also add a new tab by pressing CTRL+T if the Query Analyzer window is currently open.

To switch between several tabs in the active Query Analyzer window, press CTRL+F6 and CTRL + SHIFT + F6.

Accessing SQL Statement Management Functions

By right-clicking anywhere in the SQL Editor, you can access a number of useful additional features and SQL query management options. For example, you can override the row limit for the result set, use syntax highlighting and the code auto-complete capability, and more.

Inserting Object and Column Names into the SQL Editor

If the name of a database entity or an entity column selected in the Object Browser needs to be inserted into the SQL Editor, double-click the column. The column name will automatically be inserted into the spot where the cursor is currently positioned in the open Query Editor. You can choose from multiple text-formatting options by selecting Tools > Options >Object browser, and then selecting the most appropriate format from the On column double-click insert drop-down list.

In addition, you can drag any node from the Object Explorer tree to the SQL Editor to insert the text where the pointer is currently positioned, or at the point where the text is copied into the Query Editor (this behavior can also be customized using the Tools/Options windows).

Using Autocompletion

If code autocompletion is enabled in the query options for a currently open SQL Editor tab, Workbench displays a list of suggestions for a name (for example, a table name) as you start typing it and press Ctrl+Space, as shown in the example below. These suggestions are possible matches from the list of database objects and other items in the current database.

Autocompletion does not limit the object choices to the current database schema that is selected in the Schema list. The result set from autocompletion is a distinct list of object names that you have access to with your credentials. Therefore, if Table A, for example, exists in two schemas (schema 1 and schema 2), autocompletion will only show Table A once. It's up to you to type the schema into the SQL Editor to make sure that you use the correct one.

If the application options specify automatic invocation of autocompletion, you can also display a list of related items after typing a full name and a period (.). For example, after you type a full table name and then a period, Workbench automatically displays a list of the columns from the table.

You can select other autocompletion options in the application options, such as whether to display a list of related parameters automatically or manually.

Splitting the SQL Editor View

The SQL Editor can be split horizontally or vertically into two panes or into four panes. You may find a split view useful when working with a long, multistep query. You can see and edit two or more portions of the code in the panes that you otherwise couldn't see and edit in the SQL Editor at the same time. You can scroll the contents in one pane independently of the other panes.

To split the SQL Editor:

  1. On the Edit menu, point to Split SQL Editor and then click the split view that you want.
  2. To remove the split view, click Single Pane.

Viewing Information for Database Objects in Tooltips

You can display information for a database object in a tooltip when you point to its name in the SQL Editor. For example, in your SELECT query on a SQL Editor tab, you can point to the table name to view a description of the table in a tooltip. To view details for a database object (for example, information about the columns of a table) in an expanded version of the tooltip, pause the pointer over the object name.

Tooltips are available for database objects if Enable Quick Info when the mouse hovers over a word in editor is selected in the application options.

Result Set

The section at the bottom of the Query Analyzer window is called the Result Set. The section displays the SQL Query execution result in two tabs: the Output tab, and the Result tab.

The Output tab has three sub-tabs: Standard, providing a brief summary of the executed query, Text, providing a detailed description of the execution process, and Grid, allowing a grid view of the execution result.

The system displays the Output tab regardless of whether or not an error has occurred during a SQL script’s execution. If multiple statements were executed, and the server has returned several result sets, the result tabs are added for those queries that had been executed before the failed execution took place.

Managing the Grid View

If you do not want the Text and Grid sub-tabs to be displayed in the Output tab, click Tools, select Options > Result set options /Output options, and then clear the Enable Text and Grid Output check boxes.

The Result Set allows you to drag a column from the Results tab, and perform grouping by this column. To do this, drag the column that you want to group by to the drag a column header here area.

Exporting the Query Execution Result

You can export query execution results to the Excel 2003 XML or CSV formats. To do this, right-click anywhere in the Result tab, and then select Export. .

Menu Bar and Toolbar

The Menu bar provides access to several menus that are used to manage the bulk of the system’s functionality. The Toolbar is located under the Menu bar and provides access to the most frequently used options.

The Schema list on the toolbar displays the name of the current schema in the database. By default all queries are executed in the current schema. When you open the Query Analyzer window for the first time, this list shows the schema associated with the user name that you used to connect to the database. You can change the current schema by selecting an appropriate schema in the list.

The Path list on the toolbar displays a list of the default schemas set in the database. If the date being queried is not found in the current schema selected in the Schema list, Workbench will attempt to execute the query in the schemas listed in the Path list.

Status Bar

The Status bar is located under the Result Set section. It shows helpful status information. If you select more than 1 row in the result set, the Status bar can show the minimum, maximum, average, and other data. To make this feature available, click the drop-down arrow in the right side of the Status bar, and select the options that you want to be displayed.


Aginity Workbench for dashDB * Topic last updated August 30, 2018