The Command Editor

The Command Editor is the graphical user interface (GUI) version of the Command Line Processor. The Command Editor offers several other functions in addition to those provided by the CLP and the Command Window.

• The ability to execute multiple DB2 commands and SQL statements interactively and simultaneously. With the CLP, only one command at a time can be executed interac-tively. If you want to execute multiple commands, you have to save them in a text file and execute the file with the -f option as explained in Section 4.2.1.1, Methods to Work with the CLP.

• The ability to save all the commands you typed in the Command Editor window to a file or as a task to be executed from the Task center.

• The ability to display a Visual Explain output of the access path chosen by the DB2 optimizer for an SQL statement. The Visual Explain tool is discussed in the next section.

• The ability to display results in well-formatted tables.

N O T E Theterminate command is the only one that guaran-tees the back-end process is indeed terminated. Even if the list applications command does not display the db2bp back-end process running, use the terminate command to be certain.

N O T E We recommend issuing a terminate command before adb2stop command. This prevents the back-end process from main-taining an attachment to an instance that is no longer active.

Figure 4.17 The CLP back-end process

You can start the Command Editor either from the IBM DB2 menu or from the Control Center.

(We will talk about the Control Center in Section 4.4, General Administration Tools.) Alterna-tively, the command db2ce starts it from a command-line prompt.

Figure 4.18 shows the start screen of the Command Editor. The Target field is empty until you click on the Add button, which displays the Specify Target window. Select a Target type from the pull-down menu, and then select the database you want to work with in the Available targets pull-down menu.

The Command Editor makes an implicit connection to the database you have chosen. You can then start entering your commands from the command input window (see Figure 4.19).

Figure 4.19 shows the three tabs associated with views in the Command Editor. The tab selected in Figure 4.19 is for the Commands view. This view displays all the commands you have entered. If you want to execute several commands or statements at once, make sure to delimit them with the character specified in the Statement terminator character field (at the bottom of the window). If you entered several commands or statements in the Command Input Window, but would like to execute only a particular one, highlight it. Then you have the following options:

• To execute the command and produce the results, click on the Execute button

• To execute the command, produce the results, and the access plan, click on the Execute and Access Plan button

• To produce the access plan but not execute the command, click on the Access Plan button

Figure 4.18 The Command Editor lets you choose the database you want to work with.

If you choose to execute the command, the results can be displayed in the Command Output Window as shown in Figure 4.19. This is probably the most convenient way of displaying the output, especially when working with XML documents, as you will be able to view at once all the XML documents for all the rows selected. To configure the display of the output this way, make sure to change the display results option under tool settings. From the Command Editor choose Tools > Tools Settings > Command Editor. Then uncheck the box Display results for a single query on the Query Results page. If you do not change this configuration, you will receive your output in the Query Results view, as shown in Figure 4.20. To view a given XML document, click the button with the three dots.

For non-XML columns, you can directly perform updates by clicking on the corresponding value and making the desired change in the Query Results view. To add or delete an entire row, click the Add Row or Delete Row button respectively. None of these changes are permanent unless you click on the Commit button. If you want to cancel your changes, click on the Roll Back button.

If you choose either of the other two options that produce the access plan, a tool called Visual Explain is invoked. The next section explains this tool in more detail.

Figure 4.19 The Commands view in the Command Editor

4.2.2.1 Visual Explain

DB2 provides an SQL and XQuery facility that stores in “explain” tables detailed information about how DB2 will access the data to satisfy a query request. For example, the information in these explain tables may show that DB2 will scan the table sequentially, or it may show that the data will be accessed through the indexes associated to the table. The method to access the data that DB2 chooses is called the access plan, and the particular component of DB2 that makes this decision is called the DB2 optimizer, which can be considered the “brain” of DB2. To analyze the access plan, you can use text-based tools like the db2exfmt and the db2expln command-line tools; however, it is often useful to display this information in a graph. Use Visual Explain to graphically display an access plan for a query.

You can invoke Visual Explain from the Command Editor. Simply enter an SQL or XQuery statement in the Command Editor’s Command Input Window, and then press either the Execute and Access Plan button or the Access Plan button. Figure 4.21 shows the Visual Explain output of the query select * from sales. In this particular example, the ARFCHONG.SALES table is accessed using a table scan (TBSCAN) with a cost of 7.65 timerons. The smaller “Over-view” window is useful when you want to navigate through a large explain. Clicking on a partic-ular section of the overview window will take you to a corresponding section in the larger window. Visual Explain can also be invoked from the Control Center; see Chapter 17, Database Performance Considerations, for more details.

Figure 4.20