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
0 comments:
Post a Comment