Hasznos TSM SQL lekérdezések

SQL is implemented as if a query was executed in the following order:

  1. FROM clause
  2. WHERE clause
  3. GROUP BY clause
  4. HAVING clause
  5. SELECT clause
  6. ORDER BY clause

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

TSM szerver crash időpontok keresése actlog szünetek alapján

Min 300 mp-es réseket keresve az elmúlt egy hétben, crash előtti utolsó üzenettel (az utolsó oszlop mutatja percben, hogy mennyi ideig nem volt új actlog bejegyzés):

select unique, message as last_message, date_time, cast(diff/60 as dec(6,0)) from (select unique, message, date_time, cast(date_time-lead(date_time) over (order by date_time)  as dec(6,0)) as diff from actlog where date_time>current_timestamp - 7 days) where (diff < -300)

Ütemező IP címének lekérdezése

db2 connect to tsmdb1
db2 set schema tsmdb1
db2 "select * from schedule_node_addresses where nodeid=(select nodeid from nodes where nodename='KERESETTNODE')"
db2 connect reset

Drive és path ONLINE státuszok ellenőrzése

select substr(dr.LIBRARY_NAME,1,15) as LIBRARY, substr(dr.DRIVE_NAME,1,15) as DRIVE_NAME, substr(dr.ONLINE,1,3) as "DRIVE ONLINE?", substr(pa.SOURCE_NAME || ' - ' || pa.ONLINE,1,20) as "PATH ONLINE?", pa.device from drives as dr, paths as pa where pa.DESTINATION_NAME=dr.DRIVE_NAME order by library

Í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

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=0406 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)

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

Log4j sérülékenységben érintett TSM node-ok lekérdezése

select substr(node_name,1,25) as NODE_NAME, substr(client_products,1,10) as PRODUCT,PLATFORM_NAME,substr(CLIENT_VERSION || '.' || CLIENT_RELEASE || '.' || CLIENT_LEVEL || '.' || CLIENT_SUBLEVEL,1,10) as CLI_VER, day(current_timestamp - LASTACC_TIME) as LAST_ACC from nodes where locked='NO' and (LASTACC_TIME between current_timestamp - 30 days and current_timestamp) and not PLATFORM_NAME like 'TDP%' and not PLATFORM_NAME like 'DB2%' and ((CLIENT_VERSION=8 and CLIENT_RELEASE=1 and CLIENT_LEVEL in (11,12)) or (CLIENT_VERSION=8 and CLIENT_RELEASE=1 and CLIENT_LEVEL=13 and CLIENT_SUBLEVEL=0) or (CLIENT_VERSION=7 and CLIENT_RELEASE=1 and CLIENT_LEVEL=8 and CLIENT_SUBLEVEL in (10,11,12))) order by node_name ASC

Egyéb

String listában keresés

select * from TAB1 where ',' || 'ABC,BCD' || ',' like '%,' || Y || ',%' 

Node-ok melyeknél az utolsó mentés és a replikáció közt eltelt idő több, mint 30 óra

select NODE_NAME, min(day(BACKUP_END-LAST_REPL_COMP)*24+hour(BACKUP_END-LAST_REPL_COMP)) || ' hours ago' as LAST_REPLICATION from filespaces where node_name in (select distinct node_name from nodes where (LASTACC_TIME between current_timestamp - 3 days and current_timestamp)) and (BACKUP_END between LAST_REPL_COMP and current_timestamp) group by NODE_NAME having min(day(BACKUP_END-LAST_REPL_COMP)*24+hour(BACKUP_END-LAST_REPL_COMP))>=30 order by min(day(BACKUP_END-LAST_REPL_COMP)*24+hour(BACKUP_END-LAST_REPL_COMP))

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

Node-ok tárolt adatmennyisége primary poolokban

select NODE_NAME, case when sum(REPORTING_MB)/1024 < 1024 then sum(REPORTING_MB)/1024 || ' G' else sum(REPORTING_MB)/1024/1024 || ' T' end as PRIMARY_GB from occupancy where STGPOOL_NAME in (select STGPOOL_NAME from stgpools where pooltype='PRIMARY') group by node_name order by sum(REPORTING_MB) desc

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)) || ')'
SELECT l.PRODUCT_D || ' (' || TRIM(CHAR(n.client_version))||'.'||TRIM(CHAR(n.client_release)) ||'.'||TRIM(CHAR(n.client_level)) || '.'||TRIM(CHAR(n.client_sublevel)) || ')' 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)) ||'.'||TRIM(CHAR(n.client_level)) || '.'||TRIM(CHAR(n.client_sublevel)) || ')'

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

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

Node-ok ütemezései és megőrzési idejei

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

Node occupancy-k

select a.NODE_NAME, BACKUP_MB /1024 as BACKUP_GB, ARCHIVE_MB/1024 as ARCHIVE_GB,(select sum(PHYSICAL_MB) from occupancy where node_name like 'SP1-VADP-DCA%' and upper(filespace_name) like '%' || upper(a.node_name) || '%') as VM_BACKUP_GB  from auditocc a where 0<BACKUP_MB or 0<ARCHIVE_MB order by a.node_name
select substr(right(filespace_name,length(filespace_name)-8),1,20) as VM_NAME,sum(PHYSICAL_MB)/1024 as VM_BACKUP_GB from occupancy where node_name='VCENTER01' group by filespace_name

File és VM szintű mentési riport

select SZERVER, SCHEDULE_NAME as UTEMEZES_NEVE,substr(timestampdiff(16,current_timestamp - max(UTSO_MENTES)),1,10) as UTSO_MENTES from \
( \
SELECT substr(e.NODE_NAME,1,30) as SZERVER, \
substr(e.schedule_name,1,25) as schedule_name, \
e.COMPLETED as UTSO_MENTES \
from events e where not e.node_name in (select node_name from nodes where platform_name='TDP VMware') and e.schedule_name in (select s.schedule_name from client_schedules s where s.domain_name=e.domain_name and s.perunits!='ONE TIME' and (current_timestamp<s.expiration or s.expiration is NULL)) \
UNION \
SELECT substr(replace(f.filespace_name,'\VMFULL-','') || ' [VM]',1,30) as SZERVER, \
substr('VADP: ' || lower(f.node_name),1,25) as schedule_name, \
f.backup_end as UTSO_MENTES \
from filespaces f where f.node_name in (select node_name from nodes where  platform_name='TDP VMware') \
) \
WHERE not SZERVER like '/%' GROUP BY SZERVER, SCHEDULE_NAME order by lower(SZERVER)

Node-ok milyen porton keresik a kliens ütemezőt

Instanse userrel:

db2 connect to tsmdb1
db2 set schema tsmdb1
db2 "select (select nodename from nodes where nodeid=s.nodeid),hladdress,lladdress from schedule_node_addresses s"
db2 connect reset

Hasznos TSM SQL oldalak