Type of Custom SQL Alerts
Custom SQL Alert - Single Numeric Return’. returns a single number.
Custom SQL Alert - Multiple Numeric Return’ - returns a set of name/value pairs.
Examples:
An example of a query returning a single number might 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 aren’t supported as more standard SQL until 15.0.2.
In some cases you also might want to have the custom SQL return multiple rows. In that case choose the ‘Custom SQL Alert - Multiple Numeric Return’ alert type. The SQL might 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.
Here is some testing information if you want to try this stuff out yourself.
--Sybase stored procedure
create procedure testproc
as select 1234
--This would be the text you would enter in Ignite
testproc
--MSSQL
CREATE FUNCTION junk
(@Mynum int)
RETURNS int
AS
BEGIN
return @Mynum
end
--This would be the text you would enter in Ignite
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 would enter in Ignite
select junk(1234) from SYSIBM.SYSDUMMY1