Examine query optimizer debug messages in the job log

Query optimizer debug messages issue informational messages to the job log about the implementation of a query. These messages explain what happened during the query optimization process. For example, you can learn:

• Why an index was or was not used
• Why a temporary result was required
• Whether joins and blocking are used
• What type of index was advised by the optimizer
• Status of the job's queries
• Indexes used
• Status of the cursor

The optimizer automatically logs messages for all queries it optimizes, including SQL, CLI, ODBC, OPNQRYF, and SQL Query Manager.

Viewing debug messages:

To view the messages, put your job into debug mode using one of the following methods:

• Use the following command:

  STRDBG PGM(Library/program) UPDPROD(*YES)
  

  STRDBG places in the job log information about all SQL statements that run.

• Set the QRYOPTLIB parameter on the Change Query Attributes (CHGQRYA) command to a user library where the QAQQINI file exists. Set the parameter on the QAQQINI file to MESSAGES_DEBUG, and set the value to *YES. This option places query optimization information in the job log.

Pressing F10 from the command Entry panel displays the message text. To see the second-level text, press F1 (Help). The second-level text sometimes offers hints for improving query performance.

See Query optimization performance information messages and Query optimization performance information messages and open data paths for the specific meanings of the debug messages.