SQL for TSM: PART II
Activity Log
Search in
the activity log for missed schedules in the last 2 hours
tsm: SERVER1> SELECT date_time,message
FROM actlog WHERE originator='SERVER' AND -
message LIKE'ANR2578W%' AND
date_time>=current_timestamp-2 hours
DATE_TIME MESSAGE
------------------ ------------------
2007-07-26 ANR2578W Schedule
14:00:01.000000 ORACLE_HOME in
domain AIX for
node SERVER-1
has missed its
scheduled start
up window.
Search in
the activity log for messages with Error severity in the last 1 hour
tsm: SERVER1> SELECT date_time,message
FROM actlog WHERE originator='SERVER' AND severity='E' AND -
date_time>current_timestamp-1 hours
DATE_TIME MESSAGE
------------------ ------------------
2007-07-27 ANR2034E QUERY
10:22:17.000000 SPACETRIGGER: No
match found using
this criteria.(
SESSION: 252982)
Search in
the activity log for successful, missed or failed schedules in the last 1 day
tsm: SERVER1> SELECT
date_time,severity,message FROM actlog WHERE originator='SERVER' AND -
( message LIKE'ANR2507I%' OR -
message LIKE'ANR2751I%' OR -
message LIKE'ANR2578W%' OR -
message LIKE'ANR2579E%') AND -
date_time>timestamp(current_date)-(1)days
DATE_TIME SEVERITY MESSAGE
------------------ ------------------ -------------------
2007-07-25 I ANR2507I Schedule
00:14:48.000000 IN_APP1 for domain
NT started at
07/24/07 22:30:00
for node SERVER-2
completed
successfully at
07/25/07
00:14:48.(SESSIO-
N: 233833)
2007-07-25 E ANR2579E Schedule
00:30:03.000000 INC_APP2 in domain
NT for node
SERVER-3
failed (return
code 1).(SESSION:
234285)
2007-07-25 W ANR2578W Schedule
00:40:01.000000 ORACLE_HOME in
domain AIX for
node SERVER-1
has missed its
scheduled start
up window.
Search in
the activity log for a specific ANR in the last 1 day
tsm:
SERVER1> SELECT date_time,severity,message from actlog WHERE message
LIKE'ANR8438I%' -
and
date_time>timestamp(current_date)-(1)days
DATE_TIME SEVERITY MESSAGE
------------------ ------------------ ------------------
2007-07-27 I ANR8438I CHECKOUT
09:21:19.000000 LIBVOLUME for
volume R00135L3
in library 3584
completed
successfully.(SE-
SSION: 252515,
PROCESS: 470)
2007-07-27 I ANR8438I CHECKOUT
09:21:28.000000 LIBVOLUME for
volume R00049L3
in library 3584
completed
successfully.(SE-
SSION: 252515,
PROCESS: 471)
Summary
of archive operations in the last 7 days
tsm: SERVER1> select
cast(float(sum(bytes))/1024/1024/1024 as dec(8,2)) -
as "Archive data in GB" from
summary where -
activity='ARCHIVE' and
end_time>timestamp(current_date)-(7)days
Archive data in GB
--------------------
14508.09
Summary
of backup operations in a specific range
tsm: SERVER1> SELECT
CAST(FLOAT(SUM(bytes))/1024/1024/1024 AS DEC(8,2)) -
AS "Backed up data in GB" FROm
summary WHERE activity='BACKUP' -
AND start_time >{ts '2007-06-01 00:00:00'}
AND start_time <{ts '2007-07-01 00:00:00'}
Backed up data in GB
--------------------
38829.70
Statistics
of archive, backup, restore and retrieve operations per node in the last 7 days
(GB)
tsm: SERVER1> SELECT entity, activity,
CAST(FLOAT(SUM(bytes)) / 1024 / 1024 / 1024 AS DECIMAL(8,2)) -
FROM summary WHERE
end_time>current_timestamp-(7)DAY and ( activity='ARCHIVE' OR -
activity='BACKUP' OR activity='RESTORE' OR
activity='RETRIEVE' ) GROUP BY entity, activity
ENTITY ACTIVITY Unnamed[3]
------------------ ------------------ ----------
SERVER-01 ARCHIVE 81.14
SERVER-01 BACKUP 261.68
SERVER-01 RESTORE 2.91
SERVER-02 ARCHIVE 171.51
SERVER-02 BACKUP 0.00
SERVER-03 ARCHIVE 17.64
SERVER-04 ARCHIVE 168.32
SERVER-04 BACKUP 530.77
...
Total of
backup and archive per node in a specific date
tsm: SERVER1> SELECT entity,
CAST(FLOAT(SUM(bytes)) / 1024 / 1024 / 1024 AS DECIMAL(8,2)) as "GB"
-
FROM summary WHERE ( activity='ARCHIVE' OR
activity='BACKUP' ) AND -
start_time >{ts '2011-09-21 00:00:00'} AND
start_time <{ts '2011-09-22 00:00:00'} -
GROUP BY entity ORDER BY "GB"
ENTITY GB
------------ ----------
NODE01 0.28
NODE02 42.61
NODE03 50.64
NODE04 127.66
NODE05 128.93
NODE06 140.86
NODE07 211.90
...
Summary
of Operations in the Last 24 Hours (GB)
tsm: SERVER1> SELECT activity,
cast(float(sum(bytes))/1024/1024/1024 as dec(8,2)) as -
"GB" FROM summary WHERE
activity<>'TAPE MOUNT' AND activity<>'EXPIRATION' -
AND end_time>current_timestamp-24 hours
GROUP BY activity
ACTIVITY GB
------------------ ----------
BACKUP 858.56
FULL_DBBACKUP 1.15
MIGRATION 496.28
RECLAMATION 652.14
STGPOOL BACKUP 496.10
Summary
of Operations in a specific date (GB)
tsm: SERVER1> SELECT activity,
cast(float(sum(bytes))/1024/1024/1024 as dec(8,2)) as -
"GB" FROM summary WHERE
activity<>'TAPE MOUNT' AND activity<>'EXPIRATION' -
AND start_time >{ts '2011-09-21 00:00:00'}
AND start_time <{ts '2011-09-22 00:00:00'} GROUP BY activity
ACTIVITY GB
------------------ ----------
ARCHIVE 60.35
BACKUP 5743.76
FULL_DBBACKUP 73.13
MIGRATION 2704.77
RECLAMATION 701.67
RESTORE 2.48
RETRIEVE 1.81
Volumes
reclaimed in the last 48 Hours
tsm: SERVER1> SELECT start_time,
end_time-start_time AS ELAPTIME, activity, number, entity, mediaw, successful -
FROM summary WHERE activity='RECLAMATION' AND
end_time>current_timestamp-48 hours
START_TIME ELAPTIME ACTIVITY NUMBER ENTITY MEDIAW SUCCESSFUL
----------------- ----------------------
--------------- ---------- ------------------ --------- --------------
2008-11-20 0 00:22:31.000000 RECLAMATION 704 DAILY (VOL076L4) 15 YES
12:00:15.000000
2008-11-20 0 00:23:01.000000 RECLAMATION 704 DAILY (VOL066L4) 13 YES
12:22:46.000000
2008-11-20 0 00:13:40.000000 RECLAMATION 704 WEEKLY (VOL008L4) 16 YES
12:45:48.000000
2008-11-22 0 00:40:18.000000 RECLAMATION 715 DAILY (VOL092L4) 51 YES
12:00:29.000000
2008-11-22 0 00:29:51.000000 RECLAMATION 715 DAILY (VOL100L4) 21 YES
12:40:47.000000
Volumes
reclaimed in the last 48 Hours (better date format?!)
tsm: SERVER1> SELECT
substr(char(start_time),1,19) AS START_TIME, -
substr(char(end_time - start_time),1,10) AS
"ELAPTIME (D HHMMSS)", -
activity, number, entity, mediaw, successful
FROM summary WHERE -
activity='RECLAMATION' AND
end_time>current_timestamp-48 hours
START_TIME ELAPTIME (D HHMMSS) ACTIVITY NUMBER ENTITY MEDIAW SUCCESSFUL
--------------- ------------------- --------------- ----------
------------------
----------- --------------
2008-11-20 0 00:22:31 RECLAMATION 704 DAILY
(VOL076L4) 15 YES
12:00:15
2008-11-20 0 00:23:01 RECLAMATION 704 DAILY
(VOL066L4) 13 YES
12:22:46
2008-11-20 0 00:13:40 RECLAMATION 704 WEEKLY (VOL008L4) 16 YES
12:45:48
2008-11-22 0 00:40:18 RECLAMATION 715 DAILY
(VOL092L4) 51 YES
12:00:29
2008-11-22 0 00:29:51 RECLAMATION 715 DAILY
(VOL100L4) 21 YES
12:40:47
tsm: SERVER1> SELECT activity,
substr(char(start_time),1,16) AS START_TIME, number, -
substr(char(end_time - start_time),1,10) AS
"ELAPTIME (D HHMMSS)", successful FROM summary WHERE -
( activity='EXPIRATION' OR
activity='MIGRATION' OR activity='FULL_DBBACKUP' OR activity='RECLAMATION' OR
activity='STGPOOL BACKUP' ) -
AND start_time >{ts '2011-09-21 00:00:00'}
AND start_time <{ts '2011-09-22 00:00:00'}
ACTIVITY START_TIME NUMBER ELAPTIME (D HHMMSS) SUCCESSFUL
------------------ ------------------ -------- ------------------- --------------
MIGRATION 2011-09-21 10:00 6028 0 03:55:49 YES
STGPOOL BACKUP 2011-09-21 10:11 6029 0 04:38:27 YES
FULL_DBBACKUP 2011-09-21 12:15 6030 0 01:24:01 YES
EXPIRATION 2011-09-21 16:00 6032 0 01:39:47 YES
RECLAMATION 2011-09-21 17:11 6033 0 01:47:02 YES
MIGRATION 2011-09-21 20:54 6034 0 03:35:50 YES
Number of
scratch volumes
tsm: SERVER1> SELECT COUNT(*) FROM
libvolumes WHERE status='Scratch'
Unnamed[1]
-----------
18
Number of
scratch volumes in library 3584
tsm: SERVER1> SELECT COUNT(*) FROM
libvolumes WHERE status='Scratch' and library_name='3584'
Unnamed[1]
-----------
18
Number of
scratch volumes for each library
tsm: SERVER1> SELECT library_name,COUNT(*)
FROM libvolumes WHERE status='Scratch' GROUP BY library_name
LIBRARY_NAME Unnamed[2]
------------------ -----------
3584 18
Number of
volumes per device class
tsm: SERVER1> SELECT devclass_name,
COUNT(*) FROM volumes GROUP BY devclass_name
DEVCLASS_NAME Unnamed[2]
------------------ -----------
3584 133
DISK 6
Number of
volumes per storage pool
tsm: SERVER1> SELECT stgpool_name,COUNT(*)
FROM volumes GROUP BY stgpool_name
STGPOOL_NAME Unnamed[2]
------------------ -----------
AIX_ANUAL 4
AIX_ARCH1 2
AIX_ARCH2 2
AIX_DAILY 20
AIX_MONTHLY 4
AIX_NOLIMIT 1
NT_DAILY 41
NT_MONTHLY 22
Number of
volumes unavailable
tsm: SERVER1> SELECT COUNT(*) FROM volumes
WHERE access='UNAVAILABLE'
Unnamed[1]
-----------
0
Number of
volumes in error state
tsm: SERVER1> SELECT COUNT(*) FROM volumes
WHERE error_state='YES'
Unnamed[1]
-----------
1
Volumes
with write or read errors in the library
tsm: SERVER1> SELECT volumes.volume_name,
volumes.stgpool_name, volumes.pct_utilized, volumes.status, -
volumes.write_errors, volumes.read_errors
FROM volumes, libvolumes WHERE -
volumes.volume_name=libvolumes.volume_name
AND ( volumes.write_errors>0 OR volumes.read_errors>0 )
VOLUME_NAME STGPOOL_NAME PCT_UTILIZED STATUS
WRITE_ERRORS READ_ERRORS
------------------ ------------------ ------------ ------------------ ------------ -----------
P10128 AIX_DAILY 27.1 FILLING
1 0
P10129 AIX_DAILY 8.2 FULL
2 0
P10135 NT_MONTHLY 22.3 FILLING 0 1
...
Number of
volumes per library
tsm: SERVER1> SELECT library_name,COUNT(*)
FROM libvolumes GROUP BY library_name
LIBRARY_NAME Unnamed[2]
------------------ -----------
3584 72
Volume
information ordered by (%) reclaim
tsm: SERVER1> SELECT
volume_name,devclass_name,stgpool_name,pct_reclaim,pct_utilized,status,access
FROM volumes order by pct_reclaim
VOLUME_NAME DEVCLASS_NAME STGPOOL_NAME PCT_RECLAIM PCT_UTILIZED STATUS ACCESS
--------------- -----------------
---------------- ----------- ------------ -------------- -------------
TA0148L4 D3584 DAILY 0.0 9.7 FILLING READWRITE
TA0149L4 D3584 DAILY 0.0 13.5 FILLING READWRITE
TA0045L4 D3584 DAILY 0.1 0.1 FILLING READWRITE
TA0144L4 D3584 DAILY 0.1 24.0 FILLING READWRITE
TA0122L4 D3584 WEEKLY 0.2 23.3 FILLING READWRITE
TA0172L4 D3584 DAILY 0.2 0.0 FILLING READWRITE
TA0023L4 D3584 DAILY 0.3 0.0 FILLING READWRITE
TA0125L4 D3584 WEEKLY 0.3 99.6 FULL READWRITE
...
Full
volumes with utilization (%) less than XX
tsm: SERVER1> SELECT
volume_name,devclass_name,stgpool_name,pct_reclaim,pct_utilized FROM volumes -
WHERE status='FULL' AND pct_utilized < 10
VOLUME_NAME DEVCLASS_NAME STGPOOL_NAME PCT_RECLAIM PCT_UTILIZED
--------------- ------------------ ---------------- ----------- ------------
R00010L3 3584 NT_DAILY 94.9 5.2
R00015L3 3584 AIX_DDAILY 99.9 0.0
R00026L3 3584 NT_DAILY 94.2 6.0
R00028L3 3584 AIX_DAILY 99.9 0.0
...
Full
volumes with reclaimable space (%) greater than XX
tsm: SERVER1> SELECT
volume_name,devclass_name,stgpool_name,pct_reclaim,pct_utilized FROM volumes -
WHERE status='FULL' AND pct_reclaim >90
VOLUME_NAME DEVCLASS_NAME STGPOOL_NAME PCT_RECLAIM PCT_UTILIZED
--------------- ------------------ ---------------- ----------- ------------
R00010L3 3584 NT_DAILY 94.9 5.2
R00015L3 3584 AIX_DAILY 99.9 0.0
R00026L3 3584 NT_DAILY 94.2 6.0
R00028L3 3584 AIX_DAILY 99.9 0.0
...
Full
volumes with reclaimable space (%) greater than XX in the library
tsm: SERVER1> SELECT volumes.volume_name,
volumes.stgpool_name, volumes.pct_utilized, volumes.pct_reclaim, -
volumes.status, volumes.access FROM volumes,
libvolumes WHERE volumes.volume_name=libvolumes.volume_name -
AND volumes.status='FULL' AND
volumes.pct_reclaim>80 ORDER BY stgpool_name
VOLUME_NAME STGPOOL_NAME PCT_UTILIZED PCT_RECLAIM
STATUS ACCESS
------------------ ------------------ ------------
-----------
------------------
------------------
256AFB NIGHTLY 12.4 87.5
FULL READWRITE
295AFB NIGHTLY 11.3 88.6
FULL READWRITE
...
Volumes
in a specific storage pool with reclaimable space (%) greater than XX
tsm: SERVER1> SELECT volume_name,devclass_name,stgpool_name,pct_reclaim,pct_utilized
FROM volumes -
WHERE pct_reclaim>80 AND
stgpool_name='OFFSITE'
VOLUME_NAME DEVCLASS_NAME STGPOOL_NAME PCT_RECLAIM PCT_UTILIZED
------------------ ------------------ ------------------ ----------- ------------
tape11 LTO OFFSITE 99.9 0.0
tape84 LTO OFFSITE 85.0 15.0
tape86 LTO OFFSITE 90.3 9.6
tape90 LTO OFFSITE 90.3 9.6
...
Number of
tapes per storage pool in the library
tsm: SERVER1> SELECT volumes.stgpool_name,
count(*) FROM volumes, libvolumes WHERE -
volumes.volume_name=libvolumes.volume_name
GROUP BY stgpool_name
STGPOOL_NAME Unnamed[2]
------------------ -----------
AIX_DAILY 338
AIX_ARCH1 22
...
tsm: SERVER1> SELECT volume_name FROM
libvolumes WHERE status='Private' AND last_use IS NULL AND -
volume_name NOT IN (SELECT volume_name FROM
volumes )
VOLUME_NAME
---------------
TAPE01L4
TAPE01L4
TAPE01L4
TAPE01L4
Some
information about volumes in the library
tsm: SERVER1> SELECT volume_name,
stgpool_name, pct_utilized, pct_reclaim, status, access FROM volumes -
WHERE volume_name IN ( SELECT volume_name
FROM libvolumes )
VOLUME_NAME STGPOOL_NAME PCT_UTILIZED PCT_RECLAIM
STATUS ACCESS
---------------- ---------------- ------------
----------- -------------- ------------
290AFB AIX_DAILY 59.3 41.2
FILLING READWRITE
241AFB AIX_DAILY 59.8 40.1
FULL READWRITE
265AFB NT_MONTHLY 0.4 0.1
FILLING READWRITE
365AFB AIX_ARCH1 47.7 0.0
FILLING READWRITE
...
Some
information about volumes in the library - another way
tsm: SERVER1> SELECT volumes.volume_name,
volumes.stgpool_name, volumes.pct_utilized, volumes.pct_reclaim,
volumes.status, -
volumes.access FROM volumes, libvolumes WHERE
volumes.volume_name=libvolumes.volume_name ORDER BY stgpool_name
VOLUME_NAME STGPOOL_NAME PCT_UTILIZED PCT_RECLAIM
STATUS ACCESS
------------------ ------------------ ------------
-----------
------------------
------------------
290AFB AIX_DAILY 59.3 41.2
FILLING READWRITE
241AFB AIX_DAILY 59.8
40.1 FULL READWRITE
265AFB NT_MONTHLY 0.4 0.1
FILLING READWRITE
365AFB AIX_ARCH1 47.7 0.0
FILLING READWRITE
...
Nodes
that have data stored in a specifc volume
tsm: SERVER1> SELECT DISTINCT node_name,
volume_name, stgpool_name FROM volumeusage WHERE volume_name='TAPE10'
NODE_NAME VOLUME_NAME STGPOOL_NAME
------------------ ------------------ ------------------
NODE45 TAPE10 DAILY
NODE10 TAPE10 DAILY
NODE33 TAPE10 DAILY
NODE20 TAPE10 DAILY
Number of
nodes that have data stored per volume
tsm: SERVER1> SELECT volume_name,
stgpool_name, COUNT(DISTINCT node_name) AS "Number of Nodes" FROM -
volumeusage GROUP BY volume_name,
stgpool_name
VOLUME_NAME STGPOOL_NAME Number of Nodes
----------------- ----------------- ---------------
TA0016L4 DAILY 31
TA0017L4 DAILY 1
TA0018L4 WEEKLY 30
TA0019L4 DAILY 44
TA0023L4 DAILY 1
...
Number of
volumes in the library per owner (useful in a library manager environment)
tsm: SERVER1> SELECT owner,count(*) FROM
libvolumes WHERE status<>'Scratch' GROUP BY owner
OWNER Unnamed[2]
------------------ -----------
library_client_1 141
library_client_2 105
library_client_3 53
library_client_4 101
library_server 257
Compare
size and number of files between two storage pools
tsm: SERVER1> SELECT
stgpool_name,SUM(logical_mb)AS Logical_MB,SUM(num_files)AS Num_Files FROM -
occupancy WHERE stgpool_name='DAILY' OR
stgpool_name='COPY_DAILY' GROUP BY stgpool_name
STGPOOL_NAME LOGICAL_MB NUM_FILES
---------------- ------------------------- -----------
DAILY 1277890.99 350851
COPY_DAILY 1246583.48 350639
Utilization
(%) of storage pool disk_pool
tsm: SERVER1> SELECT pct_utilized FROM
stgpools WHERE stgpool_name='DISK_POOL'
PCT_UTILIZED
------------
20.9
Maximum
scratch volumes allowed and number of volumes used per stgpool (needs tsm
version +5.3)
tsm: SERVER1>SELECT
stgpool_name,devclass,maxscratch,numscratchused FROM stgpools
STGPOOL_NAME DEVCLASS MAXSCRATCH NUMSCRATCHUSED
------------------ ------------------ ----------- --------------
DAILY 3584 1100 521
Number of
full tsm db backups in the last 24 hours
tsm: SERVER1> SELECT COUNT(*) FROM
volhistory WHERE -
type='BACKUPFULL' AND
date_time>=current_timestamp-24 hours
Unnamed[1]
-----------
1
Number of
full or incremental tsm db backups in the last 24 hours
tsm: SERVER1> SELECT COUNT(*) FROM
volhistory WHERE ( type='BACKUPFULL' OR type='BACKUPINCR' ) -
AND date_time>=current_timestamp-24 hours
Unnamed[1]
-----------
2
Information
about tsm db backups in the last 48 hours
tsm: SERVER1> SELECT date_time, type,
backup_series, volume_seq, devclass, volume_name FROM volhistory WHERE -
( type='BACKUPFULL' OR type='BACKUPINCR' OR
type='DBSNAPSHOT' ) AND date_time>=current_timestamp-48 hours
DATE_TIME TYPE BACKUP_SERIES VOLUME_SEQ DEVCLASS VOLUME_NAME
----------------- -------------- ------------- ---------- -------------- --------------
2008-11-19 BACKUPFULL 3878 1 3584 TAPE10
04:01:55.000000
2008-11-20 BACKUPFULL 3879 1 3584 TAPE48
04:02:20.000000
Information
about drm volumes
tsm: SERVER1> SELECT drmedia.volume_name, volumes.stgpool_name,
drmedia.state, drmedia.voltype, volumes.status, -
volumes.pct_utilized FROM drmedia, volumes
WHERE drmedia.volume_name=volumes.volume_name ORDER BY drmedia.state
VOLUME_NAME STGPOOL_NAME STATE VOLTYPE STATUS PCT_UTILIZED
------------------ ------------------ ------------------ ------------
------------------ ------------
tape06 OFFSITE COURIERRETRIEVE CopyStgPool EMPTY 0.0
tape18 OFFSITE VAULT CopyStgPool FILLING 50.6
tape38 OFFSITE VAULT CopyStgPool FILLING 80.9
tape79 OFFSITE VAULT CopyStgPool FILLING 91.0
...
Information
about drm volumes in the library
tsm: SERVER1> SELECT drmedia.volume_name,
drmedia.state, drmedia.voltype FROM drmedia, libvolumes WHERE -
drmedia.volume_name=libvolumes.volume_name
ORDER BY voltype
VOLUME_NAME STATE VOLTYPE
------------------ ------------------ ------------
tape48 MOUNTABLE CopyStgPool
tape59 MOUNTABLE CopyStgPool
...
Information
about drm volumes in the library (another way)
tsm: SERVER1> SELECT volume_name, state,
voltype FROM drmedia WHERE -
volume_name IN ( SELECT volume_name FROM
libvolumes ) ORDER BY voltype
VOLUME_NAME STATE VOLTYPE
------------------ ------------------ ------------
tape48 MOUNTABLE CopyStgPool
tape59 MOUNTABLE CopyStgPool
...
Information
about drm volumes in the library with state different from
"MOUNTABLE"
tsm: SERVER1> SELECT drmedia.volume_name,
drmedia.state, drmedia.voltype FROM drmedia, libvolumes WHERE -
drmedia.volume_name=libvolumes.volume_name
AND drmedia.state<>'MOUNTABLE'
VOLUME_NAME STATE VOLTYPE
------------------ ------------------ ------------
tape36 COURIER CopyStgPool
tape82 COURIER CopyStgPool
...
Drm
volumes with tsm db backups
tsm: SERVER1> SELECT volume_name, state,
upd_date, location, voltype FROM drmedia -
WHERE voltype='DBBackup' OR
voltype='DBSnapshot'
VOLUME_NAME STATE UPD_DATE LOCATION VOLTYPE
------------------ ------------------ ------------------ ------------------ ------------
tape10 VAULT 2008-03-05 Iron Mountain DBBackup
11:00:00.000000
tape15 VAULT 2008-03-04 Iron Mountain DBBackup
11:00:00.000000
tape45 VAULT 2008-03-03 Iron Mountain DBBackup
...
Number of
Volumes per DRM State
tsm: SERVER1> SELECT state,count(*) as
"Number of volumes" FROM drmedia GROUP BY state
STATE Number of volumes
------------------ -----------------
COURIERRETRIEVE 26
MOUNTABLE 2
VAULT 76
VAULTRETRIEVE 1
tsm: SERVER1> SELECT COUNT(*) FROM
sessions WHERE session_type='Node'
Unnamed[1]
-----------
16
Number of
nodes sessions in Media Wait state
tsm: SERVER1> SELECT COUNT(*) FROM
sessions WHERE session_type='Node' AND state='MediaW'
Unnamed[1]
-----------
1
Nodes
sessions in Media Wait state
tsm: SERVER1> SELECT client_name,
session_id, start_time, state, mount_point_wait, input_mount_wait,
input_vol_wait -
FROM sessions WHERE state='MediaW'
CLIENT_NAME SESSION_ID START_TIME STATE
MOUNT_POINT_WAIT
INPUT_MOUNT_WAIT INPUT_VOL_WAIT
------------- -----------
------------------
--------- ------------------ ------------------ ----------------
NODE23
1577742 2008-11-21 MediaW ,F00827,81
11:26:03.000000
NODE15 1581236 2008-11-21 MediaW
11:37:06.000000
Nodes
using tapes (drives)
tsm: SERVER1> SELECT client_name,
session_id, start_time, state, bytes_sent, bytes_received, input_vol_access,
output_vol_access -
FROM sessions WHERE ( input_vol_access is not
NULL OR output_vol_access is not NULL )
CLIENT_NAME
SESSION_ID START_TIME
STATE BYTES_SENT BYTES_RECEIVED INPUT_VOL_ACCESS OUTPUT_VOL_ACCESS
------------- ----------- ------------------
--------- -------------- ------------------ ------------------
------------------
NODE10 1578627 2008-11-21 RecvW 476 2913518005 ,3M0922,1214
08:37:41.000000
NODE25 1578776 2008-11-21 RecvW 540 123087561 ,F01091,117
08:46:52.000000
Information
about sessions from a specific node
tsm: SERVER1> SELECT session_id,
start_time, commmethod, state, wait_seconds, CAST(bytes_sent/1024/1024 AS
DEC(8,2)) AS "MB_Sent", -
CAST(bytes_received/1024/1024 AS DEC(8,2)) AS
"MB_Rcvd", mount_point_wait FROM sessions WHERE client_name='MY_NODE'
SESSION_ID START_TIME COMMMETHOD STATE WAIT_SECONDS MB_Sent MB_Rcvd
MOUNT_POINT_WAIT
-----------
------------------
----------------
----------- ------------ ----------
---------- ------------------
1569587 2008-11-20 Tcp/Ip RecvW 0 0.00 1648.92
10:23:37.000000
Performance
of nodes sessions
tsm: SERVER1> SELECT
client_name,session_id, current_timestamp-start_time AS ElapTime, commmethod,
state, -
CAST(bytes_sent/1024/1024 AS DEC(8,2)) AS
"MB_Sent", CAST(bytes_received/1024/1024 AS DEC(8,2)) AS "MB_Rcvd",
-
cast((cast(bytes_sent as
dec(18,0))/cast((current_timestamp-start_time) seconds as decimal(18,0))) /
1024 / 1024 AS DEC (18,2)) AS "Sent_MB/s", -
cast((cast(bytes_received as
dec(18,0))/cast((current_timestamp-start_time) seconds as decimal(18,0))) /
1024 / 1024 AS DEC (18,2)) AS "Rcvd_MB/s" -
FROM sessions WHERE session_type='Node'
CLIENT_NAME
SESSION_ID ELAPTIME
COMMMETHOD STATE MB_Sent MB_Rcvd
Sent_MB/s Rcvd_MB/s
------------- -----------
--------------------- --------------- --------- ---------- ----------
------------ -------------
NODE10 76499 0 20:53:40.000000 Tcp/Ip Run
0.03 402998.64 0.00 5.35
NODE34 76500 0 20:53:40.000000 Tcp/Ip RecvW 0.03
398363.23 0.00 5.29
NODE28 76501 0 20:52:18.000000 Tcp/Ip RecvW 0.02
370801.49 0.00 4.93
NODE79
76502 0 20:52:01.000000 Tcp/Ip Run 0.03 443600.35 0.00 5.90
...
Search a
specific file from a Node
tsm: SERVER1> SELECT * FROM backups WHERE
node_name='MY_NODE' AND ll_name='dsm.opt'
NODE_NAME: MY_NODE
FILESPACE_NAME: /opt
FILESPACE_ID: 6
STATE: ACTIVE_VERSION
TYPE: FILE
HL_NAME: /tivoli/tsm/client/ba/bin/
LL_NAME: dsm.opt
OBJECT_ID: 8395325
BACKUP_DATE: 2008-11-03 19:02:35.000000
DEACTIVATE_DATE:
OWNER: root
CLASS_NAME: DEFAULT
NODE_NAME: MY_NODE
FILESPACE_NAME: /opt
FILESPACE_ID: 6
STATE: ACTIVE_VERSION
TYPE: FILE
HL_NAME:
/tivoli/tsm/client/domino/bin/domdsmc_notesb/
LL_NAME: dsm.opt
OBJECT_ID: 8091124
BACKUP_DATE: 2008-10-27 19:14:35.000000
DEACTIVATE_DATE:
OWNER: notesuser
CLASS_NAME: DEFAULT
NODE_NAME: MY_NODE
FILESPACE_NAME: /opt
FILESPACE_ID: 6
STATE: INACTIVE_VERSION
TYPE: FILE
HL_NAME: /tivoli/tsm/client/ba/bin/
LL_NAME: dsm.opt
OBJECT_ID: 8091063
BACKUP_DATE: 2008-10-27 19:14:34.000000
DEACTIVATE_DATE: 2008-11-03 19:02:35.000000
OWNER: root
CLASS_NAME: DEFAULT
Search a
specific file from a node with more details
tsm: SERVER1> SELECT * FROM backups WHERE
node_name='MY_NODE' AND filespace_name='/opt' -
AND hl_name='/tivoli/tsm/client/ba/bin/' AND
ll_name='dsm.opt'
NODE_NAME: MY_NODE
FILESPACE_NAME: /opt
FILESPACE_ID: 6
STATE: ACTIVE_VERSION
TYPE: FILE
HL_NAME: /tivoli/tsm/client/ba/bin/
LL_NAME: dsm.opt
OBJECT_ID: 8395325
BACKUP_DATE: 2008-11-03 19:02:35.000000
DEACTIVATE_DATE:
OWNER: root
CLASS_NAME: DEFAULT
NODE_NAME: MY_NODE
FILESPACE_NAME: /opt
FILESPACE_ID: 6
STATE: INACTIVE_VERSION
TYPE: FILE
HL_NAME: /tivoli/tsm/client/ba/bin/
LL_NAME: dsm.opt
OBJECT_ID: 8091063
BACKUP_DATE: 2008-10-27 19:14:34.000000
DEACTIVATE_DATE: 2008-11-03 19:02:35.000000
OWNER: root
CLASS_NAME: DEFAULT
Objects
backed up of a specific node in the last 24 hours
tsm: SERVER1> SELECT
backup_date,filespace_name,type,hl_name,ll_name,owner, class_name FROM backups
-
WHERE node_name='MY_NODE' AND
backup_date>=current_timestamp-24 hours
BACKUP_DATE FILESPACE_NAME TYPE
HL_NAME LL_NAME OWNER CLASS_NAME
---------------- ----------------- ----------
---------------
------------------
----------- -------------
2008-11-19 / FILE /etc/ mtab root DEFAULT
19:04:08.000000
2008-11-19 / FILE /etc/ showdasd.list root DEFAULT
19:04:08.000000
2008-11-19 / FILE /etc/ sudoers root DEFAULT
19:04:08.000000
2008-11-19 /home FILE /support/ .bash_history support DEFAULT
19:03:25.000000
Information
about the currently running processes
tsm: SERVER1> SELECT process_num, process,
-
substr(char(start_time),1,19) AS START_TIME,
-
substr(char(current_timestamp -
start_time),1,10) AS "ELAPTIME (D HHMMSS)", -
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 "MB/s" -
FROM processes
PROCESS_NUM
PROCESS START_TIME ELAPTIME (D HHMMSS) MB MB/s
-----------
------------------
---------------
-------------------
---------- ----------
27
Space Reclamation
2008-11-22 0 02:28:26 58925.78 6.61
12:00:29
28
Migration
2008-11-22 0 00:23:01 46425.55 33.61
14:05:54
29
Migration
2008-11-22 0 00:23:01 37984.68 27.50
14:05:54
30
Migration
2008-11-22 0 00:23:01 41261.84 29.87
14:05:54
31
Migration
2008-11-22 0 00:23:01 39817.22 28.83
14:05:54
32
Migration
2008-11-22 0 00:23:01 41910.42 30.34
14:05:54
33
Migration
2008-11-22 0 00:23:01 43771.08 31.69
14:05:54
Total
client data stored (TB)
tsm: SERVER1> SELECT CAST(FLOAT(SUM(logical_mb))
/ 1024 / 1024 AS DEC(8,2)) FROM occupancy
Unnamed[1]
----------
73.04
Total
client data stored (TB) (another way - auditocc is updated by audit lic
command, take care)
tsm: SERVER1> SELECT CAST(FLOAT(SUM(total_mb))
/ 1024 / 1024 AS DEC(8,2)) FROM auditocc
Unnamed[1]
----------
72.46
Some TSM
Server information
tsm: SERVER1> SELECT server_name,
platform, -
VARCHAR(version)||'.'||VARCHAR(release)||'.'||VARCHAR(level)||'-'||VARCHAR(sublevel),
-
server_hla, server_lla, server_url, logmode,
crossdefine, licensecompliance FROM status
SERVER_NAME: TSM-SERVER1
PLATFORM: AIX-RS/6000
Unnamed[3]: 5.3.3-2
SERVER_HLA: 10.10.10.5
SERVER_LLA: 1500
SERVER_URL:
LOGMODE: NORMAL
CROSSDEFINE: ON
LICENSECOMPLIANCE: VALID
tsm: SERVER1>SELECT
tabschema,tabname,remarks FROM tables
TABSCHEMA
TABNAME REMARKS
---------
------------------
------------------
ADSM
ACTLOG Server
activity log
ADSM
ADMINS Server
administrators
ADSM
ADMIN_SCHEDULES
Administrative command schedules
ADSM
ARCHIVES Client
archive files
ADSM
AR_COPYGROUPS Management
class archive copy groups
ADSM
ASSOCIATIONS Client
schedule associations
ADSM
AUDITOCC Server
audit occupancy results
ADSM
BACKUPS Client
backup files
ADSM
BACKUPSETS Backup Set
ADSM
BU_COPYGROUPS Management
class backup copy
...
