CLIENTEVENTS – Desc=”Display number of client events per state in a specific date. $1 = YYYY-MM-DD”
SELECT DATE(scheduled_start) as DATE, status, count(*) as number_events FROM events WHERE DATE(scheduled_start)='$1' and node_name is not null GROUP BY DATE(scheduled_start), status
COMPARESTGPOOLS – Desc=”Compare amount of space and number of files between two stgpools. $1 – stgpool_1 and $2 – stgpool_2. Example: run comparestgpools OFF_TAPE OFF_COPY”
SELECT stgpool_name, cast(float(sum(logical_mb))/1024 as dec(10,2)) as GB, sum(num_files) as number_files, ( (SELECT CAST(FLOAT(SUM(logical_mb))/1024 AS DEC(8,2)) FROM occupancy WHERE stgpool_name=upper('$2') GROUP BY stgpool_name) - (SELECT CAST(FLOAT(SUM(logical_mb))/1024 AS DEC(8,2)) FROM occupancy WHERE stgpool_name=upper('$1') GROUP BY stgpool_name) ) as pending_copy_gb, ( (SELECT SUM(num_files) FROM occupancy WHERE stgpool_name=upper('$2') GROUP BY stgpool_name) - (SELECT SUM(num_files) FROM occupancy WHERE stgpool_name=upper('$1') GROUP BY stgpool_name) ) as pending_num_files FROM occupancy WHERE stgpool_name=upper('$1') or stgpool_name=upper('$2') GROUP BY stgpool_name
DRIVEPATH – Desc=”Display information about drives and paths”
SELECT cast((b.source_name) as char(25)) as source_name, cast((a.library_name) as char(15)) as library, cast((a.drive_name) as char(15)) as drive, cast((a.device_type) as char(10)) as device_type, cast((a.drive_serial) as char(20)) as serial, cast((b.device) as char(35)) as device, cast((a.drive_state) as char(10)) as drive_state, cast((volume_name) as char(15)) as volume, cast((a.online) as char(12)) as drive_online, cast((b.online) as char(12)) as path_online FROM drives a, paths b WHERE a.drive_name=b.destination_name and a.library_name=b.library_name
DRMDB – Desc=”List DRM TSM DB volumes – $1 dbs or dbb”
SELECT cast ((volume_name) as char (8)) as VolSer, cast ((state) as char (15)) as State, upd_date, cast ((voltype) as char (10)) as VolType FROM drmedia WHERE upper(voltype) like upper('$1%')
DRMINLIB – Desc=”Information about drm volumes in the library”
SELECT cast ((drmedia.volume_name) as char(8)) as VolSer, cast ((drmedia.stgpool_name) as char (17)) as Storage_pool, cast ((drmedia.state) as char (15)) as State, cast ((drmedia.voltype) as char(10)) As VolType FROM drmedia, libvolumes WHERE drmedia.volume_name=libvolumes.volume_name ORDER BY voltype
DRMNUMVOL – Desc=”Number of volumes per DRM state”
SELECT state,count(*) as number_of_volumes FROM drmedia GROUP BY state
DRMVOL – Desc=”List details about DRM volumes”
SELECT cast ((drmedia.volume_name) as char(8)) as VolSer,cast ((volumes.stgpool_name) as char (17)) as Storage_pool, cast ((drmedia.state) as char (15)) as State, cast ((drmedia.voltype) as char(10)) As VolType, cast ((volumes.status) as char(7)) as Status, cast ((volumes.access) as char(9)) as Access, volumes.pct_utilized FROM drmedia, volumes WHERE drmedia.volume_name=volumes.volume_name ORDER BY drmedia.state
FALSEPRIVATE – Desc=”False private tapes”
SELECT volume_name FROM libvolumes WHERE status='Private' AND last_use IS NULL AND volume_name NOT IN (SELECT volume_name FROM volumes )
NODESINFO – Desc=”Show nodes information (node, domain, platform, contact, tsm version, amount of data stored and last access date)”
SELECT cast((n.node_name) as char(50)) as NODE , cast((n.domain_name) as char(12)) as DOMAIN, n.platform_name, cast((n.contact) as char(48)) as CONTACT, cast((TRIM(CHAR(client_version))||'.'||TRIM(CHAR(client_release))||'.'||TRIM(CHAR(client_level))||'-'||TRIM(CHAR(client_sublevel))) as char(13)) as TSM_VERSION, cast(float(a.total_mb)/1024 as DEC(8,2)) AS TOTAL_GB, DATE(n.lastacc_time) as LASTACC_DATE FROM nodes n, auditocc a WHERE n.node_name=a.node_name ORDER BY TOTAL_GB DESC
NODESNOTACCESS – Desc=”Display nodes that have not accessed TSM server in the last X days. $1 = number of days”
SELECT cast((n.node_name) as char(50)) as NODE , cast((n.domain_name) as char(12)) as DOMAIN, n.platform_name, cast((n.contact) as char(48)) as CONTACT, cast((TRIM(CHAR(client_version))||'.'||TRIM(CHAR(client_release))||'.'||TRIM(CHAR(client_level))||'-'||TRIM(CHAR(client_sublevel))) as char(13)) as TSM_VERSION, cast(float(a.total_mb)/1024 as DEC(8,2)) AS TOTAL_GB, DATE(n.lastacc_time) as LASTACC_DATE FROM nodes n, auditocc a WHERE n.node_name=a.node_name AND DAYS(current_date)-DAYS(lastacc_time)>$1 ORDER BY lastacc_time
NUMNODES – Desc=”Display number of nodes (total, by domain, by platform, by client_os_level and by TSM client version)”
SELECT count(*) as total_number_of_nodes from nodes
SELECT domain_name,COUNT(*) as number_nodes FROM nodes GROUP BY domain_name
SELECT platform_name,COUNT(*) number_nodes FROM nodes GROUP BY platform_name
SELECT client_os_name,COUNT(*) number_nodes FROM nodes GROUP BY client_os_name
SELECT TRIM(CHAR(client_version))||'.'||TRIM(CHAR(client_release))||'.'||TRIM(CHAR(client
