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 [2020/03/24 16:32] – [Egyéb] adminsql [2024/04/02 16:37] (aktuális) – 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 23: Sor 32:
      
 ====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 
 + 
 +=== 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.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</file>
  
 === Írási/olvasási hibás szalagok listázása === === Írási/olvasási hibás szalagok listázása ===
Sor 52: Sor 79:
   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=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ő === === Drive használat adatgyűjtő ===
Sor 134: Sor 165:
 </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 || ',%' 
  
 ===Node-ok melyeknél az utolsó mentés és a replikáció közt eltelt idő több, mint 30 óra === ===Node-ok melyeknél az utolsó mentés és a replikáció közt eltelt idő több, mint 30 óra ===
Sor 155: Sor 194:
  
   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 183: Sor 223:
 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 198: Sor 242:
 === 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
 +
 ==== 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)]]