Selecting a data source

You can select your data source from any of the five tabbed pages presented on the Source page. Each data source type has its own set of required values that must be entered in order to properly access your data.

To select a single SQL statement:

  1. In the Input SQL statements text box, enter one or more SQL statements that you want to tune.
  2. Ensure the statement terminator character that you want to use appears in the field provided.
  3. Ensure the default schema for the database you selected appears in the field provided. This value is used for any unqualified objects found in the SQL statements you submit.
  4. Click Next to advance to the Scope page.

To select queries stored in a local file:

  1. Click the Local File tab.
  2. Click Browse And Upload Files.
  3. From the File Upload box that appears, navigate to the file you want to load, select it and click Open.
  4. Ensure the values are correct for the following properties:
    • Statement terminator
    • Encoding type
    • Default schema
    • Maximum number of statements (between 1 and 10,000)
  5. Click Next to advance to the Scope page

To select queries from the dynamic statement cache:

  1. Click the Statement cache tab.
  2. Use the provided search filters to narrow down qualified SQL statements.
    Note: If the IFCID 0318 trace function is turned on, filtering by dynamic statement cache statistics is also supported. Refer to the DB2 Knowledge Center topic, Capturing performance information for dynamic SQL statements for more information on how to externalize statement cache statistics for performance analysis.

    More options

    Expanding this panel provides you with more options for capturing data from the dynamic statement cache.

    • Enable the Using the SYSPROC.OPT_RUNSQL option to enable this stored procedure. Doing so allows you to capture SQL statements from dynamic statement caches and store them in the DSN_STATEMENT_CACHE_TABLE table.
    • Enable the Capture from data sharing option if you are working in a data sharing environment and want to select data sharing group members as your data source, and collect statement data from them for tuning
  3. Click Next to go to the Scope page, from which you can sort any gathered statements by GROUP_MEMBER.

To select statements from a package or plan:

  1. Click the Packages and plans tab.
  2. Click the option button that is associated with the capture you want to do (package or plan).
  3. Use the provided search filters to narrow down qualified SQL statements.
  4. In the field provided, enter the maximum number of statements to capture.
  5. Click Next to advance to the Scope page

To select statements from a user-defined repository:

  1. Click the User defined repository tab.
  2. In the fields provided, enter the qualifier and name of the table or view from which you want to capture runtime metrics or SQL text.
    Note: The text of the SQL statements and the runtime metrics can be in more than one table. If this is the case, you will need to create a view that joins the tables on an ID that is unique to each set of runtime metrics, and you must have the SELECT privilege on that view.

    For example:

    CREATE VIEW
    .(
    SUBSYSTEM, PLANNAME, COLLID, PACKNAME, VERSION, CONSISTOKEN, OWNER, TEXT, ID, SEQNO,
SCHEMA, STARTINTERVAL, ENDINTERVAL, SECTNO, STMTNO, EXECCOUNT, CPUTIME, ELAPTIME, NGETPAGE,
METRICID) AS ( SELECT 'db2subsys', 'plannam',A.COLLID, A.NAME, C.VERSION, C.CONTOKEN, C.OWNER,
A.STATEMENT, A.STMT_ID, A.STMT_ID, C.OWNER, TIMESTAMP('2015-06-15 13:05:12'), TIMESTAMP('2015-06-15 14:05:12'),
A.SECTNO,A.STMTNO,1, -1,-1,-1,-1 FROM SYSIBM.SYSPACKSTMT A, SYSIBM.SYSPACKAGE C where
A.COLLID=C.COLLID AND A.NAME=C.NAME AND C.NAME='AOC5OADM') ;
    where
    • SUBSYSTEM is the Db2® subsystem from which the runtime metrics and SQL statements were collected [VARCHAR(128)]
    • PLANNAME is the name of the plan [VARCHAR (128)]
    • COLLID is the collection ID of the Db2 plan or package [VARCHAR (128)]
    • PACKNAME is the name of the package [VARCHAR (128)]
    • VERSION is the version identifier of the package. The value is an empty string for a trigger package with TYPE='T', and for a package created using the BIND PACKAGE command that is the initial version of the package (TYPE='blank').[VARCHAR (122), NOT NULL]
    • CONSISTOKEN is the consistency token for the DBRM or Db2 package [CHAR (8)]
    • OWNER is the primary authorization ID used to run the statement [VARCHAR (128)]
    • TEXT is all or part of the SQL statement [CLOB (2M), NOT NULL]
    • ID is a unique value, used to identify fragments of the statement spread across different rows of a table (INTEGER)
    • SEQNO is a to-be-deprecated value (INTEGER)
    • SCHEMA is the value of the CURRENT SCHEMA special register
    • STARTINTERVAL is the start time of the interval within which the runtime metrics were collected (TIMESTAMP)
    • ENDINTERVAL is the end time of the interval within which the runtime metrics were collected (TIMESTAMP)
    • SECTNO is the number of the section within the Db2 package where the SQL statement is located (SMALLINT)
    • STMTNO is the number of the SQL statement within the package (SMALLINT)
    • EXECCOUNT is the number of times that the SQL statement ran (BIGINT)
    • CPUTIME is the amount of CPU time that was needed to run the statement the number of times specified by the EXECCOUNT property (FLOAT)
    • ELAPTIME is the amount of Class 2 time that was needed to run the statement the number of times specified by the EXECCOUNT property (FLOAT)
    • NGETPAGE is the number of getpage requests that were issued (BIGINT)
    • METRICID is an ID, unique to the runtime metrics collected for the SQL statement, that is used to join a table of runtime metrics to a table of objects references by SQL statements in the repository database (INTEGER)

    If using object filters, you will need to create a view, for example:

    CREATE VIEW

    .( OBJ_QUALIFIER, OBJ_NAME, OBJ_TYPE, DBNAME, TBSPNAME, OBJ_METRICID)

    where
    • OBJ_QUALIFIER is the qualifier or schema of the referenced object [VARCHAR (128)]
    • OBJ_NAME is the name of the referenced object [VARCHAR (128)]
    • OBJ_TYPE is the type of object referenced [VARCHAR (20)]:
      • T (table)
      • I (index)
      • D (database)
      • R (table space)
    • DBNAME is the name of the database in which the referenced object is stored [(VARCHAR (20)]
    • TBSPNANE is the name of the table space in which the referenced object is stored [VARCHAR (20)]
    • OBJ_METRICID is a reference to the matching column in the repository database defined by the user (INTEGER)
  3. Click Next to go to the Scope page.