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 [2023/10/27 15:28] – 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: SQL is implemented as if a query was executed in the following order:
Sor 32: Sor 32:
      
 ====Hibakereséshez==== ====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 === === TSM szerver crash időpontok keresése actlog szünetek alapján ===
Sor 219: 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 244: Sor 252:
   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 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   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]]