The goal of this tutorial is to teach basic Oracle/SQL functionality in the context of the CMS Tracker construction project and associated 'TrackerDB' Oracle database. It is not intended to be comprehensive; the reader is expected to read all the documentation linked in the document. It is intended to teach the reader how to use SQL to execute simple queries that will return some data from the TrackerDB. It is assumed that the reader will then read more advanced Oracle documentation as more complicated queries become desireable.
Data recorded during construction of the CMS Tracker is stored in the TrackerDB, an Oracle Database. Oracle is a (proprietary) Relational Database Management System (RDBMS). 'Relational' denotes the tabular strucutre in which the data is stored. The data is ultimately stored in files. Oracle's job is to manage these files, providing a means for accessing & modifying existing data and creating new data. Communication with the Oralce server can be facilitated by a number of protocols, the most popular of which is probably SQL (Structured Query Language). The SQL syntax supported by Oracle does differ from the standard (ANSI) SQL, used by MySQL and PostgreSQL. The TrackerDB is running Oracle version 9i as of 6/16/2005.
One great utility of Oracle databases, is that data can remotely be read from or written to the database using the internet. The TrackerDB is not connected to the internet directly. Instead it is served to the internet by the hosts 'cmstrkdb.in2p3.fr' and 'lyopc72.in2p3.fr'.
Much information is available using the BigBrowser, a java application, and associated plugins. For instance, histograms of metrology data from gantry assembly are well implemented in the BigBrowser. However, the data that can be obtained and the format the data is dislplayed in are both limited by the programming behind the BigBrowser.
The perl program relay_v2.5.pl (by Thomas Bergauer) accepts a file containing a SQL query as an argument and returns the response from the TrackerDB to STDOUT. The Oracle server has a daemon running on a specified port that is constantly listening for connections using the TCP/IP protocol over the internet. The details are not important, but can be found in relay_v2.5.pl if desired. It is enough to understand that the relay program accepts a query expressed in the SQL language as input and then handles the details of requesting and returing the data over the internet. The relay application is documented on Thomas Bergauer's pageand on the TrackerDB home page.
On a unix/linux system with perl5 installed and an internet connection, data can be retrieved from the TrackerDB using relay_v2.5.pl:
>perl relay_v2.5.pl [query_file.sql]
Where 'query_file.sql' is a file containing an Oracle-SQL query. For instance, one of the following example queries could be used:
| SQL Query File | Description |
|---|---|
| Object Assembly | Dumps all data from 'OBJECT_ASSEMBLY' for a particular part. |
| Object Description | Dumps all data from 'OBJECT_DESCRIPTION' |
| 4-Hybrid | Dumps all data from 'TESTWITHPA_1_HYB_' for a particular hybrid. |
| ARCS | Dumps all ARCS testing data for a particular module. |
| History | Dumps all data from the HISTORY table for a particular part serial number. |
| LT FIRST | Dumps all LT data for the first test of a particular module. |
| LT COLD | Dumps all LT data for the cold test of a particular module. |
| LT LAST | Dumps all LT data for the last test of a particular module. |
How can object data stored in different tables be returned in the same query? Tables can be joined together into composite virtual tables when the proper conditions are specified. Consider an example. Each object (Hybrid, Module, etc.) in the TrackerDB is assigned a numeric code specifying its type. This is the column 'type' from the table 'OBJECT_ASSEMBLY'. This numerical code is of little use to humans in practice, since there are many codes and it is difficult to remember a large number for each part type. What is useful for humans is the column 'type_description' in the table 'OBJECT_DESCRIPTION'. Given the type and version of an object, its description can be obtained from this table. For example, modules of type 3.3.13.11 have the type description 'OB_L56P.6U'. Here, 'OB' stands for 'outer barrel', 'L56' stands for 'layers 5 and 6 R-Phi', and '.6U' means the module has 6 read out chips with connectors facing up. Since the type description can be interpereted to understand some physical properties of the module, typically one is interested in the type description and not the type. The query 'join1.sql' shows how to join the columns 'object_id' and 'type' from table 'OBJECT_ASSEMBLY' with the column 'type_description' from 'OBJECT_DESCRIPTION', where the type and version are the same.
| SQL Query File | Description |
|---|---|
| join1.sql | Joining 'OBJECT_ASSEMBLY' and 'OBJECT_DESCRIPTION' |
Data recorded during module production is stored in xml format. Each action (4-hybrid testing, ARCS testing, LT testing) has an xml format defined for how data will be stored in a particular xml file. XML files generally contain raw data, as well as instructions for how & where the data should be uploaded into the TrackerDB. Data can be inserted into the TrackerDB from both Windows and Unix/Linux platforms.
XML files can be uploaded using the BigBrowser, but it is not generally practical to do so.
There are at least 6 xml files that must be uploaded for each module produced. At UCSB, the current target production rate is 18 modules per day (soon to increase). This means over 100 XML files need to be uploaded daily. It is not practical to upload 100 XML files using the BigBrowser. Instead, there is a command line utility that accepts a path to an XML file as an argument and uploads the data. The program Update.bat should be used to upload data from Windows platforms. The program CmdLineBB should be used to upload data from Unix/Linux platforms. The following parameters must be set for either program to work correctly:
| Parameter | Description of Correct Value |
|---|---|
| INST_DIR | Path to installation of TrackerDB |
| arg0 | Center |
| arg1 | Tool ID |
| arg2 | Working Directory (Log files will be written into this directory) |
| arg3 | '$INST_DIR' (CSH), '%INST_DIR%' (Windows) |
| arg4 | 'pro' |
| arg5 | Password for TrackerDB |
Both programs accept the path to an xml file as an argument. The usage is:
Windows:
C:\TrackerDB\>Update.bat [xml file]
Unix/Linux:
../TrackerDB/>./CmdLineBB [xml file]
where '[xml file]' is the full path of the xml file to be uploaded.
| Production Stage | XML Files |
|---|---|
| Shipping & Receiving | Big Browser |
| Hybrid Wire Bonding | |
| Hybrid Pull Testing | |
| Hybrid Testing | hybrid-test_example.xml |
| Module Registration | Big Browser |
| Gantry Assembly | gantry_example.xml |
| OGP Survey | ogp_example.xml |
| Module Wire Bonding | |
| Module Pull Testing | |
| Module ARCS Testing | arcs_example.xml |
| Data | Tables |
|---|---|
| Shipping | TRANSFERS |
| HISTORY | |
| Hybrid Wire Bonding | |
| Hybrid Pull Testing | |
| Hybrid Testing | HYBMEASUREMENTS_2_HYB_ |
| TESTWITHPA_1_HYB_ | |
| Gantry | GANTRYVAL_1_MOD_ |
| SENSASSEMB_1_MOD_ | |
| HYBASSEMB_1_MOD_ | |
| SENSPOSCHECK_1_MOD_ | |
| HYBPOSCHECK1_1_MOD_ | |
| OGP | GANTRYVAL_1_MOD_ |
| GLUEMONITORING_1_MOD_ | |
| SENSPOSCHECK2_1_MOD_ | |
| HYBPOSCHECK2_1_MOD_ | |
| Module Wire Bonding | |
| Module Pull Testing | |
| Module ARCS Testing | MODULBASIC_2_MOD_ |
| MODVALIDATION_2_MOD_ | |
| Module LT Testing | MODULBASIC_2_MOD_ |
| MODLT_2_MOD_ | |
| MODULLTSUMMARY_2_MOD_ | |
| MODULLTFIRST_2_MOD_ | |
| MODULLTCOLD_2_MOD_ | |
| MODULLTLAST_2_MOD_ |
A tool id is uniquely assigned to each CMS production station. The tool id is uploaded to the database along with the data recorded. This way data can be traced back to the aparatus that recorded it. Organizing data by tool id can be useful, for instance, when comparing hybrid wirebonding data between the two wirebonding machines, or comparing ARCS data taken on different stands.
| UCSB Tool ID's | |
|---|---|
| Gantry Tool ID | 56 |
| OGP Tool ID | ? |
| K&S 8090 Wirebonder Tool ID | 127 |
| K&S 8060 Wirebonder Tool ID | 128 |
| 4-Hybrid Test Stand Tool ID | 801 |
| Module1 (ARCS) Tool ID | 802 |
| Module2 (ARCS) Tool ID | 803 |
| Module3 (ARCS) Tool ID | ? |
| Susy (LT) Tool ID | 804 |