Excluding Multiple SQL Statements from Charts

danc

I'm aware of the ability to exclude statements by using individual hash ids, and additionally by using the quickpoll filter to exclude statements from being collected.  However, I'm looking for a way to exclude a set of queries based on a string being present in them - I've got a series of queries that aggregate data across databases (100+) on a server, and each query shows up as a separate hash in DPA.  Trying to exclude these one by one is not something I want to do, and was hoping there was a way to exclude them using a wildcard on the sql text - something like 'and not like '%aggregationTable%'.

I don't necessarily need to exclude them from being collected, but I would like to exclude them from the charts, to prevent skewing of the data.

any help would be appreciated

thanks

Dan

darichar

You could periodically add those SQLs to the ignite.CON_EXCLUDED_SQL table, and use a DPA alert to execute the code to do the insert. Here is the SQL that worked for me. Give this a try and let me know if it does what you want.

Note: DPA will fill in the #DBID# value with the proper value when it executes the alert.

SQL Text

insert into ignite.CON_EXCLUDED_SQL (DBID, SQLHASH, EXCLUDE_DATE)

select #DBID#, st.H, CURRENT_TIMESTAMP

from ignite.CONST_#DBID# st

left outer join ignite.CON_EXCLUDED_SQL e on e.SQLHASH=st.H and e.DBID=#DBID#

where st.ST like '%<your exclude text here>%'

and e.SQLHASH is null; -- ignore SQLs that already exist in the exclude table

select 1; -- run this so the alert returns some value

Alert Definition

Create a custom alert and select the Custom SQL Alert - Single Numeric Return type. Fill in the alert with the values you need and paste the above SQL into the SQL text field including the "select 1" at the end:

• You can execute as frequently as 1 min or whatever you need.
• The alert will never actually fire, it's just used to execute the above SQL periodically.
• The Execute Against value should be Repository.
• Put something like "N/A" in for the Units value
• Set the Min value for the High threshold to a value of 2. Since the code always returns a value of 1 this will make sure it never fires.

If you have questions let me know.