TSM Topics Feed

TSM 5.5 SQL

The following is a list of the TSM select statements I have created over the last 10+ years. You will need to modify some to allow them to work with your environment, but they should provide a starting point for some complex queries. I was hoping to index this before I published it, but realized if I waited until that was done I would never get it posted.

================================
  TSMAdmin Select Statements
================================

Query drive name, device name, and status
-----------------------------------------
select SOURCE_NAME, DESTINATION_NAME, DEVICE, ONLINE from paths where libname='PD703-STLB03'

######################
# Define Drive Paths #
######################

select 'define path $1 ', drive_name, ' srct=server destt=drive libr=$2 device=/dev/$3 online=yes' from status, drives where DRIVE_SERIAL='$4'


##################
# AUDITOCCUPANCY #
##################

select NODE_NAME, BACKUP_MB, BACKUP_COPY_MB, ARCHIVE_MB, ARCHIVE_COPY_MB, TOTAL_MB from AUDITOCC order by TOTAL_MB desc

select NODE_NAME, BACKUP_MB, BACKUP_COPY_MB, (BACKUP_MB+BACKUP_COPY_MB) AS TOTAL_MB, (BACKUP_MB-BACKUP_COPY_MB) AS "ONSITE-OFFSITE=DIFF" from AUDITOCC order by "ONSITE-OFFSITE=DIFF" desc

select '$1 ' as SERVER_NAME, NODE_NAME, BACKUP_MB, BACKUP_COPY_MB, (BACKUP_MB+BACKUP_COPY_MB) AS TOTAL_MB, (BACKUP_COPY_MB-BACKUP_MB) AS "OFFSITE DIFF" from AUDITOCC order by "OFFSITE DIFF" desc

def script AUDIT_STORAGE desc="Shows the occupancy for each nodes data and the offsite difference"
upd script AUDIT_STORAGE "select '$1 ' as SERVER_NAME, NODE_NAME, BACKUP_MB, BACKUP_COPY_MB, -"
upd script AUDIT_STORAGE '(BACKUP_MB+BACKUP_COPY_MB) AS TOTAL_MB, (BACKUP_COPY_MB-BACKUP_MB) AS -'
upd script AUDIT_STORAGE ' "OFFSITE DIFF" from AUDITOCC order by "OFFSITE DIFF" desc '


Delete 98% empty Copy Pool Volumes
-----------------------------------

select 'delete volume ' || cast(volume_name as char(6)) || ' discard=yes wait=yes' from volumes where pct_reclaim>97.9 and stgpool_name like '%COPY'

select a.server_name, b.admin_name from status a, admins b where b.admin_name=b.CLIENT_OWNER and cast((current_timestamp-b.LASTACC_TIME)hours as decimal)>1488


Migration Select
----------------
select distinct a.entity, cast((sum(a.bytes)/1073741824) as decimal(18,2)) AS "TOTAL GB MIGRATED", cast((b.EST_CAPACITY_MB/1024) as decimal(18,2)) AS EST_CAPACITY_GB, cast(((sum(a.bytes)/1073741824)/7) as decimal(18,2)) AS AVG_DAILY_MIGR from summary a, stgpools b where cast((current_timestamp-a.start_time)hours as decimal)<168 and upper(a.activity)='MIGRATION' and a.entity=b.stgpool_name group by a.entity, b.EST_CAPACITY_MB

select entity,sum(bytes)/1024/1024/1024 as GB from summary where activity='MIGRATION' and start_time>current timestamp - 1 day group by entity

select entity,sum(bytes)/1024/1024/1024 as GB from summary where activity='MIGRATION' and cast((current_timestamp-start_time)hours as decimal)<744 and

select entity, cast((AVG(bytes)/1024/1024/1024)as decimal(18,2)) AS "TOTAL GB MIGRATED" from summary where activity='MIGRATION' and start_time>current timestamp -30 day group by entity

or

select entity, cast((AVG(bytes)/1024/1024/1024)as decimal(18,2)) AS "TOTAL GB MIGRATED" from summary where activity='MIGRATION' and cast((current_timestamp-start_time)hours as decimal)<744 group by entity

select a.entity,sum(a.bytes)/1024/1024/1024 as GB_MIGRATED, b.EST_CAPACITY_MB/1024 AS EST_CAPACITY_GB from summary a, stgpools b where a.entity=b.stgpool_name and a.activity='MIGRATION' and a.start_time>current timestamp - 1 day group by a.entity, b.EST_CAPACITY_MB


Show Tape Drive Utilization over a two week period
----------------------------------------------------
select sum(cast((end_time-start_time)hours as decimal)) AS "Total Hrs Tapes Were Mounted" from summary where activity='TAPE MOUNT' and library_name='PD703-STLB03' and cast((current_timestamp-start_time)hours as decimal)<336


select cast((sum(end_time-start_time))hours as decimal) AS "Total Hrs Tapes Were Mounted" from summary where activity='TAPE MOUNT' and library_name='PD703-STLB03' and cast((current_timestamp-start_time)hours as decimal)<336


List of STG Pools with more than 10 reclaimable volumes
---------------------------------------------------------
select stgpool_name,count(*) as number from volumes where pct_reclaim>80 group by stgpool_name having count(*)>10

or

select stgpool_name from volumes where pct_reclaim>80 group by stgpool_name having count(*)>10

STGPOOL BACKUP Process Report
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

select entity, cast(sum(bytes/1073741824) as decimal(18,2)) AS "GB BACKED UP" from summary where activity='STGPOOLBACKUP' and cast((current_timestamp-start_time)hours as decimal)<24 group by entity

select ACTIVITY, char(cast((start-time)as date))||' '|| substr(cast((start_timeselect )time),0,9) AS START_TIME, END_TIME, SUBSTR(CHAR(START_TIME-END_TIME),3,9) AS "ELAPSED TIME", successful from summary where activity='STGPOOL BACKUP'


Storage Agent Count
--------------------
select count (server_name) from servers where cast((current_timestamp-LASTACC_TIME)hours as decimal)<744 and server_name like '%STA'

select SERVER_NAME, HL_ADDRESS, LASTACC_TIME from servers where cast((current_timestamp-LASTACC_TIME)hours as decimal)<744 and server_name like '%STA'

select SERVER_NAME, HL_ADDRESS, cast((LASTACC_TIME) AS DATE) AS LASTACC_TIME from servers where cast((current_timestamp-LASTACC_TIME)hours as decimal)<744 and server_name like '%STA'


Client Backup Summary
----------------------

select entity, activity, CAST((START_TIME)AS DATE) AS "START DATE", CAST((START_TIME)AS TIME) AS "START TIME", CAST((END_TIME) AS TIME) AS "END TIME", SUBSTR(CHAR(END_TIME-START_TIME),1,10) AS "ELAPSED TIME", EXAMINED, cast(((bytes/1024)/1024) as decimal(18,2)) AS "MB BACKED UP", IDLE/60 AS IDLE_WAIT, MEDIAW/60 AS MEDIA_WAIT, comm_wait/60 as COMM_WAIT, SUCCESSFUL FROM SUMMARY where activity='STGPOOL BACKUP' and start_time>(current_timestamp - 1 days)

select entity, activity, CAST((START_TIME)AS DATE) AS "START DATE", CAST((START_TIME)AS TIME) AS "START TIME", CAST((END_TIME) AS TIME) AS "END TIME", SUBSTR(CHAR(END_TIME-START_TIME),1,10) AS "ELAPSED TIME", EXAMINED, cast(((bytes/1024)/1024) as decimal(18,2)) AS "MB BACKED UP", IDLE/60 AS IDLE_WAIT, MEDIAW/60 AS MEDIA_WAIT, comm_wait/60 as COMM_WAIT, PROCESSES, SUCCESSFUL FROM SUMMARY where activity='STGPOOL BACKUP' and start_time>(current_timestamp - 2 days)

select entity, activity, CAST((START_TIME)AS TIME) AS "START TIME", CAST((END_TIME)AS DATE) AS "END DATE",CAST((END_TIME) AS TIME) AS "END TIME", substr(cast((end_time-start_time) as char(22)),7,2) || ' ' || substr(cast((end_time-start_time) as char(22)),9,2) || ':' || substr(cast((end_time-start_time) as char(22)),11,2) || ':' || substr(cast((end_time-start_time) as char(22)),13,2) AS ELAPSED_TIME, EXAMINED, cast((bytes/1073741824) as decimal(18,2)) AS "GB BACKED UP", IDLE/60 AS IDLE_WAIT, MEDIAW/60 AS MEDIA_WAIT, comm_wait/60 as COMM_WAIT, SUCCESSFUL FROM SUMMARY where activity='BACKUP' and start_time>(CURRENT TIMESTAMP - 10 DAYS) and entity='EMC_HCNCPRODCAM'

select entity, CAST((START_TIME)AS DATE) AS "DATE", CAST((START_TIME)AS TIME) AS "START TIME", CAST((END_TIME) AS TIME) AS "END TIME", SUBSTR(CHAR(START_TIME-END_TIME),3,9) AS "ELAPSED TIME", cast((bytes/1073741824) as decimal(18,2)) AS "GB BACKED UP",successful from summary where cast((current_timestamp-start_time)hours as decimal)<744 and upper(activity)='BACKUP' and cast((bytes/1073741824) as decimal(18,2))>1000

select entity, CAST((START_TIME)AS DATE) AS "DATE", CAST((START_TIME)AS TIME) AS "START TIME", CAST((END_TIME) AS TIME) AS "END TIME", cast(SUBSTR(CHAR(START_TIME-END_TIME),3,9)as time) AS "ELAPSED TIME", affected AS Files_Copied, cast((bytes/1073741824) as decimal(18,2)) AS "GB BACKED UP",successful from summary where cast((current_timestamp-start_time)hours as decimal)<24 and upper(activity)='STGPOOL BACKUP'

select entity, CAST((START_TIME)AS DATE) AS "DATE", CAST((START_TIME)AS TIME) AS "START TIME", CAST((END_TIME) AS TIME) AS "END TIME", SUBSTR(CHAR(START_TIME-END_TIME),3,9) AS "ELAPSED TIME", AFFECTED AS BACKED_UP, FAILED, cast((bytes/1073741824) as decimal(18,2)) AS "GB BACKED UP",successful from summary where cast((current_timestamp-start_time)hours as decimal)<10 and upper(activity)='BACKUP' and entity like 'EMC_HCNCPRODCAM'

select entity, CAST((START_TIME)AS DATE) AS "DATE", CAST((START_TIME)AS TIME) AS "START TIME", CAST((END_TIME) AS TIME) AS "END TIME", SUBSTR(CHAR(START_TIME-END_TIME),3,9) AS "ELAPSED TIME", AFFECTED AS BACKED_UP, cast((mediaw/60) as decimal(4,1)) AS Media_Wait, cast((comm_wait/60) as decimal(4,1)) AS Comm_Wait, cast((bytes/1073741824) as decimal(18,2)) AS "GB BACKED UP",successful from summary where cast((current_timestamp-start_time)hours as decimal)<18 and upper(activity)='BACKUP' and entity like

select entity, schedule_name, CAST((START_TIME)AS DATE) AS "DATE", CAST((START_TIME)AS TIME) AS "START TIME", CAST((END_TIME) AS TIME) AS "END TIME", SUBSTR(CHAR(START_TIME-END_TIME),3,2) AS "ELAPSED TIME", AFFECTED AS BACKED_UP, (comm_wait/60) AS COMM_WAIT, mediaw, FAILED, cast((bytes/1073741824) as decimal(18,2)) AS "GB BACKED UP",successful from summary where cast((current_timestamp-start_time)days as decimal)<90 and upper(activity)='BACKUP' and entity like '%HH%ORA' order by "ELAPSED TIME" desc

select entity, schedule_name, activity, CAST((START_TIME)AS DATE) AS "DATE", CAST((START_TIME)AS TIME) AS "START TIME", CAST((END_TIME) AS TIME) AS "END TIME", SUBSTR(CHAR(START_TIME-END_TIME),3,9) AS "ELAPSED TIME", cast((bytes/1073741824) as decimal(18,2)) AS "GB BACKED UP",successful from summary where cast((current_timestamp-start_time)hours as decimal)<744 and (upper(activity)='BACKUP' or upper(activity)='ARCHIVE')


CLIENT COPYGROUP QUERY:
-----------------------
select a.node_name, b.DOMAIN_NAME, b.SET_NAME, b.CLASS_NAME, b.COPYGROUP_NAME, b.VEREXISTS, b.VERDELETED, b.RETEXTRA, b.RETONLY, b.DESTINATION from nodes a, BU_COPYGROUPS b where a.domain_name=b.domain_name and b.CLASS_NAME in (select class_name from mgmtclasses where defaultmc='Yes' and set_name<>'ACTIVE') and b.set_name<>'ACTIVE' order by a.node_name


STGPOOL BACKUP FILES COUNT QUERY:
---------------------------------
select sum(affected) AS FILES_BACKED_UP from summary where activity='STGPOOL BACKUP' and cast((current_timestamp-start_time)hours as decimal)<10

select server_name, activity, entity, CAST((START_TIME)AS DATE) AS "START DATE", CAST((START_TIME)AS TIME) AS "START TIME", CAST((END_TIME)AS DATE) AS "END DATE", CAST((END_TIME) AS TIME) AS "END TIME", SUBSTR(CHAR(START_TIME-END_TIME),3,9) AS "ELAPSED TIME", EXAMINED, cast((bytes/1073741824) as decimal(18,2)) AS "GB BACKED UP", SUCCESSFUL FROM SUMMARY, status where activity='STGPOOL BACKUP' and cast((current_timestamp-start_time)hours as decimal)<16


RESTORE QUERY:
--------------
select entity, schedule_name, CAST((START_TIME)AS DATE) AS "DATE", CAST((START_TIME)AS TIME) AS "START TIME", CAST((END_TIME) AS TIME) AS "END TIME", SUBSTR(CHAR(START_TIME-END_TIME),3,9) AS "ELAPSED TIME", (comm_wait/60) AS COMM_WAIT, (mediaw/60) AS MEDIA_WAIT, AFFECTED AS FILES_RESTORED, FAILED, cast((bytes/1073741824) as decimal(18,2)) AS "GB RESTORED",successful from summary where cast((current_timestamp-start_time)days as decimal)<3 and upper(activity)='RESTORE' and entity like 'PD0NP-WAG040'


Client Backups That Take 24+ hours
------------------------------------
select entity, schedule_name, start_time, end_time, cast((end_time-start_time)hours as decimal) AS Elapsed_Hours from summary where cast((current_timestamp-start_time)hours as decimal)<168 and upper(activity)='BACKUP' and cast((end_time-start_time)hours as decimal)>24

select a.server_name, b.entity, b.schedule_name, b.start_time, b.end_time, cast((b.end_time-b.start_time)hours as decimal) AS Elapsed_Hours, cast((b.bytes/1073741824) as decimal(18,2)) AS "GB BACKED UP" from status a, summary b where cast((current_timestamp-b.start_time)hours as decimal)<744 and upper(b.activity)='BACKUP' and cast((b.end_time-b.start_time)hours as decimal)>10 order by b.entity, b.start_time

select a.server_name, b.entity, b.schedule_name, cast((b.start_time)as date) AS START_DATE, cast((b.start_time)as time) AS START_TIME, cast((b.end_time)AS date) AS END_DATE, cast((b.end_time)AS time) AS END_TIME, cast((b.end_time-b.start_time)hours as decimal) AS Elapsed_Hours, cast((b.bytes/1073741824) as decimal(18,2)) AS "GB BACKED UP" from status a, summary b where cast((current_timestamp-b.start_time)hours as decimal)<744 and upper(b.activity)='BACKUP' and cast((b.end_time-b.start_time)hours as decimal)>10 order by b.entity

Client Backup Sums
-------------------
TSM 6.x:
select cast((sum(bytes/1073741824)) as decimal(18,2)) AS "GB BACKED UP" from summary where start_time>(CURRENT TIMESTAMP - 10 HOURS)

select cast((sum(bytes/1073741824)) as decimal(18,2)) AS "GB BACKED UP" from summary where start_time>(CURRENT TIMESTAMP - 1 DAYS)

TSM 5.x
select entity, cast((sum(bytes/1073741824)) as decimal(18,2)) AS "GB BACKED UP" from summary where cast((current_timestamp-start_time)hours as decimal)<24 and upper(activity)='BACKUP' group by entity order by "GB BACKED UP" having count(*)>10

select cast((sum(bytes/1073741824)) as decimal(18,2)) AS "GB BACKED UP" from summary where upper(activity)='BACKUP' and cast((current_timestamp-start_time)hours as decimal)<744

select cast((sum(bytes/1073741824)) as decimal(18,2)) AS "GB BACKED UP" from summary where cast((current_timestamp-start_time)hours as decimal)<721 and activity='NAS Backup'

select cast((sum(bytes/1073741824)) as decimal(18,2)) AS "GB BACKED UP" from summary where cast((current_timestamp-start_time)hours as decimal)<1464 and cast((current_timestamp-start_time)hours as decimal)>744 and upper(activity)='BACKUP'

select cast((sum(bytes/1073741824)/31) as decimal(18,2)) AS "AVG 31 DAY BACKUP AMOUNT" from summary where cast((current_timestamp-start_time)hours as decimal)<744 and upper(activity)='BACKUP'


TDP Activity last 24hrs.
------------------------
select entity,activity,sum(bytes)/1048576 AS MB_BACKED_UP,sum(end_time-start_time) AS ELAPSED_TIME,sum(affected) AS FILES_BACKED_UP,sum(failed) AS FILES_FAILED,sum(mediaw) AS MEDIA_WAIT from summary where entity in (select node_name from filespaces where filespace_type like '%API%' group by node_name) and cast((current_timestamp-start_time)hours as decimal)<24 and entity like '%CRP' group by entity,activity              


NAS Activity Last 48hrs.
-------------------------
select entity, SCHEDULE_NAME, CAST((START_TIME)AS DATE) AS "DATE", CAST((START_TIME)AS TIME) AS "START TIME", CAST((END_TIME) AS TIME) AS "END TIME", SUBSTR(CHAR(START_TIME-END_TIME),3,9) AS "ELAPSED TIME", cast((bytes/1073741824) as decimal(18,2)) AS "GB BACKED UP", successful from summary where cast((current_timestamp-start_time)hours as decimal)<48 and upper(activity)='NAS BACKUP'

select entity, cast((sum(bytes/1073741824)) as decimal(18,2)) AS "GB BACKED UP",successful from summary where cast((current_timestamp-start_time)hours as decimal)<48 and upper(activity)='NAS BACKUP' group by entity


Backupset Generation Query Scripts
-----------------------------------
select substr(MESSAGE,9,256) AS MESSAGE, MSGNO, PROCESS, DATE_TIME from actlog where MSGNO in (0987,2017,3501,3505) and date_time>'2008-01-09 00:00:00' and (MESSAGE like '%BACKUPSET%' or MESSAGE LIKE '%Backup set%') order by date_time, process

select b.node_name AS "INFOCROSSING NAME", b.tcp_name AS "SUNOCO NAME", cast((a.date_time)AS DATE) AS "START DATE",cast((a.date_time)AS TIME) AS "START TIME" from backupsets a, nodes b where a.node_name=b.node_name and  a.node_name=substr(b.command,20,12)


select b.node_name AS "INFOCROSSING NAME", b.tcp_name AS "SUNOCO NAME", substr(a.start_time,1,20) AS "START TIME", substr(a.end_time,1,20) AS "END TIME", EXAMINED, cast((bytes/1073741824) as decimal(18,2)) AS "GB BACKED UP", SUCCESSFUL FROM SUMMARY a, nodes b where b.tcp_name like


select ENTITY, CAST((START_TIME)AS DATE) AS "DATE", CAST((START_TIME)AS TIME) AS"START TIME", CAST((END_TIME) AS TIME) AS "END TIME", SUBSTR(CHAR(END_TIME-START_TIME),1,10) AS "ELAPSED TIME", EXAMINED, cast((bytes/1073741824) as decimal(18,2)) AS "GB BACKED UP", SUCCESSFUL FROM SUMMARY where ACTIVITY='BACKUPSETGEN'

select ENTITY, CAST((START_TIME)AS DATE) AS "DATE", CAST((START_TIME)AS TIME) AS"START TIME", CAST((END_TIME) AS TIME) AS "END TIME", SUBSTR(CHAR(END_TIME-START_TIME),1,10) AS "ELAPSED TIME", EXAMINED, cast((bytes/1073741824) as decimal(18,2)) AS "GB BACKED UP", ACTIVITY, SUCCESSFUL FROM SUMMARY where ACTIVITY in ('NAS Backup','NAS Restore')

select a.ENTITY, CAST((a.START_TIME)AS DATE) AS "DATE", CAST((a.START_TIME)AS TIME) AS "START TIME", CAST((a.END_TIME) AS TIME) AS "END TIME", SUBSTR(CHAR(a.END_TIME-a.START_TIME),1,10) AS "ELAPSED TIME", a.EXAMINED, cast((a.bytes/1073741824) as decimal(18,2)) AS "GB BACKED UP", a.ACTIVITY, a.SCHEDULE_NAME, SUBSTR((b.COMMAND),27,10)AS FILESPACE, SUCCESSFUL FROM SUMMARY a, ADMIN_SCHEDULES b where a.ACTIVITY='NAS Backup' and a.SCHEDULE_NAME=b.SCHEDULE_NAME


Process Summary last 24hrs.
----------------------------
select number,start_time,(CAST((END_TIME-START_TIME) SECONDS AS DECIMAL(18,13))*3600) AS "ELAPSED_TIME",activity,entity,successful,bytes,((bytes/)/CAST((END_TIME-START_TIME) SECONDS AS DECIMAL(18,13))*3600) AS "Bytes/HR",examined,affected,failed,mediaw from summary where cast((current_timestamp-start_time)hours as decimal)<24 and activity is not NULL and activity<>'TAPE MOUNT' and commmeth is NULL

select count(library_name) as STK_MOUNTS from summary where library_name like 'LB_SL8500_TSMNDC%' and START_TIME>(CURRENT TIMESTAMP - 1 DAYS)


select entity, cast((AVG(bytes/1073741824)) as decimal(18,2)) AS "AVG GB MIGRATED" from summary where entity like '%DISK' and cast((current_timestamp-start_time)hours as decimal)<744 and upper(activity)='MIGRATION' group by entity


****MORE ACCURATE****
select entity, cast(((sum(bytes/1073741824))/31) as decimal(18,2)) AS "AVG GB MIGRATED" from summary where entity like '%DISK' and cast((current_timestamp-start_time)hours as decimal)<744 and upper(activity)='MIGRATION' group by entity


select b.stgpool_name, cast((b.EST_CAPACITY_MB/1024) as decimal(18,2)) AS "GB POOL SIZE", cast((AVG(a.bytes/1073741824)) as decimal(18,2)) AS "AVG GB MIGRATED", from summary a, stgpools b where a.entity=b.stgpool_name and a.stgpool_name like '%DISK' and cast((current_timestamp-start_time)hours as decimal)<744 and upper(activity)='MIGRATION' group by b.stgpool_name


EXPIRATION Results
----------------------
SELECT ACTIVITY, EXAMINED, AFFECTED, START_TIME, SUBSTR(CHAR(START_TIME-END_TIME),3,9) AS "ELAPSED TIME" FROM SUMMARY WHERE ACTIVITY='EXPIRATION'



select number,start_time,(end_time-start_time) AS "ELAPSED_TIME",activity,entity,successful,bytes,bytes,examined,affected,failed,mediaw from summary where cast((current_timestamp-start_time)hours as decimal)<96 and activity is not NULL and activity='MIGRATION' and commmeth is NULL


Node Listing
--------------------------

Repeat_Offenders
================
select a.node_name, a.schedule_name, b.platform_name || ' ' || cast(b.CLIENT_OS_LEVEL as char(20)) as "CLIENT_OS_LEVEL", -
cast(b.client_version as char) || '.' || cast(b.client_release as char) || '.' || cast(b.client_level as char) || '.' || cast(b.client_sublevel as char(2)) as CLIENT_VERSION, -
a.status, count(*) as "3_DAY_COUNT" from EVENTS a, nodes b where -
(a.scheduled_start >= '2011-05-01')and (a.scheduled_start >=current_timestamp - 10 days) -
and a.node_name=b.node_name and (a.status='Failed' or a.status='Missed') -
and a.node_name is not NULL group by a.node_name, a.schedule_name, b.client_version, b.client_release, b.client_level, -
b.client_sublevel, a.status, b.platform_name, b.client_os_level having count(*)>=2 order by a.status, "3_DAY_COUNT" desc


Normal
======

select platform_name || ' ' || client_os_level AS PLATFORM_NAME, cast(client_version as char) || '.' || cast(client_release as char) || '.' || cast(client_level as char) || '.' || cast(client_sublevel as char(2)) as TSM_Version, count(distinct tcp_name) AS COUNT from nodes where platform_name not like 'TDP%' group by platform_name, client_os_level, client_version, client_release, client_level, client_sublevel

select node_name, cast((REG_TIME) AS DATE) AS REG_DATE, reg_admin, cast(client_version as char) || '.' || cast(client_release as char) || '.' || cast(client_level as char) || '.' || cast(client_sublevel as char(2)) as version from nodes order by node_name

==========================================
|Show TSM Server version and Platform OS |
==========================================

select server_name, platform, cast(version as char) || '.' || cast(release as char) || '.' || cast(level as char) || '.' || cast(sublevel as char(2)) as TSM_Version, cast((install_date)as date) AS INSTALL_DATE from status


Shows Node Name, OS version, TSM version, TSM server instance
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

select server_name, node_name, PLATFORM_NAME || ' ' || cast(CLIENT_OS_LEVEL as char(20)) as OS_Version, cast(client_version as char) || '.' || cast(client_release as char) || '.' || cast(client_level as char) || '.' || cast(client_sublevel as char(2)) as TSM_Version from nodes, status

Specific
========

select node_name, contact, cast(client_version as char) || '.' || cast(client_release as char) || '.' || cast(client_level as char) || '.' || cast(client_sublevel as char(2)) as version from nodes where client_version<=5 and ((client_release=1 and (client_level<8 or (client_level=8 and client_sublevel=0))) or (client_release=2 and (client_level<5 or (client_level=5 and client_sublevel<=1))) or (client_release=3 and (client_level<5 or (client_level=5 and client_sublevel<=2))) or (client_release=4 and (client_level<1 or (client_level=1 and client_sublevel<=1))))

Audit Report Version
====================
select distinct a.tcp_name, a.PLATFORM_NAME || ' ' || cast(a.CLIENT_OS_LEVEL as char(20)) as OS_Version, cast(a.client_version as char) || '.' || cast(a.client_release as char) || '.' || cast(a.client_level as char) || '.' || cast(a.client_sublevel as char(2)) as TSM_Version, a.tcp_address, cast((a.lastacc_time)AS date) AS LAST_ACCESS, b.server_name from nodes a, status b

Last Access
===========

select distinct tcp_name, cast((lastacc_time)AS date) AS LAST_ACCESS, contact from nodes where lastacc_time>'2009-03-01 00:00:00' and (contact not like 'Decomm%' and contact not like 'removed%')

select count(distinct tcp_name) from nodes where lastacc_time>'2009-08-01 00:00:00'

select count(node_name) from nodes where lastacc_time>'2009-08-01 00:00:00'

select count(node_name) from nodes where node_name not like '%CLONE' and platform_name not like '%DP%' and node_name not like '%MONTHLY' and node_name not like '%YEARLY' and node_name not like '%-M' and node_name not like '%-Y'

select count(node_name) from nodes where platform_name not like '%DP%'

select count(node_name) from nodes where platform_name like '%DP%'

select server_name, node_name, tcp_name as HOST_NAME, DOMAIN_NAME, cast(client_version as char) || '.' || cast(client_release as char) || '.' || cast(client_level as char) || '.' ||cast(client_sublevel as char(2)) as tsm_version, client_os_level AS windows_OS_level, tcp_address, BACKDELETE, cast((current_timestamp-LASTACC_TIME)days as integer)AS DAYS_SINCE_ACCESS from nodes, status where node_name like '%SQL' and lastacc_time>current_timestamp - 60 day


Count Platform Types
--------------------------
select count(distinct tcp_name) as #_DB_Nodes from nodes where platform_name='TDP MSExchg' or node_name like '%EXCH' or platform_name like 'TDP Domino%'

select count(distinct tcp_name) as #_DB_Nodes from nodes where platform_name like 'TDP%' or platform_name like 'DB2%' or platform_name like 'TDPI HP11_64' and platform_name not in ('TDP R3 HP','

select platform_name, count(distinct tcp_name) from nodes group by platform_name

select platform_name, domain_name, count(platform_name) AS Client_Type from nodes where domain_name like '%APP%' group by domain_name, platform_name order by domain_name

select count(distinct tcp_name) from nodes where platform_name not like 'TDP%' and lastacc_time>current_timestamp - 60 day

select count(node_name) from nodes where platform_name not like 'TDP%'

select count(distinct tcp_name) from nodes where platform_name like 'TDP%' or platform_name like 'DP%' and lastacc_time>current_timestamp - 60 day

select count(tcp_name) from nodes where platform_name like 'TDP%' or platform_name like 'DP%'

select count(node_name) from nodes where platform_name not like 'TDP%' and lastacc_time>current_timestamp - 60 day



Show Node's Default MgmtClass Copygroup Settings
-------------------------------------------------
select distinct a.node_name, b.DOMAIN_NAME, b.SET_NAME, b.CLASS_NAME, b.COPYGROUP_NAME, b.VEREXISTS, b.VERDELETED, b.RETEXTRA, b.RETONLY, b.DESTINATION from nodes a, bu_copygroups b, mgmtclasses c where a.DOMAIN_NAME=b.DOMAIN_NAME and b.SET_NAME=c.SET_NAME and b.CLASS_NAME=c.CLASS_NAME and c.DEFAULTMC='Yes' and b.set_name<>'ACTIVE' group by a.node_name, b.domain_name, b.set_name, b.class_name, b.copygroup_name, b.VEREXISTS, b.VERDELETED, b.RETEXTRA, b.RETONLY


Filespace Counts
--------------------------

select count(*) from filespaces where node_name in (select node_name from nodes where platform_name not like 'TDP%' and platform_name not like 'DB2%')

select server_name, NODE_NAME, FILESPACE_NAME, FILESPACE_ID, CAPACITY, PCT_UTILIZED, cast((current_timestamp-backup_end)days as integer) AS DAYS_SINCE_BACKUP from status, filespaces where cast((current_timestamp-backup_end)days as integer)>3

select server_name, NODE_NAME, FILESPACE_NAME, FILESPACE_ID, CAPACITY, PCT_UTIL, cast((current_timestamp-backup_end)days as integer) AS DAYS_SINCE_BACKUP from status, filespaces where cast((current_timestamp-backup_end)days as integer)>3 and node_name not like '%MONTHLY' and node_name not like '%YEARLY' and node_name not like '%-Y' and node_name not like '%-M'

select NODE_NAME, FILESPACE_NAME, FILESPACE_ID, CAPACITY, PCT_UTIL, cast((current_timestamp-backup_end)days as integer) AS DAYS_SINCE_BACKUP from status, filespaces where cast((current_timestamp-backup_end)days as integer)>3 and node_name not like '%MONTHLY' and node_name not like '%YEARLY' and node_name not like '%-Y' and node_name not like '%-M' and node_name like '%$1%'


Filespace Capacity
--------------------------

select server_name, node_name, filespace_name, filespace_id AS ID, cast(sum(capacity*(pct_util/100))as decimal(9,2)) AS Total_MB_Disk_Used, backup_end AS Last_Backup from filespaces, status where cast((current_timestamp-backup_end)days as decimal)>30 group by server_name, node_name, filespace_name, filespace_id, backup_end

select sum(capacity*(pct_util/100)) AS Total_Disk_Used from filespaces where node_name in (select node_name from nodes where platform_name not like 'DB%' and platform_name not like 'TDP%') and node_name in (select node_name from nodes where node_name not like '%MONTHLY' and node_name not like '%M' and node_name not like '%SPEC' and node_name not like '%Y' and node_name not like '%YEARLY')

select sum(capacity) AS Total_Disk from filespaces where node_name in (select node_name from nodes where platform_name not like 'DB%' and platform_name not like 'TDP%') and node_name in (select node_name from nodes where node_name not like '%MONTHLY' and node_name not like '%M' and node_name not like '%SPEC' and node_name not like '%Y' and node_name not like '%YEARLY')

Filespace Age/Capacity/File Count
---------------------------------
select b.node_name, a.stgpool_name, a.filespace_name, sum(a.NUM_FILES) AS TOTAL_FILES, cast(sum(a.PHYSICAL_MB/1024)as decimal(9,2)) AS TOTAL_GB, b.backup_end from occupancy a, filespaces b, where a.node_name=b.node_name and a.filespace_name=b.filespace_name and cast((current_timestamp-backup_end)days as decimal)>90 group by b.node_name, a.stgpool_name, a.filespace_name

select server_name, NODE_NAME, FILESPACE_NAME, FILESPACE_ID AS ID, CAPACITY, PCT_UTIL, cast((BACKUP_END)as date) AS LAST_BACKUP from filespaces, status where cast((current_timestamp-backup_end)days as decimal)>90

select c.server_name, b.NODE_NAME, cast((lastacc_time) as date) AS LAST_ACCESS, a.FILESPACE_NAME, a.FILESPACE_ID AS ID, a.CAPACITY, a.PCT_UTIL, cast((a.BACKUP_END)as date) AS LAST_BACKUP from filespaces a, nodes b, status c where b.node_name in (select node_name from nodes where lastacc_time>current_timestamp - 60 day) and cast((current_timestamp-a.backup_end)days as decimal)>90 and b.node_name=a.node_name

select a.node_name, a.FILESPACE_NAME, b.capacity, cast(((b.pct_util/100)*(b.capacity))as decimal(10,2))AS MB_USED,  sum(a.PHYSICAL_MB) AS Total_MB_Stored, cast(((sum(a.PHYSICAL_MB)/((b.pct_util/100)*(b.capacity)))*100)AS decimal(6,2)) AS Difference from occupancy a, filespaces b where a.FILESPACE_NAME=b.FILESPACE_NAME and a.node_name=b.node_name and a.node_name='PRDSBD1A' group by a.node_name, a.FILESPACE_NAME, b.capacity, b.PCT_UTIL



Storage Pool Info
-----------------
select b.server_name, a.STGPOOL_NAME, sum(a.NUM_FILES) AS TOTAL_FILES from occupancy a, status b where a.STGPOOL_NAME in (select stgpool_name from stgpools where pooltype='PRIMARY') group by a.STGPOOL_NAME, b.server_name

SELECT A.NODE_NAME, B.STGPOOL_NAME, COUNT(A.FILE_NAME) AS NUM_FILES, SUM(A.FILE_SIZE) AS SIZE, A.VOLUME_NAME FROM CONTENTS A, OCCUPANCY B WHERE A.NODE_NAME=B.NODE_NAME and A.FILESPACE_ID=B.FILESPACE_ID AND A.NODE_NAME='SZATAP7' GROUP BY A.NODE_NAME, B.STGPOOL_NAME, A.VOLUME_NAME


Drive Listing
-------------------------
select a.DRIVE_NAME, b.DEVICE, a.ELEMENT, a.DRIVE_STATE, a.ONLINE AS DRIVE_ONLINE, b.ONLINE AS PATH_ONLINE, a.DRIVE_SERIAL from drives a, paths b where a.DRIVE_NAME=b.DESTINATION_NAME and a.LIBRARY_NAME='OD703-STLB03' and SOURCE_NAME='OD703-UAX000'


Mislabeled
-------------------------
select volume_name from libvolumes where volume_name not in (select volume_name from volumes) and status='Private' and last_use is NULL and owner='OD703-UAX000' and volume_name like 'L3%'


OLD DATA GATHERING
=-=-=-=-=-=-=-=-=-=

select server_name, sum(total_mb) AS TOTAL_MB from auditocc, status where node_name in (select node_name from nodes where cast((current_timestamp-lastacc_time)days as decimal)>30) group by server_name

/*--------------------------------------------------------*/
/*  Script Name: Q_STGPOOL_BKUP_PERF                      */
/*  Description: Display STGPOOL BACKUP PERFORMANCE STATS */
/*  Example:  run Q_STGPOOL_BKUP_PERF                     */
/*--------------------------------------------------------*/
 SELECT CAST(SUMMARY.ENTITY AS CHAR(36)) AS "PROCESS NAME", -
 CAST((START_TIME)AS DATE) AS "DATE", -
  CAST((START_TIME)AS TIME) AS "START TIME", -
  CAST((END_TIME) AS TIME) AS "END TIME", -
  SUBSTR(CHAR(START_TIME-END_TIME),3,9) AS "ELAPSED TIME", -
  BYTES, -
  (BYTES/CAST((END_TIME-START_TIME) SECONDS AS
 DECIMAL(18,13))*3600) AS "BYTES/HR" -
  FROM SUMMARY -
  WHERE ACTIVITY='STGPOOL BACKUP' -
  AND END_TIME^=START_TIME


/*--------------------------------------------------------*/
/*  Script Name: Q_STGPOOL_BKUP_PERF                      */
/*  Description: Display STGPOOL BACKUP PERFORMANCE STATS */
/*  Example:  run Q_STGPOOL_BKUP_PERF                     */
/*--------------------------------------------------------*/
SELECT CAST(SUMMARY.ENTITY AS CHAR(18)) AS "PROCESS NAME", -
  CAST((START_TIME)AS DATE) AS "DATE", -
  CAST((START_TIME)AS TIME) AS "START TIME", -
  CAST((END_TIME) AS TIME) AS "END TIME", -
  SUBSTR(CHAR(START_TIME-END_TIME),3,9) AS "ELAPSED TIME", -
  BYTES, -
  (BYTES/CAST((END_TIME-START_TIME) SECONDS AS
 DECIMAL(18,13))*3600) AS "BYTES/HR" -
  FROM SUMMARY -
  WHERE ACTIVITY='STGPOOL BACKUP' -
  AND END_TIME^=START_TIME

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Drive Check Script
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

/* TSM macro 'drives'                                              */
/*                                                                 */
/* To report tape drives and their states.                         */
/*                                                                 */
/* INVOCATION:  macro drives                                       */
/*                                                                 */
/* NOTES:  Available columns:  LIBRARY_NAME, DRIVE_NAME,           */
/*         DEVICE_TYPE, ONLINE, READ_FORMATS, WRITE_FORMATS,       */
/*         ELEMENT, ACS_DRIVE_ID, DRIVE_STATE, ALLOCATED_TO,       */
/*         LAST_UPDATE_BY, LAST_UPDATE, CLEAN_FREQ, DRIVE_SERIAL   */
/*                                                                 */
/* HISTORY:                                                        */
/*                                                                 */
/*     2005/01/12  Written by Richard Sims                         */
/*                                                                 */
Select Distinct Char(DRIVES.LIBRARY_NAME,12) As "Library Name", -
 Char(DRIVES.DRIVE_NAME,14) As "Drive Name", -
 Char(DRIVES.ONLINE,13) As "Drive Online?", CHAR(DRIVES.DRIVE_STATE,11) As
"Drive State", -
 Char((Select PATHS.DEVICE From Paths Where PATHS.DESTINATION_NAME =
DRIVES.DRIVE_NAME),9) As "Device", -
 Char((Select PATHS.ONLINE From Paths Where PATHS.DESTINATION_NAME =
DRIVES.DRIVE_NAME),12) As "Path Online?" -
 From DRIVES, PATH

all:def script q-fsno-backup
all:upd script q-fsno-backup 'select NODE_NAME, FILESPACE_NAME, FILESPACE_ID, -'
all:upd script q-fsno-backup 'CAPACITY, PCT_UTIL, cast((current_timestamp-backup_end)days as integer) -'
all:upd script q-fsno-backup 'AS DAYS_SINCE_BACKUP from status, filespaces where -'
all:upd script q-fsno-backup 'cast((current_timestamp-backup_end)days as integer)>3 and node_name -'
all:upd script q-fsno-backup "not like '%MONTHLY' and node_name not like '%YEARLY' -"
all:upd script q-fsno-backup "and node_name not like '%-Y' and node_name not like '%-M' -"
all:upd script q-fsno-backup "and node_name not like '%-7YR' and node_name like upper('%$1%') "


def script tabcol desc='run this script with the table name you want to list its columns'
upd script tabcol "select colname from columns where tabname=upper('$1')"

def script stgpool_backup_rpt
upd script stgpool_backup_rpt 'select server_name, activity, entity, CAST((START_TIME)AS DATE) AS "START DATE", -'
upd script stgpool_backup_rpt 'CAST((START_TIME)AS TIME) AS "START TIME", CAST((END_TIME)AS DATE) AS "END DATE", -'
upd script stgpool_backup_rpt 'CAST((END_TIME) AS TIME) AS "END TIME", SUBSTR(CHAR(START_TIME-END_TIME),3,9) -'
upd script stgpool_backup_rpt 'AS "ELAPSED TIME", EXAMINED, cast((bytes/1073741824) as decimal(18,2)) -'
upd script stgpool_backup_rpt 'AS "GB BACKED UP", SUCCESSFUL FROM SUMMARY, status -'
upd script stgpool_backup_rpt "where activity='STGPOOL BACKUP' and -"
upd script stgpool_backup_rpt 'cast((current_timestamp-start_time)days as decimal)<$1'

select -
schedule_name as "Schedule Name                   ", -
 cast(substring(cast(scheduled_start as character(26)) from 12 for 8) as char(8)) as "Sched", -
 node_name, -
 cast(status as char(10)) as "Status", -
  case -
    when result=0  then ' 0-Succ' -
    when result=4  then ' 4-SkFi' -
    when result=8  then ' 8-Warn' -
    when result=12 then '12-Errs' -
    else cast(result as char(7)) -
  end -
    as "Result" -
from events -
where current_timestamp-scheduled_start<interval '24' hours -
  and result<>0


def script zero_scratch desc="shows libraries with zero scratch tapes"
upd script zero_scratch "select distinct library_name, ' 0 ' AS SCRATCH from libvolumes where library_name -"
upd script zero_scratch 'not in (select distinct library_name from libvolumes where -'
upd script zero_scratch "status='Scratch' group by library_name)"


def script eventx desc="Events - Exceptions"
upd script eventx "/* ---------------------------------------------*/"
upd script eventx "/* Script Name: eventx                          */"
upd script eventx "/* ---------------------------------------------*/"
upd script eventx '  select -'
upd script eventx '  schedule_name as "Schedule Name                   ", -'
upd script eventx '   cast(substring(cast(scheduled_start as character(26)) from 12 for 8) as char(8)) as "Sched", - '
upd script eventx '   node_name, -'
upd script eventx '   cast(status as char(10)) as "Status", -'
upd script eventx '    case -'
upd script eventx "      when result=0  then ' 0-Succ' -"
upd script eventx "      when result=4  then ' 4-SkFi' -"
upd script eventx "      when result=8  then ' 8-Warn' -"
upd script eventx "      when result=12 then '12-Errs' -"
upd script eventx "      else cast(result as char(7)) -"
upd script eventx '    end -'
upd script eventx '      as "Result" -'
upd script eventx '  from events -'
upd script eventx "  where current_timestamp-scheduled_start<interval '24' hours -"
upd script eventx '    and result<>0'


TSM 6.1 Format
=-=-=-=-=-=-=-=-=-=

def script event-check desc="Events - Exceptions"
upd script event-check "/* ---------------------------------------------*/"
upd script event-check "/* Script Name: event-check                          */"
upd script event-check "/* ---------------------------------------------*/"
upd script event-check '  select -'
upd script event-check '  schedule_name, -'
upd script event-check '   cast(SUBSTR(CHAR(actual_start),12,8) as char(8)) AS START, - '
upd script event-check '   node_name, -'
upd script event-check '   cast(status as char(10)) as "STATUS", -'
upd script event-check '    case -'
upd script event-check "      when result=0  then ' 0-Succ' -"
upd script event-check "      when result=4  then ' 4-SkFi' -"
upd script event-check "      when result=8  then ' 8-Warn' -"
upd script event-check "      when result=12 then '12-Errs' -"
upd script event-check "      else cast(result as char(7)) -"
upd script event-check '    end -'
upd script event-check '      as "RESULT" -'
upd script event-check '  from events -'
upd script event-check '  where timestampdiff(8, char(current_timestamp-scheduled_start))<=24 -'
upd script event-check '    and result<>0 and node_name is not NULL'


def script stgpool_backup_rpt desc="stgpool backup report that requires you provide TSM server name and days to check"
upd script stgpool_backup_rpt "select '$1 ' as SERVER_NAME, entity, activity, -"
upd script stgpool_backup_rpt 'CAST((START_TIME)AS DATE) AS "START DATE", -'
upd script stgpool_backup_rpt 'CAST((START_TIME)AS TIME) AS "START TIME", CAST((END_TIME)AS DATE) AS "END DATE", - '
upd script stgpool_backup_rpt 'CAST((END_TIME) AS TIME) AS "END TIME", substr(cast((end_time-start_time) as char(22)),7,2) -'
upd script stgpool_backup_rpt "|| ' ' || substr(cast((end_time-start_time) as char(22)),9,2) || ':' || -"
upd script stgpool_backup_rpt "substr(cast((end_time-start_time) as char(22)),11,2) || ':' || substr(cast((end_time-start_time) -"
upd script stgpool_backup_rpt 'as char(22)),13,2) AS ELAPSED_TIME, EXAMINED, cast((bytes/1073741824) as decimal(18,2)) AS -'
upd script stgpool_backup_rpt ' "GB BACKED UP", SUCCESSFUL FROM SUMMARY where -'
upd script stgpool_backup_rpt "activity='STGPOOL BACKUP' and start_time>(CURRENT TIMESTAMP - $2 DAYS)"

del script old-bkup-fs
def script old-bkup-fs desc="Reports on filespaces over 90 days old with their data amounts and number of files"
upd script old-bkup-fs 'select c.server_name, a.NODE_NAME, a.FILESPACE_NAME, sum(a.NUM_FILES) AS TOTAL_FILES, -'
upd script old-bkup-fs 'sum(a.PHYSICAL_MB) AS PHYSICAL_MB, cast((b.BACKUP_END)as date) AS LAST_BACKUP -'
upd script old-bkup-fs 'from occupancy a, filespaces b, status c where a.NODE_NAME=b.NODE_NAME and -'
upd script old-bkup-fs "a.FILESPACE_NAME=b.FILESPACE_NAME and a.type='Bkup' and -"
upd script old-bkup-fs 'cast((current_timestamp-b.backup_end)days as decimal)>90 group -'
upd script old-bkup-fs 'by a.NODE_NAME, a.FILESPACE_NAME, b.BACKUP_END, c.server_name'

select a.node_name, a.domain_name, b.filespace_name, b.stgpool_name, sum(b.num_files) AS NUM_FILES, SUM(b.PHYSICAL_MB) AS PHYSICAL_MB from nodes a, occupancy b, mgmtclasses c where a.node_name=b.node_name and a.domain_name=c.domain_name and a.lastacc_time>'2010-10-20 00:00:00' and c.DEFAULTMC='Yes' and c.SET_NAME<>'ACTIVE' group by a.node_name, a.domain_name, b.filespace_name, b.stgpool_name

select '$1 ' AS server_name, c.node_name, a.DOMAIN_NAME, b.SET_NAME, b.CLASS_NAME, a.COPYGROUP_NAME, a.DESTINATION from bu_copygroups a, mgmtclasses b, node_name c where c.domain_name=b.domain_name and a.domain_name=b.domain_name and a.set_name=b.set_name and a.CLASS_NAME=b.class_name and b.DEFAULTMC='Yes' and a.set_name<>'ACTIVE'


select node_name, domain_name, stgpool_name, sum(num_files) AS TOTAL_FILES, sum(PHYSICAL_MB) AS TOTAL_MB from occupancy group by node_name, stgpool_name

def script q_default_mc desc='Shows the node_name, domain, policy set, mc, copygroup, and destination'
upd script q_default_mc "select '$1 ' AS server_name, c.node_name, a.DOMAIN_NAME, b.SET_NAME, b.CLASS_NAME, -"
upd script q_default_mc 'a.COPYGROUP_NAME, a.DESTINATION from bu_copygroups a, mgmtclasses b, nodes c where -'
upd script q_default_mc 'c.domain_name=b.domain_name and a.domain_name=b.domain_name and a.set_name=b.set_name -'
upd script q_default_mc "and a.CLASS_NAME=b.class_name and b.DEFAULTMC='Yes' and a.set_name<>'ACTIVE' "



select server_name, activity, entity, CAST((START_TIME)AS DATE) AS "START DATE", CAST((START_TIME)AS TIME) AS "START TIME", CAST((END_TIME)AS DATE) AS "END DATE", CAST((END_TIME) AS TIME) AS "END TIME", SUBSTR(CHAR(START_TIME-END_TIME),3,9) AS "ELAPSED TIME", EXAMINED, cast((bytes/1073741824) as decimal(18,2)) AS "GB BACKED UP", SUCCESSFUL FROM SUMMARY, status where activity='STGPOOL BACKUP' and cast((current_timestamp-start_time)days as decimal)<$1




select -'
schedule_name, -'
 cast(SUBSTR(CHAR(actual_start),12,8) as char(8)) AS START, - '
 node_name, -'                                                   
 cast(status as char(10)) as "STATUS", -'                        
  case -'                                                        
    when result=0  then ' 0-Succ' -"                             
    when result=4  then ' 4-SkFi' -"
    when result=8  then ' 8-Warn' -"                             
    when result=12 then '12-Errs' -"                             
    else cast(result as char(7)) -"                              
  end -'
    as "RESULT" -'                                               
from events -'
 where  start_time>(CURRENT TIMESTAMP - 10 DAYS)


DEF CLO windows
DEF CLIENTO windows INCLEXCL "include.systemstate all systemstate" seq=0
DEF CLIENTO windows INCLEXCL "include.systemobject systemstate"    seq=1

select server_name, node_name, tcp_name as HOST_NAME, DOMAIN_NAME, cast(client_version as char) || '.' || cast(client_release as char) || '.' || cast(client_level as char) || '.' ||cast(client_sublevel as char(2)) as tsm_version, client_os_level AS windows_OS_level, tcp_address, BACKDELETE, cast((current_timestamp-LASTACC_TIME)days as integer)AS DAYS_SINCE_ACCESS from nodes, status where node_name like '%SQL' and lastacc_time>current_timestamp - 60 day

select 'del filespace ' || node_name || ' ' || cast(FILESPACE_ID as char(3)) || ' type=backup nametype=fsid wait=yes' AS COMMAND from filespaces where cast((current_timestamp-backup_end)days as decimal)>90


select count(*) from nodes where node_name in (select node_name from events where (scheduled_start >= '2011-05-01') and (scheduled_start >=current_timestamp - 1 days) and (status='Failed' or status='Missed' or status='Severed') and node_name is not NULL) and platform_name='WinNT'

select node_name, day(current_timestamp-lastacc_time) AS DAYS_SINCE_ACCESS from nodes -
where day(current_timestamp-lastacc_time)>100