From the SQL Outline view, you can
import and view InfoSphere® Optim™ Performance Manager performance
data that is imported either from a InfoSphere Optim Performance Manager repository database
or a file. From this view, you can also open tools that can help you
to tune SQL statements that are not performing well.
Before you begin
You must ensure that InfoSphere Optim Performance Manager is monitoring the
database on which the SQL statements are running. Also, the database
must be configured in InfoSphere Optim Performance Manager for
extended insight monitoring. For information about capturing performance
data with InfoSphere Optim Performance Manager,
see InfoSphere Optim Performance Manager data in the SQL Outline view
If you are importing
and viewing SQL performance data from an InfoSphere Optim Performance Manager repository database,
the workspace must have an Optim Performance
Manager profile
to the repository database For more information see Defining an Optim Performance Manager profile.
If you are viewing performance
data from a file, the file must contain InfoSphere Optim Performance Manager data that was exported
from IBM® Data Studio.
About this task
You import InfoSphere Optim Performance Manager performance
data either from a repository database or a file that contains exported
performance data. The imported performance data is saved in your local
workspace.
After you import the data, it is displayed in the SQL
Outline view. You can view other saved performance data
sets in the view and compare two sets of data.
The SQL
Outline view displays performance data only for the SQL
statements that are listed in the view. If performance data is imported
for SQL statements that are not listed, the data is not displayed.
Procedure
To import and view performance data in the SQL
Outline view:
- If the SQL Outline view is not open,
open it by right-clicking a project and selecting Data
Access Developmnet > Show SQL Outline.
- In the toolbar for the SQL Outline view,
click the arrow on the Show or Hide Columns button
(
) and select Performance Data.
- Select Performance Manager from
the Performance Data Type list.
- Click Import performance data
.
- On the Import Performance Manager Data window,
specify the name of the data set to save in the workspace.
You
can specify the name of a new or existing data set. If you specify
an existing data set, the existing data is replaced with the imported
data.
- Specify the source of information to import and display. Import from one of the following sources:
- A file that contains exported performance data
- Select From an exported Optim Performance Manager data
set.
- Click Browse to select a file and click OK.
The
file must contain InfoSphere Optim Performance Manager performance
data.
- An InfoSphere Optim Performance Manager repository
- Select From Optim Performance Manager.
- Specify the information to import and click OK.
Select
the Optim Performance
Manager profile
to specify the repository database that will provide the performance
data. The monitored database, aggregation interval, and time frame
determine the performance data that is imported.
When InfoSphere Optim Performance Manager captures and stores
the performance data, it aggregates the data based on the time the
data was captured. The aggregation levels are the time intervals at
which InfoSphere Optim Performance Manager aggregates
the data.
The aggregation level that you select changes the
available timeframe values that you can select. To improve data import
performance, select the highest InfoSphere Optim Performance Manager aggregation level
that lists the timeframe values for your time interval. For example,
if aggregation levels 3 and 4 both display the timeframe values for
the time interval of interest, select level 4.
Results
The information might take a few minutes to appear in the
view, depending the amount of data that is being imported from the
repository database and how frequently InfoSphere Optim Performance Manager collects data.
Note for literal substitution: If your application
is using pureQuery client optimization and you used the
sqlLiteralSubstitution property
when you captured SQL statements to a pureQueryXML file, SQL statements
that contain literal values are nested under the versions of those
statements that contain parameter markers.
For example, suppose
that pureQueryXML file for your application contains this SQL statement:
SELECT * FROM GOSALES.INVENTORY WHERE PRODUCT_NUMBER = ?
When
you import the performance data, the SQL statement runs with the literal
value 115110. In the SQL Outline view, you must
expand the statement that has the parameter marker to see the statement
that has the literal value.
You can take any of the following
actions while viewing performance data in the SQL Outline view:
- View the access plan for an SQL statement by right-clicking it
and selecting Open Visual Explain.
- Get recommendations for tuning an SQL statement by right-clicking
the statement and selecting Get Query Tuner Report,
or open the Query Tuner editor by right-clicking an SQL statement
and selecting Open Query Tuner. See Tuning SQL statements with InfoSphere Optim Query
Tuner.
- Save the current results by clicking
.
- If you want other people to be able to import the data into the
workbench on their computers, export the current results by clicking
.
- Import a set of performance data by clicking
.
Tip: To determine if performance data has been captured
for your SQL statements, you can view the data stored in the repository
database with InfoSphere Optim Performance Manager.
Log on to InfoSphere Optim Performance Manager and
connect to the monitored database. The SQL statements and performance
data are displayed as details on the Extended Insight Analysis
Dashboard.