Useful FTS database queries for FTS release 2.0.

Command Line Access to the Database.

$ . /opt/glite/yaim/etc/site-info.def
$  sqlplus $FTA_GLOBAL_DB_USER/"$FTA_GLOBAL_DB_PASSWORD@$FTA_GLOBAL_DB_CONNECTSTRING"
SQL>

rlwap

$  rlwrap sqlplus $FTA_GLOBAL_DB_USER/"$FTA_GLOBAL_DB_PASSWORD@$FTA_GLOBAL_DB_CONNECTSTRING"
SQL>

Schema Versions

SQL> SELECT major || '.' || minor || '.' || patch FROM t_schema_vers ;
MAJOR||'.'||MINOR||'.'||PATCH
--------------------------------------------------------------------------------
3.0.0

Add a New Channel Direct in the Database

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

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.

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