The HADES experiment @ GSI

1. The HADES Oracle databaseand its interfaces for experimentalistsIlse Koenig, GSI Darmstadt for the HADES collaboration

2. The HADES experiment @ GSI A High-Acceptance Di-Electron Spectrometer ~ 80000 readout channels first production beam time Nov 2002 one prod. beam time each year ~ 500 – 800 files/day (~0.8 TB/day) (10 times more expected in 2010) actually ~ 90 analysis parameter containers (analysis of real and simulation data, ~ 40 MB for single file analysis) Expanded view Expanded view CHEP 2009 The HADES Oracle DatabaseIlse Koenig

3. Main usage of the HADES database users file catalog slow control data beam time logbook geometry parameters electronics setup run validation online analysis simulation offline analysis • related to runs by time stamps DAQ EPICS web interface ROOT macros Oracle Run: smallest data set individually treated several files, one per event builder CHEP 2009 The HADES Oracle DatabaseIlse Koenig

4. Analysis initialization concept Web GUI Oracle ASCII Files ASCII, ROOT Files Geometry Interface Runtime Database HGEANT ROOT TGeoManager • Designed for automatic initialization • Parameters (numbers, arrays, ROOT classes,…) are stored in parameter containers, automatically created by the analysis tasks and managedby the runtime database classes. • User chooses one or two parameter sources. • The analyzed files define the parameter versions. separate shared libraries Oracle Precompiler Version management Oracle: complete history of all parameters since 2002 ROOT file: local version management parameter file for local access snapshots of versions in Oracle HYDRA HYDRA Storage of parameters in Oracle: 1. Insert of parameters with a ROOT macro 2.Validation with web GUI CHEP 2009 The HADES Oracle DatabaseIlse Koenig

5. Version management in Oracle 1.Parameters are not stable, but may change from run to run 2. They may change for the same run over time (history) 3. They may come in different flavors (depending e.g. on an algorithm) 3-dimensional version management history invalid_since (year 4000 for actual version) 2 different flavors (contexts) version automatically defined date_create runs valid_since valid_until(default year 4000) defined by user CHEP 2009 The HADES Oracle DatabaseIlse Koenig

6. Parameter versions Version 1 Later new versions 3 and 4 replace the old ones Before a DST production we do a beam time specific parameter release “history date with name” Old versions can be retrieved with an old history date version 3 version 4 parameter release history year 4000 During a beam time the user inserts a version 1. Each new run is initialized with version 1. version 1 history date Later he adds a version 2. New runs are then initialized with version 2. version 2 The crossing point of run startandhistory date defines the parameter version date_create v2 invalid_since v1 real time version 1 date_create v1 runs valid_since v1 valid_since v2 year 4000 valid_until v1 run start CHEP 2009 The HADES Oracle DatabaseIlse Koenig

7. Layout of parameter containers Example: Lookup table for an unpacker (maps readout channels to detector cells) Constraints guarantee for each parameter container version that one readout channel maps to only one detector cell and both are unique. • Basically two groups of parameter containers: • Array of objects each containing the same list of parameters • Data are stored in one or more individual tables in Oracle. • Each parameter corresponds to a table column. • Many records together form a version set of the parameter container. Advantage:enforcement of data consistency via constraints and triggers Disadvantage:lack of flexibility Adding or discarding parameters needs changes in the tables and interfaces have to be done by an expert Suitable only for stable code Used for low-level analysis parameters CHEP 2009 The HADES Oracle DatabaseIlse Koenig

8. Generic parameter container parameter object • 2. All parameters are stored as name – object pairs in the same set of tables different versions parameter container name type: Int_t, Float_t, Double_t, Char_t, Text_t, UChar_t, class type stored as byte array (RAW or BLOB) number of values (single value or array) class version streamer info, root version for ROOT classes own version management any class derived from TObjectdecoded in the analysis interface by ROOT streamer allows to add or discard (invalidate) a parameter makes it (almost) code independent CHEP 2009 The HADES Oracle DatabaseIlse Koenig

9. Implementation and interfacesfor generic parameter containers • Requires only a few lines of dedicated C++ code in the container class • Generic read and write interfaces for Oracle, ROOT and ASCII files implemented in base classes • The Oracle interface stores a snapshot of parameters in a ROOT TList.The parameter container copies only parameters defined in the actual code version. • The user stores the parameter in Oracle by a ROOT macro.At validation, new parameters are added automatically.Old parameters no longer needed stay valid until explicitly set invalid. • No database expert needed! • Web-based graphical interface to Oracle for validation, searches and comparisons Suitable also for non-stable code Used for conditions and high-level analysis parameters CHEP 2009 The HADES Oracle DatabaseIlse Koenig

10. Web Interface access to parameter sets in a beam time more than 100 dedicated applications in tree-organized folders written in PL/SQL , uses Oracle application server CHEP 2009 The HADES Oracle DatabaseIlse Koenig

11. HYDRA parameter containers all parameter containers grouped by shared libraries CHEP 2009 The HADES Oracle DatabaseIlse Koenig

12. Parameter data Although stored as binary, C-type values and arrays are converted. Classes are decoded only by the analysis interface CHEP 2009 The HADES Oracle DatabaseIlse Koenig

13. Online storage of EPICS slow control data Oracle Problem: not performing for fast scans on a single channel over a long time range • Generation of run based summaries • For each run and channel: mean, min, max value, sigma, • number of data, timestamp of first entry, ... Onlinestorage of ~ 3000 channels, total rate ~100 Hz, 10-20 GB per beam time EPICS Program developed at SLAC by Lee Ann Yasuhawa and Bob Hall http://www.aps.anl.gov/epics/meetings/2002-11/talks/hall.pdf extension of the Channel Archiver, slightly modified streaming with OCI Direct Loader 10 min partitions, not indexed • Interfaces: Web interface • filtering • graphics with JAVA applet Analysis interface • ROOT graphics • Run validation • Parameter containers to be used in the event loop once per day (Oracle scheduler jobs) data moved, old partitions dropped, new partitions created 1 day partitions local index (timestamp, channel) CHEP 2009 The HADES Oracle DatabaseIlse Koenig

14. Run based summary HV[V] data for one run index of run summary • Displayed by analysis interface Example: RICH HV summaries for one day of beam time Blue points: mean HV value for one run Error bars: sigma of mean value Large error bars indicate a HV trip. The summaries allow to scan the data of a whole beam time within seconds. CHEP 2009 The HADES Oracle DatabaseIlse Koenig

15. Summary • Since start-up HADES successfully employs an Oracle database. Our overall design has been proven to be flexible enough to adapt to new requirements with small modifications and to be manageable with very restricted manpower. • The HADES initialization concept and its interfaces are also implemented in FAIRROOT, • the analysis and simulation framework used by the FAIR experiments CBM and PANDA. The HADES collaboration More than 100 scientists from 17 institutions in 9 European countries http://www-hades.gsi.de Special thanks to M. Dahlinger from GSI IT department for providing us a very stable server. CHEP 2009 The HADES Oracle DatabaseIlse Koenig