Locations from which you can capture an SQL statement

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.

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:

  1. In the Data Source Explorer, right-click a connection and select Connect, expand the connection, right-click the data server, and select Analyze and Tune > Start Tuning. The workflow assistant opens to the Capture section.
  2. 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 Window > Show View > Other. 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

Feedback