Sunday 16 January 2011

TSM ( Tivoli Storage Manager ) SQL Select Commands (Page 2)

SQL for Tivoli Storage Manager - Cont...

8.   Summary
a.     Summary of archive operations in the last 7 days
b.    Summary of backup operations in a specific range
c.     Statistics of archive, backup, restore and retrieve operations per node in the last 7 days (GB)
d.    Summary of Operations in the Last 24 Hours (GB)
e.     Volumes reclaimed in the last 48 Hours
f.     Volumes reclaimed in the last 48 Hours (better date format?!)

9.     Volumes
a.     Number of scratch volumes
b.    Number of scratch volumes in library 3584
c.     Number of scratch volumes for each library
d.    Number of volumes per device class
e.     Number of volumes per storage pool
f.     Number of volumes unavailable
g.    Number of volumes in error state
h.     Volumes with write or read errors in the library
i.      Number of volumes per library
j.      Volume information ordered by (%) reclaim
k.     Full volumes with utilization (%) less than XX
l.      Full volumes with reclaimable space (%) greater than XX
m.   Full volumes with reclaimable space (%) greater than XX in the library
n.     Volumes in a specific storage pool with reclaimable space (%) greater than XX
o.    Number of tapes per storage pool in the library
p.    Some information about volumes in the library
q.    Some information about volumes in the library - another way
r.      Nodes that have data stored in a specifc volume
s.     Number of nodes that have data stored per volume
t.      Number of volumes in the library per owner (useful in a library manager environment)

10.  Storage Pools
a.     Compare size and number of files between two storage pools
b.    Utilization (%) of storage pool disk_pool
c.     Maximum scratch volumes allowed and number of volumes used per stgpool (needs tsm version +5.3)

11.  Volume History
a.     Number of full tsm db backups in the last 24 hours
b.    Number of full or incremental tsm db backups in the last 24 hours
c.     Information about tsm db backups in the last 48 hours

12.  DRM
a.     Information about drm volumes
b.    Information about drm volumes in the library
c.     Information about drm volumes in the library (another way)
d.    Information about drm volumes in the library with state different from "MOUNTABLE"
e.     Drm volumes with tsm db backups
f.     Number of Volumes per DRM State

13.  Sessions
a.     Number of nodes sessions
b.    Number of nodes sessions in Media Wait state
c.     Nodes sessions in Media Wait state
d.    Nodes using tapes (drives)
e.     Information about sessions from a specific node
f.     Performance of nodes sessions

14.  Backups
a.     Search a specific file from a Node
b.    Search a specific file from a node with more details
c.     Objects backed up of a specific node in the last 24 hours

15.  Processes
a.     Information about the currently running processes

16.  Other
a.     Total client data stored (TB)
b.    Total client data stored (TB) (another way - auditocc is updated by audit lic command, take care)
c.     Some TSM Server information
d.    SQL Table Catalog

Summary
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='ARCHIVE' 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
  ...
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
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
Volumes
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
  ...
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

Storage Pools
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

Volume History
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

DRM
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
 
Sessions
Number of nodes sessions
  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
  ...

Backups
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

Processes
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

Other
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
SQL Table Catalog
  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
  ...