Hasznos SQL lekérdezések

Takarításhoz

Nem használt aktív management class-ok listázása

select domain_name,class_name as "MGMT CLASS without OBJECTS" from mgmtclasses where class_name not in (select distinct class_name from backups) and class_name not in (select distinct class_name from archives) and set_name='ACTIVE'

Milyen management class-ok alatt vannak egy node adatai letárolva?

select distinct node_name,class_name from backups where node_name='NODE'
select distinct node_name,class_name from archives where node_name='NODE'

Hibás PRIVATE volume-ok listázása

Olyan volume-ok, amelyek Private státuszúak, de nem voltak még használva és stg poolhoz sincsenek rendelve.

SELECT volume_name, library_name FROM libvolumes WHERE status='Private' AND last_use IS NULL AND volume_name NOT IN (SELECT volume_name FROM volumes )

Nem használt Client Optionset-ek listázása

select OPTIONSET_NAME from CLOPTSETS where OPTIONSET_NAME not in (select OPTION_SET from nodes where OPTION_SET!='NULL')

Hibakereséshez

Egy tape device-hoz tartozó drive és path ONLINE állapotának ellenőrzése

select dr.LIBRARY_NAME, dr.DRIVE_NAME, dr.ONLINE as "DRIVE ONLINE?",  pa.ONLINE as "PATH ONLINE?" from drives as dr, paths as pa where pa.DESTINATION_NAME=dr.DRIVE_NAME and pa.DEVICE='/dev/rmt87'

Írási/olvasási hibás szalagok listázása

select substr(VOLUME_NAME,1,10),substr(STGPOOL_NAME,1,20),substr(DEVCLASS_NAME,1,20),WRITE_ERRORS,READ_ERRORS from volumes where WRITE_ERRORS>0 or READ_ERRORS>0

Sorozatosan (3x) "Missed" státuszú ütemezésű node-ok listája

select substr(a.node_name,1,30) as "NODE NAME", a.schedule_name, substr(b.domain_name,1,10) as DOMAIN, substr(b.contact,1,30) as "CONTACT", substr(a.status,1,10) as STATUS, count(*) as "3 DAY COUNT" from EVENTS a, nodes b -
where (a.scheduled_start >=current_timestamp - 3 days) and a.node_name=b.node_name and a.status='Missed' and a.node_name is not NULL -
group by a.node_name, b.domain_name, a.schedule_name, a.status, b.contact -
having count(*)>=3 -
order by "3 DAY COUNT" desc

Össszesített drive használat kijelzése driveonként

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

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 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

Egyéb

STGPOOL-ok mérete és az alattuk levő DEVCLASS és LIBRARY

SELECT substr(o.stgpool_name,1,25) as STGPOOL_NAME,substr(case when s.devclass='DISK'  then s.devclass else s.devclass || ' (' || d.devtype || ')' end,1,25) as DEVCLASS ,substr(d.library_name,1,20) as LIBRARY_NAME,CAST(FLOAT(SUM(o.logical_mb))/1024/1024 AS DEC(6,2)) as TB FROM stgpools s, occupancy o, devclasses d where s.stgpool_name=o.stgpool_name and s.devclass=d.devclass_name GROUP BY o.stgpool_name,s.devclass,d.devtype,d.library_name ORDER BY o.stgpool_name

Inaktív node-ok, és tárolt adatmennyiségük

select substr(node_name,1,30) as node, (select domain_name from nodes no where no.node_name=oc.node_name),(select (days(current_timestamp)-days(lastacc_time)) as LAST_ACCESS  from nodes no where no.node_name=oc.node_name), sum(REPORTING_MB)/1024 as GB from occupancy oc where (select (days(current_timestamp)-days(lastacc_time)) as LAST_ACCESS  from nodes no where no.node_name=oc.node_name)>30 GROUP BY oc.node_name order by last_access

Kliens verziók darabszáma

SELECT l.PRODUCT_D || ' (' || TRIM(CHAR(n.client_version))||'.'||TRIM(CHAR(n.client_release)) || ')' as TSM_Client_Version, count(*) as number_nodes FROM nodes n LEFT JOIN pvuestimate_details l on n.node_name=l.node_name where n.locked='NO' and not n.tcp_address is NULL grOUP BY l.PRODUCT_D || ' (' || TRIM(CHAR(n.client_version))||'.'||TRIM(CHAR(n.client_release)) || ')'

Inaktív node-ok száma domain-enként

select no.domain_name, count(no.node_name) as inactive_nodes,(select count(node_name) as ALL_NODES from nodes no2 where no2.domain_name=no.domain_name) from nodes no where (days(current_timestamp)-days(no.lastacc_time)) \> 30 group by no.domain_name

Expiration futási idők elmúlt 30 napban

dsmadmc -id=user -pa=jelszo -displ=list -dataonly=y "q ac msgno=0167 begind=-30 endd=today" | tr '\n' ' ' | sed 's/  Message//g' | sed 's/Date.Time: /\n/g' | sed 's/ (SESS.*$//' | awk '{if ($12>0) print}'

Elmúlt egy hét adatmozgásai

SELECT date(end_time),substr(activity,1,20), cast(float(sum(bytes))/1024/1024/1024 as dec(8,2)) as GB FROM summary WHERE end_time>current_timestamp-168 hours and activity<>'EXPIRATION' and bytes>0 GROUP BY date(end_time),activity ORDER BY date(end_time) asc

TSM segéd script node-ok domain-ek közti mozgatásához

Kilistázza a node(ok) által használt mgmt class-ok forrás és cél oldali megőrzési ideit, hogy össze lehessen hasonlítani, megfelelnek- e az új domain megőrzési paraméterei. Ezután kilistázza a parancsokat a node(ok) által használt ütemezések lemásolásához az új domain-be, az update parancsokat a node(ok) áthelyezéséhez, majd az új asszociációkat bedefiniáló parancsokat.

Használat:

TSM>run tsm_move node forrás_domain cél_domain
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'))

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

Hasznos TSM SQL oldalak

sql.txt · Utolsó módosítás: 2017/03/28 15:56 szerkesztette: admin
CC Attribution-Share Alike 4.0 International
Driven by DokuWiki Recent changes RSS feed Valid CSS Valid XHTML 1.0