SQL Performance Diagnosis on IBM DB2 Universal Database for iSeries

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 3-1 Example SQL statement

SELECT year, month, returnflag, partkey, quantity, revenue_wo_tax

FROM veteam06.item_fact

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:

򐂰 Indexes

򐂰 File join order

򐂰 Temporary result

򐂰 Access plans

򐂰 Open data paths (ODPs)

All of this information is written to the job log when under debug using the STRDBG

command.

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

as:

򐂰 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.