Submit a ticketCall us

Webinar: Web Help Desk for HR, Facilities and Accounting Departments
This webinar will focus on use cases for HR, Facilities and Accounting.

Having a unified ticketing and asset management system for all the departments in your company can provide end-users with a seamless experience and make things easier for your IT team. Yet, with different business tasks and objectives, many departments don’t fully understand the capabilities of Web Help Desk and how the software can be customized for effective use in their departments.
Register Now.

Home > Success Center > Database Performance Analyzer (DPA) > Writing Custom Alerts with DPA

Writing Custom Alerts with DPA

Overview

Custom Alerts enable a user to execute SQL statements or stored procedures against the
Monitored database or DPA Performance Data Warehouse to check for customized, site specific
conditions and to generate an Alert when these conditions are met. Any parameter that can be
returned to DPA 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 with DPA. The
guide will cover Custom Stored Procedures, Alert Values, Custom SQL Statements, and Custom
Tags.

Key Features

  • Custom Alerts are unique to DPA, 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 DPA 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

Only DPA combines the best of three sources of Alert information: DPA 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 DPA 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.

Resolution

Custom Stored Procedure Alerts


Custom stored procedures can be added to the database that holds the DPA Performance Data
Warehouse. It will be run from within the Performance Data Warehouse, (as the DPA admin
user), so the DPA admin 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
“Procedure” 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 specify
on the DPA Create/Modify Alert screen:
 

I. Return Type
'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.

Use

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.

 

II. Return Type  Number

Use

Fractional and negative numbers are valid. Note that the
numeric value is returned in the AlertValue of type VARCHAR2.

 

III. Return Type 'TRUE' or 'FALSE'

Use

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. In DPA 6
the database links were created and maintained by DPA and also used for monitoring.

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;

 

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 free space for
several tablespaces (Oracle) or databases (SQL Server) and looks similar to the
following:
        APP1     500
        APP2     760
        APP3     120
        APP4     5
You can also use custom tags in your SQL statements. A common used for Oracle custom alerts
is #DBLINK# which enables querying of the monitored database. For example:

 

SELECT myvalue FROM myschema.mytable@#DBLINK#

 

An example of a custom SQL statement can be found by selecting the “% Redo Logs

Unarchived” alert.


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 DPA alerting engine with the appropriate runtime value. Valid custom tags
are shown below.

 

#DBID#

The internal DPA ID for the monitored database.
Data Type  VARCHAR2(50)
Use

  • · SQL Statement: select col from dpa_table where dbid=#DBID#
  • · Stored Procedure: myproc(…,#DBID#, …)

 

#DBLINK#

Database link used to connect to the 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#, …) (Note: Other custom tags exist for DPA internal use only.)
Last modified
18:04, 29 Nov 2016

Tags

Classifications

Public