meta data for this page
  •  

Különbségek

A kiválasztott változat és az aktuális verzió közötti különbségek a következők.

Összehasonlító nézet linkje

Előző változat mindkét oldalonElőző változat
Következő változat		Előző változat
sql [2019/09/16 16:37] – [Egyéb] adminsql [2025/05/22 11:11] (aktuális) – [Hibakereséshez] admin
Sor 1: Sor 1:
-===== Hasznos SQL lekérdezések =====+===== 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==== ====Takarításhoz====
Sor 11: Sor 20:
 <file>select distinct node_name,class_name from backups where node_name='NODE' <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> select distinct node_name,class_name from archives where node_name='NODE'</file>
 +
 +Megőrzési időkkel:
 +
 +  SELECT DISTINCT b.NODE_NAME, b.CLASS_NAME, c.SET_NAME, c.VEREXISTS, c.VERDELETED, c.RETEXTRA, c.RETONLY FROM backups b \
 +  JOIN nodes n ON b.NODE_NAME = n.NODE_NAME \
 +  JOIN bu_copygroups c ON n.DOMAIN_NAME = c.DOMAIN_NAME AND b.CLASS_NAME = c.CLASS_NAME ORDER BY b.NODE_NAME;
 +
  
 === Hibás PRIVATE volume-ok listázása === === Hibás PRIVATE volume-ok listázása ===
Sor 23: Sor 39:
      
 ====Hibakereséshez==== ====Hibakereséshez====
-   + 
-=== Egy tape device-hoz tartozó drive és path ONLINE állapotának ellenőrzése === +=== Ütemezések darabszáma az elmúlt X órában === 
-<file>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'</file>+ 
 +  select status,count(*) from events where scheduled_start>current_timestamp - 14 hours group by status 
 + 
 +=== Problémás, nem kivett szalagok === 
 + 
 +  select substr(v.volume_name,1,8) as volume_name, substr(v.stgpool_name,1,15) as stgpool_name, substr(v.devclass_name,1,10) as devclass_name, v.pct_utilized, substr(v.status,1,10) as status, substr(v.access,1,11) as access, coalesce(cast(lv.home_element as varchar(20)), 'not in libvolumes') as home_element from volumes v left join (select volume_name, home_element from (select volume_name, home_element, row_number() over (partition by volume_name order by volume_name) as rn from libvolumes) as ranked where rn = 1) lv on lv.volume_name = v.volume_name where v.access != 'READWRITE' and v.stgpool_name not like 'OFF_%' 
 + 
 +=== 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,30) 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> 
 + 
 +=== DRIVE és PATH-ok ONLINE/OFFLINE állítása === 
 + 
 +   DEFINE SCRIPT  UPD_DR_ONL_TO "select 'update drive ' || library_name || ' ' || drive_name || ' online=' || upper('$1') from drives" 
 +   DEFINE SCRIPT  UPD_PATH_ONL_TO "select substr('update path ' || source_name || ' ' || destination_name || ' srct=serv destt=dr library=' || library_name || ' online=' || upper('$1'),1,80) from paths where destination_type='DRIVE'"
  
 === Írási/olvasási hibás szalagok listázása === === Írási/olvasási hibás szalagok listázása ===
Sor 52: Sor 95:
   drive_name   drive_name
 </file> </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ő === === Drive használat adatgyűjtő ===
Sor 134: Sor 183:
 </file> </file>
 </WRAP> </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==== ====Egyéb====
 +
 +===String listában keresés===
 +
 +  select * from TAB1 where ',' || 'ABC,BCD' || ',' like '%,' || Y || ',%' 
 +  select REGEXP_SUBSTR(MESSAGE, '^[^.]+') as LEFT from messages
 +
 +===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=== ===STGPOOL-ok mérete és az alattuk levő DEVCLASS és LIBRARY===
Sor 151: Sor 213:
  
   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)) || ')' 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 === ===Inaktív node-ok száma domain-enként ===
Sor 179: Sor 242:
 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')) 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> </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 === === Primary és azonos nevű copy pooljaik összehasonlítása ===
Sor 194: Sor 261:
 === Node-ok ütemezései és megőrzési idejei === === Node-ok ütemezései és megőrzési idejei ===
  
-<WRAP>+<WRAP prewrap>
 <file> <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, \ +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
-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.set_name='ACTIVE' and a.node_name=n.node_name order by n.node_name,n.domain_name,Set,MgmtClass+
 </file> </file>
 </WRAP> </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
 +
 +==== Front-End licenszelés ====
 +
 +A v7.1-től küldik a klinsektől a licensz metrikákat a szervernek. (Q SYS -es kimenethez)
 +Az ennél régebbi kliensekkel mentett node-ok mentett adatainak active verziói:
 +  
 +  SELECT n.NODE_NAME, n.CLIENT_VERSION, n.LASTACC_TIME, CAST(FLOAT(SUM(bk.bfsize))/1024/1024/1024 AS DEC(14,1)) AS ACTIVE_GB FROM NODES n LEFT JOIN BACKUPS b ON n.NODE_NAME = b.NODE_NAME LEFT JOIN BACKUP_OBJECTS bk ON b.OBJECT_ID = bk.OBJID WHERE n.CLIENT_VERSION < 7 AND b.STATE = 'ACTIVE_VERSION' GROUP BY n.NODE_NAME, n.CLIENT_VERSION, n.LASTACC_TIME ORDER BY n.CLIENT_VERSION, n.LASTACC_TIME;
 +  
 +Teljes backup és archive lista:
 +
 +  SELECT b.node_name, b.filespace_name, CAST(FLOAT(SUM(bk.bfsize))/1024/1024/1024 AS DEC(14,1)) AS size_gb, COUNT(bk.bfsize) AS number_of_objects FROM backups b, backup_objects bk WHERE b.state='ACTIVE_VERSION' AND b.object_id = bk.objid GROUP BY b.node_name, b.filespace_name
 +  SELECT a.node_name, a.filespace_name, CAST(FLOAT(SUM(ao.bfsize))/1024/1024/1024 AS DEC(14,1)) AS size_gb, COUNT(ao.bfsize) AS number_of_objects FROM archives a, archive_objects ao WHERE a.object_id = ao.objid GROUP BY a.node_name, a.filespace_name
 +  
 +  SELECT SUBSTR(b.node_name,1,30) AS node_name, SUBSTR(b.filespace_name,1,30) AS filespace_name, CAST(FLOAT(SUM(bk.bfsize))/1024/1024/1024 AS DEC(14,1)) AS size_gb, COUNT(bk.bfsize) AS number_of_objects, (SELECT COUNT(*) FROM nodes n2 WHERE n2.tcp_address = n1.tcp_address) AS nodes_with_same_ip FROM backups b JOIN backup_objects bk ON b.object_id = bk.objid JOIN nodes n1 ON b.node_name = n1.node_name WHERE b.state = 'ACTIVE_VERSION' GROUP BY b.node_name, b.filespace_name, n1.tcp_address;
 +
 ==== Hasznos TSM SQL oldalak ==== ==== Hasznos TSM SQL oldalak ====
   * [[http://www.tsmadmin.com/p/my-sql-querries.html|TSMAdmin: My SQL queries]]   * [[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]]   * [[http://www.lascon.co.uk/tsm-sql-queries.php|LasCon TSM SQL Hints]]
-  * [[http://thobias.org/tsm/sql/index.html|SQL for Tivoli Storage Manager (Thobias)]]+  * [[https://github.com/thobiast/tsm_sql|SQL for Tivoli Storage Manager (Thobias)]]