You can capture SQL statements from
the following locations.
Common prerequisites
for capturing
For each location
that you capture from, you must meet these prerequisites. Some locations
have additional prerequisites.
- In the Data Source Explorer, connect to
the DB2 database or subsystem, if the connection is not already open.
- Ensure that the DB2 database or
subsystem that the SQL statements run against is configured for tuning
single SQL statements.
Locations outside the workflow assistant
You can capture SQL statements from these locations
without opening the workflow assistant first.
- SQL editor or routine editor
- Right-click anywhere in the editor and select Start
Tuning.
Note: If a statement that you capture from a routine
editor contains local variables, those variables are converted to
typed parameter markers during the capture process.
- Data Source Explorer
- Right-click any of these objects
and select Start Tuning.
Restriction: To capture from these sources on DB2 for z/OS,
you must have the SELECT privilege on the system catalog.
- Package
- SQL stored procedure
- Trigger that uses compiled SQL statements; in other words, a trigger
that is associated with a package
- User-defined function with compiled SQL statements; in other words,
a UDF that is associated with a package
- View
Locations inside the workflow assistant
To capture SQL statements from the following sources,
you must first follow these steps to open the workflow assistant:
- In the Data Source Explorer, right-click a connection and select Connect,
expand the connection, right-click the data server, and select . The workflow assistant opens to the Capture section.
- On the left side of the Capture section,
select the location of the SQL statement that you want to tune. Follow
the instructions in the page that opens. You can click the ? icon
to the right of the title for information about the page.
You can capture from the following locations:- Type or paste in a statement
- Select Input Text on the left side of the Capture section.
- File
- Select File on the left side of the Capture section.
- SQL category
Important: The SQL Category view is deprecated in
IBM Data Studio full client and administration client, Version 3.1.1.
By
using SQL categories, you can organize and save SQL statements that
you want to return to later for tuning. SQL categories do not behave
like Query Tuner projects, however. SQL categories do not save any
of the work that you do in the workflow assistant.
- To open the SQL Category view, select . In the Show View window, expand Query
Tuner and select SQL Category.
- XML file that defines a query workload
- Even if the database or subsystem that you are connected to does
not have an active license for tuning query workloads, you can tune
an SQL statement that is within the definition of a query workload.
- Optim™ Performance Manager
repository
- For prerequisites and instructions for capturing from this source,
see Capturing SQL statements from Optim Performance Manager.
- Sources in DB2 for z/OS
- Catalog plan or packages
- Your authorization ID must meet the
following prerequisites to capture from this location:
- SELECT privilege on the following tables:
- SYSIBM.SYSPACKAGE
- SYSIBM.SYSPACKLIST
- SYSIBM.SYSPLAN
- SYSIBM.SYSSTMT
- SYSIBM.SYSPACKSTMT
- SYSIBM.SYSPACKDEP
- SYSIBM.SYSPLANDEP
- EXECUTE privilege on the basic packages:
- AOC5OADM
- AOC5OEPJ (DB2 for z/OS Version 8)
- AOC5OEPK (DB2 for z/OS Versions 9 and 10)
- AOC5ONPT
- AOC5OPKG
- DSN_FUNCTION_TABLE table
- Your authorization ID must meet the
following prerequisites to capture from this location:
- SELECT privilege on the following tables:
- DSN_FUNCTION_TABLE
- SYSIBM.SYSPACKAGE
- SYSIBM.SYSPACKLIST
- SYSIBM.SYSPLAN
- SYSIBM.SYSSTMT
- SYSIBM.SYSPACKSTMT
- EXECUTE privilege on the basic packages:
- AOC5OADM
- AOC5OEPJ (DB2 for z/OS Version 8)
- AOC5OEPK (DB2 for z/OS Versions 9 and 10)
- AOC5ONPT
- AOC5OPKG
- DSN_STATEMNT_TABLE table
- Your authorization ID must meet the
following prerequisites to capture from this location:
- SELECT privilege on the following tables:
- DSN_STATEMNT_TABLE
- SYSIBM.SYSPACKAGE
- SYSIBM.SYSPACKLIST
- SYSIBM.SYSPLAN
- SYSIBM.SYSSTMT
- SYSIBM.SYSPACKSTMT
- EXECUTE privilege on the basic packages:
- AOC5OADM
- AOC5OEPJ (DB2 for z/OS Version 8)
- AOC5OEPK (DB2 for z/OS Versions 9 and 10)
- AOC5ONPT
- AOC5OPKG
- PLAN_TABLE table
- Your authorization ID must meet the
following prerequisites to capture from this location:
- SELECT privilege on the following tables:
- PLAN_TABLE
- SYSIBM.SYSPACKAGE
- SYSIBM.SYSPACKLIST
- SYSIBM.SYSPLAN
- SYSIBM.SYSSTMT
- SYSIBM.SYSPACKSTMT
- EXECUTE privilege on the basic packages:
- AOC5OADM
- AOC5OEPJ (DB2 for z/OS Version 8)
- AOC5OEPK (DB2 for z/OS Versions 9 and 10)
- AOC5ONPT
- AOC5OPKG
- QMF
- Your authorization ID must meet the
following prerequisites to capture from this location:
- SELECT privilege on the following tables:
- Q.OBJECT_DIRECTORY
- Q.OBJECT_DATA
- Q.OBJECT_REMARKS
- EXECUTE privilege on the basic packages:
- AOC5OADM
- AOC5OEPJ (DB2 for z/OS Version 8)
- AOC5OEPK (DB2 for z/OS Versions 9 and 10)
- AOC5ONPT
- AOC5OPKG
- QMF HPO
- Your authorization ID must meet the
following prerequisites to capture from this location:
- SELECT privilege on these tables:
- Q.OBJ_ACTIVITY_DTL
- Q.OBJ_ACTIVITY_SUMM
- EXECUTE privilege on the basic packages:
- AOC5OADM
- AOC5OEPJ (DB2 for z/OS Version 8)
- AOC5OEPK (DB2 for z/OS Versions 9 and 10)
- AOC5ONPT
- AOC5OPKG
- SQL stored procedure
- To capture from this source, you must have the SELECT privilege
on the system catalog.
- Statement cache
- Your authorization ID must meet the
following prerequisites to capture from this location:
- EXECUTE privilege on the EXPLAIN STMTCACHE ALL statement, either
dynamically or through the SYSPROC.OSC_RUNSQL stored procedure:
- Dynamically: SYSADM authority is needed to run EXPLAIN for all
statements; otherwise, only statements with the same authorization
ID can be explained.
- By stored procedure: EXECUTE privilege is required for the SYSPROC.OSC_RUNSQL
stored procedure, which explains all statements.
- EXECUTE privilege on the basic packages:
- AOC5OADM
- AOC5OEPJ (DB2 for z/OS Version 8)
- AOC5OEPK (DB2 for z/OS Versions 9 and 10)
- AOC5ONPT
- AOC5OPKG
- Ownership of DSN_STATEMENT_CACHE_TABLE, or ownership of an alias
on owner.DSN_STATEMENT_CACHE_TABLE, and SELECT,
INSERT and DELETE privileges on the table.
- Sources in DB2 for Linux,
UNIX, and Windows
- Package cache
To capture from this source, you must have the
EXECUTE privilege on the user-defined function SYSPROC.MON_GET_PKG_CACHE_STMT.
If
you are using DB2 for Linux, UNIX, and Windows, Version 9.5, you must
have SYSMON authority. You must also have EXECUTE privilege on the
stored procedure SYSPROC.SNAP_GET_DYN_SQL_V95.
If you are using
DB2 for Linux, UNIX, and Windows, Version 9.7 Fix Pack 1 or later,
you must have the EXECUTE privilege on the stored procedure SYSPROC.EXPLAIN_FROM_SECTION.
- Packages
- SQL stored procedures