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.

Also Read: TSM Administrator Daily routine tasks

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

Describes the columns in each table.

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

Check this website for more TSM SQL queries which can be used in daily monitoring of TSM Server.

PREVIOUS: 12.1 TSM Server and Client event logs and activity logs Overview
NEXT: 12.3 TSM Administrator daily tasks and activities
ALL CHAPTERS: IBM Spectrum Protect (TSM) Basic Free Tutorials

0 Comment to "12.2 Using SQL commands to query TSM DB2 database"

Post a Comment