Tivoli Storage Manager for Windows: Administrator's Reference

SELECT (Perform an SQL Query of the TSM Database)

Use this command to create and format a customized query of the TSM database. On TSM, this command uses a subset of the SQL92 and SQL93 standards.

The SELECT command is consistent with most relational database products, such as LOTUS 1-2-3(R) and Microsoft Access. It presents server information in the form of relational tables containing rows (records) and columns (fields).

To help you find what information is available, 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 type and the ordering of those values for columns that have an enumerated data type (SQL93).

You can issue the SELECT command to query these tables to determine the location of the information that you want.

Notes:

  1. To stop SELECT processing after it starts, cancel the administrative session that issued the command. Cancel the session from either the server console or another administrative session.

  2. Complicated or lengthy queries can affect TSM server performance.

  3. If an SQL query requires significant server time or resources, you will be asked to confirm your request.

  4. Because SQL SELECT queries run from temporary table storage stored in the database, at least one 4MB partition must be set aside in the database. Without this partition, temporary table storage space will become exhausted and the SELECT query will no longer run. Some conditions, such as the ORDER BY clause, GROUP BY clause, and DISTINCT operator, may require additional temporary table space.

  5. To find how much table storage space is available in your database, issue the QUERY DB command and view the Maximum Reduction parameter. To learn how to increase your table storage space, see the Exhausting Temporary Table Storage section in the Monitoring the Server chapter of Administrator's Guide for details.

  6. You cannot issue the SELECT command from a server console.

Privilege Class

Any administrator can issue this command.

Syntax

Attention: The following syntax diagram and parameter list include the more common parameters of the SELECT command. Other parameters are available. See any standard SQL documentation. However, these parameters are not supported by the SELECT command: UNION, INTERSECT, EXCEPT, and correlated subqueries. Also, subqueries (SELECT statements) are not supported in a FROM clause but can be used as predicates.

           .-,-------------------------------------------.
           V                                             |
>>-SELECT----+-value_expression---+--+-----------------+-+------>
             '-aggregate_function-'  '-AS--column_name-'
 
         .-,----------.
         V            |
>--FROM----table_name-+--+------------------+------------------->
                         '-WHERE--predicate-'
 
>--+------------------------------------------+----------------->
   '-GROUP BY--+------------+--.--column_name-'
               '-table_name-'
 
>--+-------------------+---------------------------------------->
   '-HAVING--predicate-'
 
>--+----------------------------------------------+------------><
   |             .-,-----------------------.      |
   |             V                         |      |
   '-ORDER BY--+---output_column--+------+-+----+-'
               |                  +-ASC--+      |
               |                  '-DESC-'      |
               | .-,--------------------------. |
               | V                            | |
               '---positive_integer--+------+-+-'
                                     +-ASC--+
                                     '-DESC-'
 
 

Parameters

value_expression or aggregate_function (Required)
Specifies the columns to be returned. Valid values are:

value_expression
Specifies the set of source columns to be returned. In the simplest form, this is a list of the columns specified in the FROM clause. In a more advanced form, it can include DISTINCT, CAST, or CASE expressions or subqueries.

aggregate_function
Specifies a function that extracts a single value from groups of column names. For example, AVG, COUNT, MAX, or SUM.

AS column_name
Specifies the column title to display. The default is to display the value expression or aggregate function. The default column name for expressions is UNNAMED(N).

FROM (Required)
Specifies where to find data in the database. The only valid value is:

table_name
Specifies one or more source tables from which to extract the query rows and columns. You can find these names by querying SYSCAT.TABLES. If you specify two or more tables, you are requesting a JOIN of the tables. You can specify the columns for the JOIN criteria in the WHERE parameter. Or the tables can be joined by matching every row from one table to every row from another table.

WHERE predicate
Specifies that only certain rows are displayed based on criteria in the predicate. This is an optional parameter. You can use the AND, OR, and NOT operators to string predicates together.

GROUP BY column_name
Specifies groups of rows to be formed if aggregate functions (for example, AVG, COUNT, MAX, SUM) are specified. This is an optional parameter.

HAVING predicate
Specifies a condition to be used to filter the extracted values before displaying them. This is an optional parameter.

ORDER BY
Specifies how output sorts for display. You can specify column names or numeric positions and whether you want the sort in ascending or descending order. This is an optional parameter. Valid values are:

output_column
Specifies order by column. The columns are sorted according to the order in which they are specified.

positive_integer
Specifies order by the numeric position of the columns. The columns are sorted according to the order in which they are specified.

ASC
Specifies that the columns are sorted in ascending order.

DSC
Specifies that the columns are sorted in descending order.

The SELECT command supports the following expressions, clauses, functions, and predicates:

ALL

ANY

AVG

AS

BETWEEN

CASE

CAST

COUNT

CURRENT_DATE

CURRENT_TIME

CURRENT_TIMESTAMP


CURRENT_USER

DISTINCT

EXISTS

EXTRACT

FROM

GROUP BY

HAVING

IN

JOIN

LIKE


MAX

MIN

NULL

ORDER BY

POSITION

SOME

SUBSTRING

SUM

TRIM

WHERE


Examples

The SELECT command lets you customize a wide variety of queries. To give you an idea of what you can do with the command, this section includes many examples. There are, however, many more possibilities.

Note:
Query output is shown only for the more complex commands to illustrate formatting.

Related Commands

Table 248. Commands Related to SELECT

Command Description
QUERY SQLSESSION Displays the current settings of the SQL formatting commands.
SET SQLDATETIMEFORMAT Controls the formatting of date and time in the display of SQL queries.
SET SQLDISPLAYMODE Controls the column width in the display of SQL queries.
SET SQLMATHMODE Controls how decimal numbers are displayed in SQL queries.


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