3 Replies Latest reply on Jun 8, 2016 10:58 AM by mandevil

    monitor SQL avg exe time & send alert

    NN G

      Hello,

      Want to setup an auto alert job to monitor a particular SQL statement (in DB2 LUW environment) for average execution time & send email alert when average execution time exceeds a specific duration (for example, average execution time for this SQL statement exceeds 5 sec). How can this be done?

       

      I checked out Custom alert - it doesn't look like I can use it for my situation.

       

      Any help is appreciated.

       

      Thanks.

        • Re: monitor SQL avg exe time & send alert
          mandevil

          There should be a built in alert for this. If you go to alerts -> manage alerts, under "Wait Time" select the dropdown and select "Average Wait time for a single SQL".

          See if that is what you were thinking of.

          1 of 1 people found this helpful
            • Re: monitor SQL avg exe time & send alert
              NN G

              Thanks. Could you please send me link or direct me to help page/documentation page?

               

              Another question, if I want to find trend of a particular SQL, how do I get it? Under "Trends", I am given with predefined list of SQLs (Top SQL Statements) & I am not finding option to search SQL.

               

              Thanks again.

                • Re: monitor SQL avg exe time & send alert
                  mandevil

                  Not really any documentation on the wait alerts, but it's pretty intuitive. If you still have questions, I'd recommend opening a support case and then we can show it to you in a GTM.

                  Regarding the trending for a specific SQL, the best chart in DPA IMO is the historical chart. On the trend view, select the historical chart dropdown and select the hash in question. It will bring up a page that shows how much wait that SQL has experienced over the past 30 days in correlation with what might be driving the waits (executions, disk reads, etc.). Always start with the wait states though!