~ Up_to_date_with_ me

Saturday, 27 June 2015

12.2 Using SQL commands to query TSM DB2 database



You can use SQL queries to get information from the TIvoli storage Manager Database. You can use the SQL SELECT commands to customize a wide variety of queries. Some queries require server time and resources and might impact performance.

IBM Tivoli Storage Manager Version 7 uses the DB2 open database connectivity (ODBC) driver to query the database and display the results. System catalog tables provide information about information that is available in the database. To help you find what information is available in the database, Tivoli Storage Manager provides three system catalog tables.

SYSCAT.TABLES
Contains information about all tables that can be queried with the SELECT command.

SYSCAT.COLUMNS
Describes the columns in each table.

SYSCAT.ENUMTYPES
Defines the valid values for each enumerated type and the order of the values for each type.

The simplest form of a SELECT statement is selecting all columns from a table. To use the SQL interface, you need a basic understanding of the SQL SELECT statement. For example to know what are the available tables 


select * from syscat.tables


Some Commonly used SQL Queries on TSM 6 & TSM 7


TSM DB Utilization


SELECT CAST(SUM(100-(free_space_mb*100) / tot_file_system_mb) AS DECIMAL(3,1)) AS PCT_UTILIZED FROM db

TSM log recovery utilization (%)


SELECT CAST(SUM(used_space_mb *100 / total_space_MB) AS DECIMAL(3,1)) AS PCT_UTILIZED FROM log

Space and number of files stored per client


 SELECT node_name,CAST(FLOAT(SUM(physical_mb)) / 1024 AS DEC(8,2))as "Space in GB", -
  SUM(num_files)as"Number of files" FROM occupancy GROUP BY node_name

Information about drives x paths


  SELECT b.source_name, a.library_name, a.drive_name, a.drive_serial, b.device FROM drives a, paths b WHERE a.drive_name=b.destination_name

Total client data stored (TB)


 SELECT CAST(FLOAT(SUM(logical_mb)) / 1024 / 1024 AS DEC(8,2)) FROM occupancy

[facebook src="Uptodatewithme" width="500" height="400" hide-cover="true" posts="true"/]

0 comments:

Post a Comment