Tape inventory script for Tivoli Storage Manager on open systems

Open systems connected to IBM TotalStorage SCSI Libraries

The script gives a consolidated inventory report of the all the tapes used by IBM® Tivoli® Storage Manager, which are present inside or outside of the IBM TotalStorage® SCSI Library, including the tape's status, access mode, home slot number, storage pool to which it belongs, offsite readiness and status and the TSM server or instance that owns them.

Share:

Dhruv Harnal (dhruv.harnal@in.ibm.com), Senior Associate IT Specialist, IBM

Dhruv HarnalDhruv Harnal is a part of the ISL TeamONE-Storage at IBM's India Software Lab, Pune.He has 4 years of experience in the IT industry primarily on Backup Softwares:Tivoli Storage Manager,Tivoli Data Protection and Veritas Netbackup,IBM System Storage:DS6800,SVC and DS8000,Brocade Directors and Switches,IBM TotalStorage Libraries and Multipathing Drivers.He is also a BCFP,IBM certified p5 Virtualization expert and SNIA SCP.



20 October 2008

A view of Tivoli Storage Manager's database

TSM (Tivoli Storage Manager) uses Structured Query Language (SQL) to monitor its servers. SQL is a relational database language and has been adopted as an International Standard by the International Organization Standardization (ISO). TSM dsmadmc SELECT command uses a subset of the language element part for the SELECT query of SQL.

System catalog tables

Tivoli Storage Manager provides three system catalog tables to help you find what information is available in the database:

  • SYSCAT.TABLES:-Contains information about all tables that can be queried with the SELECT SQL query command.
  • SYSCAT.COLUMNS:-Contains information about the columns in each table.
  • SYSCAT.ENUMTYPES:-Contains information about the valid values for each enumerated type and the order of the values for each type.

Consideration

The below points should be considered while issuing a SELECT statement:

  • A minimum of 4MB space is required in the database to use the SELECT command.
  • Complex queries can affect Tivoli Storage Manager server performance and hence the usage of the SELECT SQL query command has restrictions.
  • The following and correlated subqueries are not supported: UNION, INTERSECT, and EXCEPT.

Some acronyms used throughout this article are given below.

  • TSM:Tivoli Storage Manager
  • SQL:Structured Query Language
  • DB:Database

The tape inventory script

This script uses the syntax of KSH (Korn Shell) and has been intended to work on open systems with TSM version 5.2 or greater installed and connected to IBM TotalStorage SCSI libraries (including 358x, 3310,and 3200). It queries the TSM DB(particularly SYSCAT.TABLES) and pulls out a report on the inventory and state information of all the tapes associated with Tivoli Storage Manager server. Any system or operator privileged TSM administrator can execute this script from a Korn Shell. A brief on both the privileges to a TSM administrator as below:

  • System - A system administrator has the highest level of authority in Tivoli Storage Manager, can issue any administrative command and manage all policy domains and all storage pools.
  • Operator - An administrator with operator privilege can issue commands that control the immediate operation of the server and the availability of storage media.

Inside the script

The SELECT statements used in the script query the following tables of interest within the system catalog table, SYSCAT.TABLES:

  • MEDIA- This table will list the following:
    • VOLUME_NAME:The barcode label written into the tapes during LABEL LIBVOL
    • STATE:A tape can be in any of these two states i.e. MOUNTABLEINLIB:the tape belongs to a storagepool and is present in the library or MOUNTABLENOTINLIB:the tape belongs to a storagepool but is not present in the library
    • STGPOOL_NAME:The storagepool to which the tape is allocated.
    • LIB_NAME:The library that contains the volume/tape
    • STATUS:The status of a tape/volume can be FULL,FILLING or EMPTY
    • ACCESS:the access of a tape/volume can be READWRITE,READONLY or UNAVAILABLE

A sample code from the script will show the above listed fields of MEDIA table being utilized:

volume=`dsmadmc -id=$name -password=$passwd -servername=$server "select VOLUME_NAME from media where VOLUME_NAME='$i'" | tail -4 | grep -i -v ans | awk '{print$1}'`
C=`dsmadmc -id=$name -password=$passwd -servername=$server "select STATE from media where VOLUME_NAME='$i'" | tail -4 | grep -i -v ans | awk '{print$1}'`
D=`dsmadmc -id=$name -password=$passwd -servername=$server "select STGPOOL_NAME from media where VOLUME_NAME='$i'" | tail -4 | grep -i -v ans | awk '{print$1}'`
E=`dsmadmc -id=$name -password=$passwd -servername=$server "select STATUS from media where VOLUME_NAME='$i'" | tail -4 | grep -i -v ans | awk '{print$1}'`
F=`dsmadmc -id=$name -password=$passwd -servername=$server "select ACCESS from media where VOLUME_NAME='$i'" | tail -4 | grep -i -v ans | awk '{print$1}'`
I=`dsmadmc -id=$name -password=$passwd -servername=$server "select LIB_NAME from media where VOLUME_NAME='$i'" | tail -4 | grep -i -v ans | awk '{print$1}'`
  • DRMEDIA - This table contains information pertaining to DR(Disaster Recovery)in Tivoli Storage Manager. It lists the below fields:
    • VOLUME_NAME:The barcode label written into the tapes during "LABEL LIBVOL"
    • STATE:A DR tape can be in any of these states i.e. REMOTE,MOUNTABLE,NOTMOUNTABLE,VAULT,VAULTRETRIEVE,COURIER and COURIERRETRIEVE
    • STGPOOL_NAME:The copy storagepool to which the tape belongs.
    • LIB_NAME:The library that contains the volume/tape
    • VOLTYPE:DR tapes are used either for copystoragepools or dbbackups , the volume type will determine exactly that.

A DR tape in REMOTE state will imply that it is present on an offsited remote server. A sample code from the script will show the above listed fields of DRMEDIA table being utilized:

.
.
.
count=0
F2() {
print $count"\t"$volume"\t"$C"\t"$G"\t"$I"\t"$server"\t"$D"\t"$J"\t"$L"\t"$M"\t"$E"\t"$F
}
.
.
F3() {
count=`expr $count + 1`
volume=`dsmadmc -id=$name -password=$passwd -servername=$server -tab "select VOLUME_NAME from drmedia where VOLUME_NAME='$k'" | tail -4 | grep -i -v ans | awk '{print$1}'`
E=NA
G=NA
F=NA
I=`dsmadmc -id=$name -password=$passwd -servername=$server -tab "select LIB_NAME from drmedia where VOLUME_NAME='$k'" | tail -4 | grep -i -v ans | awk '{print$1}'`
}
.
.
C=`dsmadmc -id=$name -password=$passwd -servername=$server "select STATE from media where VOLUME_NAME='$i'" | tail -4 | grep -i -v ans | awk '{print$1}'`
D=`dsmadmc -id=$name -password=$passwd -servername=$server "select STGPOOL_NAME from media where VOLUME_NAME='$i'" | tail -4 | grep -i -v ans | awk '{print$1}'`
elif [ "`echo $K`" == "COURIERRETRIEVE" ]
then
F3
J=NO
L="In_transit_to_onsite"
M="`echo $K`"
D="`echo $N`"
.
.
.
  • STGPOOLS:-This table has been utilized for the POOLTYPE field which forks into PRIMARY or COPY type and only the volumes with storagepool of type COPY will be queried in the DRMEDIA table.
  • LIBVOLUMES:-This table will determine the HOME_ELEMENT(slot number in the library) and STATUS(Private or Scratch) of a volume.Volumes with status as scratch are not assigned to any storagepool and can be automatically picked by any or be manually assigned to a storagepool.

State change considerations

This section covers certain points while looking at state changes of a DR tape i.e. a coptstoragepool or a dbbackup tape.

  • Tapes which had been in a media state of VAULT gradually see their contents expire, until the tape is finally empty, at which time the state automatically changes to VAULTRETRIEVE.
  • To specify the number of days before a database backup series is expired, issue the SET DRMDBBACKUPEXPIREDAYS command. This is effective for non-virtual/local backup volumes only.
  • The volume update from VAULT to VAULTRETRIEVE is not a state change as the volume is still in the vault. With no state change there is no date/time update.

Implementation of the script

Download the script from the Downloads sub-section of this article and make sure the user has "execute" permission before running it. The first step will ask you to supply a TSM administrator's name with system or operator privilege, followed by password of the respective administrator. Note here that the script cancels the display of password for security reasons. The last step after pressing return will be the TSM's server or instance name. This name should be the one supplied using SET SERVERNAME command at the TSM server prompt.

A stepwise execution of the script is shown below:

$> ksh TSM_Tape_Inventory_v1.1.ksh
INPUT the TSM Admin/Operator's NAME :- admin INPUT the TSM Admin/Operator's PASSWORD :- INPUT the TSM SERVERNAME :- tsm_servername

Output of the script

This part will cover the outcome of the script and brief about the various fields in the output.

  • SNO- The serial number determining the number of volumes associated with the TSM server.
  • VOLNAME- The tape barcode label written into the tape/volume during LABEL LIBVOL.
  • LOC- The LOC tells about the position of tape with respect to library i.e., whether it is in the library, out of the library, is vaulted or is it a remote type. Following are the possible values to this field:
    • IN_LIB - The tape is physically present in the library
    • OUT_LIB - The tape is physically out of the library
    • DR Media States like VAULT,VAULTRETRIEVE
    • REMOTE - In which case the volume is present on an offsited remote server
  • SLOT- It contains information about the home slot to which a volume originally belongs. Alternatively, can be checked using SHOW SLOTS command at the TSM prompt.
  • LIB- The library that contains the tape/volume.
  • SVR- The server/instance which own the volume. In case you have a library manager-client setup, you are advised to run this script only on the library manager, as it will contain volume ownership for the library client too.
  • POOL- The storagepool to which the tape is assigned. In case of a PRIMARY storagepool, the script will list the storagepool name too and for a COPY pool, only CopyStgPool will be displayed, whereas for dbbackup tapes DBBackup will be the value of this field. A value of SCRATCH suggests that this volume has not been assigned to any storage pool.
  • OFFSITABLE?- Whether the tape/volume is an offsitable tape i.e., a DR tape, this value will be YES for COPY storagepool and DBBACKUP(non-remote) tapes whereas will be NA for PRIMARY storagepool and REMOTE tapes
  • OFFSITED?- If the tape belongs to COPY storagepool or DBBACKUP and if it has met the criteria for offsiting/DR, it will have the value as YES else a NO
  • DRSTATE- The state of the tape with respect to DR, for PRIMARY storagepool tapes this value will be NA.
  • STATUS- Values of this field will signify if the tape is FULL (no additional data can be written), FILLING (additional data can be added to the tape until it's at capacity) or EMPTY. For tapes that qualify for offsiting, this value will be NA.
  • ACCESS- This field determines if the tape is available for read only or read/write access. A case of UNAVAILABLE would mean the tape is not accessible for either reading or writing purpose. For tapes that qualify for offsiting, this value will be NA. A value of SCRATCH signifies that the volume is of type scratch and has no access available unless assigned to a storagepool for used for dbbackup.

The below screenshot shows the above headers of the script's output.

SNO VOLNAME LOC SLOT LIB SRVR POOL OFFSITABLE? OFFSITED? DRSTATE STATUS ACCESS

A sample output of the script excluding the headers is shown below. The first field is the serial order and tells us that there are 3 tapes associated with the TSM server. AC7594, TPD551 and TPD607 are the tape labels which in ideal case match with the barcode on the exterior of the tape. In case of discrepancies in labels, pass the OVERWRITE=YES parameter with the LABEL LIBVOL command. The VAULT and CopyStgPool signifies that the volume belongs to a storagepool of type COPY and has been offsited or kept in a vault which can also be seen from the value YES for "OFFSITED?" field. Also, note that since TPD551 is offsited, it is not available for access and the STATUS is NA. Similarly, is the case with TPD607 volume, which being a dbbackup tape is not available for readwrite purpose. Moreover, this dbbackup tape has met the conditions of offsiting and is ready to be offsited from the "OFFSITABLE?" values as YES, but has not been offsited as from the "OFFSITED?" value being NO, so this tape needs attention and must be couriered to an offsite location.

1 AC7594 IN_LIB 1091 LTO2 srvr1 TAPEPOOL NA NA NA FULL READWRITE
2 TPD551 VAULT NA srvr1 CopyStgPool YES YES VAULT NA NA
3 TPD607 IN_LIB 1025 LTO2 srvr1 DBBackup YES NO MOUNTABLE NA NA

Note- The complete output can be redirected to a file on Open Systems and also imported into a Microsoft Excel sheet with a "tab" delimiter.


Download

DescriptionNameSize
TSM Tape Inventory ksh scriptTSM_Tape_Inventory_v1.1.zip1.21KB

Resources

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Tivoli (service management) on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Tivoli, Sample IT projects
ArticleID=342537
ArticleTitle=Tape inventory script for Tivoli Storage Manager on open systems
publish-date=10202008