Description
This alert will watch for SQL statements that execute and contain a specific string. For example, if you think a specific table is no longer being used and you want DPA to let you know if it sees any SQL with that table name in it, you can use this alert.
Alert Definition
To create this alert, click on Alerts > Manage Alerts tab and create a Custom Alert of type Custom SQL Alert - Multiple Numeric Return. Configure the alert similar to below.
Note: When DPA executes this alert, it will substitute the proper data for the keywords #DBID# (database ID internal to DPA) and #FREQUENCY# (alert execution interval).
Alert Name: SQL Containing <your string>
Execution Interval: 10 MInutes
Notification Text: The following list of SQLs contained the string <your string>.
SQL Statement:
SELECT 'SQL Name: ' +ISNULL(n.NAME, sw.IZHO)+' - SQL Text: '+SUBSTRING(st.ST, 1, 40) AS SQLNameText, SUM(QP)/100 AS timesecs
FROM ignite.CONSW_#DBID# sw
INNER JOIN ignite.CONST_#DBID# st ON st.H = sw.IZHO AND st.P = 0
LEFT OUTER JOIN ignite.CON_SQL_NAME n ON n.HASH = sw.IZHO
WHERE sw.D >= CURRENT_TIMESTAMP - (#FREQUENCY#/60.0/24.0)
AND st.ST LIKE '%<Your String>%'
GROUP BY st.ST, ISNULL(n.NAME, sw.IZHO)
Note: the only customization needed for this SQL statement is the "AND st.ST LIKE '%<Your String>%'" line, 2nd from bottom. Put the text string you want in place of <Your String>
Execute Against: Repository
Units: Seconds of Wait
High Threshold: Min 0, Max empty