Issues Affecting Use of MySQL TAG DB on tier2-06

Basic Info

• reader, writer and admin accounts
• database = tier2tagdb
• Note, the mysql set up by the Atlas software uses /usr/bin/mysql which is some ancient version 3 thing that someone needs to delete.
• Collections available
  • To get most current list log into mysql db using tagreader account and show tables.
  • testIdeal07_005711_TAG_v12000201 : contains TAG's for the forty files that we found for the test

Loading

Example of filling the DB using a POOL utility included in the release (12) from root files created by Jerry and stored locally. Note that there is no protection against duplicates, so if you execute this command again, the database will be 'corrupted' with duplicate entries.

CollAppend -dst testIdeal07_005711_TAG_v12000201 MySQLltCollection \
                   -dstconnect mysql://tagwriter:****@tier2-06.uchicago.edu/tier2tagdb \
                   -src /local/workdir/testIdeal_07.005711.PythiaB_bbmu6mu4X.recon.TAG.v12000201_tid002968/testIdeal_07.005711.PythiaB_bbmu6mu4X.recon.TAG.v12000201_tid002968._31-34 RootCollection \ 
/local/workdir/testIdeal_07.005711.PythiaB_bbmu6mu4X.recon.TAG.v12000201_tid002968/testIdeal_07.005711.PythiaB_bbmu6mu4X.recon.TAG.v12000201_tid002968._35-38 RootCollection 
...

Querying

MySQL direct: One can log into mysql directly and look at the (non link) table associated with your collection name. This is not a good long term solution for a variety of reasons: schema changes, etc.. We might check with Julius to see if someone has already put together some sort of web server or other viewer.

POOLTools: Example of showing how to dump the contents using a different POOL utility with resulting output. Yes, it's quite verbose.

CollListAttrib -src testIdeal07_005711_TAG_v12000201 MySQLltCollection -srcconnect mysql://tagreader@tier2-06.uchicago.edu/tier2tagdb

firstAttribListSpec is EventNumber:unsigned int, Luminosity:float, NTrk:unsigned int, Nvx:unsigned int, 
RandomNumber:unsigned int, RunNumber:unsigned int, Stream:unsigned int, TimeStamp:float, VtxX:float, VtxY:float, 

...

Collection name: testIdeal07_005711_TAG_v12000201
Collection type: MySQLltCollection
Number of attributes is: 150
Attribute list specification is: 
NAME: EventNumber                 TYPE: unsigned int
NAME: Luminosity                  TYPE: float
NAME: NTrk                        TYPE: unsigned int
NAME: Nvx                         TYPE: unsigned int
NAME: RandomNumber                TYPE: unsigned int
NAME: RunNumber                   TYPE: unsigned int

...

NAME: TauIdWord                   TYPE: unsigned int
NAME: TopWord                     TYPE: unsigned int
NAME: DatasetID                   TYPE: int
NAME: Fraction                    TYPE: float
NAME: Stream1_ref                 TYPE: string
NAME: StreamESD_ref               TYPE: string

Total number of events scanned in collection testIdeal07_005711_TAG_v12000201 (type: MySQLltCollection) is: 39850


Number of collections scanned is: 1
Total number of events scanned in all collections is: 3985

File Catalog

The TAG DB just provides tokens (GUID+offset). One needs a file catalog to actually locate and access the data files.

Issues about access and which catalog to use

• Can we use the LRC directly using the GUID returned from the TAG DB?
  • Result: The pfn (physical file name or url) in the catalog uses the gsiftp protocol, so direct use from athena does not work.
• Can we use dq2_poolFCjobO to build a local file catalog for files on the local storage element and could be used with the result from the TAG DB?
  • Result: Ostensibly possible, but confusion on which catalog dq2 uses, such that it does not see files in UC_VOB. More technically, it sees the LRC on tier2-01 but not on tier2-05.
• If we can't use files in local storage directly anyhow, can we find the files for a query on the TAG DB, transfer them to a worker node, and build a local catalog which athena can use.
  • Result using dq2 utilities:
    • dq2_get is not available on the worker node.
    • dq2_get does not accept GUID's as input, only lfn's.
    • Workaround 1
      1. can use something like FClistLFN -u -q "WHERE GUID= ..." to get lfn's then use dq2_get to transfer those.
      2. can use pool_insertFileToCatalog to build a local catalog once the files are transferred.
      3. can use CollCopy -src ... -srcconnect ... -dst jobN.localTAG RootCollection -queryopt "SELECT RunNumber, EventNumber" -query ... to create a local file with the TAG query results.
      4. can use jobN.localTAG.root as input to athena along with files and catalog.
    • Workaround 2
      1. can try to steal as much of workaround as possible from TNT implementation.
  • Result not using dq2 utilities:
    • POOL utilities are available from the release.
    • Can use Workaround 1 from above but replace dq2_get by lfn with a dccp operation.

-- JackCranshaw - 01 Nov 2006