Managing Db2 and DDF Data Collection

Strobe for Db2 automatically starts as soon as Strobe detects Db2 activity and is initially configured to report on all types of Db2 application data.

Certain considerations might require you to change the Strobe for Db2 configuration. What level of data detail do you want Strobe to collect on your Db2 environment? You can specify whether you want to see the total CPU time consumed by a set of queries or procedures or examine them on an individual basis.

Strobe automatically invokes Strobe for Db2 and some of its components while other components must be manually activated. This chapter describes how to make these specifications for Strobe and how to limit the types of data Strobe collects. You can find detailed instructions for submitting measurement requests in the Strobe/ISPF Online Tutorials and in the Using-Strobe-to-measure-online-applications-and-batch-programs.

Collecting Execution Count and Average Statement Information

Strobe identifies the number of times a SQL statement executes during a measurement session, the average service time for each SQL statement, and in_Db2 execution CPU utilization for the CPU Usage reports, which includes any parallel processing in_Db2 execution CPU. This information is collected by default, and is displayed in these reports:

  • SQL CPU Usage Summary Report
  • SQL Wait Summary Report
  • CPU Usage by SQL Statement Report
  • Wait by SQL Statement Report.

SQL wait reporting is limited to SQL statements that were active during any wait sample of the measurement. SQL CPU reports contain in_Db2 execution CPU utilization for all SQL statements that executed during the measurement. This includes any CPU used in parallel threads. Stored procedure SQL statements are not calculated in reports unless a WLM managed stored procedure address space is measured. All iStrobe SQL reports will calculate elapsed and CPU time for stored procedures and triggers.

Because Strobe automatically collects execution count and service time data, you do not need to specify any additional parameters when measuring DBRMs.

Important

The  SQL Agent must be active to collect SQL data. For more information, see Installing.

Disabling the Collection of Db2 Data

Every time a measurement request for a Db2 application is issued, Strobe for Db2 starts. If you want to disable Strobe for Db2 (which disables both Db2 module attribution and Db2 data collection), you must specify that it should not start.

To set these parameters:

  1. Enter Y in the DATA COLLECTORS field in the SELECT ADDITIONAL PARAMETERS section of the Strobe - Add Active or Strobe - Add Queued panel.

  2. Enter N in the Db2 field on the Strobe - Data Collectors panel (see the following figure).

    Strobe

     - Data Collectors Panel
    image2021-8-11_12-15-50.png

With Strobe command language, specify the NODB2 operand when you submit an ADD command.

Important

Specifying NODB2 disables all Db2 data collection, including Db2 module attribution, SQL attribution, SQL statement count, and service time information.

Limiting the Collection of Db2 and DDF Data

Strobe uses the IBM Db2 instrumentation facility interface (IFI) to collect trace data for Db2 processing. Strobe allows you to enter filtering constraints for IFI if you want to limit the amount of Db2 trace data Strobe collects for SQL. You should be familiar with IFI and its command requirements before using this feature.

If you enter an N in the SQL CAPTURE Options Db2 field on the Strobe - Db2/DDF Data Collector Capture Option panel for local SQL, no Db2 Trace will be started. With Strobe command language, specify the DB2=NOCAPTURE operand when you submit an ADD command.

Important

Specifying N in the SQL CAPTURE Options Db2 field disables SQL statement attribution, including SQL statement count. Module attribution of the Db2 modules will still occur.

If you enter A in the LOCAL/DDF Trace Filter field on the Strobe - Db2/DDF Data Collector Capture Option panel for local SQL, an appropriate filter is automatically selected for you based on the type of application that is being measured and the Db2 release

.

If you enter F in the LOCAL/DDF Trace Filter field on the Strobe - Db2/DDF Data Collector Capture Option panel for local SQL, the Db2 Capture Filtering panel is displayed.

If you enter N in the LOCAL/DDF Trace Filter field on the Strobe - Db2/DDF Data Collector Capture Option panel for local SQL, an unfiltered trace is used to collect the data for Db2 processing.

Important

Options A and F limit the number of concurrent  Db2 measurements, per Db2 subsystem, to a maximum of eight. If you have other traces active, this number may be less. DDF does not have an A (automatic filtering) option. It is unnecessary because the trace is always constrained to just the activity from the DDF address space. However, you can add further constraints by entering F in the DDF Requester field on the  - Db2/DDF Data Collector Capture Option panel.

Db2 Capture Filtering Panel

image2021-2-5_17-39-57.png

If you enter F in the DDF Requester field on the Strobe - Db2/DDF Data Collector Capture Option panel for remote SQL, the DDF Requester Filtering panel (See the following figure) will be displayed.

DDF Requester Filtering Panel

image2021-2-5_17-41-22.png

These panels allow you to enter valid Db2 values for the various fields to be passed to IFI as constraint filters.

Notes:

  • You must surround values containing lowercase characters with two pairs of single quotes in order to retain the lowercase. For example, entering Db2-value will retain the lowercase of value.
  • Do not use the following IFI keywords when entering Freeform Filter values, because Strobe already uses them when passing the text:
    • BUFSIZE
    • CLASS
    • COMMENT
    • DEST
    • TDATA.

For DDF Requesters, you may also specify the source of remote SQL by entering a Y in the Display DDF Requesters field on the DDF Requester Filtering panel. See Specifying the Source of Remote SQL for more information.

 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*