Content

• Configuring SQL PA with ICAT
• Initiating SQL PA
• Accessing SQL PA reports
• Messages

Configuring SQL PA with ICAT

To activate SQL PA during the startup of OMEGAMON XE for DB2 PE, use the Installation and Customization Assistance Tool (ICAT) and mark it as autostart component, as shown on the following ICAT panel:

Requirement: If you specify 'Y' for SQL Performance Analyzer, you must also specify 'Y' for Performance Expert Client support.

For each affected DB2 subsystem, access the SQL Performance Analyzer configuration, as shown on the following ICAT panel. Enter the required configuration parameters. Use the ICAT online help for detailed information, if required.

Prerequisite: In order to utilize SQL PA functionality, you must activate the Performance Warehouse. SQL PA relies on the PWH framework for report generation and will not function if PWH is deactivated. From the previous ICAT panel, select option 7, 'Performance Warehouse configuration', and specify Activate Performance Warehouse ==> Y .

Initiating SQL PA

SQL analyses can be initiated from the following "Classic Interface" panels:

• Thread Activity -> Current SQL -> SQL Call Being Executed (ZSQL) -> SQL PA

• Resource Managers and Other DB2 Subsystem Information -> EDM Pool Information -> EDM Snapshot Dynamic SQL Cache Statistics (ZEDD3) -> SQL PA

• Near-Term History Information -> Thread History -> Thread History Dynamic SQL Calls (ZHTCALL) -> SQL PA

When an SQL PA Report is initiated, the following confirmation panel is shown:

Accessing SQL PA reports

SQL PA reports can be accessed through the Classic Interface - Main Menu (option "SQL PA Reports"). The following is an extract from the next edition of "Monitoring Performance from the OMEGAMON Classic Interface" and describes how to work with SQL PA reports.

19.0 SQL Performance Analyzer Reports

OMEGAMON XE for DB2 PE provides an interface to DB2 SQL Performance
Analyzer for z/OS (SQL PA), which lets you analyze former and current
SQL queries and report the results. Analyses can be initiated from the following panels:
SQL Call Being Executed, EDM Snapshot Dynamic SQL Cache Statistics, and
Thread History Dynamic SQL Calls. This topic describes how to
access the results from SQL PA, which are SQL Enhanced Explain reports,
SQL Query Limits reports, and SQL Trace information.

The output provided by SQL PA reports is based on SQL PA configuration
parameters that are applied by means of the Installation and
Customization Assistance Tool (ICAT). During the customization the
affected DB2 subsystems and Performance Warehouse options to be used by
SQL PA are determined.

When an SQL performance analysis is requested, the OMEGAMON Server
silently submits a batch job that captures the analysis data and puts it
into appropriate Performance Warehouse tables, from where it is
retrieved and reassembled and presented as an SQL PA report.

Note that SQL PA reports might be long. If you cannot page down to the end of a report, the "logical rows" session parameter value might be too low. Log on again, with the session parameter set to a higher value. You can set this parameter either on the OMEGAMON XE for DB2 PE Classic Interface panel or as a logon command parameter, for example:

logon applid(ipobd2c) data(lrows=9999)

19.1 SQL Performance Analysis: Available Reports

The SQL Performance Analysis: Available Reports menu lists currently
available SQL PA reports and lets you select a report, which gives
access to panels that display detailed information about the selected
report. You can also perform several maintenance actions on the listed
reports.



Navigation

You can scroll through the list using F7 and F8, or select a particular
list item and press F11 (Zoom) for additional information.

For additional options
* choose one of the options from the menu.
* use the PF keys.

Actions

You can refresh the list of current SQL PA reports by pressing Enter.

Use F11 (Zoom) to get access to the different report types (Enhanced Explain, Query Limits, Trace).

You can apply the following maintenance actions on a single SQL PA report by typing
the corresponding letter left to one of the listed reports. Note that
some actions are allowed only if you are the creator of the report.

D-Delete Creator only: Deletes a particular SQL PA report. Applicable if
the report status is READY, FAILED, or CANCELED.

C-Cancel Creator only: Cancels an active SQL PA invocation. Applicable
if the report status is RUNNING. (The status changes to CANCEL.
Keep hitting the Enter key until the status changes to
CANCELED.)

B-Scope to Public
Creator only: Marks the report as public so that other users
can see the report output. Applicable if the report status is
READY, FAILED, or CANCELED.

P-Scope to Private
Creator only: Marks the report as private so that other users
cannot see the report output. Applicable if the report status
is READY, FAILED, or CANCELED.

Fields

Rept # The unique report number (identical to the Performance
Warehouse log identifier).

Status The current status of the report.

SUCCESS The report has completed successfully.

RUNNING The report is currently running.

FAILED The job for this reported has failed, based on the
completion code from SQL PA.

CANCEL There is a pending Cancel request for this SQL PA
invocation.

CANCELED The report request was canceled.

Plan The plan from which the SQL was taken.

Package The package from which the SQL was taken.

SQL St# The SQL statement number.

Date The date when the report was run.

Time The time when the report was run.

Creator The creator of the report. The ID of the creator is dependent
on a couple of factors:
* If the signon security interface has been enabled, the
creator ID is the identification used by the user to sign
on.
* If the security interface has not been enabled, but signon
profiles are used, the creator ID is of the form OMUSERxx,
where xx is the two-character OMEGAMON profile name.
* If neither the signon exit nor signon profiles are used, a
default ID of OMINSTAL is used.

Note that users can only see reports that they have created or
reports that other users have marked as public.

Pub Indicator whether the report is currently public (Y) or private
(N). Public reports are viewable by any user. Private reports
are only viewable by the report's creator. Only the creator of
the report can switch the status between Public and Private.

19.2 SQL Performance Analysis: Enhanced Explain

The SQL Performance Analysis: Enhanced Explain panel displays an
Enhanced Explain report of the report selected on the SQL Performance
Analysis: Available Reports menu.



Navigation

You can scroll through the report using F7 (Up) and F8 (Down). F3 (Back)
returns to the SQL Performance Analysis: Available Reports menu.

You can select a different report type by choosing one of the options
from the menu.

19.3 SQL Performance Analysis: Query Limits

The SQL Performance Analysis: Query Limits panel displays a Query Limits
report of the report selected on the SQL Performance Analysis: Available
Reports menu.



Navigation

You can scroll through the report using F7 (Up) and F8 (Down). F3 (Back)
returns to the SQL Performance Analysis: Available Reports menu.

You can select a different report type by choosing one of the options
from the menu.

19.4 SQL Performance Analysis: Trace

The SQL Performance Analysis: Trace panel displays a Trace report of the
report selected on the SQL Performance Analysis: Available Reports menu.



Navigation

You can scroll through the report using F7 (Up) and F8 (Down). F3 (Back)
returns to the SQL Performance Analysis: Available Reports menu.

You can select a different report type by choosing one of the options
from the menu.

19.5 SQL Performance Analysis: SYSPRINT

The SQL Performance Analysis: SYSPRINT panel displays a SYSPRINT report
of the report selected on the SQL Performance Analysis: Available
Reports menu.



Navigation

You can scroll through the report using F7 (Up) and F8 (Down). F3 (Back)
returns to the SQL Performance Analysis: Available Reports menu.

You can select a different report type by choosing one of the options
from the menu.

19.6 SQL Performance Analysis: ANLSQL

The SQL Performance Analysis: ANLSQL panel displays the actual SQL
statement that was analyzed by SQL PA for the report selected on the SQL
Performance Analysis: Available Reports menu.



Navigation

You can scroll through the report using F7 (Up) and F8 (Down). F3 (Back)
returns to the SQL Performance Analysis: Available Reports menu.

You can select a different report type by choosing one of the options
from the menu.

19.7 SQL Performance Analysis: JOBERR

The SQL Performance Analysis: JOBERR panel displays diagnostics
information, if the SQL PA job of the report selected on the SQL
Performance Analysis: Available Reports menu failed.



Navigation

You can scroll through the report using F7 (Up) and F8 (Down). F3 (Back)
returns to the SQL Performance Analysis: Available Reports menu.

You can select a different report type by choosing one of the options
from the menu.

Messages

------------------------------------------------------------------------
KO2O1944I SQL PA ANALYSIS INITIATED (OSQPSQPI). REPORT=nnnnnn PLAN=pppppppp PACKAGE=kkkkkkkk

Explanation: An SQL PA report has been generated for the current SQL statement.

User response: None.

------------------------------------------------------------------------
KO2O1945I NO ACTIVE SQL STATEMENT TEXT FOUND. REPORT NOT INITIATED. (OSQPSQPI)

Explanation: An attempt was made to initiate an SQL PA analysis from
Active Thread display (panel ZSQL), EDM Snapshot (panel EDDM3), or Near
Term history (panel ZHTCALL.), but there was no active SQL statement
being displayed.

Reports can only be initiated when a current SQL statement is displayed.

User response: None.

------------------------------------------------------------------------
KO2O1946I PERFORMANCE WAREHOUSE BUSY OR NOT STARTED. PRESS ENTER TO RETRY REQUEST (SQPSQPI)

Explanation: An attempt was made to initiate an SQL PA analysis from
Active Thread display (panel ZSQL), EDM Snapshot (panel EDDM3), or Near
Term history (panel ZHTCALL.), but the interface was unable to initiate
the request with PWH.

User response: Ensure that PWH is properly configured for SQL PA using
ICAT. If PWH has been properly configured, hit Enter to retry the
request.

------------------------------------------------------------------------
KO2O1947W NO REPORTS FOUND FOR CURRENT USER (O2OSQP)

Explanation: Option V was selected to display a list of SQL PA reports,
but no reports were found that either had a creator ID equal to the
current user ID or had a scope of Public.

User response: None.

------------------------------------------------------------------------
KO2O1948I NO REPORT OUTPUT GENERATED FOR xxxxxxxx (O2OSQP)

Explanation: No output was generated for the file selected. xxxxxxxx
is one of the following: ANLREP, ANLQLM, ANLQTRC, ANLELOG. ANLLSQL,
or JOBERR.

User response: With the exception of JOBERR, refer to the appropriate
SQL PA documentation for information on how each file is generated.
JOBERR is only available when an SQL PA report has a status of FAILED.

------------------------------------------------------------------------
KO2O1949E PERFORMANCE WAREHOUSE REQUEST FAILED (O2OSQP). variable message text

Explanation:

ACTION IGNORED. REPORT NOT RUNNING
An attempt was made to cancel a report, but the report was no
longer in RUNNING status.

ACTION IGNORED. REPORT HAS NOT COMPLETED
An attempt was made to view, delete or modify the scope of a
report that has not yet completed.

SQL PA NOT CONFIGURED ON THIS SYSTEM
An attempt was made to initiate an SQL PA analysis from Active
Thread display (panel ZSQL), EDM Snapshot (panel EDDM3), or
Near Term history (panel ZHTCALL.), but the interface was
unable to initiate the request with PWH.

REPORT NOT WITHIN SCOPE
An attempt was made to alter a report by someone other than the
creator of the report. Only the creator of a report may modify
or delete it.

REQUEST=rrrrrrrr RETURN CODE=xxxxxxxx REASON CODE=xxxxxxxx
An internal error occurred during processing.

User response:

ACTION IGNORED. REPORT NOT RUNNING
None.

ACTION IGNORED. REPORT HAS NOT COMPLETED
Wait for the report to complete before performing the action.

SQL PA NOT CONFIGURED ON THIS SYSTEM
Ensure that PWH is properly configured for SQL PA using ICAT.

REPORT NOT WITHIN SCOPE
None.

REQUEST=rrrrrrrr RETURN CODE=xxxxxxxx REASON CODE=xxxxxxxx
Contact IBM support.

------------------------------------------------------------------------
KO2O1950E PWH/CAF SUBTASK FAILURE

Explanation: An internal error occurred during processing and an SVC
dump has been generated.

User response: Contact IBM support.