Submit a ticketCall us

Announcing NCM 7.7
With NCM 7.7, you can examine the rules that make up an access control list for a Cisco ASA device. Then you can apply filters to display only rules that meet the specified criteria, order the rules by line number or by the hit count, and much more.
See new features and improvements.

Home > Success Center > Database Performance Analyzer (DPA) > Custom SQL alerts

Custom SQL alerts

Table of contents

Overview

This article provides information on custom SQL alerts.

Environment

All DPA versions

Detail

  • Custom SQL Alert - Single Numeric Return returns a single number.
  • Custom SQL Alert - Multiple Numeric Return returns a set of name or value pairs.

 

Examples:

An example of a query returning a single number may be a query to alert if the number of failed orders per hour exceeded 10.

Select count(*) from order where failed=y and date=current_time – 1 hr;

However, sometimes more advanced logic is needed such as “if/then” or “while/loop”.  In those cases, they will typically write a function in the native language of the database and create the function manually in the monitored database.  It would be called like this:

Oracle

Select mycustomfunction(parm1) from dual

MSSQL

Select mycustomfunction(parm1)

Sybase

mycustomprocedure(parm1)

DB2

select dbo.mycustomfunction(parm1) from SYSIBM.SYSDUMMY1

Note the Sybase uses a procedure instead of a function.  User defined functions can be written in java in version 12 but are not supported as more standard SQL until 15.0.2.

In some cases you also may want to have the custom SQL return multiple rows.  In that case choose the ‘Custom SQL Alert - Multiple Numeric Return’ alert type.  The SQL may look like this:

Select territory, count(*)   from order where failed=y and date=current_time – 1 hr
 Group by territory;

It would then alert if individual territories exceeded the boundaries.

 

The following are testing information to try out:
 

Sybase stored procedure
create procedure testproc
as select 1234

 

This would be the text you would enter in DPA
testproc

 

MSSQL
CREATE FUNCTION junk
(@Mynum int)
RETURNS int
AS
BEGIN
return @Mynum
end

 

This would be the text you would enter in DPA
select dbo.junk(57)

 

DB2 function
create function JUNK(INSTR int)
returns int
specific JUNK
deterministic no external action contains sql

 

This function just returns the number passed in 
begin atomic
  return INSTR;
end
@

 

This would be the text you enter in DPA
select junk(1234) from SYSIBM.SYSDUMMY1

Last modified
19:08, 22 Jun 2016

Tags

Classifications

Public