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 [2024/10/21 17:19] – adminsql [2025/05/03 22:23] (aktuális) – admin
Sor 20: 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 52: Sor 59:
  
 === Drive és path ONLINE státuszok ellenőrzése === === 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>+<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 176: Sor 188:
  
   select * from TAB1 where ',' || 'ABC,BCD' || ',' like '%,' || Y || ',%'    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 === ===Node-ok melyeknél az utolsó mentés és a replikáció közt eltelt idő több, mint 30 óra ===
Sor 282: Sor 295:
   db2 "select (select nodename from nodes where nodeid=s.nodeid),hladdress,lladdress from schedule_node_addresses s"   db2 "select (select nodename from nodes where nodeid=s.nodeid),hladdress,lladdress from schedule_node_addresses s"
   db2 connect reset   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 ====