select -
  sum((end_time-start_time) minutes) as "Minutes", -
  decimal(cast(sum((end_time-start_time) minutes) as decimal (6,2)) /1440*100,6,2) as "% of 24 hour", -
  drive_name as "Drive" -
from -
  summary -
where -
  activity='TAPE MOUNT' -
  and days(current_timestamp)-days(start_time)=1 -
group by -
  drive_name

Melyik node-ok írnak közvetlenül szalagra?

select substr(message,instr(message,' ',1,6)+1,instr(message,' ',1,7)-instr(message,' ',1,6)-1) as NODE, count(*) as TAPE_MOUNTS from actlog where msgno in (0406,0839) and session in (select session from actlog where msgno=0511 and substr(message,instr(message,' ',1,6)+1,1)!='/') and current_timestamp < date_time + 7 days group by substr(message,instr(message,' ',1,6)+1,instr(message,' ',1,7)-instr(message,' ',1,6)-1)

select substr(date_time,1,19) as date_time, node from (select session,message,msgno,date_time,substr(substr(message,instr(message,' ',1,6)+1,instr(message,' ',1,7)-instr(message,' ',1,6)-1),1,30) as NODE from actlog where msgno in (0406,0839)) where session in (select session from actlog where msgno=0511 and current_timestamp < date_time + 30 days and substr(message,instr(message,' ',1,6)+1,1)!='/') order by date_time

Drive használat adatgyűjtő

#!/bin/ksh
DATE=`date +%b-%d`
TIME=`date +%Y.%m.%d %H%M`
TIMEA=`date +%H:%M`
STAT=drive_stat.csv
USERID=tsmuser
PASSWD=tsmpass
COMD="dsmadmc -id=$USERID -password=$PASSWD -dataonly=yes -noconfirm"

DRIVE1=`$COMD "q mount" | grep '[D]RV01' | wc -l`
DRIVE2=`$COMD "q mount" | grep '[D]RV02' | wc -l`

if [ "$TIME" -eq "2355" ]; then
        echo "$TIMEA,$DRIVE1,$DRIVE2" >> $STAT
        mv -f $STAT stat$DATE.csv
else
        echo "$TIMEA,$DRIVE1,$DRIVE2" >> $STAT
fi

Drive hasznalat orankenti lebontasban:

select ORA, cast(round(sum(USAGE) / (select count(*) from drives where online='YES')) as dec(4,0)) from ( \
select concat(date(start_time) || ' ',substr(time(start_time),1,2)) as ORA, sum( cast(CASE WHEN substr(end_time,1,13)=substr(start_time,1,13) THEN timestampdiff(4,end_time-start_time) WHEN substr(end_time,1,13)>substr(start_time,1,13) THEN timestampdiff(4,timestamp(concat(date(start_time) || ' ', substr(time(start_time),1,2) || ':59:59.000000'))-start_time) END as dec(5,2))) /60*100 as USAGE from summary where ACTIVITY='TAPE MOUNT' and start_time>current_timestamp - 24 hours group by concat(date(start_time) || ' ',substr(time(start_time),1,2)) \
UNION ALL \
select concat(date(end_time) || ' ',substr(time(end_time),1,2)) as ORA, sum( cast(CASE WHEN substr(end_time,1,13)>substr(start_time,1,13) THEN timestampdiff(4,end_time-timestamp(concat(date(end_time) || ' ', substr(time(end_time),1,2) || ':00:00.000000'))) END as dec(5,2))) /60*100 as USAGE from summary where ACTIVITY='TAPE MOUNT' and end_time>current_timestamp - 24 hours group by concat(date(end_time) || ' ',substr(time(end_time),1,2)) \
) group by ORA ORDER BY ORA

Drive használat grafikon az activity logban található "QUERY MOUNT" kimenetekből

#!/bin/bash
TSMUSER=userem
TSMPASS=jelszavam
TSMSERVER=TSM1
PAR1=$1
PAR2=$2
PAR3=$3
FROM=${PAR1:-24}
TO=${PAR2:-0}
DEV=${PAR3:-LTO}

red="\033[31m"
normal="\e[0m"

function seq {
  if [ $1 > $2 ] ; then
    for ((i=$1; i<=$2; i++))
      do echo $i
    done
  else
    for ((i=$1; i>=$2; i--))
      do echo $i
    done
  fi
}

DR_MAXNUM=$(dsmadmc -id=$TSMUSER -pa=$TSMPASS -se=$TSMSERVER -dataonly=y "select count(*) from drives where device_type='$DEV'")
dsmadmc -id=$TSMUSER -pa=$TSMPASS -se=$TSMSERVER -dataonly=y "select substr(DATE_TIME,1,19) as DATE_TIME, count(MESSAGE) from ACTLOG where MESSAGE like '%$DEV volume %% is mounted%' and current_timestamp - $FROM hours < DATE_TIME and current_timestamp - $TO hours \> DATE_TIME group by substr(DATE_TIME,1,19)" | while read SOR; do
    DATUM_IDO=$(echo $SOR | awk '{print $1}')
    DR_USAGE=$(echo $SOR | awk '{print $2}')
    DRNUM=""
    for I in `seq 1 $DR_USAGE`; do
        DRNUM="$DRNUM#"
    done
    for I in `seq $DR_USAGE $(($DR_MAXNUM-1))`; do
        DRNUM="$DRNUM "
    done
    if [[ $DR_USAGE -ge $(($DR_MAXNUM*0,8)) ]]; then
        echo -e "${DATUM_IDO}\t|${red}${DRNUM}${normal}|"
    else
        echo -e "${DATUM_IDO}\t|${DRNUM}|"
    fi
done | less -R

select substr(domain_name,1,15) as "Forras domain",substr(class_name,1,15) as "CLASS_NAME",VEREXISTS,VERDELETED,RETEXTRA,RETONLY from bu_copygroups where domain_name=upper('$2') and set_name='ACTIVE' and class_name in (select distinct class_name from backups where node_name like upper('$1%'))
select substr(domain_name,1,15) as "Cel domain",substr(class_name,1,15) as "CLASS_NAME",VEREXISTS,VERDELETED,RETEXTRA,RETONLY from bu_copygroups where domain_name=upper('$3') and set_name='ACTIVE' and class_name in (select distinct class_name from backups where node_name like upper('$1%'))
select substr(domain_name,1,15) as "Forras domain",substr(class_name,1,15) as "CLASS_NAME",RETVER from ar_copygroups where domain_name=upper('$2') and set_name='ACTIVE' and class_name in (select distinct class_name from archives where node_name like upper('$1%'))
select substr(domain_name,1,15) as "Cel domain",substr(class_name,1,15) as "CLASS_NAME",RETVER from ar_copygroups where domain_name=upper('$3') and set_name='ACTIVE' and class_name in (select distinct class_name from archives where node_name like upper('$1%'))
select 'copy sched $2 ' || SCHEDULE_NAME || ' $3 ' || SCHEDULE_NAME as "Utemezesek masolasa" from associations where node_name in (select node_name from nodes where node_name like upper('$1%') and domain_name=upper('$2'))
select 'upd n ' || node_name || ' do=$3' as "Node(ok) atmozgatasa" from nodes where node_name like upper('$1%') and domain_name=upper('$2')
select 'def assoc $3 ' || SCHEDULE_NAME || ' ' || node_name as "Uj asszociaciok bedefinialasa" from associations where node_name in (select node_name from nodes where node_name like upper('$1%') and domain_name=upper('$2'))

SPP -> SP archivált VM-ek listája

select substring(substring(hl_name, instr(hl_name, '/', -2) + 1), 1, instr(substring(hl_name, instr(hl_name, '/', -2) + 1), '.') - 1) as VM, substring(BACKUP_DATE, 1, 10) as ARCH_DATE from backups where node_name = 'SPP_ARC' and hl_name like 'spp_archive%vmdk.tar.lz4' and hl_name not like '%-flat.vmdk.tar.lz4' group by substring(substring(hl_name, instr(hl_name, '/', -2) + 1), 1, instr(substring(hl_name, instr(hl_name, '/', -2) + 1), '.') - 1), substring(backup_date,1,10)

Primary és azonos nevű copy pooljaik összehasonlítása

select cast(p.STGP_P as char(10)) STGPOOL_PREFIX,p.NUM_FILES-c.NUM_FILES DIFF_NUM,cast(p.LOGICAL_GB-c.LOGICAL_GB as decimal(9,1)) DIFF_GB,p.NUM_FILES PRIMARY_NUM,c.NUM_FILES COPY_NUM,cast(p.LOGICAL_GB as decimal(9,1)) PRIMARY_GB,cast(c.LOGICAL_GB as decimal(9,1)) COPY_GB from -
(select substr(STGPOOL_NAME,1,instr(STGPOOL_NAME,'_')-1) STGP_P,sum(NUM_FILES) NUM_FILES,sum(LOGICAL_MB)/1024 LOGICAL_GB from occupancy where STGPOOL_NAME like '%\_D' escape '\' or STGPOOL_NAME like '%\_F' escape '\' or STGPOOL_NAME like '%\_T' escape '\' group by substr(STGPOOL_NAME,1,instr(STGPOOL_NAME,'_')-1)) p left outer join -
(select substr(STGPOOL_NAME,1,instr(STGPOOL_NAME,'_')-1) STGP_C,sum(NUM_FILES) NUM_FILES,sum(LOGICAL_MB)/1024 LOGICAL_GB from occupancy where STGPOOL_NAME like '%\_C' escape '\' group by substr(STGPOOL_NAME,1,instr(STGPOOL_NAME,'_')-1)) c on -
p.STGP_P = c.STGP_C order by case when DIFF_NUM is null then 1 else 0 end, STGPOOL_PREFIX, DIFF_NUM

select cast(p.STGP_P as char(20)) STGPOOL_PREFIX,cast((p.NUM_FILES-c.NUM_FILES) as decimal(10)) DIFF_NUM,cast(p.LOGICAL_GB-c.LOGICAL_GB as decimal(9,1)) DIFF_GB,cast(p.NUM_FILES as decimal(10)) PRIMARY_NUM,cast(c.NUM_FILES as decimal(10)) COPY_NUM,cast(p.LOGICAL_GB as decimal(9,1)) PRIMARY_GB,cast(c.LOGICAL_GB as decimal(9,1)) COPY_GB from (select rtrim(STGPOOL_NAME,'_(TFD)') STGP_P,sum(NUM_FILES) NUM_FILES,sum(LOGICAL_MB)/1024 LOGICAL_GB from occupancy where STGPOOL_NAME like '%\_D' escape '\' or STGPOOL_NAME like '%\_F' escape '\' or STGPOOL_NAME like '%\_T' escape '\' group by rtrim(STGPOOL_NAME,'_(TFD)')) p left outer join (select rtrim(STGPOOL_NAME,'_C') STGP_C,sum(NUM_FILES) NUM_FILES,sum(LOGICAL_MB)/1024 LOGICAL_GB from occupancy where STGPOOL_NAME like '%\_C' escape '\' group by rtrim(STGPOOL_NAME,'_C')) c on p.STGP_P = c.STGP_C order by case when DIFF_NUM is null then 1 else 0 end, STGPOOL_PREFIX, DIFF_NUM

select substr(n.node_name,1,20) as Node, substr(n.domain_name,1,15) as Domain, cast((m.set_name) as char (7)) as Set, csch.schedule_name as Schedule, csch.action, csch.options, csch.objects, csch.startdate || ' ' || csch.starttime as START_TIME, csch.period || ' ' || csch.perunits as PERIOD, case when csch.sched_style='ENHANCED' then csch.dayofweek || ',' || csch.enh_month || ',' || csch.dayofmonth || ',' || csch.weekofmonth else csch.dayofweek end as melyik_nap, substr(csch.expiration,1,19) as EXPIRATION, m.class_name as MgmtClass, cast((m.defaultmc) as char(6)) as Deflt, cast((verexists) as char(8)) as VerEx, cast((verdeleted) as char(8)) as VerDel, cast((retextra) as char(8)) as RetEx, cast((retonly) as char(8)) as Retonly FROM nodes as n, mgmtclasses as m, bu_copygroups as c, associations as a, client_schedules csch WHERE a.domain_name = csch.domain_name AND a.schedule_name = csch.schedule_name and n.domain_name=m.domain_name and m.domain_name=c.domain_name and m.set_name=c.set_name and m.class_name=c.class_name and m.class_name in (select distinct class_name from backups where node_name=n.node_name) and m.set_name='ACTIVE' and a.node_name=n.node_name order by n.node_name,n.domain_name,Set,MgmtClass