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 [2025/01/22 16:45] – [Egyéb] adminsql [2026/06/12 11:14] (aktuális) – admin
Sor 43: Sor 43:
  
   select status,count(*) from events where scheduled_start>current_timestamp - 14 hours group by status   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 === === TSM szerver crash időpontok keresése actlog szünetek alapján ===
Sor 59: Sor 63:
  
 === 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 === === DRIVE és PATH-ok ONLINE/OFFLINE állítása ===
  
-   DEFINE SCRIPT  UPDATE_DRIVES_ONLINE_TO "select 'update drive ' || library_name || ' ' || drive_name || ' online=' || upper('$1') from drives" +   DEFINE SCRIPT  UPD_DR_ONL_TO "select 'update drive ' || library_name || ' ' || drive_name || ' online=' || upper('$1') from drives" 
-   DEFINE SCRIPT  UPDATE_PATHS_ONLINE_TO "select 'update path ' || source_name || ' ' || destination_name || ' srct=serv destt=dr library=' || library_name || ' online=' || upper('$1') from paths where destination_type='DRIVE'"+   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 253: Sor 257:
 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 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> </file>
 +</WRAP>
 +
 +
 +=== Storage poolok szalagkészletének részletei ===
 +
 +<WRAP prewrap>
 +<file>
 +select substr(stgpool_name,1,locate('_',stgpool_name)-1) as "POOL_GROUP", \
 +       count(*) as "ALL_VOLS", \
 +       sum(case when status='FULL' then 1 else 0 end) as "FULL_VOLS", \
 +       dec(avg(case when status='FULL' then est_capacity_mb/1024.0/1024.0 else NULL end),8,2) as "AVG_FULL_TB" \
 +from volumes \
 +where stgpool_name like '%\_T' escape '\' \
 +group by substr(stgpool_name,1,locate('_',stgpool_name)-1) \
 +order by substr(stgpool_name,1,locate('_',stgpool_name)-1)</file>
 </WRAP> </WRAP>
  
Sor 295: Sor 314:
   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
 +
 +==== Library újradefiniálás ====
 +
 +  select substr('del path ' || SOURCE_NAME || ' ' || DESTINATION_NAME || ' SRCT=' || SOURCE_TYPE || ' DESTT=' || DESTINATION_TYPE || case when DESTINATION_TYPE='DRIVE' then ' LIBRARY=' || LIBRARY_NAME end,1,100) from paths
 +  select 'del dr ' || LIBRARY_NAME || ' ' || DRIVE_NAME from drives
 +  select 'def dr ' || LIBRARY_NAME || ' ' || DRIVE_NAME from drives
 +  select substr('def path ' || SOURCE_NAME || ' ' || DESTINATION_NAME || ' SRCT=' || SOURCE_TYPE || ' DESTT=' || DESTINATION_TYPE || case when DESTINATION_TYPE='DRIVE' then ' LIBRARY=' || LIBRARY_NAME end || ' DEVI=' || DEVICE,1,120) from paths
 +
 +==== 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 ====