36 SQL Performance Diagnosis on IBM DB2 Universal Database for iSeries
3.3 Debug messages
Analyzing debug messages is another important tool for monitoring and tuning queries. When
doing so, keep in mind the following points:
The debug messages are no longer being enhanced (that is, no new messages are being
added for queries that go through SQE).
It is hard to tie a message to an SQL statement.
It is difficult to search through all of the job log messages.
There are multiple methods of directing the system to generate debug messages while
executing your SQL statements such as:
Selecting the option in the Run SQL Scripts interface of iSeries Navigator
Using the Start Debug (STRDBG) CL command
Setting the QAQQINI table parameter
Using Visual Explain
You can choose to write only the debug messages for one particular job to its job log. If you
want to use iSeries Navigator to generate debug messages, in the Run SQL Scripts window,
click Options → Include Debug Messages in Job Log as shown in Figure 3-7.
Figure 3-7 Enabling debug messages for a single job
Note: The information retrieved from an SQL package might not accurately reflect the
access plan used by the last execution of the SQL query. For example, if circumstances at
execution time cause the query to be reoptimized and the package or program is locked,
then a new access plan is dynamically generated and placed in the Plan Cache (for SQL
Query Engine (SQE) use). The version stored in the package or program is not updated.
Chapter 3. Overview of tools to analyze database performance 37
After you run your query, in the Run SQL Scripts window, select View → Joblog to view the
debug messages in the job log. In our example, we used the SQL statement shown in
Example 3-1 Example SQL statement
SELECT year, month, returnflag, partkey, quantity, revenue_wo_tax
WHERE year = 2002 and month = 6 and returnflag = 'R';
The detailed job log describes information that you can use to identify and analyze potential
problem areas in your query such as:
File join order
Open data paths (ODPs)
All of this information is written to the job log when under debug using the STRDBG
Figure 3-8 shows an example of the debug messages contained in the job log after you run
the previous query.
Figure 3-8 Job log debug messages
After you enable these settings for a particular job, only debug messages relating to queries
running in that job are written to the job log. You see the same debug messages with this
option as those explained later when the QAQQINI parameter MESSAGES_DEBUG is set to
*YES. You also see additional SQL messages, such as “SQL7913 - ODP deleted and SQL7959
- Cursor CRSRxxxx closed”, in the job log.
Select any of the debug messages displayed in the job log. Click File → Details to obtain
more detailed information about the debug message. Figure 3-9 shows an example of a
detailed debug message that is displayed.
By looking at the messages in the job log and reviewing the second-level text behind the
messages, you can identify changes that might improve the performance of the query such
Why index was or was not used
Why a temporary result was required
Join order of the file
Index advised by the optimizer
38 SQL Performance Diagnosis on IBM DB2 Universal Database for iSeries
Figure 3-9 Detailed debug message information
When running SQL interactively, either through a 5250-session or via the Run SQL Scripts
window in iSeries Navigator, you can also use the STRDBG CL command to generate debug
messages in your job log.
By setting the value of the QAQQINI parameter MESSAGES_DEBUG to *YES, you can direct
the system to write detailed information about the execution of your queries into the job’s job log.
To activate this setting through the Run SQL Scripts interface of iSeries Navigator, select the
QAQQINI table that you want to use as shown in Figure 3-10. Then, select the
MESSAGES_DEBUG parameter and change the parameter value as shown in Figure 3-10.
After you make the appropriate change, close the window, and in the Change Query
Attributes window, click OK to save your changes.
Figure 3-10 Enabling debug messages in QAQQINI
Remember: You also must run the Start Server Job (STRSRVJOB) CL command if your
query runs as a batch job.