banner_bild_01 banner_bild_02 banner_bild_03 banner_bild_04 banner_bild_05
banner_bild_06 banner_bild_07 banner_bild_08

TSM SQL Advanced Tutorial (2)

button-home

Home SQL

arrow

Previous

Next

We start with an example that combines two SQL queries from different tables into a single command. Lets assume we want to know, how many Tape Volumes we've got. A simple query like "select count(*) from volumes" would probably not return the correct value, since it counts not only tape, but also disk and virtual volumes used in TSM's server-to-server communication.
First we'll find out, which tables contain the required information. One obvious pick would be the "Volumes" table. It contains:

select colname from columns where tabname='VOLUMES'

COLNAME          
------------------
VOLUME_NAME      
STGPOOL_NAME     
DEVCLASS_NAME    
EST_CAPACITY_MB  
PCT_UTILIZED     
STATUS           
ACCESS           
PCT_RECLAIM      
SCRATCH          
ERROR_STATE      
NUM_SIDES        
TIMES_MOUNTED    
WRITE_PASS       
LAST_WRITE_DATE  
LAST_READ_DATE   
PENDING_DATE     
WRITE_ERRORS     
READ_ERRORS      
LOCATION         
CHG_TIME         
CHG_ADMIN  

As you can see there is nothing directly telling us, whether we've got a tape, disk or virtual volume, but we get information about the Deviceclass which might be helpful:

select colname from columns where tabname='DEVCLASSES'

COLNAME          
------------------
DEVCLASS_NAME    
ACCESS_STRATEGY  
STGPOOL_COUNT    
DEVTYPE          
FORMAT           
CAPACITY         
MOUNTLIMIT       
MOUNTWAIT        
MOUNTRETENTION   
PREFIX           
DRIVE            
LIBRARY_NAME     
DIRECTORY        
SERVERNAME       
RETRYPERIOD      
RETRYINTERVAL    
TWO_SIDED        
LAST_UPDATE_BY   
LAST_UPDATE

You can see, that the DEVCLASSES Table contains helpful information like ACCESS_STRATEGY and DEVTYPE. This, combined with the information from the VOLUMES Table, solves our problem. Since we query two tables, we combine two "select" commands, using one as input for the other:

select count(*) as -
 "Sequential Volumes that are NOT virtual:" -
 from volumes -
 where devclass_name in -
 (select devclass_name from devclasses where -
  access_strategy='Sequential' and -
  devtype^='SERVER')

You can execute the included query "select devclass_name from devclasses where access_strategy='Sequential' and devtype^='SERVER'" seperately to see what it does.
You'll get the names of all device classes that use a sequential access strategy to their storage units and are NOT of the type Server. The sample SQL statement uses this output as input following the "in" clause. It will return all volumes within these device classes and count them. We tell SQL to combine queries by simply writing sub-queries in parantheses as shown in the example.

 Alle erwähnten Produkte und Bezeichnungen können eingetragene Warenzeichen der jeweiligen Hersteller sein und werden als solche betrachtet.
Bei Fragen oder Problemen kontaktieren Sie bitte info@general-storage.de

Navigation  | Über GS  |  Lösungen  |  ServiceReferenzen  |  Impressum  |  Download

© 2016 by General Storage GbR. Alle Rechte vorbehalten

arrow

Previous

Next

Kontaktieren Sie uns:

Telefon: +49 6206 157686
Telefax:  +49 6206 157687
E-Mail: info@general-storage.de

arrow
arrow
GS_Logo1_Neu
gse-logo

13th ISP Symposium 2017
26.-29. September 2017,
Cologne Marriott Hotel


IBM

SSW-2258:
An Alternative to NDMP for Network Attached Storage Backup 
with dsmISI MAGS


Kundenforum
IBM Spectrum Protect und Veeam Kundenform, 28. Juni 2017, Skyloft Frankfurt Airport


Pressemitteilung:
dsmISI for Veeam ermöglicht unbegrenzte Speicher für Backups

Hier nachlesen


General Storage Lösungen
Ready for IBM Tivoli Software

tivoli_ready