Description of source type information

The tables in this section describe values that you enter when defining the type of source that holds the SQL to be extracted for the Workload Access Path Compare, Workload Index Advisor, or Workload Statistics Advisor components.

The following table lists the applicable worksheet that each source requires.

Source	Table
Db2 catalog	Information for Db2 catalog sources
Apptune trace data sets	Information for Apptune trace data sources
DBRM libraries	Information for DBRM libraries
Performance Advisor database	Information for Performance Advisor Database sources
SQL text data sets	Information for SQL text data set sources
Statement CACHE	Information for Db2 statement CACHE sources

The following table describes the information that you enter when identifying Db2 catalogs as the source of the extracted SQL.

Field name	What you enter	Description
SQL Workload Source Extract from Db2 catalog
Specify SSID for Extract	Specify the subsystem ID of the Db2 system from which you want to extract SQL text.	The Db2 catalog on this subsystem is used as the source when performing the Extract and Explain processes for the workload comparisons or the Extract and Recommindex processes for the Index Advisor.
LAST(L)/ALL(A) option (SPE2010)	Specify the LAST(L)/ALL(A) option to analyze objects in packages.	The LAST(L)/ALL(A) option enables you to either search all packages by using the ALL option or search recently bound version of package by using the LAST option. This option is applicable to the following advisors: Workload Access Path Compare Workload Index Advisor Workload Statistics Advisor

The following table describes the fields that you enter when identifying Apptune trace data sets as the source of the extracted SQL.

Field name	What you enter	Description
SQL Workload Source Extract from Apptune Trace Data
Specify SSID for Extract	Specify the Db2 subsystem on which the SQL text was executed.	The Workload Compare and Index Advisor feature use the Apptune archived trace data sets for this subsystem as the source when performing the Extract and Explain processes for the workload comparisons. You can use a wildcard character in this field.
Ignore literals in dynamic SQL	Specify whether to ignore literal values for numbers and strings in dynamic SQL when determining matches.	Enter Y (Yes) to ignore literal values in the text of a dynamic SQL statement. All dynamic SQL statements that are otherwise the same are treated as the same SQL statement. Enter N (No) to honor literal values. All dynamic SQL statements that are otherwise the same but have different literal values are treated as different SQL statements. Workload Access Path Compare and Index Advisor can ignore literal values even if you did not choose this option when Apptune collected the data in the Apptune trace records.
Time range for extract (BMC.DB2.SPE2110)	Specify the time format, start date, current date, and the duration for the Apptune trace record collection.	Use the Current date and Time Format displayed as reference to enter the Start date. Start date specifies the start date and time from which you can fetch the Apptune trace records. Enter the Duration to specify the number of days and time for which you can collect the Apptune trace records.
SQL Workload Source - Archive Trace Data
Action	Specify S to select a trace set in the list or A to add an additional archived trace data set.	Workload Compare automatically selects adjacent data sets when those data sets contain SQL data from the same workload interval. If you add new archived trace data sets, the added data sets exist only in the extraction and are not saved in the Apptune archive directory.
SQL Workload Source - Add Archive Trace Data
DSN	Specify the name of an Apptune archived trace data set to be used for the extraction.	The added data sets exist only in the list for the SQL extraction and are not saved in the Apptune archive directory. You might need to add an additional archive trace data set if it was not registered in the COPYDIR at the time that it was created.

The following table describes the fields that you enter when identifying DBRM libraries as the source of the extracted SQL.

Field name	What you enter	Description
SQL Workload Source Extract from DBRM Library
Specify SSID for Explain	Specify the Db2 subsystem on which the SQL text from the DBRM library is to be explained.	Objects do not have to be bound on this subsystem, but the tables must exist.
Table qualifier	Specify a table qualifier.	This table qualifier is used to qualify any unqualified SQL text in the Explain process.
Specify PDS with member name or wildcard member	Specify the data set and member name of the DBRM library.	You can use wildcards in the member name. Example MYUSERID.TEST.DBRMLIB(TEST) MYUSERID.PROD.DBRMLIB(APROD) MYUSERID.PROD.DBRMLIB() The wildcard character can be anywhere within the name (leading, trailing, or in the middle).

The following table describes the information that you enter when identifying a Performance Advisor Database (PADB) as the source of the extracted SQL.

Field name	What you enter	Description
Extract SQL for Performance Advisor Database
PADB Table Creator¹	Specify the name of the owner or creator that was used when the Performance Advisor Database was created.	If the creator name has changed from the default value, edit the field to contain the updated value.
PADB STMT Text Table Name¹	Specify the SQL text table name for the Performance Advisor Database	The default name is STMT_TEXT. If the SQL text table name has changed from the default value, edit the field to contain the updated value.
PADB Tables¹	Type S next to the table to use as the source for the SQL text extract process and edit the name of the table.	If the name of the table has changed from the default value, edit the table name to the updated value. You can choose from the following tables: DAILY_STMT_STATISTICS -- The daily STMT statistics table contains SQL statement statistics per statement per day. WEEKLY_STMT_STATISTICS -- The weekly STMT statistics table contains SQL statement statistics per statement per week. MONTHLY_STMT_STATISTICS -- The monthly STMT statistics table contains SQL statement statistics per statement per month. DAILY_STMT_SUMMARY -- The daily STMT summary table contains SQL statement statistics per statement per day. This table contains a subset of columns from the Daily STMT Statistics table and generally loads faster. WEEKLY_STMT_SUMMARY -- The weekly STMT summary table contains SQL statement statistics per statement per week. This table contains a subset of columns from the Weekly STMT Statistics table and generally loads faster. MONTHLY_STMT_SUMMARY -- The monthly STMT summary table contains SQL statement statistics per statement per month. This table contains a subset of columns from the Monthly STMT Statistics table and generally loads faster.
Db2 SSID where the PADB resides	Specify the subsystem ID of the Db2 subsystem where the Performance Advisor Database is installed.	You cannot use wildcards in this field.
Db2 SSID where the data was collected	Specify the subsystem ID of the Db2 subsystem from which statement text and statistics were originally collected.	This value is used as a search parameter for the data in the Performance Advisor Database. You can use a wildcard character (* or %) as part of the name (for example, DBN*).
Extract parameters for Performance Advisor Database
Last N time	Specify the number of time periods (from 1 through 10) for which you want to retrieve statement statistics and SQL text.	The time periods correspond to workload intervals in the Performance Advisor table that you selected. The default value of 1 retrieves the statement statistics and SQL text for the last workload interval time. Units of measurement are as follows: DAILY_STMT_STATISTICS - n days WEEKLY_STMT_STATISTICS - n weeks MONTHLY_STMT_STATISTICS - n months DAILY_STMT_SUMMARY - n days WEEKLY_STMT_SUMMARY - n weeks MONTHLY_STMT_SUMMARY- n months
Approximate Start Date	Specify the approximate start date to use to locate the rows in the Performance Advisor Database table.	The Extract process uses the workload interval time closest to the specified date. The specified time is greater than or equal to the workload interval time (INTVTIME) in the specified table. Valid date formats are: mm/dd/yy yy-mm-dd mm/dd/yyyy yyyy-mm-dd
For Duration	Specify the duration that defines the ending time for extracting data	The duration label that is displayed depends on the table that was selected on the previous panel. For example, if you selected the DAILY statistics table, the duration label is DAYS. If you selected the WEEKLY statistics table, the duration label is WEEKS. Note If you omit both the LAST N and Approximate Start Date field from the batch parameters, the default value is LAST 1 and the time period depends on the table selected (as described previously).

^{1 If the Performance Database names for objects have changed from those distributed by BMC, you can edit the names on the panel by typing the new name in place of the BMC name. If the length of the new name exceeds the space allowed on the panel, press F6 (field-level zoom) on the table name field to enter the new name on the zoomed panel.}

The following table describes the fields that you enter when identifying a SQL text data set as the source of the extracted SQL.

Field name	What you enter	Description
SQL Workload Source Extract from SQL Text Data Set
Specify SSID for Explain	Specify the Db2 subsystem on which the SQL text from the data set is to be explained.	The objects do not have to be bound on this subsystem.
Table qualifier	Specify the table qualifier to use to qualify any unqualified SQL text in the Explain operation.	You can change the table qualifiers for different SQL statements referencing unqualified table names with the SQL text data set by including the following statement prior to the SQL text: -- TBQUAL = qualifier The Extract process uses the qualifier for the SQL statements that follow until it encounters another TBQUAL statement or reaches the end of the file. To reset the table qualifier to the value specified on the panel and remove the qualifier from use for subsequent statements, specify the keyword QUAL as the qualifier value: -- TBQUAL = QUAL
Specify Data Set Name	Specify the data set name of the file that contains the SQL text.	Include the member name if the data set is a PDS (for example, MYUSERID.TEST.SQL(TEST)).
File Format	Specify the file type of the data set from which SQL text is to be extracted.	Valid values include: 1 (COBOL) - A COBOL program source code file that contains static SQL 2 (PL/I) - A PL/I program source code file that contains static SQL 3 (C) - A C/C++ program source code file that contains static SQL 4 (Assembler) - An assembler language source code file that contains static SQL 5 (FORTRAN) - A FORTRAN program source code file that contains static SQL 6 (SQL text only) - An SQL text file that contains SQL statements terminated by a semi-colon (;) The default format is SQL text.

The following table describes the information that you enter when identifying the Db2 statement cache as the source of the extracted SQL.

Field name	What you enter	Description
SQL Workload Source Extract from Db2 Statement CACHE
Specify SSID for Extract	Specify the subsystem ID of the Db2 system from which you want to extract SQL text.	The Statement cache on this subsystem is accessed as the source using the DSN_STATEMENT_CACHE_TABLE of the user ID associated with the runner of the batch job. This Explain table is created if the DSN_STATEMENT_CACHE_TABLE does not already exist. The runner of the batch job must have authority to issue an EXPLAIN STMTCACHE ALL statement.

Description of source type information

On this page