DB2 Command and SQL Consoles

Opening and working with DB2 Consoles

Open the DB2 Command Console or the DB2 SQL Console in one of two ways:

  1. Right click on a DB2 subsystem in the Systems Information view, then select DB2 Command Console or DB2 SQL Console from the context menu that appears.
  2. Open the standard Eclipse Console view (open Window menu >> Show View >> Other >> General >> Console >> OK) and click the Open Console button in the Console view's toolbar, then select DB2 Command Console or DB2 SQL Console from the context menu that appears. You will be prompted to choose a system and a DB2 subsystem on the chosen system.

This will show the standard Eclipse Console view if it isn't already shown, and open a console to the selected DB2 subsystem.

Note that the console view is shared among all consoles in Eclipse. You can manually choose which console is currently shown in the Console view by clicking the Display Selected Console button in the Console view's toolbar and selecting the desired console from the context menu.

To lock the current console in the console view and prevent other consoles from taking it over, you can click the Pin Console button in the Console view's toolbar. After doing so, if a console is created, it will cause a new instance of the Console view to be created, leaving the existing Console view unchanged.

Once a console is open, it can be displayed in as many Console views as required. To create a new console view, click the Open Console button, then select New Console View from the context menu that appears.

You can clear the output of the console or close the console by clicking the Clear Console and Close Console buttons on the Console view's toolbar, respectively.

Previous commands you enter in a DB2 console will be retained in the command history for the console while it remains open. You can access the command history in four ways:

  1. Using the shortcuts CTRL+Up and CTRL+Down while the cursor is in the text entry portion of the console.
  2. By pressing CTRL+Space to show content proposals with the cursor in the text entry portion of the console. Commands that match the text that was entered will be listed at the bottom of the list of proposals.
  3. By clicking the Retrieve Preview Command button on the Console view's toolbar. This will show a dialog which lists previous commands and allows you to filter them in the standard Eclipse fashion. Commands picked from the dialog will be inserted at the cursor position (replacing any selected text) instead of replacing the entered text.
  4. By clicking the downwards pointing arrow next to the Retrieve Preview Command button on the Console view's toolbar; this will show a list of the previous 10 commands entered. Commands picked from this list will be inserted at the cursor position (replacing any selected text) instead of replacing the entered text.

Back to top

DB2 Command Console

The DB2 Command Console allows you to enter DB2 administrative commands. For example, you can enter:

DIS DATABASE (*)
to list existing databases.

As you enter text, the console will perform limited syntax highlighting on the commands, keywords, and keyword parameters. Note that only recognized commands will be highlighted.

If you press CTRL+Space, the console will provide a list of content proposals. This can be used to see the list of available commands, and keywords. Command proposals will include a link to the documentation for the selected command.

Limitation: Syntax highlighting and content proposals always use DB2 v10 syntax. While the DB2 Command Console can be used with other supported versions of DB2, the syntax highlighting and content proposals provided by the DB2 Command Console may be incorrect.

DB2 SQL Console

The DB2 SQL Console allows you to evaluate DB2 SQL statements. For example, you can enter:

SELECT NAME FROM SYSIBM.SYSTABLES;
to list the names of all the tables in the DB2 subsystem. Your SQL statement must finish with a semicolon immediately followed by a newline character (i.e. type a semicolon then press the Enter key). If this is not done, the statement will not be executed (this is to allow SQL statements over multiple lines).

The output of select statements will be formatted as a textual table.

As you enter text, the console will syntax highlight recognized primary SQL statement commands. The remainder of the SQL statements will not be syntax highlighted.

If you press CTRL+Space, the console will provide a list of content proposals. This can be used to see the list of available primary SQL statement commands. Proposals will include a link to the documentation for the selected command.

Limitation: Syntax highlighting and content proposals always use DB2 v10 syntax. While the DB2 SQL Console can be used with other supported versions of DB2, the syntax highlighting and content proposals provided by the DB2 SQL Console may be incorrect.

Limitation: All limitations which apply to File Manager DB2's SQL Execute functionality also apply to the DB2 SQL Console. For example, if File Manager DB2's SQL Execution functionality cannot SELECT columns of BLOB type, then this will fail in the DB2 SQL Console as well.

Back to top

Using a custom DB2 Infocenter for DB2 Documentation

The information popup for a command in a DB2 console will contain a link to the documentation (the info center page) for that command. The info center used by default is http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp, but this can be customized in the File Manager preference pane.

Controlling number of rows fetched in SELECT Statement

From 15.1.2 host build onwards, The DB2 SQL command console in the client supports a functionality that will have the flexibility to fetch number of rows at a time.

If the number of rows in the table are more than 250, then running the SQL SELECT query would first warn the user that the number of rows fetched are more than 250 as shown below

Record Count Warn Dialog

If the user continues then all the rows in the table would be fetched and if the number of rows are too many then it would lead to slow execution.

To avoid this the user has the flexibility to opt cancel and then click on the option to prompt a dialog that will allow to enter row limit from the console toolbar.

Row fetch limit option

The number of rows to fetch at a time can be entered in this dialog which would be saved during different eclipse sessions

Row Count

After saving this count, on executing the Select query the number of fetched rows shown in the console will be the one that was entered in the dialog

Now the dialog will be prompted which will display a message to fetch next records or stop after fetching them

Row continue dialog

This dialog keeps on displaying if the user press OK and there are more rows in the table till the end is reached.

Note:- If the client interacts with host version prior to 15.1.2, then this option(to enter fetch row count) would be disabled from the toolbar.

Back to top