TSM SQL常用語法

找尋還有哪些Volumes還是Scratch
SELECT STATUS, VOLUME_NAME FROM LIBVOLUMES WHERE STATUS='Scratch'

找尋有Error的Volumes
SELECT VOLUME_NAME, WRITE_ERRORS, READ_ERRORS FROM VOLUMES WHERE WRITE_ERRORS>1 OR READ_ERRORS>1

找尋沒有並置群組的Node
SELECT NODE_NAME,CONTACT,DOMAIN_NAME FROM NODES WHERE COLLOCGROUP_NAME IS NULL

找尋磁帶內有哪些Node資料
SELECT NODE_NAME, STGPOOL_NAME, VOLUME_NAME FROM VOLUMEUSAGE WHERE VOLUME_NAME='XXXXXX'

找尋磁帶內Node的詳細資料
SELECT FILE_NAME, FILESPACE_NAME, NODE_NAME, VOLUME_NAME FROM CONTENTS WHERE NODE_NAME='XXXXXX' AND VOLUME_NAME='YYYYYY'

找尋Node使用的磁帶
SELECT NODE_NAME, STGPOOL_NAME, VOLUME_NAME FROM VOLUMEUSAGE WHERE NODE_NAME='XXXXXX'

找尋檔案在哪捲磁帶
SELECT FILE_NAME, FILESPACE_NAME, NODE_NAME, VOLUME_NAME FROM CONTENTS WHERE FILE_NAME='AAA' AND FILESPACE_NAME='BBB' AND NODE_NAME='CCC' AND VOLUME_NAME='DDD'

!!!注意!!!，此指令耗費極久的搜尋時間

=============
  其餘相關指令
=============
SELECT DISTINCT =>橫列

DB2裡面需要查詢特定關鍵字則用%取代*
select * from VOLUMEUSAGE where node_name like '%NAGI%'

=============
 參考資料
=============
http://thobias.org/tsm/sql/index.html
http://www.tsmsql.com/query/5500/
http://www.lascon.co.uk/tsm-sql-queries.php#sqlnode