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 to query the database and display the results.
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 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.
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.
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. |
To help you find what information is available in the database, Tivoli Storage Manager provides three system catalog tables:
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 | +--------------------------------------------------------------------------------+
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 | | | +--------------------------------------------------------------------------------+
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 DSMSERV 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'
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.
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. |
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.
select * from summary
The result might look like this:
START_TIME: 1999-07-22 19:32:00.000000 END_TIME: 1999-07-22 19:32:56.000000 ACTIVITY: BACKUP NUMBER: 43 ENTITY: DWE COMMMETH: Named Pi ADDRESS: EXAMINED: 7 AFFECTED: 7 FAILED: 0 BYTES: 2882311 IDLE: 51 MEDIAW: 0 PROCESSES: 1 SUCCESSFUL: YES ANS8002I Highest return code was 0.
select * from summary where start_time>= '1999-09-24 00:00'
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.
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.