Using top-n filters

You can use top-n filters to limit the number of rows that are returned by the CAE Server when viewing SQL text.

About this task

By using top-n filters when viewing SQL text to limit the number of rows that are returned by the CAE Server, you can improve performance and reduce the impact your activity browsing has on the CAE Server. Top-n filtering enables you to locate the most problematic SQL statement and using the smallest possible amount of system resources.

The reduction of overhead provided by top-n filters is on the order of one half to one third in the CAE Agent, due to the duplication and slight expansion of the records during translated to Java™ data structures. In the CAE Server, the reduction is more substantial because only a small fraction of the records reach the CAE Server.

To limit the number of rows returned by the CAE Server when viewing SQL text, select the column of interest from the top-n drop-down. You can choose the top-n column regardless of your current drill down location. If the top-n you select does not apply to the activity you are currently viewing in the data table, then an exclamation mark icon is displayed to alert you that top-n filtering does not affect the results of the current drill down. When you navigate to view SQL text, the selected top-n filter is applied. To specify that top-n should not be used for the next SQL drill down, select No TopN.

You can perform top-n filtering on any column or field that is visible in the details panel with the following exceptions:

  • Avg Elapsed
  • AvgX Elapsed
  • <% CPU>
  • Avg CPU
  • AvgX CPU
  • Avg Delay
  • AvgX Delay
  • <%Delay>
  • Avg GetPages
  • AvgXGetPages
  • <%UAT>
  • Buffer Pool Hit Ratio

The number of rows (n) that are returned by the top-n filter are controlled by the Rows drop-down, located beneath the data table. Available values are:

5
Returns the top 5 results.
25
Returns the top 25 results.
50
Returns the top 50 results.
100
Returns the top 100 results.
200
Returns the top 200 results.
500
Returns the top 500 results.
1000
Returns the top 1000 results.

The following limitations that apply to top-n filtering:

  • Limited interaction between top-n and table sorting - When you click a column header to sort in ascending or descending order, the sort is performed only for rows that are shown on the current page of the table. To sort based on all data, you must click the Fetch button or the Run button. The attribute that is being filtered on appears as the first column in the table.
  • Top-n filtering applies to only one display type - Top-n filtering applies only to SQL text summary displays in Activity Summaries (Operational) and the SQL text summary displays in Activity Summaries (Structural). These are sometimes referred to in the documentation as Instance Folder displays and are the closest CAE equivalent to Option 16 (SQL) in ISPF Summary displays.
  • Percent calculations - Percent values are calculated using the records that are returned by the CAE Server, not on all records that exist. This means the values returned from the top-n filter will always add up to 100%.

Procedure

  1. From any window within the CAE Browser Client, click Activity Browser.
  2. Select the appropriate Sources, Targets, and Intervals.
  3. From TopN list, select the top-n column of interest.
  4. From Rows drop-down list, select the number of results you want to receive by selecting the appropriate number (5, 25, 50, 100, 200, 500 or 1000).
  5. Select the SQL-S line command for the activity of interest, to drill down to display SQL text.
    The top-n results are shown in the data table.
    Note: When you select a top-n column on which to filter results, including those that are not currently displayed in the data table, the selected column is shown in the results as the first column in the table and is assigned a column title that matches the one listed in the property table detail display.