Excellent SQL commands for TSM Administrators

* Which client nodes currently locked from server access?

select node_name from nodes where locked='YES'



*How to use string operators in select statement - TSM.

select * from actlog where message like 'ANR2565I%'



* Which administrative clients currently locked from server access?

select admin_name from admins where locked='YES'



* Which client nodes that has not specified the correct password lately?

select node_name from nodes where invalid_pw_count <>0



* Which administrative clients that has not specified the correct password lately?

select admin_name from admins where invalid_pw_count <>0



* Which nodes in the WINDOWS policy domain are not associated with the daily backup schedule STANDARD?

select node_name from nodes where domain_name='WINDOWS'and node_name-

not in (select node_name from associations -

where domain_name='WINDOWS'and schedule_name='STANDARD')



* Which administrators have policy authority?

select admin_name from admins -

where upper(system_priv)<>'NO'or upper(policy_priv)<>'NO'



* What messages of type E (ERROR) or W (WARNING) have been issued in the time period for which activity log records have been maintained?

select date_time,msgno,message from actlog where severity='E'or severity='W'



* Which administrative schedules have been defined or altered by administrator ADMIN ?

select schedule_name from admin_schedules where chg_admin='ADMIN'



* What are the relative administrative schedule priorities?

select schedule_name,priority from admin_schedules order by priority



* Which management classes have an archive copy group with a retention period greater than 365 days?

select domain_name,set_name,class_name -

from ar_copygroups where retver='NOLIMIT'or cast(retver as integer)>365



* Which management classes specify more than 5 backup versions?

select domain_name,set_name,class_name -

from bu_copygroups where verexists ='NOLIMIT'or cast(verexists as integer)>5



* Which client nodes are using the client option set named SECURE ?

select node_name from nodes where option_set='SECURE'



* How many client nodes are in each policy domain?

select domain_name,num_nodes from domains



* How many files have been archived from each node?

select node_name,count(*)from archives group by node_name



* Which clients are using space management?

select node_name from auditocc where spacemg_mb <>0



* If the reclamation threshold were to be changed to 50 percent for storage pool TAPE , how many volumes would be reclaimed?

select count(*)from volumes -

where stgpool_name='TAPE'and upper(status)='FULL'and pct_utilized <50



* If the DAILY management class in the STANDARD policy domain is changed or deleted, how many backup files would be affected for each node?

select node_name,count(*)as "Files"-

from backups where class_name='DAILY'and -

node_name in (select node_name from nodes where domain_name='STANDARD')-

group by node_name



* For all active client sessions, determine how long have they been connected and their effective throughput in bytes per second.

select session_id as "Session",-

client_name as "Client",state as "State",-

current_timestamp-start_time as "Elapsed Time",(-

cast(bytes_sent as decimal(18,0))/cast((current_timestamp-start_time)-

seconds as decimal(18,0)))as "Bytes sent/second",-

(cast(bytes_received as decimal(18,0))/cast((current_timestamp-start_time)-

seconds as decimal(18,0)))as "Bytes received/second"-

from sessions



* How long have the current background processes been running and what is their effective throughput in time and files per second?

select process_num as "Number",process,-

current_timestamp-start_time as "Elapsed Time",-

(cast(files_processed as decimal(18,0))/cast((current_timestamp-start_time)-

seconds as decimal(18,0)))as "Files/second",-

(cast(bytes_processed as decimal(18,0))/cast((current_timestamp-start_time)-

seconds as decimal(18,0)))as "Bytes/second"-

from processes



* How many client nodes are there for each platform type?

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



* How many filespaces does each client node have, listed in default ascending order?

select node_name,count(*)as "number of filespaces"-

from filespaces group by node_name order by 2



* How to display all columns for all tables from syscat.columns without headers

select char(concat(concat(t.tabname,'.'),c.colname),35)as "TC",char -

(coalesce(nullif(substr(c.typename,1,posstr(c.type name,'(')-1)-

,''),c.typename),10),char(c.length,5),c.remarks -

from syscat.columns as c,syscat.tables AS t -

where c.tabname =t.tabname order by tc



* How to examine which volumes are UNAVAILABLE

select VOLUME_NAME,ACCESS from volumes where access ='UNAVAILABLE'



* How to examine which volumes have more than three write errors

select VOLUME_NAME,WRITE_ERRORS from volumes where write_errors >3



* How to examine which volumes have read errors

select VOLUME_NAME,READ_ERRORS from volumes where read_errors >0



* How to examine which volumes have an error state different from No

select VOLUME_NAME,ERROR_STATE from volumes where error_state !='No'



* How to examine which volumes have access different from READWRITE

select VOLUME_NAME,ACCESS from volumes where access !='READWRITE'



* How to examine which volumes have less than ten percent utilization in device class beginning with the letters SUN

select volume_name,pct_utilized,status,access from volumes-

where pct_utilized <10 and devclass_name like 'SUN%'



* How to examine which volumes do not have an access beginning with the letters READ

select volume_name,pct_utilized,pct_reclaim,stgpool_name,-

status,access from volumes where access not like 'READ%'



* How to list the content of all volumes and display the filesize in MB, ordered by client node name, volume name and size

select node_name,-

volume_name,-

decimal(file_size/1024/1024,12,2)mb,-

concat(substr(file_name,1,posstr(file_name,'')-1),-

substr(file_name,posstr(file_name,'')+1))-

from contents -

order by node_name,volume_name,mb



* How to find all clients which store their backup data in the DISKPOOL storage pool

select node_name as "CLIENT NODENAME",-

bu_copygroups.destination as "STGPOOL DESTINATION",-

nodes.domain_name as "CLIENT DOMAIN",-

bu_copygroups.domain_name as "COPYGROUP DOMAIN"-

from nodes,bu_copygroups where -

nodes.domain_name =bu_copygroups.domain_name and -

bu_copygroups.destination=upper('diskpool')and -

bu_copygroups.set_name=upper('active')-

order by nodes.domain_name



* How to find all volumes which have data for a specified client, and their status

select volumeusage.volume_name,-

volumes.access,-

volumes.error_state,-

volumeusage.stgpool_name -

from volumeusage,volumes -

where volumeusage.node_name='ONE-ON-ONE'and-

volumeusage.volume_name=volumes.volume_name -

order by volume_name



* How to find all storage pools where a client (FRED) has stored data

select distinct(STGPOOL_NAME)from OCCUPANCY where node_name='FRED'