Custom SQL alerts

10/9/2018 2:45 AM

10/9/2018 2:45 AM

This article provides information on custom SQL alerts.

Database Performance Analyzer

• 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