EcalCondDB/sql/mon_summary.sql

0001 /*
0002  * mon_summary.sql
0003  *
0004  * A program to output a summary of the monitoring data that has been
0005  * written to the online DB.  For every IOV and tag the number of
0006  * channels written to a data table is listed.
0007  */
0008
0009 set linesize 1000;
0010 set pagesize 50000;
0011 set feedback off;
0012
0013 alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';
0014
0015 DROP FUNCTION mon_summary;
0016 DROP TYPE mon_summary_t;
0017 DROP TYPE mon_summary_o;
0018
0019
0020
0021 CREATE OR replace TYPE mon_summary_o AS OBJECT (
0022   location varchar2(30),
0023   run_gen_tag varchar2(30),
0024   run_type varchar2(30),
0025   config_tag varchar2(30),
0026   config_ver number(10),
0027   run_iov_id number(10),
0028   run_num number(10),
0029   run_start date,
0030   run_end date,
0031   run_db_timestamp timestamp,
0032   mon_gen_tag varchar2(30),
0033   mon_ver varchar2(30),
0034   mon_iov_id number(10),
0035   subrun_num number(10),
0036   subrun_start date,
0037   subrun_end date,
0038   mon_db_timestamp timestamp,
0039
0040   crystal_consistency_cnt number(10),
0041   tt_consistency_cnt number(10),
0042   crystal_status_cnt number(10),
0043   pn_status_cnt number(10),
0044   occupancy_cnt number(10),
0045   pedestals_cnt number(10),
0046   pedestals_online_cnt number(10),
0047   pedestal_offsets_cnt number(10),
0048   test_pulse_cnt number(10),
0049   pulse_shape_cnt number(10),
0050   shape_quality_cnt number(10),
0051   delays_tt_cnt number(10),
0052   laser_blue_cnt number(10),
0053   laser_green_cnt number(10),
0054   laser_red_cnt number(10),
0055   laser_ired_cnt number(10),
0056   pn_blue_cnt number(10),
0057   pn_green_cnt number(10),
0058   pn_red_cnt number(10),
0059   pn_ired_cnt number(10),
0060   pn_ped_cnt number(10),
0061   pn_mgpa_cnt number(10),
0062
0063   CONSTRUCTOR FUNCTION mon_summary_o(x NUMBER)
0064     RETURN SELF AS RESULT
0065 );
0066 /
0067 show errors;
0068
0069
0070
0071 CREATE OR REPLACE TYPE BODY mon_summary_o AS
0072   CONSTRUCTOR FUNCTION mon_summary_o(x NUMBER)
0073     RETURN SELF AS RESULT
0074   AS
0075   BEGIN
0076   /* A null constructor */
0077     RETURN;
0078   END;
0079 END;
0080 /
0081 show errors;
0082
0083
0084
0085 CREATE OR REPLACE TYPE mon_summary_t AS TABLE OF mon_summary_o;
0086 /
0087 show errors;
0088
0089
0090
0091 CREATE OR replace FUNCTION mon_summary (i_run NUMBER := NULL) RETURN mon_summary_t PIPELINED IS
0092   sql_stmt varchar2(4000);
0093   summary mon_summary_o := mon_summary_o(NULL);
0094   TYPE table_data_t IS TABLE OF NUMBER(10) INDEX BY VARCHAR2(32);
0095   table_data table_data_t;
0096   t VARCHAR2(32);  /* table name */
0097 BEGIN
0098
0099 /* Initialize table_data */
0100 table_data('MON_CRYSTAL_CONSISTENCY_DAT') := 0;
0101 table_data('MON_TT_CONSISTENCY_DAT') := 0;
0102 table_data('MON_CRYSTAL_STATUS_DAT') := 0;
0103 table_data('MON_PN_STATUS_DAT') := 0;
0104 table_data('MON_OCCUPANCY_DAT') := 0;
0105 table_data('MON_PEDESTALS_DAT') := 0;
0106 table_data('MON_PEDESTALS_ONLINE_DAT') := 0;
0107 table_data('MON_PEDESTAL_OFFSETS_DAT') := 0;
0108 table_data('MON_TEST_PULSE_DAT') := 0;
0109 table_data('MON_PULSE_SHAPE_DAT') := 0;
0110 table_data('MON_SHAPE_QUALITY_DAT') := 0;
0111 table_data('MON_DELAYS_TT_DAT') := 0;
0112 table_data('MON_LASER_BLUE_DAT') := 0;
0113 table_data('MON_LASER_GREEN_DAT') := 0;
0114 table_data('MON_LASER_RED_DAT') := 0;
0115 table_data('MON_LASER_IRED_DAT') := 0;
0116 table_data('MON_PN_BLUE_DAT') := 0;
0117 table_data('MON_PN_GREEN_DAT') := 0;
0118 table_data('MON_PN_RED_DAT') := 0;
0119 table_data('MON_PN_IRED_DAT') := 0;
0120 table_data('MON_PN_MGPA_DAT') := 0;
0121
0122 /* Loop through the MON runs */
0123 FOR result IN (select loc.location, rtype.run_type, rtype.config_tag, rtype.config_ver, rtag.gen_tag run_gen_tag,
0124                       riov.iov_id run_iov_id, riov.run_num, riov.run_start, riov.run_end, riov.db_timestamp run_db_timestamp,
0125                       mver.mon_ver, mtag.gen_tag mon_gen_tag,
0126                       miov.iov_id mon_iov_id, miov.subrun_num, miov.subrun_start, miov.subrun_end, miov.db_timestamp mon_db_timestamp
0127                  from location_def loc
0128                  join run_tag rtag on rtag.location_id = loc.def_id
0129                  join run_type_def rtype on rtype.def_id = rtag.run_type_id
0130                  join run_iov riov on riov.tag_id = rtag.tag_id
0131                  join mon_run_iov miov on miov.run_iov_id = riov.iov_id
0132                  join mon_run_tag mtag on mtag.tag_id = miov.tag_id
0133                  join mon_version_def mver on mver.def_id = mtag.mon_ver_id
0134                  where riov.run_num = i_run
0135                  order by loc.location asc,
0136                           riov.run_num asc, rtype.run_type asc, rtype.config_tag asc, rtag.gen_tag asc,
0137                           miov.subrun_num asc, mver.mon_ver asc, mtag.gen_tag asc)
0138
0139 LOOP
0140   summary.location         := result.location;
0141   summary.run_gen_tag      := result.run_gen_tag;
0142   summary.run_type         := result.run_type;
0143   summary.config_tag       := result.config_tag;
0144   summary.config_ver       := result.config_ver;
0145   summary.run_iov_id       := result.run_iov_id;
0146   summary.run_num          := result.run_num;
0147   summary.run_start        := result.run_start;
0148   summary.run_end          := result.run_end;
0149   summary.run_db_timestamp := result.run_db_timestamp;
0150   summary.mon_gen_tag      := result.mon_gen_tag;
0151   summary.mon_ver          := result.mon_ver;
0152   summary.mon_iov_id       := result.mon_iov_id;
0153   summary.subrun_num       := result.subrun_num;
0154   summary.subrun_start     := result.subrun_start;
0155   summary.subrun_end       := result.subrun_end;
0156   summary.mon_db_timestamp := result.mon_db_timestamp;
0157
0158   /* Loop through all the data tables saving the number of channels written */
0159   t := table_data.FIRST;
0160   WHILE t IS NOT NULL
0161   LOOP
0162     sql_stmt := 'select count(*) from ' || t || ' where iov_id = :iov_id';
0163     EXECUTE IMMEDIATE sql_stmt INTO table_data(t) USING summary.mon_iov_id;
0164     t := table_data.NEXT(t);
0165   END LOOP;
0166
0167   /* assign table_data to summary object elements */
0168   summary.crystal_consistency_cnt := table_data('MON_CRYSTAL_CONSISTENCY_DAT');
0169   summary.tt_consistency_cnt      := table_data('MON_TT_CONSISTENCY_DAT');
0170   summary.crystal_status_cnt      := table_data('MON_CRYSTAL_STATUS_DAT');
0171   summary.pn_status_cnt           := table_data('MON_PN_STATUS_DAT');
0172   summary.occupancy_cnt           := table_data('MON_OCCUPANCY_DAT');
0173   summary.pedestals_cnt           := table_data('MON_PEDESTALS_DAT');
0174   summary.pedestals_online_cnt    := table_data('MON_PEDESTALS_ONLINE_DAT');
0175   summary.pedestal_offsets_cnt    := table_data('MON_PEDESTAL_OFFSETS_DAT');
0176   summary.test_pulse_cnt          := table_data('MON_TEST_PULSE_DAT');
0177   summary.pulse_shape_cnt         := table_data('MON_PULSE_SHAPE_DAT');
0178   summary.shape_quality_cnt       := table_data('MON_SHAPE_QUALITY_DAT');
0179   summary.delays_tt_cnt           := table_data('MON_DELAYS_TT_DAT');
0180   summary.laser_blue_cnt          := table_data('MON_LASER_BLUE_DAT');
0181   summary.laser_green_cnt         := table_data('MON_LASER_GREEN_DAT');
0182   summary.laser_red_cnt           := table_data('MON_LASER_RED_DAT');
0183   summary.laser_ired_cnt          := table_data('MON_LASER_IRED_DAT');
0184   summary.pn_blue_cnt             := table_data('MON_PN_BLUE_DAT');
0185   summary.pn_green_cnt            := table_data('MON_PN_GREEN_DAT');
0186   summary.pn_red_cnt              := table_data('MON_PN_RED_DAT');
0187   summary.pn_ired_cnt             := table_data('MON_PN_IRED_DAT');
0188   summary.pn_ped_cnt              := table_data('MON_PN_IRED_DAT');
0189   summary.pn_mgpa_cnt             := table_data('MON_PN_MGPA_DAT');
0190
0191
0192   PIPE ROW(summary);
0193 END LOOP;
0194 RETURN;
0195 END;
0196 /
0197 show errors;
0198
0199 /* Executes the summary program */
0200 SELECT * FROM TABLE(mon_summary());