Administrator's Guide


Using SQL to Query the Tivoli Storage Manager Database

You can use a standard SQL SELECT statement to get information from the database. The TSM SELECT command is a subset of the SQL92 and SQL93 standards.

TSM also provides an open database connectivity (ODBC) driver. The driver allows you to use a relational database product such as Lotus Approach to query the database and display the results.

Using the ODBC Driver

Tivoli Storage Manager provides an ODBC driver for Windows 95, Windows NT 3.51, and Windows NT 4.0 operating systems. The driver supports the ODBC Version 2.5 application programming interface (API). Because TSM supports only the SQL SELECT statement (query), the driver does not conform to any ODBC API or SQL grammar conformance level. After you install this driver, you can use a spreadsheet or database application that complies with ODBC to access the database for information.

The ODBC driver set-up is included in the client installation package. The client installation program can install the ODBC driver and set the corresponding registry values for the driver and data sources. For more information on setting up the ODBC driver, see Installing the Clients.

To open the TSM database through an ODBC application, you must log on to the server (the defined data source). Use the name and password of a registered TSM administrator. After you log on to the server, you can perform query functions provided by the ODBC application to access database information.

Issuing SELECT Commands

You can issue the SELECT command from the command line of an administrative client. You cannot issue this command from the server console.

The TSM SELECT command supports a subset of the syntax of the SELECT statement as documented in the SQL92 and SQL93 standards. For complete information about how to use the SELECT statement, refer to these standards or to other publications about SQL.

Issuing the SELECT command to the server can use a significant amount of server resources to run the query. Complicated queries or queries that run for a long time can interfere with normal server operations. If your query requires excessive server resource to generate the results, you will receive a message asking you to confirm that you wish to continue.

Database Space Requirements:To allow any use of the SELECT command, the database must have at least 4MB of free space. For complex queries that require significant processing, additional free space is required in the database.

Learning What Information Is Available: System Catalog Tables

To help you find what information is available in the database, TSM 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.

For example, to get a list of all tables available for querying in the database, enter the following command:

select * from syscat.tables

The following shows part of the results of this command:


+--------------------------------------------------------------------------------+
|     TABSCHEMA: TSM                                                             |
|       TABNAME: ACTLOG                                                          |
|   CREATE_TIME:                                                                 |
|      COLCOUNT: 11                                                              |
|INDEX_COLCOUNT: 1                                                               |
|  UNIQUE_INDEX: FALSE                                                           |
|       REMARKS: Server activity log                                             |
|                                                                                |
|     TABSCHEMA: TSM                                                             |
|       TABNAME: ADMINS                                                          |
|   CREATE_TIME:                                                                 |
|      COLCOUNT: 17                                                              |
|INDEX_COLCOUNT: 1                                                               |
|  UNIQUE_INDEX: TRUE                                                            |
|       REMARKS: Server administrators                                           |
|                                                                                |
|     TABSCHEMA: TSM                                                             |
|       TABNAME: ADMIN_SCHEDULES                                                 |
|   CREATE_TIME:                                                                 |
|      COLCOUNT: 15                                                              |
|INDEX_COLCOUNT: 1                                                               |
|  UNIQUE_INDEX: TRUE                                                            |
|       REMARKS: Administrative command schedules                                |
|                                                                                |
|     TABSCHEMA: TSM                                                             |
|       TABNAME: ARCHIVES                                                        |
|   CREATE_TIME:                                                                 |
|      COLCOUNT: 10                                                              |
|INDEX_COLCOUNT: 5                                                               |
|  UNIQUE_INDEX: FALSE                                                           |
|       REMARKS: Client archive files                                            |
+--------------------------------------------------------------------------------+

Examples

The SELECT command lets you customize a wide variety of queries. This section shows two examples. For many more examples of the command, see the Administrator's Reference.

Example 1: Find the number of nodes by type of operating system by issuing the following command:

select platform_name,count(*) as "Number of Nodes" from nodes
group by platform_name

This command gives results like the following:


+--------------------------------------------------------------------------------+
|PLATFORM_NAME     Number of Nodes                                               |
|-------------     ---------------                                               |
|OS/2                           45                                               |
|AIX                            90                                               |
|Windows                        35                                               |
|                                                                                |
+--------------------------------------------------------------------------------+

Example 2: For all active client sessions, determine how long have they been connected and their effective throughput in bytes per second:

select session_id as "Session", client_name as "Client", state as "State",
  current_timestamp-start_time as "Elapsed Time",
  (cast(bytes_sent as decimal(18,0)) /
  cast((current_timestamp-start_time)seconds as decimal(18,0)))
  as "Bytes sent/second",
  (cast(bytes_received as decimal(18,0)) /
  cast((current_timestamp-start_time)seconds as decimal(18,0)))
  as "Bytes received/second"
  from sessions

This command gives results like the following:


+--------------------------------------------------------------------------------+
|                 Session: 24                                                    |
|                  Client: ALBERT                                                |
|                   State: Run                                                   |
|            Elapsed Time: 0 01:14:05.000000                                     |
|       Bytes sent/second: 564321.9302768451                                     |
|   Bytes received/second: 0.0026748857944                                       |
|                                                                                |
|                 Session: 26                                                    |
|                  Client: MILTON                                                |
|                   State: Run                                                   |
|            Elapsed Time: 0 00:06:13.000000                                     |
|       Bytes sent/second: 1638.5284210992221                                    |
|   Bytes received/second: 675821.6888561849                                     |
|                                                                                |
+--------------------------------------------------------------------------------+

Using SELECT Commands in Tivoli Storage Manager Scripts

A Tivoli Storage Manager script is one or more commands that are stored as an object in the database. You can run a script from an administrative client, the web interface, or the server console. You can also include it in an administrative command schedule to run automatically. See Tivoli Storage Manager Server Scripts for details. You can define a script that contains one or more SELECT commands. Tivoli Storage Manager is shipped with a file that contains number of sample scripts. The file, scripts.smp, will be in the server directory. To create and store the scripts as objects, you can issue the RUNFILE command during installation:

> dsmserv runfile scripts.smp

You can also run it as a macro from an administrative command line client:

macro scripts.smp

The sample scripts file contains TSM commands. These commands delete any scripts with the same names as those to be defined and then define the scripts. The majority of the samples create SELECT commands, but others do such things as define and extend database volumes and back up storage pools. You can also copy and change the sample scripts file to create your own scripts.

Here are a few examples from the sample scripts file:

def script q_inactive_days '/* ------------------------------------------*/'
upd script q_inactive_days '/* Script Name:  Q_INACTIVE                  */'
upd script q_inactive_days '/* Description: Display nodes that have not  */'
upd script q_inactive_days '/*              accessed TSM for a           */'
upd script q_inactive_days '/*              specified number of days     */'
upd script q_inactive_days '/* Parameter 1: days                         */'
upd script q_inactive_days '/* Example:     run q_inactive_days 5        */'
upd script q_inactive_days '/* ------------------------------------------*/'
upd script q_inactive_days "select node_name,lastacc_time from nodes where -"
upd script q_inactive_days " cast((current_timestamp-lastacc_time)days as -"
upd script q_inactive_days " decimal) >= $1 "
 
/* Define a DB volume and extend the database                           */
 
def script def_db_extend '/*  -----------------------------------------*/'
upd script def_db_extend '/*  Script Name:  DEF_DB_EXTEND              */'
upd script def_db_extend '/*  Description: Define a database volume,   */'
upd script def_db_extend '/*               and extend the database     */'
upd script def_db_extend '/*  Parameter 1: db volume name              */'
upd script def_db_extend '/*  Parameter 2: extension megabytes         */'
upd script def_db_extend '/*  Example:  run def_db_extend VOLNAME 12   */'
upd script def_db_extend '/*  -----------------------------------------*/'
upd script def_db_extend ' def dbv  $1 '
upd script def_db_extend ' if (rc_ok) extend db $2'
upd script def_db_extend ' if (warning, error) q db f=d'

Canceling a SELECT Command

If a SELECT command will require a significant amount of resources, TSM asks if you want to continue. You can cancel the command at that time. Cancel the command from the console session or an administrative client session.

Controlling the Format of SELECT Results

TSM provides commands to control the format of results of SELECT commands. You can control:

Note:Using the SET commands to change these settings keeps the settings in effect only for the current administrative client session. You can query these settings by using the QUERY SQLSESSION command.

Querying the SQL Activity Summary Table

You can query the SQL activity summary table to view statistics about each client session and server process. For a listing of the column names and their descriptions from the activity summary table, enter the following command:

select colname,remarks from columns where tabname='summary'  

Here are a few example queries of the activity summary table.

You can determine how long to keep information in the summary table. For example, to keep the information for 5 days, enter the following command:

set summaryretention 5

To keep no information in the table, specify a value of 0.

Creating Output for Use by Another Application

You can redirect the output of SELECT commands to a file in the same way as you would redirect the output of any command. When redirecting this output for use in another program (for example, a spreadsheet or database program), write the output in a format easily processed by the program to be used.

Two standard formats for tabular data files are comma-separated values (CSV) and tab-separated values (TSV). Most modern applications that can import tabular data can read one or both of these formats.

Use the administrative client command line options -COMMADELIMITED or -TABDELIMITED to select one of these formats for tabular query output. All tabular output during the administrative session will be formatted into either comma-separated or tab-separated values. For details about using command line options, see the Administrator's Reference.

The use of command output redirection and one of the delimited output format options lets you create queries whose output can be further processed in other applications. For example, based on the output of a SELECT command, a spreadsheet program could produce graphs of average file sizes and file counts summarized by type of client platform.

For details about redirecting command output, see the Administrator's Reference.


[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]