Suppose you want to protect your system from bad queries by preventing users from running full table scans. For example, your company call center logs details about the calls in a call history table, which has grown very large. You want to prevent users from running full table scans on this particular table.
-
Select a ruleset to edit.
- Open the Workload Designer portlet and the Workload Designer view.
- From the For System list, select PROD1.
- In the local Working section, click the ruleset name, BadQueries.
-
Create a filter.
- From the ruleset toolbar, click Filters.
- Next to Filters, click .
- Enter the name of the filter, in this case Full Table Scan.
- Click Save.
-
Add classification criteria for the filter to apply to queries that attempt to run full table scans on the table CallHistoryTable.
- Click the Classification tab.
- From the Add Classification Criteria list, select Target.
- Click Add. The Edit Target Criteria dialog box appears.
- From the Target Type list, select Table.
- From the Database list, select the database that has the call history table: History.
- Under Table, type the name of the table you want to prevent full table scans on: CallHistoryTable.
- Click Include. The table shows up in the Selected list under Included with the name History.CallHistoryTable.
- From the Selected list, click next to History.CallHistoryTable. The Edit Criteria dialog box appears.
-
Select the criteria Full Table Scan, click Include, and click OK.
- Click OK.
- Click Save.
-
Activate the ruleset.
- Go back to the Workload Designer view.
- For the BadQueries ruleset, click and select Make Active.
-
[Optional] Review the queries rejected according to the criteria you specified.
- Open the Workload Monitor portlet.
- Click in the portlet frame and select Settings.
- Click the Systems tab.
- From the Systems list, select PROD1.
- Click OK.
-
In the Dynamic Pipes View, click on the number below the request status icon for system filters.
- Review the summary table to identify the queries rejected by this filter. The Reason column includes For Rule Name 'Full Table Scan'.