Version 1

    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