TSM SQL

Some sql for TSM to help you daily TSM admin jobs

Finding the tapes needed to restore a node

select distinct node_name,volume_name,stgpool_name from volumeusage where node_name='xxxxx'

Show all the tapes >20% full

select 'MARKER', volume_name,pct_utilized from volumes where pct_utilized >20 and (volume_name like '5%' or volume_name like '6%')

To check yesterday's backup

select Entity,Successful,Bytes,Examined,Affected,Failed from summary where activity='BACKUP' and cast((current_timestamp-start_time)hours as decimal(8,0)) < 24 order by Entity

To check a node the filespace name and volume

Run tt nodename

To check last 24 hours severity "E" message

Run seve

How many client nodes are registered by domain

select domain_name,num_nodes from domains

How many client nodes are registered by platform?

select platform_name,count(*)as "Number of Nodes" from nodes group by platform_name

Query all tapes for a node

select distinct node_name,volume_name,stgpool_name from volumeusage where node_name='PWAT1UXSD01'

Query all tapes for a file

select volume_name,node_name,filespace_name,file_name from contents where node_name='nwsrv1' and filespace_name='NWSRV1\DATA:' and file_name='\users\jelliott\ofvgoarc\*'

How many tapes are used by each node

select count(DISTINCT volume_name) as volumes, node_name, stgpool_name from volumeusage group by node_name, stgpool_name order by volumes desc

How much data is stored for each filespace? - run tt nodename

select node_name,filespace_name, physical_mb,stgpool_name FROM occupancy where node_name='ECO36934'  and type='Bkup'

How many volumes does a storage group use?

select stgpool_name,count(*) as count from volumes group by stgpool_name

How many volumes are going offsite? (have to run before tape eject)

select volume_name,stgpool_name,access from volumes where (stgpool_name='offsite_pool_name') and (access='offsite')

Display the number of nodes on each tape

select volume_name, stgpool_name, count(distinct node_name) as nodes from volumeusage group by volume_name, stgpool_name order by 3 desc

Offsite tapes needed to restore a node

select distinct volume_name from volumeusage where node_name='xxxxxx'

Show last 24 hours warning and error log

SELECT * From ACTLOG Where SEVERITY In ('W','E') And (DAYS(CURRENT_TIMESTAMP)- DAYS(DATE_TIME)) <1

What tape is used today

select volume_name,last_write_date from volumes order by last_write_date desc

Display tape location by home_element

select home_element, volume_name from libvolumes order by home_element

Database backup rate and time for last 30 days

select activity, ((bytes/1048576)/cast ((end_time-start_time) seconds as decimal(18,13))*3600) "MB/Hr", start_time, end_time from summary where activity='FULL_DBBACKUP' and days(end_time) - days(start_time)=0

q assoc * *   -policy domain name, schedule name, associate nodes

disable session (client/server/admin/all)

Customize a schedule to run Mon, Wed, Fri

DEF SCH domain_name schedule_name T=C ACT=I STARTT=22:00:00 DUR=2 DAY=Monday,Wednesday,Friday

All tapes for a node

select distinct node_name,volume_name,stgpool_name from volumeusage where node_name='xxxxx'

What tapes were used today

select volume_name,last_write_date from volumes order by last_write_date

Which volume contain the file

select volume_name,node_name,filespace_name,file_name from contents where node_name='nodename' and filespace_name='filespace' and file_name='filename'

Last 24-hours backup

SELECT activity , CAST(sum(bytes/1024/1024/1024) AS decimal(8,2)) AS "Total GB" FROM summary WHERE activity='BACKUP' AND start_time>=current_timestamp - 24 hours GROUP BY activity

Data backup by node

SELECT entity as "Node", CAST(sum(bytes/1024/1024) AS decimal(8,2)) AS "Total MB" FROM summary WHERE activity='BACKUP' AND start_time>=current_timestamp - 24 hours GROUP BY entity

select * from occupancy

select sum(physical_mb)/1024 from occupancy where stgpool_name <> '3583COPY'

select sum(physical_mb)/1024 from occupancy where stgpool_name <> '3584COPYN'

select sum(num_files) from occupancy

select sum(est_capacity_mb)/1024 from volumes where devclass_name <> 'DISK'

select node_name,sum(physical_mb)/1024 from occupancy group by node_name > /tmp/nodes19.03.2006

select node_name,sum(physical_mb)/1024 from occupancy group by node_name order by 2 desc

select node_name,sum(num_files) from occupancy group by node_name order by 2 desc

select * from libvolumes where status='Private' and last_use!='Data'

select NODENAME as "CLIENT",DATE_TIME,MESSAGE as "ERROR MESSAGES in the LAST 24H" from actlog  where (current_timestamp-date_time)hours between '1' AND '24' and (SEVERITY='E' or SEVERITY='W' ) and MESSAGE not like 'ANR2034E%' and MESSAGE not like 'ANR8214E%' and MESSAGE not like 'ANR8213W%' and MESSAGE not like 'ANR0482W%' and MESSAGE not like 'ANR0480W%' and MESSAGE not like 'ANR2335W%' and MESSAGE not like 'ANE4987E%' and MESSAGE not like 'ANE4005E%'

select ENTITY as "CLIENT_NAME",sum(BYTES)/1048576 as "MB SENT" from summary where ENTITY in (select node_name from nodes)  and START_TIME >='08/01/2010 07:00:00.000000' and START_TIME<='08/02/2010 23:00:00.000000' group by ENTITY

select node_name,sum(physical_mb)/1024 from occupancy where stgpool_name <> '3584COPYN' group by node_name order by 2 desc

select volume_name, read_errors,write_errors from volumes where read_errors>0 or write_errors>0

SELECT CAST((100 - (CAST(MAX_REDUCTION_MB AS FLOAT) * 256 ) /(CAST(USABLE_PAGES AS FLOAT) - CAST(USED_PAGES AS FLOAT) ) * 100) AS DECIMAL(4,2)) AS PERCENT_FRAG FROM DB  

select count(*) from contents where volume_name='<vol_name>'

select volume_name from volumes where stgpool_name='3583COPY'

select tabname from tables

select colname from columns

select colname from columns where tabname='<tabname>' and colname='<colname>'

find $FLOPPY_DIR -name "WRITE.TOK" | awk '/0624/ {print $0}' | grep -v save

select node_name from nodes where DOMAIN_NAME='PODO_SQL'

select node_name,sum(physical_mb)/1024 from occupancy where stgpool_name <> '3583COPY' group by node_name order by 2 desc > /tmp/nodes_occ_21feb07

select node_name as '"CLIENT"',status as '"STATUS"',DOMAIN_NAME,SCHEDULE_NAME from events where node_name in (select node_name from nodes) and SCHEDULED_START >='$YESTRDAY $START'

select node_name,sum((capacity+1)*(PCT_UTIL+1)/100) from filespaces group by node_name

 select ENTITY as "CLIENT_NAME" ,sum(BYTES)/1048576 as "MB_SENT" from summary where ENTITY in (select node_name from nodes) and START_TIME >= '2006-01-24' group by ENTITY

select volume_name,stgpool_name,est_capacity_mb,pct_utilized,status,access,access,location,write_error ,read_errors,times_mounted,devclass_name from volumes

select volume_name,library_name from libvolumes order by volume_name

select count(*) from sessions where session_type='Node'

select volume_name,library_name from libvolumes order by volume_name

select volume_name from volumes where pct_reclaim >70

select volume_name,status,last_use from libvolumes where status='Private'

select node_name,file_name,file_size as "CLIENT" from contents where node_name in ( select node_name from nodes where domain_name='PODO_NT') and file_size \> 10000000000 ( in bytes )

select cast(float(sum(bytes))/1024/1024/1024 as decimal(14,2)) as TGBU from summary where (end_time between '2010-09-01' and '2010-09-15') and activity='BACKUP'

client TSM level

select node_name,cast(client_version as char)||'.'||cast(client_release as char)||'.'||cast(client_level as char)||'.'||cast(client_sublevel as char(2)) as "Client_Version" from nodes

select node_name,cast(client_version as char)||'.'||cast(client_release as char)||'.'||cast(client_level as char)||'.'||cast(client_sublevel as char(2)) as "Client_Version" from nodes where node_name='ECO49932'

How many tapes used by TIER-5_TPE at 2009(read date)

select volume_name,stgpool_name,EST_CAPACITY_MB,PCT_UTILIZED,LAST_READ_DATE from volumes where stgpool_name='TIER-5_TPE' and (year(current_timestamp) - year(LAST_READ_DATE))=1 order by LAST_READ_DATE

Some DB2 command

get db2 configuration

db2 get db cfg for tsmdb1

check the history database backup

db2 list history backup all for db tsmdb1

check the history of reorg

db2 list history reorg all for db tsmdb1

check the history of archive log

db2 list history archive log all for db tsmdb1

db2 list applications

db2 list active databases

db2 list tables for all

db2 list tables for schema tsmdb1    (tsm tables)

db2 list utilities show detail   (can monitor online database backup, if a process slow down server, can elect to throttle the utility)

db2 GET ALERT CFG FOR DATABASES(DBM)

db2 GET AUTHORIZATIONS