This procedure describes how to select collection options and start a collection from an ISPF session.
This procedure describes how to select collection options and start a collection from an ISPF session. (You can also select options and start a collection using MVS modify commands. The same collection options are provided with each method. See the START(DTR) Command.) Only one
CA Detector® for DB2 for z/OScollection can be active for a selected DB2 subsystem. However, you can run collections concurrently on multiple DB2 subsystems.
By design, the following dynamic statement types are
notcollected: COMMENT, CONNECT, EXPLAIN, GRANT, LABEL, RELEASE, REVOKE, and SET.
Consider the following items before starting a collection:
- If you plan to enable any of the following collection options, a collection profile is required. Create the collection profile before starting the collection:
- Dynamic SQL Stats
- View By Keys
- Dynamic Exceptions
- Static Exceptions
- SQL Errors
- If you plan to externalize the collected data, a data store is required. You can use an existing data store that meets the data store compatibility requirements. You can also create a data store. If you plan to externalize SQL exceptions and errors, the Exception SQL and SQL errors options must be enabled when the data store is created.
Follow these steps:
- SelectInitialize SSID collectionon theCA Detector® for DB2 for z/OSMain Menu.The first Start Collection Display panel appears.
- Specify the collection options for interval time and time limit, collection externalization, and automatic collection. Press Enter.If you are starting a collection in a member of a data sharing group, specify Y in the Sysplex Interval Time field. You must use the sysplex interval to view the combined collection statistics of all members in a data sharing group. For more information about the sysplex interval, see Integrated Data Sharing Statistics.The next Start Collection Display panel appears.
- Specify the SQL collection and exception collection options:
Press Enter.A panel message verifies that the collection has started. Informational messages are also written to the Xmanager job log and the MVS syslog. These messages include the user ID of the person who started the collection. (If the collection was started by MVS modify command, the user ID is CONSOLE.)
- Triggered SQL CollectionSpecifies whether to collect SQL statistics for triggered SQL. This option also applies to SQL from native stored procedures and native SQL UDFs. These statistics do not include table and index data.This type of collection introduces slightly more overhead. If you do not have triggered SQL or you do not want to collect triggered SQL statistics, specify N.
- Plan Excl/Incl ListSpecifies whether to use the Plan Exclude/Include list to exclude or include plans from the collection. When this option is set to Y, the list is loaded from the specified collection profile. If any exception collection options are specified, the plans in the list are included or excluded.This processing can be activated even when the collection does not include any exception collection options.
- Standard ActivitySpecifies whether to collect static and dynamic SQL activity. To capture dynamic SQL, exceptions, and data based on user-defined keys, specify Y. To capture SQL errors only, specify N.
- Dynamic SQL StatsSpecifies whether to collect statistics for dynamic SQL text strings. When this option is enabled, you can collect the aggregate statistics for each unique dynamic SQL text string that is executed during the collection interval. This option is independent of SQL exception collection.These statistics do not include SQL host variables. To include that information, enable Dynamic Exceptions, Static Exceptions, or both on this panel.In the collection profile, enable dynamic SQL statistics collection in the include/exclude list entry.
- View By KeysSpecifies whether to collect data based on the values that are defined for the additional View By keys. When this option is enabled, more thread identification information is collected. This information lets you organize standard activity data by criteria such as user ID and correlation ID, in addition to the standard plan hierarchy view.This option is independent of the SQL exception and SQL error collection.In the collection profile, enable the appropriate thread information and connection types in the additional keys group.
- Dynamic ExceptionsSpecifies whether to collect data on dynamic SQL activity exceptions.If you want to externalize the data, the data store must be created with the exception SQL option enabled.In the collection profile, enable dynamic SQL exception collection in the global defaults and in the application group definitions.
- Static ExceptionsSpecifies whether to collect data on static SQL activity exceptions. When this option is enabled, more monitoring for SQL performance exceptions for static SQL statements is performed. If you specify Y, you must also specify Y for Standard Activity and Dynamic Exceptions on this panel.In the collection profile, enable static SQL exception collection in the global defaults and in the application group definitions.
- Exception cache sizeSpecifies the number of 1-MB blocks in the exception collection storage cache. This value can reduce the exception collection overhead at the cost of some additional storage. If 0 is specified, the exception cache is not allocated.Once this value is established, it can increase during times of high activity or can decrease with lower system activity.Limits:0-20
- SQL ErrorsSpecifies whether to collect data on SQL error activity. Specifying A instead of Y also collects data on warning activity. To capture SQL error text, select this option and the SQL Error Text option.In the collection profile, enter the maximum number of SQL errors to retain for an interval and for a single SQL code in the SQL error group. You can also exclude your choice of SQL error codes from collection.If you want to externalize the data, the data store must be created with the SQL Errors option enabled.
- SQL Error TextSpecifies whether to collect SQL error text data on SQL error activity.If you want to externalize the data, the data store must be created with the SQL Errors option enabled.
- Host variablesSpecifies whether to collect host variables. This option applies to SQL exceptions and SQL errors.To collect host variables, you must also enable one or more of the following options on this panel: Dynamic Exceptions, Static Exceptions, or SQL Error Text.
- Collection ProfileSpecifies the collection profile to use when any options that require a collection profile have been enabled.