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'

TSM DRM States and Cycling process

DRM States:

There are six important states in DRM concept.
mountable

Tapes are inside tape library and therefore mountable into tape drives
Courier

Tapes are outside library now and can be handed over to courier service guys so that they can deliver tape cartridges to offsite.
Vault
Tapes are inside vault now at DR site

4.Vaultretrieve
Tapes are expired as they do not contain any valid data now. These tape cartridges can now be retrieved from vault.

5.Courierretrieve
Tapes with expired data have been retrieved from vault by courier guys but still not delivered at main site.
6.Onsiteretrieve
Tapes with expired data has reached main site and can be checked in , into tape library as scratch cartridges.



Daily ( or weekly) Cycle:
Depending upon your business needs, you may decide to remove offsite tape cartridges on daily or weekly basis.
There are two ways to do that. Simpler way is to use ISC console, while more typical way is to use dsmadmc command line tool to identify and remove offsite cartridges.

Let start with ISC first.
Login to ISC as iscadmin and identify DRM cartridges with mountable state. These are the cartridges, which are eligible for movement
You have to remove these cartridges from library by changing their state to next state, which is courier state.
After tapes reach DR site vault, you can change these tape cartridges state to Vault.

Once data on the tape cartridges in vault expired, the state of tape cartridges will automatically change to Vaultretrieve. You have to query from ISC or command line to find out which tape cartridges are in state of Vaultretrieve. Once you found out which tape cartridges are to be brought back, you have to ask your courier service to bring these tape cartridges back.
Once you know that your expired cartridges have been picked up by your courier service, you have to change state of these tape cartridges to Courierretrieve through ISC or command line.

Now when you get these expired cartridges back at main site, you just have to change the state of these cartridges to Onsiteretreive. As soon as you change the state to Onsiteretreive, history of these cartridges will be deleted from TSM and then you can check in these cartridges, one by one into tape library as scratch cartridges.