SQL Query Optimization Messages › Execution-Time Messages

Execution-Time Messages

Scope

Execution-time messages report query execution statistics for the following statement types:

• OPEN and FETCH CURSOR
• INSERT (single row and searched)
• UPDATE (positioned and searched)
• DELETE (positioned and searched)

Purpose

You can use these execution statistics to determine:

• Which statements are using the most resources.
• Determine which query execution processes within a statement that is using the most resources.
• Compare bind-time resource estimations with actual performance.
• Compare resource usage between trial versions of a query.

Operation

Execution-time messages are written to the SYSADM.SYSMSG table with sequence numbers higher than bind-time messages for a plan. A maximum of 32767 messages can be written for each statement of a plan.

Execution-time messages may be requested at either the summary or detail level:

• Summary-level messages are written when a plan is closed.
• Detail-level messages are written after the statement is executed or, for a cursor, when it is closed. For example, if you set plan option PLNCLOSE=T for a plan in a CICS transaction, these messages will be written for each CICS transaction or explicit COMMIT or ROLLBACK. To get a summary of all activity against the plan, use PLNCLOSE=R. This will cause the plan to only be closed when the SQL User Requirements Table is closed.

WARNING! Because of the number of rows inserted into the SYSADM.SYSMSG table by detail level or summary level CICS with PLNCLOSE=T, you may experience poor performance with these options.