Tivoli Header

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 SELECT command is a subset of the SQL92 and SQL93 standards.

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

Using the ODBC Driver

Tivoli Storage Manager provides an ODBC driver for Windows. 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 database through an ODBC application, you must log on to the server (the defined data source). Use the name and password of a registered 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 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.

Note:
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. See Exhausting Temporary Table Storage for details.

Learning What Information Is Available: System Catalog Tables

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.

You can issue the SELECT command to query these tables to determine the location of the information that you want. 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 they have 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                                     |
|                                                                                |
+--------------------------------------------------------------------------------+

Exhausting Temporary Table Storage

SQL SELECT queries run from temporary table storage in the database. At least a 4MB partition must be available in the database for this purpose. Without this partition, temporary table storage space will become exhausted, and the SELECT query will no longer run.

To determine how much temporary table storage space is available in your database, issue the QUERY DB command. The server displays a report, like the following:

+--------------------------------------------------------------------------------+
|Available Assigned   Maximum   Maximum    Page     Total      Used %Util  Max.  |
|    Space Capacity Extension Reduction    Size     Pages     Pages       %Util  |
|     (MB)     (MB)      (MB)      (MB) (bytes)                                  |
|--------- -------- --------- --------- ------- --------- --------- ----- -----  |
|       8        4         4         0   4,096     1,024        94   9.3   9.2   |
+--------------------------------------------------------------------------------+

Check the value in the Maximum Reduction field. If this field shows a value of at least 4MB, you can perform SELECT queries.

If the Maximum Reduction value is below 4MB, you will not be able to perform SELECT queries. The database is either full or fragmented.

Note:
Complex SELECT queries (for example, those including the ORDER BY clause, the GROUP BY clause, or the DISTINCT operator) may require more than 4MB temporary table storage space.

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 a number of sample scripts. The file, scripts.smp, is in the server directory. To create and store the scripts as objects in your server's database, issue the DSMSERV RUNFILE command during installation:

> dsmserv runfile scripts.smp

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

macro scripts.smp

The sample scripts file contains TSM commands. These commands first delete any scripts with the same names as those to be defined, 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, the server 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

Tivoli Storage Manager 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 ]