Inhaltsverzeichnis
TSM SQL Queries in TSM 5.5 (Embedded) und TSM 6.1 (DB2) sowie TSM 6.2
Mit der neuen Version IBM Tivoli Storage Manager TSM 6.1 und 6.2 hat sich einiges getan und durch den Einzug von DB2 als Datenbank-Engine sind einige der alten Queries nicht mehr verfügbar/nutzbar oder müssen umgeschrieben werden.
Im folgenden werden einige dieser Queries dokumentiert und dargestellt, wie sie in den beiden Versionen funktionieren.
Thomas Baumann tiri GmbH
syscat.tables
Die Systemtabelle, die alle TSM spezifischen Tabellen enthält, hat sich von 5.5 nach 6.1 geändert, in 6.1 gibt es weniger Tabellen. Siehe auch Redbooks: The TSM database system catalog
sql
select distinct tabname from syscat.tables
-zeigt an, welche Tabellen in TSM 5.5 nicht aber in syscat.tables.6.1 vorhanden sind,+zeigt an, welche Tabellen in 6.1, nicht aber in 5.5 vorhanden sind.
text
+ACTIVITY_SUMMARY ACTLOG ADMINS ADMIN_SCHEDULES ARCHIVES AR_COPYGROUPS ASSOCIATIONS -AUDITOCC BACKUPS BACKUPSETS BU_COPYGROUPS -CLIENTOPTS CLIENT_SCHEDULES -CLOPTSETS -COLLOCGROUP COLUMNS CONTENTS -DATAMOVERS DB -DBBACKUPTRIGGER -DBSPACETRIGGER -DBVOLUMES +DBSPACE DEVCLASSES -DOMAINS DRIVES +DRMASTGPOOLS DRMCSTGPOOLS -DRMEDIA -DRMMACHINE -DRMMACHINECHARS -DRMMACHINENODE -DRMMACHINERECINST -DRMMACHINERECMEDIA DRMPSTGPOOLS -DRMRECOVERYMEDIA -DRMSRPF -DRMSTANZA DRMSTATUS -DRMTRPF -ENUMTYPES EVENTS FILESPACES -GROUP_MEMBER +GROUPMEMBER LIBRARIES LIBVOLUMES -LICENSE_DETAILS LICENSES -LOG -LOGSPACETRIGGER -LOGVOLUMES MEDIA -MGMTCLASSES NODEGROUP NODES OCCUPANCY OPTIONS -PATHS POLICYSETS -PROCESSES PROF_ASSOCIATIONS -PROFILES -RECLAIM_ANALYSIS -RESTORES -SAN -SCRIPT_NAMES SCRIPTS -SERVER_GROUP +SERVERGROUP SERVERS SESSIONS -SHREDSTATUS SPACEMGFILES -STATUS STGPOOLS -STGSPACETRIGGER -SUBSCRIPTIONS -SUMMARY TABLES TABNAME -VFSMAPPINGS VOLHISTORY VOLUMES VOLUMEUSAGE
DB2 Date/Time Funktionen
| DB2 SQL | Ergebnis |
|---|---|
SELECT current timestamp as NOW from sysibm.sysdummy1 | NOW --------------------------- 2009-11-01 20:08:25.821297 |
SELECT current time as TIME FROM sysibm.sysdummy1 | TIME --------- 20:10:45 |
SELECT current date as TODAY FROM sysibm.sysdummy1 | TODAY ----------- 2009-11-01 |
select current timezone as TZ FROM sysibm.sysdummy1 | TZ --------- 10000 |
select current time - current timezone as NOW_GMT FROM sysibm.sysdummy1 | NOW_GMT --------- 19:14:18 |
select days (current date) - days (date('1974-02-02')) as days_since_1974 FROM sysibm.sysdummy1
| DAYS_SINCE_1974 ---------------- 13056 |
Die Anführungszeichen bei der Date Funktion sind extrem wichtig. Die Funktion funktioniert dann zwar auch, liefert aber falsche Ergebnisse!
DB2 LIMIT Output
- SELECT COL1,COL2 from SUMMARY FETCH FIRST 10 ROWS ONLY
TSM Monitor Queries
Im folgenden sind die Queries, die durch den TSM Monitor verwendet werden.
| Name | TSM Query (5.5) | TSM Query (6.1) | Funktion |
|---|---|---|---|
| allnodes | select NODE_NAME, domain_name, platform_name, - client_os_level, TCP_ADDRESS, - trim(char(client_version)) ||'.'|| trim(char(client_release)) ||'.'|| - trim(char(client_level)) ||'.'|| trim(char(client_sublevel)) as version - from nodes - order by NODE_NAME | dto. | Diese Abfrage zeigt alle existierenden Nodes, ihre Domains, etc. |
| inactivenodes | select NODE_NAME, domain_name, platform_name, - LASTACC_TIME from nodes where - LASTACC_TIME < current_timestamp - 24 hours - order by LASTACC_TIME asc | dto. | Zeigt in der Reihenfolge ältester Zugriff alle Knoten, auf die nicht innerhalb der letzten 24 Std. zugegriffen wurde |
| filespaces | select nodes.node_name as NODE_NAME, - filespaces.filespace_name as FSNAME, - substr(char(filespaces.backup_end), 1, 10) as FS_LASTBACKUP - from nodes, filespaces - WHERE filespaces.node_name=nodes.node_name - ORDER BY node_name | select no.node_name, fs.filespace_name as FSNAME, - fs.filespace_type as FSTYPE, SUBSTR(char(fs.backup_end),1,10) as FS_LASTBACKUP - from nodes no, filespaces fs - WHERE fs.node_name=no.node_name - ORDER BY node_name | Liste aller Filespaces und deren letzes Backup |
| stalledfilespaces | select node_name, filespace_name, - backup_start, backup_end - from filespaces where - cast((current_timestamp-backup_end)days as decimal) >= 3 | select node_name, filespace_name, - backup_start, backup_end - from filespaces where - cast(days(current timestamp) - days(backup_end) as decimal) >= 3 - ORDER BY node_name | Liste aller Filespaces, die ggf. stalled sind (und nicht innerhalb der letzten 3 Tage gesichert wurden) |
| spacenode | SELECT o.node_name||' ' as NODE_NAME, - sum(cast(o.physical_mb/1024 as decimal (10,2))) as TOTAL_GB, - sum(cast(o.num_files as decimal (10,0))) as TOTAL_FILES_STORED, - dec(dec(sum(o.num_files))/(- select dec(sum(num_files)) from occupancy)*100,6,4- ) as PCT_FILES_STORED, n.platform_name FROM occupancy o,nodes n - WHERE o.node_name=n.node_name - GROUP BY n.node_name, o.node_name, n.platform_name - ORDER BY TOTAL_GB desc | dto. | Alle Knoten und ihre Speicherbelegung |
| datanode | select vu.node_name, ao.total_mb, - count(distinct vu.volume_name) as tapes, - ao.total_mb/count(distinct vu.volume_name) as mbtape - from volumeusage vu, auditocc ao - where vu.node_name=ao.node_name - group by vu.node_name, ao.total_mb - order by total_mb desc |  TSM6.1 hat folgende Spalten: NODE_NAME BACKUP_MB BACKUP_COPY_MB ARCHIVE_MB ARCHIVE_COPY_MB SPACEMG_MB SPACEMG_COPY_MB TOTAL_MB BACKUP_ACTIVE_MB | Anzeige wieviel Speicher die Nodes verwenden und über wieviele Bänder sie verteilt sind. |
| tapesnode | select distinct node_name, volume_name, stgpool_name - from volumeusage - ORDER BY volume_name | dto. | Anzeige aller Tapes, die zu einem Knoten gehören |
| serverinfo_vertical | select SERVER_NAME, SERVER_HLA, SERVER_LLA, - INSTALL_DATE, RESTART_DATE, - AVAILABILITY, - LASTLICENSEAUDIT, LICENSECOMPLIANCE, - LOGMODE, CROSSDEFINE, - PLATFORM, VERSION, RELEASE, LEVEL, SUBLEVEL - from status | select SERVER_NAME, SERVER_HLA, SERVER_LLA, - INSTALL_DATE, RESTART_DATE, - AVAILABILITY, - LASTLICENSEAUDIT, LICENSECOMPLIANCE, - CROSSDEFINE, PLATFORM, - VERSION, RELEASE, LEVEL, SUBLEVEL - from status | Allg. Infos über den Server |
| otherservers | select SERVER_NAME, - HL_ADDRESS, LL_ADDRESS, - DESCRIPTION, LASTACC_TIME from servers | dto. | Infos über andere TSM Server |
| database_vertical | select AVAIL_SPACE_MB, CAPACITY_MB, - MAX_EXTENSION_MB, MAX_REDUCTION_MB, - PAGE_SIZE, USABLE_PAGES, USED_PAGES, - PCT_UTILIZED, MAX_PCT_UTILIZED, - PHYSICAL_VOLUMES, BUFF_POOL_PAGES, TOTAL_BUFFER_REQ, - CACHE_HIT_PCT, CACHE_WAIT_PCT, - NUM_BACKUP_INCR, BACKUP_CHG_MB, BACKUP_CHG_PCT, - LAST_BACKUP_DATE, - DB_REORG_EST, DB_REORG_EST_TIME - from db | Einige Änderungen infolge DB2 select * from db select * from log | Datenbank Informationen |
| mgmtclasses | select DOMAIN_NAME, - SET_NAME, CLASS_NAME, - DEFAULTMC, DESCRIPTION - from mgmtclasses - ORDER BY DOMAIN_NAME | dto. | Alle Managementklassen des TSM Servers |
| policysets | select DOMAIN_NAME, - SET_NAME, DEFMGMTCLASS, - DESCRIPTION - from policysets - ORDER BY DOMAIN_NAME | dto. | Alle Policy Sets |
| domains | select DOMAIN_NAME, - SET_LAST_ACTIVATED, DEFMGMTCLASS, - NUM_NODES, - BACKRETENTION, ARCHRETENTION, - DESCRIPTION, ACTIVESTGPOOLS - from domains - order by domain_name | dto. | Alle Domains |
| backupcopygroups | SELECT domain_name, class_name, - VEREXISTS, VERDELETED, - RETEXTRA, RETONLY, - destination - FROM bu_copygroups - WHERE set_name='ACTIVE' - ORDER BY domain_name | dto. | Destination Pools und Infos über alle Backup Copygroups |
| archivecopygroups | SELECT domain_name, class_name, - retver, destination - FROM ar_copygroups - where set_name='ACTIVE' - ORDER BY domain_name | dto. | Destination Pools und Infos über alle Archive Copygroups |
| drives | select LIBRARY_NAME, DRIVE_NAME, - DEVICE_TYPE, ONLINE, - ELEMENT, DRIVE_STATE, - ALLOCATED_TO, CLEAN_FREQ, - DRIVE_SERIAL, VOLUME_NAME - from drives - ORDER BY LIBRARY_NAME | dto. | Alle Tapedrives 1) |
| paths | select SOURCE_NAME, SOURCE_TYPE, - DESTINATION_NAME, DESTINATION_TYPE, - LIBRARY_NAME, NODE_NAME, - DEVICE, EXTERNAL_MANAGER, - LUN, DIRECTORY, ONLINE - from paths - ORDER BY SOURCE_NAME | dto. | Alle Pfade 2) |
| drmedia | select VOLUME_NAME, STGPOOL_NAME, - LIB_NAME, VOLTYPE, UPD_DATE, - LOCATION, STATE from drmedia | dto. | List of DR Media 3) |
| backupstatus_24h | select Entity, Successful, - cast(float(sum(bytes))/1024/1024/1024 as dec(8,3)) as GB, - Examined, Affected, Failed - from summary where activity='BACKUP' - and cast((current_timestamp-start_time)hours as decimal(8,0)) LESS 24 - group by Entity,Successful,Examined,AFFECTED,Failed | select Entity, Successful, - cast(float(sum(bytes))/1024/1024/1024 as decimal (8,3)) as GB, - Examined, Affected, Failed - from summary where activity='BACKUP' and - start_time>=current_timestamp - 24 hours - group by Entity,Successful,Examined,AFFECTED,Failed - ORDER BY Successful | Liste aller Backups, die innerhalb der letzen 24h gestartet wurden 4) |
| backups_24h | SELECT entity AS "Node_name", - CAST(sum(bytes/1024/1024/1024) AS decimal(8,3)) AS "GB_xfer", - start_time, end_time, END_TIME-START_TIME as duration, - comm_wait, Affected, Failed, Successful - FROM summary WHERE activity='BACKUP' AND - start_time>=current_timestamp - 24 hours - group by start_time, end_time, Successful, - Examined,Affected,Failed,entity,comm_wait - ORDER BY START_TIME ASC | SELECT entity AS "Node_name", - cast(sum(decimal(bytes,31,3)/1024/1024/1024) AS decimal(8,3)) as "GB_xfer", - start_time, end_time, substr(char(start_time),1,19) as start, - substr(char(end_time),1,19) as end, (substr(char(end_time - start_time),9,2) - || ':' || substr(char(end_time - start_time),11,2) || ':' - || substr(char(end_time - start_time),13,2)) as duration,- comm_wait, Affected, Failed, Successful - FROM summary WHERE activity='BACKUP' AND - start_time>=current_timestamp - 24 hours - group by start_time, end_time, Successful, - Examined,Affected,Failed,entity,comm_wait - ORDER BY START_TIME ASC | Backupzeiten innerhalb der letzten 24h 5) |
| archivestatus_24h | select Entity, Successful, - cast(float(sum(bytes))/1024/1024/1024 as dec(8,3)) as GB, - Examined, Affected, Failed - from summary where activity='ARCHIVE' and - cast((current_timestamp-start_time)hours as decimal(8,0)) LESS 24 - group by Entity,Successful,Examined,AFFECTED,Failed | select Entity, Successful, - cast(float(sum(bytes))/1024/1024/1024 as dec(8,3)) as GB, - Examined, Affected, Failed - from summary where activity='ARCHIVE' and - start_time>=current_timestamp - 24 hours - group by Entity,Successful,Examined,AFFECTED,Failed | Alle Archivierungen, die innerhalb der letzten 24h gestartet wurden 6) |
| archives_24h | SELECT entity AS "Node_name", - CAST(sum(bytes/1024/1024/1024) AS decimal(8,3)) AS "GB_xfer", - start_time, end_time, END_TIME-START_TIME as duration, - comm_wait, Affected, Failed, Successful - FROM summary WHERE activity='ARCHIVE' AND - start_time>=current_timestamp - 24 hours - group by start_time, end_time, Successful, - Examined,Affected,Failed,entity,comm_wait - ORDER BY START_TIME ASC | dto. | Info zu allen Archivierungen der letzen 24h 7) |
| clientscheddef | select schedule_name, domain_name, - description, action, priority, - starttime, period, perunits, - dayofweek, sched_style - from client_schedules - order by schedule_name | dto. | Definierte Schedules |
| clientschedules | select node_name, schedule_name, - scheduled_start, ACTUAL_START, COMPLETED, - status, result, REASON - from events - where scheduled_start >= current_timestamp - 24 hours - and node_name != '' and status != 'Future' - and status != 'Started' ORDER by node_name | dto. | Eine der wichtigsten Abfragen überhaupt, da hier auch Informationen über den Grund ausgegeben werden 8) |
| adminscheddef | select schedule_name, command, - description, active, priority, - starttime, period, perunits, - dayofweek, sched_style - from admin_schedules - ORDER BY schedule_name | dto. | Liste aller admin. Schedules |
| adminschedules | select schedule_name, scheduled_start, - ACTUAL_START,COMPLETED,status, - result, reason - from events where - scheduled_start >= current_timestamp - 24 hours - and node_name is null and status != 'Future' - and status != 'Started' | Beispiel:
SCHEDULE_NAME: MAINTENANCE
SCHEDULED_START: 2009-11-03 06:30:00.000000
ACTUAL_START: 2009-11-03 06:30:02.000000
COMPLETED: 2009-11-03 06:44:54.000000
STATUS: Completed
RESULT: 0
REASON: Ok
| Informationen über die Ergebnisse der Admin-Schedules der letzten 24h |
| assos | select domain_NAME, schedule_name, - node_name, chg_time - from associations - order by domain | dto. | Alle Schedule Zuordnungen |
| migration | SELECT ENTITY, Activity, -
Start_Time as "Start_Time", End_Time, -
END_TIME-START_TIME as duration, -
Examined, Affected, -
cast((BYTES/1048576)/1024 as decimal(12,3)) as GB, -
FAILED, Mediaw, Processes, Successful -
FROM ADSM.SUMMARY -
where ACTIVITY like upper('MIGRATION') -
and date(start_time) >= current_date -1 days -
ORDER BY Start_Time ASC
|
SELECT ENTITY, Activity, -
Start_Time, End_Time, -
END_TIME-START_TIME as duration, -
Examined, Affected, -
cast((BYTES/1048576)/1024 as decimal(12,3)) as GB, -
FAILED, Mediaw, Processes, Successful -
FROM summary -
where ACTIVITY like upper('MIGRATION') -
and date(start_time) >= current_date -1 days -
ORDER BY Start_Time ASC
| Migrationen der letzen 24h 9) |
| processes | SELECT process_num, process, - substr(char(start_time),1,19) AS START_TIME, - substr(char(current_timestamp - start_time),1,10) as duration, - cast(float(bytes_processed) /1024/1024 AS DEC(8,2)) AS MB, - cast((cast(bytes_processed as dec(18,0))/ - cast((current_timestamp-start_time) seconds as decimal(18,0))) - / 1024 / 1024 AS DEC (18,2)) as MBs, status FROM processes - ORDER BY process_num | dto. | Alle aktuell laufenden Prozesse |
| ... | ... | ... | ... |
| expiration | select activity, -
cast ((end_time) as date) as "Date", -
(examined / cast ((end_time-start_time) seconds -
as decimal (18,13))*3600) as "Obj/Hr" -
from summary -
where activity='EXPIRATION' and days (end_time) - days (start_time) = 0
| division by zero vermeiden | Die TSM Expiration Rate ist die Anzahl der TSM Objecte die in einer bestimmten Zeit “expired” sind. Es ist nicht die Anzahl der geprüften Objekte, die weitaus höher sein dürfte. IBM empfiehlt die Expiration Rate größer als 3800000 Objekte pro Stunde zu haben. |
| orphanfs | select node_name, filespace_name - from filespaces f - where not exists - (select node_name, filespace_name - from occupancy o - where f.node_name = o.node_name - and f.filespace_name = o.filespace_name) | dto. | Zeigt Filespaces an, die in der FS-Tabelle existieren aber keinen korrespondierenden Eintrag in der OCCUPANCY Tabelle haben. |
| dr_objects | select char(stgpool_name,12) as POOL, cast(sum(physical_mb)/1024 as decimal(10,1)) as Physical_GB, cast(sum(logical_mb)/1024 as decimal(10,1)) as Logical_GB, sum(num_files) as OBJECTS from occupancy group by stgpool_name order by 1 | dto. | Der Wert von OBJECTS des COPYPOOLs muss gleich der Summe der Objekte der primären Pools sein, damit der COPYPOOL komplett synchronisiert ist. |
| expiredobj | . | select * from backups where NODE_NAME='NAGIOS.TIRI.LAN' AND LL_NAME='messages' AND HL_NAME='/var/log/'
NODE_NAME: NAGIOS.TIRI.LAN
FILESPACE_NAME: /
FILESPACE_ID: 2
STATE: ACTIVE_VERSION
TYPE: FILE
HL_NAME: /var/log/
LL_NAME: messages
OBJECT_ID: 2926514
BACKUP_DATE: 2009-11-12 21:02:17.000000
DEACTIVATE_DATE:
OWNER: syslog
CLASS_NAME: DEFAULT
NODE_NAME: NAGIOS.TIRI.LAN
FILESPACE_NAME: /
FILESPACE_ID: 2
STATE: INACTIVE_VERSION
TYPE: FILE
HL_NAME: /var/log/
LL_NAME: messages
OBJECT_ID: 2571707
BACKUP_DATE: 2009-11-02 21:09:04.000000
DEACTIVATE_DATE: 1900-01-01 00:00:00.000000
OWNER: syslog
CLASS_NAME: DEFAULT
NODE_NAME: NAGIOS.TIRI.LAN
FILESPACE_NAME: /
FILESPACE_ID: 2
STATE: INACTIVE_VERSION
TYPE: FILE
HL_NAME: /var/log/
LL_NAME: messages
OBJECT_ID: 2571764
BACKUP_DATE: 2009-11-02 21:09:04.000000
DEACTIVATE_DATE: 1900-01-01 00:00:00.000000
OWNER: syslog
CLASS_NAME: DEFAULT
NODE_NAME: NAGIOS.TIRI.LAN
FILESPACE_NAME: /
FILESPACE_ID: 2
STATE: INACTIVE_VERSION
TYPE: FILE
HL_NAME: /var/log/
LL_NAME: messages
OBJECT_ID: 2571803
BACKUP_DATE: 2009-11-02 21:09:04.000000
DEACTIVATE_DATE: 1900-01-01 00:00:00.000000
OWNER: syslog
CLASS_NAME: DEFAULT
NODE_NAME: NAGIOS.TIRI.LAN
FILESPACE_NAME: /
FILESPACE_ID: 2
STATE: INACTIVE_VERSION
TYPE: FILE
HL_NAME: /var/log/
LL_NAME: messages
OBJECT_ID: 2571826
BACKUP_DATE: 2009-11-02 21:09:04.000000
DEACTIVATE_DATE: 1900-01-01 00:00:00.000000
OWNER: syslog
CLASS_NAME: DEFAULT
NODE_NAME: NAGIOS.TIRI.LAN
FILESPACE_NAME: /
FILESPACE_ID: 2
STATE: INACTIVE_VERSION
TYPE: FILE
HL_NAME: /var/log/
LL_NAME: messages
OBJECT_ID: 2688830
BACKUP_DATE: 2009-11-03 21:17:50.000000
DEACTIVATE_DATE: 1900-01-01 00:00:00.000000
OWNER: syslog
CLASS_NAME: DEFAULT
NODE_NAME: NAGIOS.TIRI.LAN
FILESPACE_NAME: /
FILESPACE_ID: 2
STATE: INACTIVE_VERSION
TYPE: FILE
HL_NAME: /var/log/
LL_NAME: messages
OBJECT_ID: 2688839
BACKUP_DATE: 2009-11-03 21:17:50.000000
DEACTIVATE_DATE: 1900-01-01 00:00:00.000000
OWNER: syslog
CLASS_NAME: DEFAULT
NODE_NAME: NAGIOS.TIRI.LAN
FILESPACE_NAME: /
FILESPACE_ID: 2
STATE: INACTIVE_VERSION
TYPE: FILE
HL_NAME: /var/log/
LL_NAME: messages
OBJECT_ID: 2688848
BACKUP_DATE: 2009-11-03 21:17:50.000000
DEACTIVATE_DATE: 1900-01-01 00:00:00.000000
OWNER: syslog
CLASS_NAME: DEFAULT
NODE_NAME: NAGIOS.TIRI.LAN
FILESPACE_NAME: /
FILESPACE_ID: 2
STATE: INACTIVE_VERSION
TYPE: FILE
HL_NAME: /var/log/
LL_NAME: messages
OBJECT_ID: 2689031
BACKUP_DATE: 2009-11-03 21:18:42.000000
DEACTIVATE_DATE: 1900-01-01 00:00:00.000000
OWNER: syslog
CLASS_NAME: DEFAULT
NODE_NAME: NAGIOS.TIRI.LAN
FILESPACE_NAME: /
FILESPACE_ID: 2
STATE: INACTIVE_VERSION
TYPE: FILE
HL_NAME: /var/log/
LL_NAME: messages
OBJECT_ID: 2748527
BACKUP_DATE: 2009-11-04 21:08:35.000000
DEACTIVATE_DATE: 1900-01-01 00:00:00.000000
OWNER: syslog
CLASS_NAME: DEFAULT
NODE_NAME: NAGIOS.TIRI.LAN
FILESPACE_NAME: /
FILESPACE_ID: 2
STATE: INACTIVE_VERSION
...
| .. |
| messages | select date_time, - msgno, nodename, - substr(message,27) as MESSAGE - from actlog - where date_time>(current_timestamp - 72 hours) - and (msgno=4005 or msgno=4007 or msgno=4037 or msgno=4987) | dto. | Anzeige aller Fehler innerhalb der letzen 72h |