![]() |
![]() |
Use this command to create and format a customized query of the Tivoli Storage Manager database. On Tivoli Storage Manager, 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(R) 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, Tivoli Storage Manager provides three system catalog tables:
You can issue the SELECT command to query these tables to determine the location of the information that you want.
Notes:
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
The SELECT command supports the following expressions, clauses, functions,
and predicates:
|
|
|
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.
+--------------------------------------------------------------------------------+ | TABSCHEMA: SERVER1 | | TABNAME: ACTLOG | | CREATE_TIME: 1999-05-01 07:39:06 | | COLCOUNT: 10 | |INDEX_COLCOUNT: 1 | | UNIQUE_INDEX: FALSE | | REMARKS: Server activity log | | | | TABSCHEMA: SERVER1 | | TABNAME: ADMIN_SCHEDULES | | CREATE_TIME: 1995-05-01 07:39:06 | | COLCOUNT: 14 | |INDEX_COLCOUNT: 1 | | UNIQUE_INDEX: TRUE | | REMARKS: Administrative command schedules | | | | TABSCHEMA: SERVER1 | | TABNAME: ADMINS | | CREATE_TIME: 1995-05-01 07:39:06 | | COLCOUNT: 15 | |INDEX_COLCOUNT: 1 | | UNIQUE_INDEX: TRUE | | REMARKS: Server administrators | | | | TABSCHEMA: SERVER1 | | TABNAME: ARCHIVES | | CREATE_TIME: 1995-05-01 07:39:06 | | COLCOUNT: 10 | |INDEX_COLCOUNT: 5 | | UNIQUE_INDEX: FALSE | | REMARKS: Client archive files | +--------------------------------------------------------------------------------+
select node_name from nodes where locked='YES' select admin_name from admins where locked='YES'
select node_name from nodes where invalid_pw_count <>0 select admin_name from admins where invalid_pw_count <>0
select node_name from nodes where domain_name='STANDARD' and node_name not in (select node_name from associations where domain_name='STANDARD' and schedule_name='DAILYBACKUP')
select admin_name from admins where upper(system_priv) <>'NO' or upper(policy_priv) <>'NO'
select date_time,msgno,message from actlog where severity='E' or severity='W'
select schedule_name from admin_schedules where chg_admin='JAKE'
select schedule_name,priority from admin_schedules order by priority
select domain_name,set_name,class_name from ar_copygroups where retver='NOLIMIT' or cast(retver as integer) >365
select domain_name,set_name,class_name from bu_copygroups where verexists ='NOLIMIT' or cast(verexists as integer)>5
select node_name from nodes where option_set='SECURE'
select domain_name,num_nodes from domains
Attention: This command could take a long time to complete.
select node_name,count(*) from archives group by node_name
select node_name from auditocc where spacemg_mb <>0
select count(*) from volumes where stgpool_name='TAPE' and upper(status)='FULL' and pct_utilized < 50
select node_name, count(*) as "Files" from backups where class_name='DAILY' and node_name in (select node_name from nodes where domain_name='STANDARD') group by node_name
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
+--------------------------------------------------------------------------------+ | 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 | +--------------------------------------------------------------------------------+
select process_num as "Number", process, current_timestamp-start_time as "Elapsed Time", (cast(files_processed as decimal(18,0)) / cast((current_timestamp-start_time)seconds as decimal(18,0))) as "Files/second", (cast(bytes_processed as decimal(18,0)) / cast((current_timestamp-start_time)seconds as decimal(18,0))) as "Bytes/second" from processes
+--------------------------------------------------------------------------------+ | Number: 1 | | PROCESS: Expiration | | Elapsed Time: 0 00:24:36.000000 | | Files/second: 6.3216755870092 | | Bytes/second: 0.0000000000000 | | | +--------------------------------------------------------------------------------+
select platform_name,count(*) as "Number of Nodes" from nodes group by platform_name
+--------------------------------------------------------------------------------+ |PLATFORM_NAME Number of Nodes | |------------- --------------- | | | | AIX 6 | | SunOS 27 | | Win32 14 | | | +--------------------------------------------------------------------------------+
select node_name, count(*) as "number of filespaces" from filespaces group by node_name order by 2
+--------------------------------------------------------------------------------+ |NODE_NAME number of filespaces | |------------------ -------------------- | | ALBERT 2 | | MILTON 2 | | BARNEY 3 | | SEBASTIAN 3 | | MAILHOST 4 | | FALCON 4 | | WILBER 4 | | NEWTON 4 | | JEREMY 4 | | WATSON 5 | | RUSSELL 5 | | | +--------------------------------------------------------------------------------+
Related Commands
Table 250. 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. |