Overview 

The LHC Logging Service provided by the AB-CO-DM section and supported mainly by Chris Roderick, is composed of several components. This presentation by Chris Roderick gives an overview of the Service.

The clients expect that the data they send get stored. Since mid-2003 and up to 03.08.2004, the data was stored on the Oracle Database DEVDB9, the only centrally available and supported Oracle9i instance in 2003.

Since mid 2004 we are using the dedicated database cluster LHCLOGDB (~9 Tbyte of disk space). This forms part of the Production Software Environment. Many clients are operational: SPS, SPS EA, PS, LEIR, LHC HWC (QPS, Cryo, PIC, Power, BLM, RADMON, RF, etc.), LHC Experiements, etc.

The LHC Logging Service is based on a 3-tier architecture; several machines are used to host the Oracle Application Servers (middle) tier. In the AS, dedicated OC4J containers are deployed, in which Java Servlets run. It is anticipated that the AS instances will work in a Real Application Cluster setup, whereby the load is taken over transparently if one machine is down. This is not yet the case.

Our clients implement an XML schema or use the Measurement Database Service to submit their logging data to the service (as well as the meta data). This data loading chain is fully operational and in production, the Data Loading application on CS-CCR-OAS1 takes care of the XML parsing and loading process. For Data Extraction, the TIMBER interface allows users to get to selected logged variables, show their data graphically,and extract in file format.

For our new clients, we have put in place an identical Test Software Environment in order to allow them to test out the service, and to allow us to validate the new client (checking errors, meta data, data volumes, data rates, naming, hierarchy). Similarly, they have an identical TEST TIMBER interface. Note that the data resides on the same database server as the production environment and is backed up. Data can be transferred upon request when a client moves to the Production Software Environment. Once again, for reasons of redundancy and symmetry, the Test Software Environment is deployed on both machines.

Production Software Environment 

This environment is for users of the LHC Logging System that are logging production data.

• TIMBER
• Data Loading Interface - Data Loaders (Backup Version)

Test Software Environment

This environment has been established in order to provide an area that new users can use for initial integration with the LHC Logging System.

• TIMBER
• Data Loading Interface - Data Loaders (Backup Version)

Data Access APIs

In addition to TIMBER, there are two methods to extract data from the Measurement and/or Logging Databases - using either a Java API (recommended approach), or using a PL/SQL API (for non-Java clients):

Java API

The Java API is accessible via Common Build (documentation is here). Before using this API, clients should contact ab-dep-co-dm@cern.ch to register their applications, and ensure that their intended usage is appropriate and that they are kept informed of updates. This API , is complemented by a set of domain objects, whose documentation is here

PL/SQL API

The PL/SQL API is accessed by making SQL and PL/SQL calls to the relevant database account. The API allows the creation and maintenance of variable lists, and queries to extract for either a single variable or a list of variables the following data representations:

• ALL DATA in the given time range.
• ALL DATA in the given time range, but the LAST AVAILABLE VALUE (if any) prior to the range if no data is found within the given time range.
• The LAST AVAILABLE VALUE within the given time range.
• A STATISTICAL SUMMARY (no of records, min/max timestamps, min/max values) of the data within the given time range.
• A TIME SCALED set of data. Important information about the use of time scaling is here.

To query data, the client first needs to set the query parameters (variable or variable list name, time range, and if required - optional time scaling parameters) - as explained below, and then make a standard query to the relevant view. There are many views exposing data for different data formats in different data representations (as listed above).
This image shows all of the available views which can be queried.

In order to use this API, clients should first contact ab-dep-co-dm@cern.ch to register their applications, and ensure that their intended usage is appropriate and that they are kept informed of updates.

Once the formalities are outof the way, clients need to carry out the following steps:

1. Make a PL/SQL call to register their client-id and application name (without this step, nothing will work):
   
   --This needs to be done once, every time you connect to the database
   DECLARE
     l_client_id varchar2(30) := YOUR_CLIENT_ID_HERE;
     l_app_name varchar2(30) := 'YOUR_APP_NAME_HERE';
   
   BEGIN data_access.set_client(l_client_id, l_app_name); END;
   /
   
   
2. Set the query parameters. e.g. (for a single variable)
   
   DECLARE
     l_var varchar2(40) := 'RPHH.UA83.RQ4.L8B1:V_MEAS';
     l_start varchar2(30) := '2007-11-21 08:00:00';
     l_end varchar2(30) := '2007-11-21 09:00:00';
   
   BEGIN
     /* These two methods could be called seperately, e.g.
     if you want to change the variable but not the time window, or vice versa */
     data_access.SET_VARIABLE(l_var);
     data_access.SET_TIME_WINDOW(l_start, l_end);
   END;
   /
3. Query the data from the appropriate view e.g. (for numeric data, for a single variable)
   
   --ALL DATA in the given time range
   select utc_stamp, value from var_numeric_data_v;
   
   --LAST AVAILABLE VALUE (if any) in the given time range
   select utc_stamp, value from var_numeric_last_data_v;
   
   -- ALL DATA in the given time range, or LAST AVAILABLE VALUE (within 7 days by default) if no data found.
   select utc_stamp, value from var_numeric_data_or_last_v;
   
   -- STATISTICAL SUMMARY (no of records, min/max timestamps, min/max values) of the data within the given time range.
   select variable_name, min_utc_stamp, max_utc_stamp, no_of_values, min_value, max_value from var_stats_data_v;

Below are some examples of creating, modifying, deleting, and using variable lists:

1. Creating a variable list (WARNING!! - lists should only be used for fixed sets of variables which will be re-used repeatedly -
   DO NOT continually create and delete lists!):
   
   DECLARE
     l_list varchar2(40) := 'POWERCONVERTERS_SECTOR_45';
     l_list_descr varchar2(250) := 'Example Powerconverter Variables in sector 45';
   begin
     data_access.create_variable_list(l_list, l_list_descr);
   end;
   /
   
   
2. Adding some variables to the list:
   
   DECLARE
      l_list varchar2(40) := 'POWERCONVERTERS_SECTOR_45';
      l_var_name varchar2(40);
   begin

     l_var_name := 'RPHH.UA83.RQ4.L8B1:V_MEAS';
      data_access.add_to_variable_list(l_list, l_var_name);
   
      l_var_name := 'RPHH.UA83.RQ4.L8B1:V_REF';
      data_access.add_to_variable_list(l_list, l_var_name);
   
      l_var_name := 'RPHH.UA83.RQ4.L8B1:I_MEAS';
      data_access.add_to_variable_list(l_list, l_var_name);
   
      l_var_name := 'RPHH.UA83.RQ4.L8B1:I_REF';
      data_access.add_to_variable_list(l_list, l_var_name);
   
   end;
   /
   

3. To save your changes, you have to issue a commit. This can take some time - be patient ;-) :
   
   commit;
   
   
4. Query ALL variable lists that you own:
   
   select list_name, description from variable_lists_v;
   
   
5. Set a list to use
   
   DECLARE
      l_list varchar2(40) := 'POWERCONVERTERS_SECTOR_45';
   BEGIN
      data_access.set_variable_list(l_list);
   END;
   /
   
   
6. Show variables in the current list (this can take some time - be patient ;-) :
   
   select variable_name, datatype_name, unit, description from variable_list_variables_v;
   
   
7. Set the query parameters for a list:
   
   DECLARE
      l_list varchar2(40) := 'POWERCONVERTERS_SECTOR_45';
      l_start varchar2(30) := '2007-11-21 08:00:00';
      l_end varchar2(30) := '2007-11-21 09:00:00';
   
   BEGIN
     /* These two methods could be called seperately, e.g.
     if you want to change the variable list but not the time window, or vice versa */
      data_access.SET_VARIABLE_LIST(l_list);
      data_access.SET_TIME_WINDOW(l_start, l_end);
   END;
   /
   
   
8. Query the data for all variables in the list using the appropriate views e.g. (for ALL numeric variables in the list ) :
   

   --ALL DATA in the given time range
   select variable_name, utc_stamp, value from list_numeric_data_v;
   
   --LAST AVAILABLE VALUE (if any) in the given time range
   select variable_name, utc_stamp, value from list_numeric_last_data_v;
   
   -- ALL DATA in the given time range, or LAST AVAILABLE VALUE (within 7 days by default) if no data found.
   select variable_name, utc_stamp, value from list_numeric_data_or_last_v;
   
   -- STATISTICAL SUMMARY (no of records, min/max timestamps, min/max values) of the data within the given time range.
   select variable_name, min_utc_stamp, max_utc_stamp, no_of_values, min_value, max_value from list_stats_data_v;
   
   

9. DELETE ALL variables from an existing list in a single operation, whilst leaving the list entry in the database:
   
   DECLARE
      l_list varchar2(40) := 'POWERCONVERTERS_SECTOR_45';
   
   BEGIN
      data_access.TRUNCATE_VARIABLE_LIST(l_list);
   END;
   /
   

10. DELETE a variable list :
    
    DECLARE
       l_list varchar2(40) := 'POWERCONVERTERS_SECTOR_45';
    
    BEGIN
       data_access.DELETE_VARIABLE_LIST(l_list);
    END;
    /
    

Don't forget to COMMIT your changes!

If time scaling is neccesary (check here), in addition to the normal query parameters, the time scaling parameters also need to be set e.g.

DECLARE
  l_algorithm varchar2(40) := 'AVG';
  l_no_intervals number := 10;
  l_interval_type varchar2(30) := 'MINUTE';

BEGIN
  data_access.SET_TIME_SCALE_PARAMETERS(l_algorithm, l_no_intervals, l_interval_type);
END;
/

Afterwards, to query the time scaled data, the views ending in '_ts_data_v' need to be used e.g.

-- query numeric time scaled data for a single numeric variable
select utc_stamp, value from var_numeric_ts_data_v;

-- query numeric time scaled data for a all numeric variables in the current list
select variable_name, utc_stamp, value from list_numeric_ts_data_v;

The FULL set of PL/SQL Data Access API methods including default values are defined below:

/**
* Sets the details of the client and their application which is using this API
*/
procedure set_client(
  p_client_id in varchar2,
  p_client_application in varchar2
);

/**
* Sets the variable list to be used for queries in the current session.
*/
procedure set_variable_list(
  p_list_name in varchar2
);

/**
* Sets the variable to be used for queries in the current session.
*/
procedure set_variable(
  p_variable_name in varchar2
);

/**
* Sets the query time window to be used for the current session.
* When using the views to return ALL DATA in the given time range, or LAST AVAILABLE VALUE if no data found,
* the API will by default, search for data upto 7 days before the given start time. You can change this behaviour
* by passing a value for the parameter p_oldest_stamp. The shorter the period to check - the faster the API will
* return data. Therefore try to use this intelligently. If you no you have data at least once per hour, then set
* p_oldest_stamp to be 1 HOUR prior to the p_start_stamp value.
* By default, the API expect to receive timestamp strings in the format 'YYYY-MM-DD HH24:MI:SS'. You can change
* this behaviour by passing a value for the parameter p_stamp_format.
*/
procedure set_time_window(
  p_start_stamp in varchar2,
  p_end_stamp in varchar2,
  p_oldest_stamp in varchar2 default null,
  p_stamp_format in varchar2 default 'YYYY-MM-DD HH24:MI:SS'
);

/**
* Sets the algorithm and frequency to be used for queries that make use of the time_scale package.
* Valid algorithms are: MIN, MAX, AVG, SUM, REPEAT, and INTERPOLATE.
* The frequency is determined via the combination of:
* p_no_of_intervals (the number of intervals between generated Timestamps)
* and
* p_interval_type (the type of the interval to be between generated Timestamps).
* Valid interval types are: 'SECOND', 'MINUTE', 'HOUR', or 'DAY'
*/
procedure set_time_scale_parameters(
  p_algorithm in varchar2,
  p_no_of_intervals in number default 1,
  p_interval_type in varchar2 default 'HOUR'
);

/**
* Create a new list (with the given name and description) to store groups of variables for data retreival.
*/
procedure create_variable_list(
  p_list_name in varchar2,
  p_list_description in varchar2 default null
);

/**
* Deletes the variable list with the given list name.
*/
procedure delete_variable_list(
  p_list_name in varchar2
);

/**
* Adds the variable with the given variable name to the variable list with the given list name.
*/
procedure add_to_variable_list(
  p_list_name in varchar2,
  p_variable_name in varchar2
);

/**
* Deletes the variable with the given variable name from the variable list with the given list name.
*/
procedure delete_from_variable_list(
  p_list_name in varchar2,
  p_variable_name in varchar2
);

/**
* Removes ALL variables from the variable list with the given list name.
*/
procedure truncate_variable_list(
  p_list_name in varchar2
);