Show Children Hide Children

Main FTS Pages
FtsRelease22
Install
Configuration
Administration
Procedures
Operations
Development
Previous FTSes
FtsRelease21
FtsRelease21
All FTS Pages
FtsWikiPages
Last Page Update
SteveTraylen
2007-10-08

Useful FTS database queries for FTS release 2.0.

It is sometimes required to access and run some special custom query on the database to obtain particular information not available via the FTS application.

Command Line Access to the Database.

Use YAIM's site-info.def file to get the values to connect: $FTA_GLOBAL_DB_CONNECTSTRING, $FTA_GLOBAL_DB_USER and $FTA_GLOBAL_DB_PASSWORD.

$ . /opt/glite/yaim/etc/site-info.def
$  sqlplus $FTA_GLOBAL_DB_USER/"$FTA_GLOBAL_DB_PASSWORD@$FTA_GLOBAL_DB_CONNECTSTRING"
SQL>
or if you have rlwap installed.
$  rlwrap sqlplus $FTA_GLOBAL_DB_USER/"$FTA_GLOBAL_DB_PASSWORD@$FTA_GLOBAL_DB_CONNECTSTRING"
SQL>

Schema Versions

To find the current installed schema version.
SQL> SELECT major || '.' || minor || '.' || patch FROM t_schema_vers ;
MAJOR||'.'||MINOR||'.'||PATCH
--------------------------------------------------------------------------------
3.0.0

Add a New Channel Direct in the Database

Instead of using the web service and client commands you may want to add a channel around the back door.
SQL> insert into t_channel (channel_name,SOURCE_SITE,DEST_SITE,NOSTREAMS,NOFILES,CHANNEL_STATE) 
              VALUES ('STAR-CERN','*','CERN','10','40','Active');

List of Active Users on a Channel

To get a list of DNs submitting jobs to a channel:
SQL> select distinct user_dn, count(*) from t_job where channel_name = 'STAR-CERN' and 
             submit_time >= TIMESTAMP '2007-03-19 00:00:00 +00:00' group by user_dn ;
USER_DN
--------------------------------------------------------------------------------
  COUNT(*)
----------
/C=CH/O=CERN/OU=GRID/CN=XXXXXXXXXXXXX
       548

/DC=ch/DC=cern/OU=Organic Units/OU=Users/CN=YYYYYYYYYYYYYYYYYYYYY
       974
SQL> 

Check which VO a DN is in.

Given a DN to check which VO the user is operating as.
SQL> select distinct vo_name,count(*) from t_job where user_dn='/C=RU/O=RDIG/OU=users/OU=sinp.msu.ru/CN=XXXXX' 
               group by vo_name;

VO_NAME                                              COUNT(*)
-------------------------------------------------- ----------
cms                                                      3348

Count of the Active Transfers by a VO on a Channel

select tx_time, running_total  from (
select distinct tx_time, sum(value) over (order by tx_time asc)
running_total from (
select 
  decode(x,1,tx_start,tx_finish) tx_time,
  decode(x,1,1,-1) value
from (
    select SYS_EXTRACT_UTC(t_transfer.transfer_time) tx_start
        ,  SYS_EXTRACT_UTC(t_transfer.transfer_time + NUMTODSINTERVAL
(t_transfer.duration, 'SECOND')) tx_finish
    from t_transfer, t_job 
    where t_transfer.job_id = t_job.job_id
    and t_job.vo_name = 'cms'
    and t_job.channel_name = 'CERN-INFN'
    and SYS_EXTRACT_UTC(t_transfer.transfer_time) >= 
            TIMESTAMP '2007-05-10 14:00:00 +02:00'
) , (
  select 1 x from dual 
  union all
  select 2 from dual
))) order by tx_time asc

-- SteveTraylen - 13 Apr 2007

Edit | Attach | Watch | Print version | History: r6 < r5 < r4 < r3 < r2 | Backlinks | Raw View | WYSIWYG | More topic actions
Topic revision: r6 - 2007-10-08 - SteveTraylen
 
    • Cern Search Icon Cern Search
    • TWiki Search Icon TWiki Search
    • Google Search Icon Google Search

    LCG All webs login

This site is powered by the TWiki collaboration platform Powered by PerlCopyright & 2008-2019 by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding TWiki? Send feedback