Business Intelligence

Appendix B — Tools for Analysis and Tuning

With a cost-based optimizer and a broad set of indexing choices, the right analysis tools are a necessity. On an iSeries server, a variety of tools are available for evaluating what the optimizer is choosing for implementation methods and how to influence its choices. Depending how much is known at the beginning of the analysis process, different tools may be selected, or a combination of tools and methodologies may be used. While the scope of this document does not include the utilization of the various tools, here is a list and brief description of five of the tools available:

Optimizer Feedback via "Debug Mode"
The DB2 UDB for iSeries optimizer is generous in providing feedback in the form of OS/400 messages. The messages will be written to the query's joblog. Users can initiate these messages by using the OS/400 command Start Debug (STRDBG), or the QAQQINI parameter "MESSAGES_DEBUG" with a value of "*YES".

Database Monitor
As part of DB2 UDB for iSeries, users have access to the Database Monitor. The monitor has the ability to collect summary data, as well as detail data on the query implementation plans and runtime attributes. Once set up, the monitor can track query plans and performance, either across the system or for a particular job. The analyst turns the monitor on and lets the system run normally. The monitor collects information about the system and the queries being run on it. When the monitor is turned off, the information is sent to a table that the analyst can then query. The monitor can provide information, such as: which queries are the longest running, which queries are building temporary indexes, and index (access path) suggestions from the optimizer. Once these queries are identified, the analyst can address any performance problems that these queries are causing. More information on a set of analysis queries can be found at: ibm.com/eserver/iseries/db2/dbmonqrys.htm

iSeries Navigator
iSeries Navigator is the graphical interface to an iSeries server and DB2 UDB for iSeries. iSeries Navigator is part of OS/400 and is delivered via Client Access Express. iSeries Navigator has been significantly enhanced over the last few releases. Through its GUI front end, experienced, as well as new, iSeries administrators can manage their systems and database effectively and efficiently.

Most important for application administrators and database analysts, however, is the improved capabilities to manage databases, run SQL statements, and monitor query performance. With iSeries Navigator, it is easy to display a list of schemas (libraries) and tables, authorize users, and even look at the contents of a table — all with a double click of the mouse.

An SQL script window is available from the File menu for running interactive SQL statements. While running SQL statements in the Script window, you can put your server job into "debug mode." This tells the optimizer to provide feedback in the joblog. From the SQL Script window, you can display the messages in the joblog and analyze the optimization plan of the query.

The Database Monitor interface lets you monitor query performance just by specifying a job and library name. Through iSeries Navigator, a summary or detailed monitor can be started and stopped, and the output analyzed with a standard set of reports. Database monitors started via the OS/400 command interface can be imported into iSeries Navigator and analyzed, as well. More information on iSeries Navigator can be found at: ibm.com/eserver/iseries/navigator/

PRTSQLINF
Print SQL Information is an OS/400 command that will print the query implementation plan contents from a high-level language (HLL) program or service program with embedded static SQL, or a SQL package object used with extended dynamic SQL requests. The output of the PRTSQLINF command is a spooled file. Browsing the spooled file output will show the query optimizer feedback for the SQL statements contained in the program or package.

SQL Explain information can also be requested for an object using iSeries Navigator if your operating system is at V5R2.

Given that a query plan can be regenerated dynamically, and the fact that the SQL Query Engine can store and use up to three different plans for the same SQL request, the PRTSQLINF information may not reflect the query plan that is being executed. You may have to make use of one of the other tools — such as the database monitors, debug messages, or Visual Explain — to see the plan you are interested in analyzing.

Visual Explain
Visual Explain (VE) is the latest tool used to understand and analyze query implementation plans and optimizer feedback. Visual Explain is part of iSeries Navigator and is used to render the data in a database monitor table into visual and textual information. The output is a combination of diagrams and text that "explains" the access plans of the query, optimizer recommendations, and database and system environments.

Visual Explain can render the plans of a query, with or without actually running the query to completion. The two options are: EXPLAIN and RUN AND EXPLAIN.