Helpfull TSM Select Statements

[athos154]

Hopefully others will find these select statements as helpfull as I have over the years:
(Some of these can run very long, and can be resource intensive. Use at your own risk.)
(The description follows the select statement.)

select distinct node_name,volume_name,stgpool_name from volumeusage where node_name='xxxxx'
* To find what tapes are used by a node (needed for restore)

select node_name, sum(logical_mb) as Data_In_MB, sum(num_files) as Num_of_files from occupancy group by node_name order by node_name
* To identify # and space of data files on all node

select node_name,sum(logical_mb) as Logical_MB,sum(physical_mb) as Physical_MB, sum(num_files) as Num_of_files from occupancy where node_name='xxxxxxxxxxxxxx' group by node_name
* To identify # and space of data files on a single node

select node_name,count(ll_name) as Num_Active_files from backups where node_name='xxxxxxxxxxxxx' and STATE='ACTIVE_VERSION' group by node_name
* To identify # of ACTIVE data files on a node (for restore) (This query can take a very
long time.)

select client_name,input_vol_access,output_vol_access from sessions where (input_vol_access is not NULL) or (output_vol_access is not NULL)
* To identify clients reading from or writing to tape.

select domain_name,tcp_name,nodes.node_name,filespace_name,stgpool_name,logical_mb from nodes,occupancy where nodes.node_name=occupancy.node_name
* Billing Information (Occupancy by node)

all:select server_name,count(volume_name) as "# of Pending Vols" from volumes,status where status like '%%PEND%%' group by server_name
* To identify # of volumes in "pending" status per server

all:select count(volume_name) as "# of vols" from volumes where status='PENDING' and pending_date between '2009-10-26 00:00:00.000000' and '2009-10-27 00:00:00.000000'
* To identify total # of tapes in pending status for date (forcast tapes going scratch)

all:select stgpool_name,count(volume_name) as "# of vols" from volumes where status='PENDING' and stgpool_name='OFFSITE' and pending_date between '2010-01-27 00:00:00.000000' and '2010-01-28 00:00:00.000000' group by stgpool_name
* To identify # of tapes in pending status for date in the offsite pool (forcast tapes returning from Iron Mountain)

select node_name from nodes where substr(char(reg_time), 1, 10) >='2009-12-01' and node_name not like '%%_LT%%'and node_name not like '%%EXP20%%'
* To identify nodes registered since date

all:select server_name,node_name,reg_time from nodes,status where reg_time>'2009-08-15 00:00:00.000000'
* To identify nodes registered since date

select node_name,sum(capacity) as MB_Capacity,sum(capacity*(pct_util/100)) as MB_Used_Space from filespaces group by node_name order by MB_Used_Space
* To identify FS space and used capacity on all clients (not TSM occupancy)

select node_name,sum(capacity) as MB_Capacity,sum(capacity*(pct_util/100)) as MB_Used_Space from filespaces where node_name='xxxxxxxxxxx' group by node_name
* To identify FS space and used capacity on the client for a specific node (not TSM occupancy)

all:select server_name,volume_name,type,date_time from volhistory,status where volume_name='xxxxxx'
* To find volume history for a tape (V5)

all:select volume_name,type,date_time from volhistory where volume_name='xxxxxx'
* To find volume history for a tape (V6)

select node_name,substr(char(LASTACC_TIME), 1, 10) as LASTACC_DATE from nodes where node_name not like '%%EXP20%%' and substr(char(LASTACC_TIME), 1, 10) <='2010-01-01'
* To list nodes with last access date less than/greater than date (change date and lt/gt symbol)

select count(node_name) from nodes where node_name not like '%%EXP20%%' and substr(char(LASTACC_TIME), 1, 10) <='2010-01-20'
* To count # of nodes with last access date less than/greater than date (change date and lt/gt symbol)

select count(ll_name) from backups where STATE<>'ACTIVE_VERSION' and substr(char(BACKUP_DATE), 1, 10)<substr(char(current_date - interval '45' day), 1, 10)
* To identify # of files expired if retention policy is changed (change interval day)

select date_time,volume_name,location from volhistory where date_time between '2010-03-16 14:00:00.000000' and '2010-03-16 23:00:00.000000'
* Search for volumes on library manager created today

select assoc_object,object_name from prof_associations where profile_name='xxxxxxxxx'
* Show profile associations for profiles

Select admin_name,client_access from admins where CLIENT_ACCESS like '%%node_name%%' group by client_access,admin_name
* show admins who have client access on these nodes (change node_name)

Select distinct nodename,count(nodename) from actlog where msgno=0480 group by nodename
* Show severed sessions by nodename

select date_time,server_name,message from actlog,status where msgno=0314 and date_time>(current_timestamp - interval '6' minute)
* Show ANR0314W errors within the last 6 minutes.

select entity,activity,sum(bytes),sum(end_time-start_time),sum(affected),sum(failed),sum(mediaw) from summary where entity in (select node_name from filespaces where filespace_type like '%API%' group by node_name) and start_time between '2010-06-01 17:00:00' and '2010-06-09 17:00:00' group by entity,activity
* show TDP backup activity

select entity,activity,sum(bytes)as sum_bytes,start_time,end_time,sum(affected)as sum_affected,sum(failed)as sum_failed,sum(mediaw) as sum_mediaw from summary where entity in (select node_name from filespaces where filespace_type like '%API%' and node_name='xxxxxxxxxxxxxxxxxxxxxxx' group by node_name) and start_time between '2010-06-08 17:00:00' and '2010-06-10 17:00:00' group by entity,activity,start_time,end_time
* show detailed TDP backup activity for a specific node

select node_name,domain_name from nodes where domain_name in (select domain_name from domains where activestgpools is not null)
* show all nodes in an active data pool

select schedule_name,command from admin_schedules where schedule_name like 'MOVE_NODEDATA_DR%%'
* Show nodes being moved via move_nodedata_dr schedules

select distinct node_name,volume_name from volumeusage where stgpool_name='ACTIVE_DATA_DR'
* Show nodes on volumes in the ACTIVE_DATA_DR stg pool

select distinct node_name,volume_name from volumeusage where volume_name in (select volume_name from volumes where stgpool_name='ACTIVE_DATA_DR' and access='READWRITE')
* Show nodes on volumes in the ACTIVE_DATA_DR stg pool that are access=readwrite

select msgno,nodename,sessid,message from actlog where msgno in (4952,4953,4954,4955,4956,4957,4958,4959,4960,4961,4964,4966,4967,4968,4970) and date_time between '2010-07-25 17:00:00' and '2010-07-30 17:00:00' and nodename='NODENAME' order by sessid
* show BACKUP results for a specific node (change NODENAME)

select msgno,nodename,sessid,message from actlog where msgno in (4955,4959,4961,4964,4966,4967) and date_time between '2011-04-15 17:00:00' and '2011-04-18 17:00:00' and nodename='NODENAME' order by sessid,msgno
* show RESTORE results for a specific node (change NODENAME)

select msgno,nodename,sessid,message from actlog where msgno in (4955,4959,4961,4964,4966,4967) and date_time between '2011-04-15 17:00:00' and '2011-04-18 17:00:00' order by sessid,msgno
* show restore results for all nodes

select activity,cast ((end_time) as date) as "Date",(examined/cast ((end_time-start_time) seconds as decimal (18,13))*3600) "Obj/Hr" from summary where activity='EXPIRATION' and days (end_time) - days (start_time) = 0
* show Expiration Rate for servers (IBM recommendS that the Expiration Rate be greater than 3800000 objects per hour)

 

[unpaired]

it is great thank you

 

[unpaired]

SELECT start_time,END_TIME,activity,entity FROM summary WHERE activity='BACKUP' AND (start_time <= '2012-05-23 08:00:00' and end_time >= '2012-05-23 06:00:00')

between 06:00-08:00 hour backup activity

 

[maktsm]

Thanks for such useful commands athos154.
Wanted to know if there is any such statement which can be used to track location for a file belonging to a particular node that has to be restored.

Thanks.

 

[shcart]

maktsm this is a two or three step job. First you use a select to find the object_id followed by a show bfo to find where the file is and if it is part of an aggregate another show bfo command to show the bitfile aggregate object. Be very careful abuse of the select ... from backups ... can run for many hours if you get it wrong you need to soecify as much detail as possible as follows

PLEASE REMEMBER ALWAYS SPECIFY AT LEAST NODE_NAME AND FILESPACE_NAME these are index fields and prevent a complete table scan of probably the biggest table in a TSM instance.

Using unix Syntax

Note Like most of TSM NODE_NAME is uppercase, unlike most of TSM Filespace_name, Hl_name and ll_name are case sensitive.

select * from backups where node_name='YOURNODENAME' AND filespace_name='/YourFileSystem' and HL_NAME='/Your/path/herE/' and LL_NAME='YourFILEName'

once you get the details of the object from the field OBJECT_ID (shown below) move to the next step

NODE_NAME: YOURNODENAME
FILESPACE_NAME: /YourFileSystem
FILESPACE_ID: 1
STATE: ACTIVE_VERSION
TYPE: FILE
HL_NAME: /Your/path/herE/
LL_NAME: YourFILEName
OBJECT_ID: 201461790
BACKUP_DATE: 2013-05-23 14:43:27.000000
DEACTIVATE_DATE:
OWNER: itsyou
CLASS_NAME: DEFAULT

Example: based on above Object ID

show bfo 0 201461790
?Bitfile Object: 0.201461790?**Sub-bitfile 0.511049 is stored in the following
aggregate(s)
Super-bitfile: 0.201461483, Offset: 0.1028530, Length 0.13784

NOTE: This output displays that file represented by this object id (bitfile) is stored in a Super-bitfile: 0.201461483 so we need to do the show bfo again for the Super-bitfile to get the name of the volume:

show bfo 0 201461483

Bitfile Object: 0.201461483
**Archival Bitfile Entry
Bitfile Type: PRIMARY Storage Format: 9
Bitfile Size: 0.5504831 Number of Segments: 1
Storage Pool ID: 10 Volume ID: 409631 Volume Name: A00115L1

**Archival Bitfile Entry
Bitfile Type: COPY Storage Format: 9
Bitfile Size: 0.5504831 Number of Segments: 1
Storage Pool ID: -2 Volume ID: 409666 Volume Name: A00073L1

From the above you can determmine the file is located on Primary Tape A00115L1 and Copy Tape A00073L1

 

[seayl]

Very helpful!!!

Thanks for these select statements!!

Can you help me with this one, please?

In v5 I had a very simple select statement that gave me detailed information about my storage pools. It no longer works in v6.3.4. STGPOOL_DETAIL

SELECT STGPOOL_NAME AS STGPOOL, VOLUME_name AS VOLUME, STATUS, CAST(MEAN(EST_CAPACITY_MB/1024) AS DECIMAL(5,2)) AS GB_PER_VOL FROM VOLUMES

I did know to change MEAN to AVG, so that works, but now I get this error.

SELECT STGPOOL_NAME AS STGPOOL, VOLUME_name AS VOLUME, STATUS, CAST(AVG(EST_CAPACITY_MB/1024) AS DECIMAL(5,2)) AS GB_PER_VOL FROM VOLUMES
ANR0162W Supplemental database diagnostic information: -1:42803:-119 ([IBM][CLI Driver][DB2/AIX64] SQL0119N An expression starting with "STATUS" specified in a SELECT clause, HAVING clause, or ORDER BY clause is not specified in the GROUP BY clause or it is in a SELECT clause, HAVING clause, or ORDER BY clause with a column function and no GROUP BY clause is specified. SQLSTATE=42803
).
ANR0516E SQL processing for statement SELECT STGPOOL_NAME AS STGPOOL , VOLUME_name AS VOLUME , STATUS , CAST ( AVG ( EST_CAPACITY_MB/1024 ) AS DECIMAL ( 5 , 2 ) ) AS GB_PER_VOL FROM VOLUMES failed.

So, I added the GROUP BY clause (No luck with many iterations unless I leave bits that I need out) and the GROUP BY clause target just seems to keep moving.

SELECT STGPOOL_NAME AS STGPOOL, VOLUME_name AS VOLUME, STATUS, CAST(AVG(EST_CAPACITY_MB/1024) AS DECIMAL(5,2)) AS GB_PER_VOL FROM VOLUMES GROUP BY STGPOOL_NAME, VOLUME_NAME
ANR0162W Supplemental database diagnostic information: -1:42803:-119 ([IBM][CLI Driver][DB2/AIX64] SQL0119N An expression starting with "STATUS" specified in a SELECT clause, HAVING clause, or ORDER BY clause is not specified in the GROUP BY clause or it is in a SELECT clause, HAVING clause, or ORDER BY clause with a column function and no GROUP BY clause is specified. SQLSTATE=42803
).
ANR0516E SQL processing for statement SELECT STGPOOL_NAME AS STGPOOL , VOLUME_name AS VOLUME , STATUS , CAST ( AVG ( EST_CAPACITY_MB/1024 ) AS DECIMAL ( 5 , 2 ) ) AS GB_PER_VOL FROM VOLUMES GROUP BY STGPOOL_NAME , VOLUME_NAME failed.

Below are other attempts/results:
SELECT STGPOOL_NAME AS STGPOOL, VOLUME_name AS VOLUME, STATUS, CAST(AVG(EST_CAPACITY_MB/1024) AS DECIMAL(5,2)) AS GB_PER_VOL FROM VOLUMES GROUP BY STGPOOL_NAME,STATUS
ANR0162W Supplemental database diagnostic information: -1:42803:-119 ([IBM][CLI Driver][DB2/AIX64] SQL0119N An expression starting with "VOLUME_NAME" specified in a SELECT clause, HAVING clause, or ORDER BY clause is not specified in the GROUP BY clause or it is in a SELECT clause, HAVING clause, or ORDER BY clause with a column function and no GROUP BY clause is specified. SQLSTATE=42803
).
ANR0516E SQL processing for statement SELECT STGPOOL_NAME AS STGPOOL , VOLUME_name AS VOLUME , STATUS , CAST ( AVG ( EST_CAPACITY_MB/1024 ) AS DECIMAL ( 5 , 2 ) ) AS GB_PER_VOL FROM VOLUMES GROUP BY STGPOOL_NAME , STATUS failed.

SELECT STGPOOL_NAME AS STGPOOL, VOLUME_name AS VOLUME, STATUS, CAST(AVG(EST_CAPACITY_MB/1024) AS DECIMAL(5,2)) AS GB_PER_VOL FROM VOLUMES GROUP BY VOLUME_NAME,STATUS
ANR0162W Supplemental database diagnostic information: -1:42803:-119 ([IBM][CLI Driver][DB2/AIX64] SQL0119N An expression starting with "STGPOOL_NAME" specified in a SELECT clause, HAVING clause, or ORDER BY clause is not specified in the GROUP BY clause or it is in a SELECT clause, HAVING clause, or ORDER BY clause with a column function and no GROUP BY clause is specified. SQLSTATE=42803
).
ANR0516E SQL processing for statement SELECT STGPOOL_NAME AS STGPOOL , VOLUME_name AS VOLUME , STATUS , CAST ( AVG ( EST_CAPACITY_MB/1024 ) AS DECIMAL ( 5 , 2 ) ) AS GB_PER_VOL FROM VOLUMES GROUP BY VOLUME_NAME , STATUS failed.

SELECT STGPOOL_NAME AS STGPOOL, VOLUME_name AS VOLUME, STATUS, CAST(AVG(EST_CAPACITY_MB/1024) AS DECIMAL(5,2)) AS GB_PER_VOL FROM VOLUMES GROUP BY STGPOOL_NAME, VOLUME_NAME,STATUS
ANRW0091E The following command generates a syntax error: " {0} ". See the activity log for more information. To view the activity log, use the Activity Log tab in this server's properties notebook.
SELECT STGPOOL_NAME AS STGPOOL, VOLUME_name AS VOLUME, STATUS, CAST(AVG(EST_CAPACITY_MB/1024) AS DECIMAL(5,2)) AS GB_PER_VOL FROM VOLUMES GROUP BY STGPOOL_NAME, VOLUME_NAME
ANR0162W Supplemental database diagnostic information: -1:42803:-119 ([IBM][CLI Driver][DB2/AIX64] SQL0119N An expression starting with "STATUS" specified in a SELECT clause, HAVING clause, or ORDER BY clause is not specified in the GROUP BY clause or it is in a SELECT clause, HAVING clause, or ORDER BY clause with a column function and no GROUP BY clause is specified. SQLSTATE=42803
).
ANR0516E SQL processing for statement SELECT STGPOOL_NAME AS STGPOOL , VOLUME_name AS VOLUME , STATUS , CAST ( AVG ( EST_CAPACITY_MB/1024 ) AS DECIMAL ( 5 , 2 ) ) AS GB_PER_VOL FROM VOLUMES GROUP BY STGPOOL_NAME , VOLUME_NAME failed.

SELECT STGPOOL_NAME AS STGPOOL, CAST(AVG(EST_CAPACITY_MB/1024) AS DECIMAL(5,2)) AS GB_PER_VOL FROM VOLUMES GROUP BY STGPOOL_NAME
STGPOOL GB_PER_VOL
ARCHIVETAPEPOOL 400.00
DISKPOOL 68.47
ICS_ARCHIVE_POOL 200.00
LOCAL2COPYPOOL 331.75
LOCALCOPYPOOL 192.03
LOCALTAPEPOOL 339.79
NT2BKSETCOPYPOOL 317.02
NTBKSETCOPYPOOL 178.98
NTLTO2TAPEPOOL 317.44
NT_BACKUPSET_DISKPOOL 79.98
OFFSITE2COPYPOOL 340.00
OFFSITECOPYPOOL 194.18
ORADISKPOOL 78.98
ORALTO2COPYPOOL 512.48
ORALTO2TAPEPOOL 661.17
TEMPCOPYPOOL 200.00

SELECT STGPOOL_NAME AS STGPOOL, STATUS, CAST(AVG(EST_CAPACITY_MB/1024) AS DECIMAL(5,2)) AS GB_PER_VOL FROM VOLUMES GROUP BY STGPOOL_NAME,STATUS
STGPOOL STATUS GB_PER_VOL
ARCHIVETAPEPOOL FILLING 400.00
DISKPOOL ONLINE 68.47
ICS_ARCHIVE_POOL FILLING 200.00
LOCAL2COPYPOOL FILLING 400.00
LOCAL2COPYPOOL FULL 334.10
LOCAL2COPYPOOL PENDING 0.00
LOCALCOPYPOOL FILLING 200.00
LOCALCOPYPOOL FULL 190.44
LOCALTAPEPOOL FILLING 418.59
LOCALTAPEPOOL FULL 311.79
LOCALTAPEPOOL PENDING 0.00
NT2BKSETCOPYPOOL FILLING 400.00
NT2BKSETCOPYPOOL FULL 321.08
NT2BKSETCOPYPOOL PENDING 0.00
NTBKSETCOPYPOOL FILLING 200.00
NTBKSETCOPYPOOL FULL 157.97
NTLTO2TAPEPOOL FILLING 400.00
NTLTO2TAPEPOOL FULL 321.29
NTLTO2TAPEPOOL PENDING 0.00
NT_BACKUPSET_DISKPOOL ONLINE 79.98
OFFSITE2COPYPOOL EMPTY 0.00
OFFSITE2COPYPOOL FILLING 400.00
OFFSITE2COPYPOOL FULL 351.36
OFFSITE2COPYPOOL PENDING 0.00
OFFSITECOPYPOOL FILLING 200.00
OFFSITECOPYPOOL FULL 191.76
ORADISKPOOL ONLINE 78.98
ORALTO2COPYPOOL FILLING 435.32
ORALTO2COPYPOOL FULL 530.02
ORALTO2TAPEPOOL FILLING 441.38
ORALTO2TAPEPOOL FULL 698.82
ORALTO2TAPEPOOL PENDING 0.00
TEMPCOPYPOOL FILLING 200.00

 

[tony2018]

maktsm this is a two or three step job. First you use a select to find the object_id followed by a show bfo to find where the file is and if it is part of an aggregate another show bfo command to show the bitfile aggregate object. Be very careful abuse of the select ... from backups ... can run for many hours if you get it wrong you need to soecify as much detail as possible as follows

PLEASE REMEMBER ALWAYS SPECIFY AT LEAST NODE_NAME AND FILESPACE_NAME these are index fields and prevent a complete table scan of probably the biggest table in a TSM instance.

Using unix Syntax

Note Like most of TSM NODE_NAME is uppercase, unlike most of TSM Filespace_name, Hl_name and ll_name are case sensitive.

select * from backups where node_name='YOURNODENAME' AND filespace_name='/YourFileSystem' and HL_NAME='/Your/path/herE/' and LL_NAME='YourFILEName'

once you get the details of the object from the field OBJECT_ID (shown below) move to the next step

NODE_NAME: YOURNODENAME
FILESPACE_NAME: /YourFileSystem
FILESPACE_ID: 1
STATE: ACTIVE_VERSION
TYPE: FILE
HL_NAME: /Your/path/herE/
LL_NAME: YourFILEName
OBJECT_ID: 201461790
BACKUP_DATE: 2013-05-23 14:43:27.000000
DEACTIVATE_DATE:
OWNER: itsyou
CLASS_NAME: DEFAULT

Example: based on above Object ID

show bfo 0 201461790
?Bitfile Object: 0.201461790?**Sub-bitfile 0.511049 is stored in the following
aggregate(s)
Super-bitfile: 0.201461483, Offset: 0.1028530, Length 0.13784

NOTE: This output displays that file represented by this object id (bitfile) is stored in a Super-bitfile: 0.201461483 so we need to do the show bfo again for the Super-bitfile to get the name of the volume:

show bfo 0 201461483

Bitfile Object: 0.201461483
**Archival Bitfile Entry
Bitfile Type: PRIMARY Storage Format: 9
Bitfile Size: 0.5504831 Number of Segments: 1
Storage Pool ID: 10 Volume ID: 409631 Volume Name: A00115L1

**Archival Bitfile Entry
Bitfile Type: COPY Storage Format: 9
Bitfile Size: 0.5504831 Number of Segments: 1
Storage Pool ID: -2 Volume ID: 409666 Volume Name: A00073L1

From the above you can determmine the file is located on Primary Tape A00115L1 and Copy Tape A00073L1

Hi, I hope you're still around. Can you use search patterns in LL_Name to search for files?

 

[shcart]

Hi, I hope you're still around. Can you use search patterns in LL_Name to search for files?

I do not have access to the index information at the moment but I suspect LL_NAME is unlikely to be an indexed field. If this is true then YES you can use it but its still best to limit the search using NODE and FILESPACE. Dont forget you can use wildcards in the indexed search too.

 

[sandragon]

Dig this one out of the past. Is there a way to get an occupancy only by primary storage used? Management is requesting that for billing and all the queries here seem to include DR copy pools along with Primary storage pools.

 

[marclant]

Dig this one out of the past. Is there a way to get an occupancy only by primary storage used? Management is requesting that for billing and all the queries here seem to include DR copy pools along with Primary storage pools.

If all the primary data is backed up to a copypool, should be as simple as dividing by two.

One could argue that both the primary and the copy is storage used, so they should be billed for both anyway.

You could do something line this:

select occupancy.node_name,sum(reporting_mb) as MB from -
occupancy join stgpools on occupancy.stgpool_name=stgpools.stgpool_name where -
stgpools.pooltype<>'COPY' group by occupancy.node_name

 

[tony2018]

I have 2 tape librarys, 1 for primary and 1 for copy pool. Is there a way to get a scratch count for each library instead of a total combine?
The cmd below gives me the total combine
select count(*) as Scratch_count from libvolumes where status='Scratch'

 

[marclant]

I have 2 tape librarys, 1 for primary and 1 for copy pool. Is there a way to get a scratch count for each library instead of a total combine?

Easy, just need to add the field library_name and group by it.
select library_name,count(*) as Scratch_count from libvolumes where status='Scratch' group by library_name