meta data for this page
Ez a dokumentum egy előző változata!
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 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
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