TSM 6.x SQL

The following are  alist of SQL querries I use frequently since moving to TSM v6.x

================
DRIVE CHECK
================
DEFINE SCRIPT  DRV-CHECK DESC="Show tape drive status better than Q DRIVE"
UPDATE SCRIPT  DRV-CHECK "select cast((library_name)as char(8)) as LIBRARY_NAME, -"
UPDATE SCRIPT  DRV-CHECK "cast((DRIVE_NAME)as char(6)) as DRIVE_NAME, -"
UPDATE SCRIPT  DRV-CHECK "cast((drive_state)as char(10)) as DRIVE_STATE, -"
UPDATE SCRIPT  DRV-CHECK "cast((volume_name)as char(8)) as VOL_NAME, cast((online)as char(10)) as ONLINE, -"
UPDATE SCRIPT  DRV-CHECK "cast((ALLOCATED_TO)as char(15)) as DRV_OWNER from drives order by library_name, drive_name"

====================
PATH CHECK
====================
DEFINE SCRIPT  PATH-CHECK DESC="Show paths that are offline"
UPDATE SCRIPT  PATH-CHECK "select cast((source_name)as char(8)) as SERVER, cast((LIBRARY_NAME)as -"
UPDATE SCRIPT  PATH-CHECK "char(8)) as LIB_NAME, cast((destination_name)as char(10)) as DRIVE, -"
UPDATE SCRIPT  PATH-CHECK "cast((device)as char(16)) as DEVICE_NAME, cast((online)as char(10)) -"
UPDATE SCRIPT  PATH-CHECK "as ONLINE from paths where online<>'YES' "

===================
QPROC
===================
DEFINE SCRIPT  QPROC DESC="A more easier way to track the progress of processes"
UPDATE SCRIPT  QPROC "select process_num, process, cast((bytes_processed/1073741824) as decimal(10,2)) -"
UPDATE SCRIPT  QPROC "AS ""GB_MIGRATED"" , FILES_PROCESSED, cast((start_time)as date) -"
UPDATE SCRIPT  QPROC "as START_DATE,cast((start_time)as time) as START_TIME from processes"


==================
VOLCHECK
==================

ams:select a.stgpool_name, a.collocate, a.MAXSCRATCH, a.NUMSCRATCHUSED, count(b.volume_name) as REC_COUNT from stgpools a, volumes b where a.stgpool_name=b.stgpool_name and b.pct_utilized<75 and a.stgpool_name not like '%POOL' group by a.stgpool_name, a.collocate, a.MAXSCRATCH, a.NUMSCRATCHUSED


===============================
Show Volumes With <75% Utilized
===============================
select cast((volume_name)as char(8))as VOLNAME, cast((stgpool_name)as char(15))as STGPOOL, pct_utilized from volumes where pct_utilized<10 and stgpool_name not like '%POOL' order by stgpool_name, pct_utilized


select 'del vol', cast((volume_name)as char(8))as VOLNAME, cast((stgpool_name)as char(15))as STGPOOL, pct_utilized from volumes where pct_utilized=0 and stgpool_name not like '%POOL' and status='PENDING' order by stgpool_name, pct_utilized

select 'ustsm01:del vol', cast((volume_name)as char(8))as VOLNAME, cast((stgpool_name)as char(15))as STGPOOL, pct_utilized from volumes where pct_utilized=0 and stgpool_name not like '%POOL' and status='PENDING' order by stgpool_name, pct_utilized

select 'move data', cast((volume_name)as char(8))as VOLNAME, 'wait=yes', cast((stgpool_name)as char(15))as STGPOOL, pct_utilized from volumes where pct_utilized<5 and stgpool_name not like '%POOL' order by pct_utilized, stgpool_name

==============================
See nodes NOT in a collo-group
==============================
select cast((node_name)as char(15))as NODE, BACKUP_MB, ARCHIVE_MB, TOTAL_MB from auditocc where node_name in (select node_name from nodes where domain_name='WINDOWS' and COLLOCGROUP_NAME is NULL) order by node_name


=====================
Event Table Querries
=====================

Total Schedules
----------------

select count(*) as "TOTAL_SCHEDULES" from events where (scheduled_start>='2011-05-01')and (scheduled_start>=current_timestamp - 1 day) and node_name is not NULL

select count(*) as "TOTAL_SCHEDULES" from events where (scheduled_start>='2011-05-01')and scheduled_start>=(current_timestamp - 2 days) and scheduled_start<=(current_timestamp - 1 day) and node_name is not NULL


Total Missed Schedules
----------------------
select count(*) as "TOTAL_SCHED_FAIL" from events where (scheduled_start>='2011-05-01')and (scheduled_start>=current_timestamp - 1 day) and node_name is not NULL and status in ('Failed','Missed','Severed')


Total Failures By Platform Type
--------------------------------
select a.platform_name, count(b.node_name) as COUNT from nodes a, events b where (b.scheduled_start >= '2011-05-01')and (b.scheduled_start >=current_timestamp - 30 days) -
and (b.status='Failed' or b.status='Missed' or b.status='Severed') and b.node_name is not NULL and a.node_name=b.node_name group by a.platform_name


def script event_exceptions_by_platform desc='Shows the number of missed/failed/severed by platform for x days'
upd script event_exceptions_by_platform 'select a.platform_name, count(b.node_name) as COUNT from nodes a, events -'
upd script event_exceptions_by_platform "b where (b.scheduled_start >= '2011-05-01')and (b.scheduled_start >=current_timestamp - $1 days) -"
upd script event_exceptions_by_platform "and (b.status='Failed' or b.status='Missed' or b.status='Severed') and -"
upd script event_exceptions_by_platform 'b.node_name is not NULL and a.node_name=b.node_name group by a.platform_name'

Total Failures by Result
------------------------
select a.platform_name, b.result, count(b.node_name) as COUNT from nodes a, events b where (b.scheduled_start >= '2011-05-01')and (b.scheduled_start >=current_timestamp - 30 days) -
and (b.status='Failed' or b.status='Missed' or b.status='Severed') and b.node_name is not NULL and a.node_name=b.node_name group by a.platform_name, b.result

select b.result, count(b.node_name) as COUNT from nodes a, events b where (b.scheduled_start >= '2011-05-01')and (b.scheduled_start >=current_timestamp - 30 days) -
and (b.status='Failed' or b.status='Missed' or b.status='Severed') and b.node_name is not NULL and a.node_name=b.node_name group by b.result

Q Events w/Platform (If Platform is NULL/Empty then the node has not connected)
-------------------------------------------------------------------------------
select cast((b.node_name)as char(15))as NODE, a.platform_name, cast((b.schedule_name)as char(15)) AS Sched_Name, cast((b.SCHEDULED_START)as date) as SCHED_START, -
cast((b.status) as char(8)) AS STATUS from nodes a, events b where (b.scheduled_start >= '2011-05-01')and (b.scheduled_start >=current_timestamp - 7 days) -
and (b.status='Failed' or b.status='Missed' or b.status='Severed') and b.node_name is not NULL and a.node_name=b.node_name


Repeat Offenders
----------------
select cast((server_name) as char(10)) AS TSM_SERVER, count(*) as "3_DAY_COUNT", -
cast((schedule_name)as char(15)) AS Sched_Name, cast((node_name) as char(20)) AS NODE_NAME, -
cast((status) as char(8)) AS STATUS from EVENTS, status where -
(scheduled_start >= '2011-05-01')and (scheduled_start >=current_timestamp - 3 days) -
and (status='Failed' or status='Missed' or status='Severed') and node_name is not NULL -
group by server_name,node_name,schedule_name, -
status having count(*)>=3 order by status, "3_DAY_COUNT" desc


=============
Querry Nodes
=============

Querry Nodes by Platform
-------------------------
select cast((node_name)as char(18))as NODE, cast((tcp_name) as char(18))as TCP_NAME, cast((platform_name)as char(15)) as PLATFORM, cast((lastacc_time)as date) from nodes where lastacc_time>(current_timestamp - 1 day) and platform_name='AIX'


==========================
DEFINE/DELETE ASSOCIATIONS
==========================
select 'ustsm03:def assoc', domain_name, schedule_name, node_name from associations where node_name in



=================
FILESPACE DELETE
=================

select 'del filesp', cast((node_name)as char(30))as NODE, FILESPACE_ID, 'namet=FSID', 'wait=yes' from filespaces where node_name like '%KEEP_1_MONTH%' and node_name in (select node_name from nodes where lastacc_time<current_timestamp - 30 days)


===============
Processes Table
===============

SELECT -
  process_num as PROC_NUM, -
  process, -
  TRANSLATE('ab cd:ef:gh', DIGITS(current_timestamp-start_time), '______abcdefghi_____',' ') as ELAPSED_TIME, -
  CAST(float(files_processed) / timestampdiff(2,CHAR(TIMESTAMP(current_timestamp+1 SECOND) - (start_time))) AS DEC(20,2)) AS "Files/sec", -
  CAST(float(bytes_processed)/1024 / timestampdiff(2,CHAR(TIMESTAMP(current_timestamp+1 SECOND) - (start_time))) AS DEC(20,2)) AS "KB/sec", -
  CAST(bytes_processed/1024/1024/1024 as INTEGER) AS gb -
FROM -
  processes

=====================
Summary Table Selects
=====================

SELECT -
  entity, -
  activity, -
  CAST((start_time)as date) AS START_DATE, CAST((start_time)as time) AS START_TIME, -
  TRANSLATE('ab cd:ef:gh', DIGITS(current_timestamp-start_time), '______abcdefghi_____',' ') as ELAPSED_TIME, -
  CAST(float(AFFECTED) / timestampdiff(2,CHAR(TIMESTAMP(current_timestamp+1 SECOND) - (start_time))) AS DEC(20,2)) AS "Files/sec", -
  CAST(float(bytes)/1024 / timestampdiff(2,CHAR(TIMESTAMP(current_timestamp+1 SECOND) - (start_time))) AS DEC(20,2)) AS "KB/sec", -
  CAST(bytes/1024/1024/1024 as INTEGER) AS gb -
FROM -
  summary -
WHERE -
  entity like 'SAP_AB8%' -
  and -
  end_time>=(current_timestamp - 1 day) - 
  and -
  activity='ARCHIVE'
  
  
  SELECT -
  entity, -
  activity, -
  CAST((start_time)as date) AS START_DATE, CAST((start_time)as time) AS START_TIME, -
  CAST(float(AFFECTED) / timestampdiff(2,CHAR(TIMESTAMP(current_timestamp+1 SECOND) - (start_time))) AS DEC(20,2)) AS "Files/sec", -
  CAST(float(bytes)/1024 / timestampdiff(2,CHAR(TIMESTAMP(current_timestamp+1 SECOND) - (start_time))) AS DEC(20,2)) AS "KB/sec", -
  CAST(bytes/1024/1024/1024 as INTEGER) AS gb -
FROM -
  summary -
WHERE -
  entity like 'SAP_AB8%' -
  and -
  end_time>=(current_timestamp - 1 day) -
  and -
  activity='ARCHIVE'

  select entity, activity, start_time, end_time, bytes from summary where entity like 'SAP_AB8%' and activity='ARCHIVE'
  
=-=-=-=-=-=-=-=-=-=-=-=
Script for Vol Delete
=-=-=-=-=-=-=-=-=-=-=-=

def script q-pending desc="Shows tapes that are pending as scratch due to expiration"
upd script q-pending "select cast((a.server_name)as char(7)) || ':del vol', -"
upd script q-pending "cast((b.volume_name)as char(8)) as VOLNAME from status a, -"
upd script q-pending "volumes b where b.pct_utilized=0 and -"
upd script q-pending "b.stgpool_name not like '%POOL' and b.status='PENDING' order by volume_name"

1 comment: