EcalCondDB/sql/ecal_payload_o2o.sql

0001 CREATE OR REPLACE PROCEDURE payload_o2o AS
0002
0003 obj  VARCHAR2(100);
0004 id   NUMBER;
0005 cnt1 NUMBER;
0006 cnt2 NUMBER;
0007 start_time TIMESTAMP;
0008
0009 BEGIN
0010   -- EcalPedestals
0011   obj  := 'EcalPedestals';
0012   SELECT count(*) INTO cnt1 FROM ecalpedestals@devdb10.cern.ch;
0013   SELECT max(last_id) INTO id FROM o2o_log WHERE object_name = obj;
0014
0015   IF id IS NULL THEN
0016     -- it is the first transfer, ensure ALL ids are transferred
0017     -- use -1 because all iov_id are > 0
0018     id := -1;
0019   END IF;
0020
0021   -- get the start time
0022   SELECT systimestamp INTO start_time FROM dual;
0023
0024   -- execute transfer
0025   INSERT INTO ecalpedestals@devdb10.cern.ch
0026   (iov_value_id, time)
0027   SELECT
0028     miov.iov_id, riov.run_num
0029   FROM
0030     location_def ldef, run_type_def rdef, run_tag rtag, run_iov riov,
0031     /* selects the mon_run_iov with the greatest subrun_num */
0032     (SELECT iov_id, run_iov_id,
0033        MAX(subrun_num) KEEP (DENSE_RANK FIRST ORDER BY subrun_num ASC)
0034        FROM mon_run_iov GROUP BY iov_id, run_iov_id) miov
0035   WHERE
0036       miov.run_iov_id = riov.iov_id
0037   AND riov.tag_id = rtag.tag_id
0038   AND rdef.def_id = rtag.run_type_id
0039   AND ldef.def_id = rtag.location_id
0040   AND ldef.location='H4'
0041   AND rdef.run_type='PEDESTAL'
0042   AND rdef.config_tag='PEDESTAL-STD'
0043   AND rdef.config_ver=1
0044   AND miov.iov_id > id
0045 ;
0046
0047   INSERT INTO ecalpedestals_item@devdb10.cern.ch
0048   (iov_value_id, pos, det_id, mean_x12, rms_x12, mean_x6, rms_x6, mean_x1, rms_x1)
0049   SELECT
0050     dat.iov_id,
0051     /* Creates a column of row numbers ordered by logic_id, for std::vec */
0052     ROW_NUMBER() OVER (PARTITION BY dat.iov_id ORDER BY dat.logic_id ASC),
0053     cv.id1,
0054     dat.ped_mean_g12,
0055     dat.ped_rms_g12,
0056     dat.ped_mean_g6,
0057     dat.ped_rms_g6,
0058     dat.ped_mean_g1,
0059     dat.ped_rms_g1
0060   FROM
0061     ecalpedestals@devdb10.cern.ch iov, mon_pedestals_dat dat, channelview cv
0062   WHERE dat.iov_id = iov.iov_value_id
0063     AND cv.logic_id = dat.logic_id
0064     AND cv.name='Offline_det_id'
0065     AND iov.iov_value_id > id
0066 ;
0067
0068   -- update log
0069   SELECT count(*) INTO cnt2 FROM ecalpedestals@devdb10.cern.ch;
0070   IF cnt2 != cnt1 THEN
0071     SELECT max(iov_value_id) INTO id FROM ecalpedestals@devdb10.cern.ch;
0072     INSERT INTO o2o_log (object_name, last_id, num_transfered, transfer_start, transfer_duration)
0073       VALUES (obj, id, cnt2 - cnt1, start_time, start_time - systimestamp);
0074   END IF;
0075
0076   COMMIT;
0077 END;
0078 /
0079
0080 SHOW ERRORS;