===== Hasznos TSM SQL lekérdezések =====

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

  - FROM clause
  - WHERE clause
  - GROUP BY clause
  - HAVING clause
  - SELECT clause
  - ORDER BY clause

====Takarításhoz====

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

<file>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'</file>

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

<file>select distinct node_name,class_name from backups where node_name='NODE'
select distinct node_name,class_name from archives where node_name='NODE'</file>

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

<file>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 )</file>


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

<file>select OPTIONSET_NAME from CLOPTSETS where OPTIONSET_NAME not in (select OPTION_SET from nodes where OPTION_SET!='NULL')</file>
  
====Hibakereséshez====

=== Ütemezések darabszáma az elmúlt X órában ===

  select status,count(*) from events where scheduled_start>current_timestamp - 14 hours group by status

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

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

=== Drive és path ONLINE státuszok ellenőrzése ===
<file>select substr(dr.LIBRARY_NAME,1,15) as LIBRARY, substr(dr.DRIVE_NAME,1,15) as DRIVE_NAME, substr(dr.drive_serial,1,12) as SERIAL, substr(dr.ONLINE,1,3) as "DRIVE ONLINE?", substr(pa.SOURCE_NAME || ' - ' || pa.ONLINE,1,20) as "PATH ONLINE?", substr(pa.device,1,15) as DEVICE, substr(element,1,4) as ELEMENT from drives as dr, paths as pa where pa.DESTINATION_NAME=dr.DRIVE_NAME order by library</file>

=== Írási/olvasási hibás szalagok listázása ===
<file>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</file>
  
=== Sorozatosan (3x) "Missed" státuszú ütemezésű node-ok listája ===
<file>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</file>

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

<WRAP prewrap>
<file>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
</file>

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

<file>
#!/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
</file>

===Drive hasznalat orankenti lebontasban:===

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

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

<file>
#!/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
</file>
</WRAP>

=== 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
  
<WRAP prewrap>
<file>
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'))
</file>

=== 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 ===
<file>
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
</file>
<file>
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
</file>
</WRAP>

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

<WRAP prewrap>
<file>
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
</file>
</WRAP>

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

<file>
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)
</file>


=== 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 ====
  * [[http://www.tsmadmin.com/p/my-sql-querries.html|TSMAdmin: My SQL queries]]
  * [[http://www.lascon.co.uk/tsm-sql-queries.php|LasCon TSM SQL Hints]]
  * [[https://github.com/thobiast/tsm_sql|SQL for Tivoli Storage Manager (Thobias)]]