# # AMS ORACLE tables (preliminary), October 1999. A.Klimentov # # # use ORACLE time in the tables. Time is CDT for STS runs # and local for MC and 'Ground' runs. # # July 2000. V0.1 major modifications # # September 2000. V1.1 major modifications for m_runs table, new offline # tables from V.Choutko # December 2000. add 'UNIX' begin, end, insert time into TDV catalogs # TDV tables are modified, use indices instead of TDV name # in tables, keep three tables with partitions for all TDVs # # Last Edit : August 6, 2001. Table m_datasets : ------------------------ # # Description of AMS datasets # dataset varchar(255) data set name (STS-91, GSI98, CERN98, MC, ...) timef date first event time or production date timel date time of last event timei date insert or create time nruns number(12,0) total number of runs in dataset cruns number(12,0) number of calibration runs bruns number(12,0) bad runs tevents number(24,0) total number of events events number(24,0) number of events in non-calibration runs comments varchar2(3000) short description of dataset content, READ.ME not NULL dataset primary key dataset Table m_alldset ---------------------- # dataset name and dataset number correspondance dataset varchar(255) data set name (STS-91, GSI98, CERN98, MC, ...) ndataset number(5) Table m_datasetfiles ---------------------- # # Datasets from m_datasets have different DST, NTUPLES, preselected NTUPLES, # m_datasets_types table shows what is available for each datasets # dataset varchar(255) data set name (STS-91, GSI98, CERN98, MC, ...) rawfiles integer(2) raw flat files for the current DST taginfo integer(2) tag info " " primnt integer(2) primary ntuples " " mcgen integer(2) MC generator events " " mc4v integer(2) MC 4 vectors mchits integer(2) MC hits rootraw integer(2) ROOT raw files rootdst integer(2) ROOT DST files <--> tbd9 integer(2) 10 - 19 reserved for the future tbd10 integer(2) tbd11 integer(2) tbd12 integer(2) tbd13 integer(2) tbd14 integer(2) tbd15 integer(2) tbd16 integer(2) tbd17 integer(2) tbd18 integer(2) tbd19 integer(2) Table m_tdv_names ----------------- name char(255) not null, TDV name datamc number(3,0), TDV data/MC flag : 0 - MC, !0 - data idx number(5,0) not null index to be used in other tables to refer TDV name Table m_tdv -------------- idx number(5,0) not null, index tinsert date not null, insert time tbegin date, validity begin time tend date, end uinsert number(24) not null, insert time UNIX ubegin number(24), validity begin time UNIX uend number(24), end prodstat number(5,0), 1/0 1 - production, 0 - not for the production timestamp number(24), record insert time (UNIX) dirpath varchar(255) not null path to TDV directory filename varchar(255) not null TDV file name PARTITION by RANGE(ubegin) (PARTITION pre_STS91 VALUES LESS THAN (896824000), PARTITION STS91 VALUES LESS THAN (897688800), PARTITION year_1998 VALUES LESS THAN (915145201), PARTITION year_1999 VALUES LESS THAN (978303601), PARTITION year_2000 VALUES LESS THAN (1009839601), PARTITION year_2001 VALUES LESS THAN (1041462001), PARTITION year_2002 VALUES LESS THAN (1072911601), PARTITION year_2003 VALUES LESS THAN (1104534001), PARTITION year_2004 VALUES LESS THAN (1136070001), PARTITION year_2005 VALUES LESS THAN (1167606001), PARTITION year_2006 VALUES LESS THAN (1199142001), PARTITION year_2007 VALUES LESS THAN (1230764401) Table m_tdvdef -------------- idx number(5,0) not null, tinsert date not null, tbegin date, tend date, uinsert number(24) not null, ubegin number(24), uend number(24), prodstat number(5,0), 1/0 1 - production, 0 - not for the production timestamp number(24), record insert time (UNIX) dirpath varchar(255), not null path to TDV directory filename varchar(255) not null TDV file name Table m_tdv_blob -------------- idx number(5,0) not null, index uinsert number(24) not null, insert time UNIX ubegin number(24), validity begin time UNIX uend number(24), end prodstat number(5,0), 0/1/2 0 - not for the production 1 - valid for production 2 - just written to be confirmed timestamp number(24), record insert time (UNIX) tdv blob PARTITION by RANGE(ubegin) (PARTITION pre_STS91 VALUES LESS THAN (896824000), PARTITION STS91 VALUES LESS THAN (897688800), PARTITION year_1998 VALUES LESS THAN (915145201), PARTITION year_1999 VALUES LESS THAN (978303601), PARTITION year_2000 VALUES LESS THAN (1009839601), PARTITION year_2001 VALUES LESS THAN (1041462001), PARTITION year_2002 VALUES LESS THAN (1072911601), PARTITION year_2003 VALUES LESS THAN (1104534001), PARTITION year_2004 VALUES LESS THAN (1136070001), PARTITION year_2005 VALUES LESS THAN (1167606001), PARTITION year_2006 VALUES LESS THAN (1199142001), PARTITION year_2007 VALUES LESS THAN (1230764401) Table m_tdvdef_blob -------------- idx number(5,0) not null, uinsert number(24) not null, ubegin number(24), uend number(24), prodstat number(5,0), 1/0 1 - production, 0 - not for the production timestamp number(24), record insert time (UNIX) tdv blob Table m_runs_d : -------------- ndataset number(8,0) dataset id run number(24,0) run number runtype varchar(255) type of run (data, laser, trcalib, mc) daqtag varchar(7) run tag, given by AMS DAQ events number(12,0) number of events particle varchar(10) beam source or target type, -1 for STS and ISS energymin number(5,1) beam energy MeV | -1 for ISS and STS energymax number(5,1) beam energy MeV | min=max for testbeam spectrum varchar(255) spectrum description trigmask varchar(255) trigger mask position number(5,0) detector position | as it is written in logbook phi number(5,2) phi angle in deg theta number(5,2) theta angle in deg x number(6,2) X in cm y number(6,2) Y in cm z number(6,2) Z in cm timef date time of the first event timel date time of the last event utimef number(10,0) unix time of the first event utimel number(10,0) unix time of the last event cdt varchar(4) cdt, gmt, ut, etc rerunflag number(4,0) tbd errors varchar(255) errors comments varchar(255) comments not NULL dataset, run, rawfilepath, timef for GSI98 and CERN98 datasets daqtag shows the detector position (but not for all runs daqtag was entered correctly) spectrum : cosmic, undercutoff, uniform, loguniform, terrestial muons trigmask : rules TOF : 4x4 && LEVEL3 : (sub.detector ':' trigger ' AND '&&', OR '||') table m_mcruns_d -------------------------- NDATASET NOT NULL NUMBER(8) RUN NOT NULL NUMBER(24) RUNTYPE VARCHAR2(255) DAQTAG VARCHAR2(7) EVENTS NUMBER(12) PARTICLE VARCHAR2(10) ENERGYMIN NUMBER(8,1) ENERGYMAX NUMBER(8,1) SPECTRUM VARCHAR2(255) TRIGMASK VARCHAR2(255) POSITION VARCHAR2(80) PHI NUMBER(5,2) THETA NUMBER(5,2) X NUMBER(6,2) Y NUMBER(6,2) Z NUMBER(6,2) TIMEF NOT NULL DATE TIMEL NOT NULL DATE CDT VARCHAR2(4) RERUNFLAG NUMBER(4) COMMENTS VARCHAR2(255) ERRORS VARCHAR2(255) Table m_execs ------------- EXE file description name varchar(255) // exec name id number(20,0) // id to refer version number(10,2) // version platform number(10,0) // platform id compiler number(10,0) // compiler id Table m_platforms ----------------- type varchar(255) // id number(10,0) Table m_compilers ----------------- name varchar(255) // n number(10,0) // Run tables // m_runs_d - basic table for // m_prodruns - information how and when run was processed // m_runtable - list of runs selected for the production (now) // m_prodinfo - production status for runs from m_runtable // m_prodinfo ------------- // runs production status run number(24,0) run number NOT NULL idx number(24,0) run id NOT NULL events number(20,0) number of events in run fevent number(20,0) first event number levent number(20,0) last event number eventsp number(20,0) events processed lpevent number(20,0) last processed event number CriticalErrors number(10,0) critical errors Errors number(10,0) errors CPUTime number(20,4) cpu time (total) ElapsedTime number(20,4) elapsed time (total) Status number(5,0) run status prodid number(20,0) production version and description HostId number(5,0) timestamp number(24,0) Table m_prodruns ------------------ // list of processed runs run number(24,0) run number NOT NULL idx number(24,0) run id NOT NULL events number(20,0) number of events processed firstevent number(20,0) first processed event lastevent number(20,0) last processed event CriticalErrors number(10,0) critical errors Errors number(10,0) errors CPUTime number(20,4) cpu time (total) TimeSpent number(20,4) elapsed time (total) status number(5,0), execid number(20,0) program version and description datacards number(20,0) datacards id hostid number(6,0) host id where program was executed timestamp number(24,0) record insertion time Table m_tags ------------------ run number(24,0) run number NOT NULL idx number(24,0) run id NOT NULL event number(8,0) event number position number(24,0) event position in raw data file tag1 number(24,0) event tag tag2 number(24,0) # partitioning by run, run/eventno can be presented several times # for different dates of production, insertion/creation Table m_ntuples : ----------------- run number run number filepath varchar(255) path to ntuples file timestamp date ntuples production date timef date NT 1st event time timel date NT last event time version number(6,2) version of the production program mask number(4,0) tbd comments varchar(255) comments Partition by RUN Table m_mcntuples_d : --------------------- ndataset number dataset id run number run number ntupletype number filepath varchar(255) path to ntuples file timestamp date ntuples production date version number(6,2) version of the production program mask number(4,0) tbd comments varchar(255) comments not NULL : run, filepath Table m_sts_chronology : ------------------------ dataset varchar(255) dataset mode varchar(80) shuttle or station mode timestamp date time of event UT metdd number(3) MET : Days methh number(2) Hours metmm number(2) Minutes metss number(2) Seconds power_step number(3,0) Power Step 0/1/2/3 srdl_mode number(4) SRDL status hrdl_mode number(4) HRDL status daq_mode number(4) DAQ status comments varchar(255) comments mode : Docking, Undocking, Maneovring, etc indexes : MET Not NULL : timestamp, MET, dataset power step : 0000 0000 ^^ ||__ power step 1 |___ power step 2 ^^__ power step 3 srdl(hrdl,daq)_mode : 0000 0000 0000 0000 ^ | srdl off = 0/-1 scheduled/unknown,undefined |_ srdl on = 1 Table m_position : ----------------------- timestampb unixtime position start time timestampe unixtime position end time altitude number(4,0) shuttle/ISS altitude theta number(4,1) Theta angle (rad) phi number(4,1) Phi angle (rad) velocity number(5,0) Table m_gbatch : ----------------------- tinsert date not null insertion timestamp tcreate date exec build date version number(6,2) exec version platform varchar2(20) platform (DUNIX, LINUX, etc) and OS version compiler varchar2(40) version of C++, Fortran compilers idx number(6) datacards idx from m_datacard_sets exefile blob exe file Table m_datacards : ------------------ name varchar2(80) idx number(10,0) tinsert date not null insertion timestamp Table m_datacards_cards : ----------------------- idx number(6) datacards idx from m_datacard_sets datacards varchar2(4000) datacards Table m_runstatusdesc (m_clientstatusdesc, m_hoststatusdesc, m_interfaces) --------------------- status varchar(40); n number(6,0); m_runtable : ------------- //runs selected for processing run number(24,0) run number NOT NULL id number(24,0) uid fevent number(20,0) first event number levent number(20,0) last event number utimef number(24,0) unix time of the first event utimel number(24,0) unix time of the last event priority number(10,0) rawfilepath varchar(1024) path to raw data file status number(10,0) history number(10,0) stime number(24,0) submit for processing cuid number(12,0) timef date ascii time of first event stimea date ascii time when job was submitted timestamp number(24,0) record insert time m_nominal_hosts ------- name varchar(40) id number(10,0) interface number(6,0) platform number(10,0) compiler number(10,0) ncpu number(5,0) ram number(10,0) in MB clock number(10,0) in MHz timestamp number(24,0) m_nominal_ntuples ----------------- id number(10,0) mode number(10,0) updfreq number(10,0) host number(10,0) output char(256) m_nominal_clients ----------------- id number type number(10,0) maxclients number(6,0) memory number(6,0) scriptpath varchar2(1024) logpath varchar2(1024) submitcmd varchar2(1024) hostid number(6,0) logintheend number(6,0) timestamp number(24,0) table m_active_clients ----------------------- id number // client ID given by dbase server lastupdate number(24,0) // starttime number(24,0) // start time status number(6,0) // current status starttimea date // ASCII start time lasttime date // ASCII last update time timestamp number(24,0) // record insert time table m_active_clients_ref ----------------------- id number // client ID given by dbase server ior varchar(1024) type number(6,0) // client type (see ClientType in server.idl) interface number(6,0) uiid number // uid from ActiveRef structure table m_active_clients_id ----------------------- id number pid number ppid number hostid number(6,0) type number(6,0) exitingstatus number(6,0) interface number(6,0), timestamp number(24,0) // record insert time table m_active_hosts --------------------- id number(10,0) host id interface number(5,0) interface status number(6,0) status active number(5,0) number of active clients allowed number(5,0) nominal number of clients processed number(10,0) number of processed clients failed number(10,0) number of clients failed killed number(10,0) number of killed clients lastfailed number(24,0) time of last failure lastupdate number(24,0) last reply from host lastuptime date last update time timestamps number(24,0) record insert time table m_hosts_duty ------------------ host duty cycle id number(10,0) id status number(6,0) status acttime number(24,0) time in production (secs) downtime number(24,0) "marked" as inactive atimestamp number(24,0) last time "active" dtimestamp number(24,0) last time "down" table m_dst ---------------------- insert number(24,0) insert time begin number(24,0) begin end number(24,0) end run number(24,0) run number firstevent number(24,0) first event in dst lastevent number(24,0) last events number(20,0) number of events status number(6,0) DST status type number(6,0) DST type size number(24,0) size in MBytes name varchar(1024) name lastuptime date timestamp number(24,0) table m_dstinfo ---------------------- type number(6,0) dst type id number(24,0), hostid number(5,0), host id dirpath varchar(1024), runmode number(5,0), updfreq number(20,0), diehard number(20,0), freespace number(20,0), totalspace number(20,0) table m_prodinfo5 ------------------------- events number(20,0) events processed CriticalErrors number(10,0) critical errors Errors number(10,0) errors CPUTime number(20,4) cpu time (total) ElapsedTime number(20,4) elapsed time (total) timestamp number(24,0) table m_hosts_duty -------------------------- id number(10) host id status number(6) host status from m_hoststatus acttime number(24) active time in seconds downtime number(24) downtime in seconds atimestamp number(24) acttime set time dtimestamp number(24) downtime set time timestamp number(24) table m_computers -------------------------- name varchar(10) computer name id number(10) computer id type varchar(24) model ncpu number(4,0) number of cpu's clock number(6) CPU clock in MHz ram number(6) RAM in MByte sn varchar(24) serial number vendor varchar(24) vendor inet number(4) number of networking interfaces purchased date purchased date cost number(10) cost in CHF table m_disks -------------------------- hostid number(10) computer id name varchar(10) disk name (as in /etc/fstab) type varchar(24) disk type size number(10) size in GB available number(10) available GB purchased date purchased date vendor varchar(24) cost number(10) cost in CHF dirs varchar(255) directories