Hello
I got another problem few weeks ago. In quick: I need to store a variable
for monitoring graph, collected at 5 minutes. I also don't want to use rrdtool,
nor mrtg but to extract the data directly from a database, to create charts
(600 values each) on 3 basic periods: day, month, year. I need to extract only
precomputed averages for the month and year (~600 rows), for the speed, and I
don't want to precompute them in the application. So here is the table, and the
trigger to get all the work inside the sapdb, and my question is if exists other
ways to do that (in order of speed or elegance).
create table dbc.orx (
id serial primary key,
h fixed(10,0), -> unix time
val FIXED(10,0), -> value
avgt int) -> average type
insert 3 averages :
session.sql ('insert into dbc.orx (h ,val ,avgt) values ( '1043324062','0',1 )')
session.sql ('insert into dbc.orx (h ,val ,avgt) values ( '1043324063','0',2 )')
session.sql ('insert into dbc.orx (h ,val ,avgt) values ( '1043324064','0',3 )')
avg 1 is for 1800 seconds
avg 2 is for 7200 seconds
avg 3 is for 86400 seconds
CREATE TRIGGER mkavg FOR dbc.orx AFTER INSERT EXECUTE (
VAR
lasth FIXED(10,0) ;
i FIXED(10,0) ;
q FIXED(10,0) ;
j FIXED(10,0) ;
x FIXED(10,0) ;
difh FIXED(10,0) ;
SET j = 0;
SET x = 0;
SET q = 0;
DECLARE c01h CURSOR FOR SELECT h FROM dbc.orx WHERE avgt = 1 ;
FETCH LAST c01h INTO :lasth;
CLOSE c01h;
SET difh = h - lasth ;
IF ( difh >= 1800 ) THEN
BEGIN
DECLARE c02h CURSOR FOR SELECT val FROM dbc.orx WHERE h >= :lasth and avgt = 0;
FETCH c02h INTO :i ;
WHILE $RC = 0 DO
BEGIN
SET j = j + i ;
FETCH NEXT c02h INTO :i ;
END ;
CLOSE c02h ;
SELECT count(*) FROM dbc.orx WHERE h >= :lasth and avgt = 0;
FETCH LAST INTO :x;
SET q = j / x ;
INSERT INTO dbc.orx (h, val, avgt) VALUES (:new.h, :q, 1) ;
END ;
DECLARE c11h CURSOR FOR SELECT h FROM dbc.orx WHERE avgt = 2 ;
FETCH LAST c11h INTO :lasth;
CLOSE c11h;
SET difh = h - lasth ;
IF ( difh >= 7200 ) THEN
BEGIN
DECLARE c12h CURSOR FOR SELECT val FROM dbc.orx WHERE h >= :lasth and avgt = 1;
FETCH c12h INTO :i ;
WHILE $RC = 0 DO
BEGIN
SET j = j + i ;
FETCH NEXT c12h INTO :i ;
END ;
CLOSE c12h ;
SELECT count(*) FROM dbc.orx WHERE h >= :lasth and avgt = 1;
FETCH LAST INTO :x;
SET q = j / x ;
INSERT INTO dbc.orx (h, val, avgt) VALUES (:new.h, :q, 2) ;
END ;
DECLARE c21h CURSOR FOR SELECT h FROM dbc.orx WHERE avgt = 3 ;
FETCH LAST c21h INTO :lasth;
CLOSE c21h;
SET difh = h - lasth ;
IF ( difh >= 86400 ) THEN
BEGIN
DECLARE c22h CURSOR FOR SELECT val FROM dbc.orx WHERE h >= :lasth and avgt = 2;
FETCH c22h INTO :i ;
WHILE $RC = 0 DO
BEGIN
SET j = j + i ;
FETCH NEXT c22h INTO :i ;
END ;
CLOSE c22h ;
SELECT count(*) FROM dbc.orx WHERE h >= :lasth and avgt = 2;
FETCH LAST INTO :x;
SET q = j / x ;
INSERT INTO dbc.orx (h, val, avgt) VALUES (:new.h, :q, 3) ;
END ;
IF $rc = 0 THEN STOP($rc) ;
)
Regards
dim
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
