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 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. If an SQL query requires significant server time or resources, you will be asked to confirm your request.

  3. Any SQL query requires at least one free 4MB partition. The more processing that a query requires, the more free space is required. Complicated or lengthy queries can affect TSM server performance.

  4. 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.

>>-SELECT------------------------------------------------------->
 
      .-,------------------------------------------------.
      V                                                  |
>---------+-value_expression---+---+------------------+--+------>
          '-aggregate_function-'   '-AS--column_name--'
 
           .-,-----------------------.
           V                         |
>----FROM-----table_name--subquery---+---+-------------------+-->
                                         '-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. Valid values are:

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.

subquery
Specifies another SELECT command from which the selected columns are to be taken.

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 224. 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 ]