D0 Database WIP and Available Infrastructure: Updated Nov, 2002

A description of the D0 database effort is given below including the people involved and the status of each set of tables being developed. Also, a description of the methodology required to get a database application into production, and the tools available are outlined.

Database Applications

The following databases applications are in some stage of planning, design, and/or development. Some are being used at the production level. Data in BLUE font are proposed names or standards.

Application Name	Design	Dev/Int/ Prd	Responsible Party(s)	Comments	Milestone	Designer Owner	Application Owner	CVS Name(s)	Tablespace Name
Online Apps
SMT Calibration	Y	D/P	Harold Fox			smt_calib_design	smt_calib	onl_smt_calib_db	smt_calib
CAL Calibration	Y	D/P	Ursula Bassler			cal_calib_design	cal_calib	onl_cal_calib_db	cal_calib
CFT Calibration	Y	D/P	Andrei Mayorov			cft_calib_design	cft_calib	onl_cft_calib_db	cft_calib
Muon Calibration	Y	D/P	Andrzej Zieminski, Jundong Huang, Usha Rao			muon_calib_design	muon_calib	onl_muon_calib_db	muon_calib
Hardware Controls	Y	D/P	Stan K, Jeremy Simmons, V. Sirotenko			hdb_design	hdb	hdbAdmin	hdb
Run Controls/Run Grabber/Run Summary	Y	D/P	Jeremy Simmons, Vladimir Sirotenko			run_ctl_design	run	onl_run_db	run
Offline Calibration
Offline top-level calibration	Y	D/I/P	Taka Yasuda			calib_design	offline_calibration	d0_calibration_db	offline_calibration
Offline CAL calibration	Y	D	Ursula Bassler				cal_calib_design	cal_calibration_db	cal_calib
Offline SMT Calibration	Y	D/I/P	Marian Zdrazil, John Weigand, Taka Yasuda			smt_calib_design	smt_calib_design	smt_calibration_db	smt_calib
Offline Muon MDT, MSC, PDT Calibration	Y	D/I/P	Andrzej Zieminski, Jundong Huang, Usha Rao			muon_calib_design	muon_calib_design	muon_MDT_calibration_db, muon_MSC_calibration_db, muon_PDT_calibration_db	muon_calib
CFT Offline Calibration	Y	D/I/P	Andrei Mayorov				cft_calib_design	cft_calibration_db	cft_calib
CPS Offline Calibration	Y	D/I/P	Zhong-min Wang				cps_calib_design	cps_calibration_db	cps_calib
FPS Offline Calibration	Y	D	Zhong-min Wang			fps_calibration_design	calibration_design	fps_calibration_db	fps_calib
FPD Offline Callibration	Y	D	Christophe Royon			Not in design yet	fpd_calib_design	fpd_calibration_db	fpd_calib
Offline luminosity and streams	N		Michael Begel, Jeremy Simons				luminosity_design	luminosity_db	luminosity
L1, L2 , L3 Trigger	Y	D/I/P V 0	Elizabeth Gallas, Jeremy Simons.			trigger_design	trigger_design	trigger_db	trigger
Survey Apps
SMT Survey	N		Veronica Sorin/ Yuri Gerstein				smt_survey_design	smt_survey_db	smt_survey
CAL Survey	N		Rich Smith?				cal_survey_design	cal_survey_db	cal_survey
CPS Survey	N		Dong, Phil				cps_survey_design	cps_survey_db	cps_survey
FPS Survey	N		Sailesh Chopra				fps_survey_design	fps_survey_db	fps_survey
MUON Survey	N		Andrzej Zieminski, Al Ito			muon_survey_design	muon_survey_design	muon_survey_db	muon_survey
Solenoid Survey	N		Rich Smith			solenoid_survey_design	solenoid_survey_design	solenoid_survey_db	solenoid_survey
Other Offline Apps
Sam File and Event	Y	D/I/P	SAM team			sam_design	sam_design	sam_db	sam
Colaboration and Speakers' Bureau	Y	D/I/P	Elizabeth Gallas			sb_design
Releases request	Y	D	Harry Melanson.
VLPC calibration		D/I	Volker Buescher			cassette_design	cassette_design	vlpc_db	vlpc
Data and MC processing Management	N	D	Lee Lueking, Carmenita Moore			sam_design	sam_design	sam_db	sam
RCP	Y		Marc Paterno, John Weigand			rcp_design	rcp_design	rcp_db	rcp

CPS, FPS and FPD systems use the CFT online calibration database to store the online calibration constants instead of their own databases.

Procedure for Rolling Out a Database

The procedure for making a database application, from plan to production includes the following:

Rough draft. Plan with rough idea of what the functionality, data and relationships among the tables might be.
Use Oracle Designer to create the schema design, and designer repository.
All development is performed using a "development" instance of the database. DDL saved in cvs.
Employ the DDL which is created from Designer to create the tables, keys, triggers.
Must provide rate and sizing information for DBA's.
Setup the cutting scripts as a product on d0mino, or d0ola. An INT instance is created with controlled DDL and thoroughly tested.
When the application is ready for production use, DDL has version control imposed and the PRD database instance is created. The methodology for version cutting and evolution is written up.

HELP IS AVAILABLE from database support group to get started using Oracle Designer, and at all levels of the procedure. Julie Trumbo (offline) and Jeremy Simmons (online) help with designs.

Current Database Hardware

On-line has d0olb for development and d0ola is planned for production. Offline has d0ora1 (currently integration and production) and d0ora3 (currently development).

D0OM-Oracle and CORBA Backends

There are currently two ways for D0 applications to connect to the database through the d0 database server mechanism. D0OM-CORBA has support only for reading information from the database and can NOT write or update the database. A specialized manager would need to call a tailored DB server to provide additional functionality.

The framework needs to support multiple ORB users. There was an early discussion about this in a March SAM meeting and Steve White is working on a set of CORBA utilities which will be distributed through the normal SAM distribution channel, and through the D0 code tree. Jim K. is prepared to use these in the D0Framework.

Database Servers

Database servers can run on any platform as they communicate to Oracle via SQL*NET. Currently, all the db servers run on the database server machines. Eventually, there may be a need for a caching server to be included in the calibration server, where this would best be operated is still to be determined. It might make sense, for example on the farms, to run such a server near the worker nodes (d0bbin might be appropriate). There may also be a need to run an additional calibration server on the central-analysis machine for analysis users.

There is a standard db_server_gen tooled to generate most of what is needed to make a database server for a given set of database tables. Documentation for this is available though could be improved.

Other Ways to Access DB info

Al Ito and Veronica Sorin have been using Excel spreadsheets to enter survey constants into the database. For the muon chamber constants, the data is entered into a spreadsheet, then uploaded to a temporary database, massaged, and both the raw information and the new info are stored. There are tutorials available from the d0db page. These methods are not automatable.

Web access to tables is provided through the use of DCORACLE and the misweb tool. Harry Melanson has used htmlgen to generate web pages using a python script. Web serving on D0world is not set up like the ora machines with some kind of configuration control. This needs someone to clean it up.

There are some cases of special GUIs that have been developed as interfaces for examining and manipulating DB info. Examples of tools written in Java include the L1/L2 trigger worksheet which allows drag and drop features for working with trigger sets, and the sam project editor and admin console.