Submit a ticketCall us

Looking to compare latest NPM features with previous versions of NPM?
The NPM new feature summary offers a comparison of new features and improvements offered with this release.

 

Home > Success Center > Database Performance Analyzer (DPA) > Write custom alerts

Write custom alerts

Table of contents

Overview

Custom Alerts enable a user to execute SQL statements or stored procedures against a monitored database or the database repository itself to check for customized, site specific conditions and to generate an Alert when these conditions are met. Any parameter that can be returned using a SQL statement or stored procedure can be used as the basis for a Custom Alert. This product guide focuses specifically on creating Custom Alerts. The guide will cover Custom Stored Procedures, Alert Values, Custom SQL Statements, and Custom Tags.

Environment

All DPA versions

Detail

Key Features

  • Custom Alerts are unique to Ignite, giving DBAs unmatched flexibility to trigger Alerts on any condition for which a SQL statement or stored procedure can be constructed.
  • Custom alerts are designed to give the DBA the ability to check for conditions outside the normal bounds of pre-defined Ignite alerts.
  • Alerts can be provided via standard email, pagers, and cell phone text messaging. They can also be integrated with enterprise monitoring tools via SNMP.

 

Benefits Overview

DPA combines the best of three sources of Alert information: Wait-time data, existing database vendor supplied statistics, and custom SQL statements or stored procedures. Designed for any level DBA, they can generate Alerts at multiple severity levels based on numeric ranges, text values or true/false results from the custom code. This gives the DBA advance notice of a condition that affects a specific user community, and the ability to make changes before the users are impacted.

  • Custom Alerts give DBAs proactive control of their database, notifying them of issues before they become problems for database customers.
  • Custom Alerts allow DBAs to create Alerts on their own PL/SQL queries, giving flexibility to Alert on unique information of interest to the expert DBA.  
  • The result is improved customer service, fewer trouble tickets, and increased compliance with database SLAs.

 

Custom Stored Procedure Alerts

Custom stored procedures can be added to the database that holds the DPA repository. It will be run from that database as the Ignite admin user, so the user needs visibility and privileges to execute the custom code.
Once you have written the stored procedure and have added it to the database, use a call to the stored procedure in the Create/Modify Alert screen making sure the Alert Category field has one of the “Custom Procedure Alert” alert types selected.

 

Required Stored Procedure Parameters
Your custom stored procedure may include any number of parameters, but it must include exactly 2 output parameters (the alert will break if there are more than 2 output parameters), and in exactly this order relative to each other, (i.e. other parameters can be intermixed, but these 2 must be in this relative order):

  • AlertValue    OUT VARCHAR2
  • AlertString    OUT VARCHAR2

AlertValue is the result of the stored procedure conforming to the Return Type that you specified when creating the alert:

Return Type Use
'Info', 'Low', 'Medium', 'High' The return value is case-insensitive, so returning ‘low’ is treated the same as ‘Low’.  Also, returning the first letter of the level name is sufficient, so ‘I’, ‘L’, ‘M’ and ‘H’ are valid.
Number Fractional and negative numbers are valid.  Note that the numeric value is returned in the AlertValue of type VARCHAR2.
'TRUE' or 'FALSE' The value of 'TRUE' (case insensitive) signals that the desired condition has been met, causing the alert level to be set to the level specified on the Create/Modify Alert screen.

AlertString is defined as the result of the stored procedure.  The value of this will be sent as the content or body of the alert and can contain anything allowed by the local SMTP or SNMP servers.

Both AlertValue and AlertString are specified using Custom Tags (see discussion below). 

For Oracle databases, a very useful custom tag for stored procedures is #DBLINK#. It is a placeholder for the database link used to query the monitored database. It must be specified in single quotes and passed into the stored procedure as a VARCHAR2 input parameter.

Note: In DPA, database links must be created and maintained by the DBA group.

Here is an example of a stored procedure call using custom parameters:

myproc('param1', #ALERTVALUE#, 100, #ALERTSTRING#, '#DBLINK#')

 

The corresponding stored procedure definition could look like:

procedure myproc(    myp1            IN     VARCHAR2,
                                   alertvalue     OUT     VARCHAR2,
                                   myp3             IN     NUMBER,
                                   alertstring     OUT     VARCHAR2,
                                   dblink            IN     VARCHAR2 )

 

The stored procedure could then make use of these values.  The following is a coding example that passes back a Return Type (i.e., alertvalue) of TRUE or FALSE:

-- Assume the alert condition is not met
 alertvalue  := 'FALSE';
 alertstring := '';
 
 -- Query based on input values
 EXECUTE IMMEDIATE
      'select x from myschema.mytable@' || dblink
      || ' WHERE y = ' || myp1
      INTO myvar;
 
 -- Test the desired condition
 IF myvar > myp3 THEN
     -- Condition has been met
     alertvalue := 'TRUE';
     alertstring := 'My condition met: value = ' || myvar;
 END IF;

For concrete examples on how to write custom stored procedures, you can look at some of our example alerts in the DPA section of our thwack community forum.

 

Stored Procedures Errors
Your custom stored procedure may produce expected or unexpected errors.  Errors raised from your stored procedure will result in the alert level being set to Broken.  The error text will be available on the Alert History screen accessible from the History link on the main Alerts page.

To avoid errors while writing and debugging your stored procedure, do not activate the alert until it works the way you want.  Also, test the alert (via the Test Alert button on the Create/Modify Alert screen) to determine if an error or the desired results are produced.

 

Custom SQL Statement Alerts

You can create two types of Custom SQL Statement Alerts:

  • Single Return Type – the SQL statement must return one row and one column of numeric data.  An example is a query that retrieves the current value of a database parameter.
  • Multiple Return Type – the SQL statement can return many rows of data with two paired-value columns of data.  An example is a query that returns the amount of freespace for several tablespaces (Oracle) or databases (SQL Server) and looks similar to the following:

APP1        500
APP2        760
APP3        120
APP4           5

 

Thresholds are tested against each row of data.  For example, if a threshold of 150 were specified, the APP3 and APP4 rows would be flagged and sent in the alert text.

You can also use custom tags in your SQL statements. A common used for Oracle custom alerts is #DBID# which enables querying of DPA-specific wait time performance data from the repository. For example:

Each monitored database has a unique set of performance data tables defined in the Ignite repository.  This tagged value would allow generic SQL Statements to be used for each.

SQL Statement Errors
Errors produced by your SQL statement will result in the alert’s level being set to Broken, just as occurs with Stored Procedure alerts. See the Stored Procedure Errors section above for more details.

 

Custom Tags

To provide additional capabilities to your custom stored procedures and SQL statements, custom tags have been provided. You can embed custom tags in your statement or procedure call, which are replaced by the Ignite alerting engine with the appropriate runtime value. Valid custom tags are shown below.

#DBID# The internal Ignite ID for the monitored database.
Data Type VARCHAR2(50)
Use

 • SQL Statement: select col from ignite_table where dbid=#DBID#

 • Stored Procedure: myproc(…,#DBID#,  …)

#DBLINK# Database link used to connect to an Oracle monitored database.
Data Type VARCHAR2(50)
Use

 • SQL Statement: select mycol from
                            myschema.mytable@#DBLINK#

 • Stored Procedure: myproc(…,'#DBLINK#',  …)

#ALERTVALUE# Required output VARCHAR2 parameter for stored procedures (not used in SQL Statements).  On return from the stored procedure, it contains a value corresponding to the Return Type.  Must appear in the parameter list before #ALERTSTRING#.
Data Type VARCHAR2(500)
Use myproc(…, #ALERTVALUE#, …)
Comment If Return Type is set to ‘Number’, the maximum value is defined by the valid range for type NUMBER in Oracle (as defined on the database that holds the Performance Data Warehouse).
#ALERTSRTING# Required output VARCHAR2 parameter for stored procedures (not used in SQL Statements).  On return from the stored procedure, it contains a description of the alert condition.
Data Type VARCHAR2(4000)
Use myproc(…, #ALERTSTRING#, …)
#FREQUENCY# Optional input parameter to your SQL statement or stored procedure.  It contains the value, in minutes, for the Run Alert Every field on the Create/Modify Alert screen.
Data Type NUMBER
Use

 • SQL Statement: select mycol from    
                  myschema.mytable@#DBLINK#
                  where mydate > SYSDATE –
                                       (#FREQUENCY#/1440)

 • Stored Procedure: myproc(…, #FREQUENCY#, …)

Last modified
19:19, 22 Jun 2016

Tags

Classifications

Public