CREATE PROCEDURE STORADM.CPU_USAGE_SVCNODE
(IN interval_len INTEGER)
LANGUAGE SQL
BEGIN

DECLARE SQLCODE INTEGER DEFAULT 0;
DECLARE begin_time TIMESTAMP;
DECLARE node_name VARCHAR(64);
DECLARE date_tk INTEGER;
DECLARE entity_tk INTEGER;
DECLARE kpi_tk INTEGER;
DECLARE cpu_usage REAL;

DECLARE node_names cursor FOR (SELECT entity_value FROM STORADM.REP_ENTITY );


SET begin_time = (CURRENT_TIMESTAMP - interval_len MINUTES);
INSERT INTO STORADM.REP_DATE VALUES (DEFAULT, begin_time, interval_len);
SELECT IDENTITY_VAL_LOCAL() INTO date_tk FROM SYSIBM.SYSDUMMY1;

OPEN node_names;

fetch node_names INTO node_name;
while(SQLCODE=0) do

SELECT storadm.rep_entity.entity_tk INTO entity_tk
FROM storadm.rep_entity WHERE entity_value = node_name;

SELECT avg(samples.cpu_util_perc)/100.0 INTO cpu_usage
FROM
    (
        SELECT svc_node_id,cpu_util_perc
        FROM tpcreport.latest_prf_svc_node
        UNION
        SELECT svc_node_id,cpu_util_perc FROM tpcreport.prf_svc_node WHERE prf_timestamp > begin_time
    ) AS samples
    JOIN
    tpcreport.STORAGESUBSYSTEM_NODE ON tpcreport.storagesubsystem_node.redundancy_id = samples.svc_node_id
GROUP BY tpcreport.storagesubsystem_node.display_name
HAVING tpcreport.storagesubsystem_node.display_name = node_name;

SELECT storadm.rep_kpi.kpi_id INTO kpi_tk
FROM storadm.rep_kpi WHERE kpi_name = 'SVC_NODE_CPU_LOAD' AND kpi_activation_date < begin_time ORDER BY kpi_activation_date DESC;

INSERT INTO storadm.rep_fact VALUES(DEFAULT, date_tk, entity_tk, kpi_tk, cpu_usage);

fetch node_names INTO node_name;
END while;

close node_names;




END
@