I'm no SQL expert, but I've collected these:
* 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.typename,'(')-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'
I hope this helps,
Regards,
DCat the Bureaucrat
Your Personal Public Servant
