SELECT (Perform an SQL query of the Tivoli Storage Manager database)
Use the SELECT command to create and format a customized query of the IBM® Tivoli® Storage Manager database.
Tivoli Storage Manager provides an SQL interface to a DB2® program. Restrictions and guidelines for handling SQL queries are handled directly by DB2.
- 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).
Usage notes
You cannot issue the SELECT command from a server console.
Because the select command does not lock and unlock records, contention for a record can cause the server to erroneously issue message ANR2034E: SELECT: No match found using this criteria. Check your selection criteria, and if you believe that it is correct, try the command again.
To stop the processing of a SELECT command after it starts, cancel the administrative session from which the command was issued. Cancel the session from either the server console or another administrative session.
Temporary table spaces are used to process SQL queries within DB2. Inadequate temporary space can cause SQL queries to fail.
To export output to a comma-separated file for import into a spreadsheet, use -comma and > command-line options on the dsmadmc command.
Privilege class
Any administrator can issue this command.
Syntax
For SELECT statement syntax and guidelines, search the DB2 product information.
SELECT * FROM SUMMARY WHERE ACTIVTY='EXPIRATION' AND START_TIME >'2009-05-10 00:00:00' AND START_TIME <'2009-05-11 23:23:23'
List of examples
The SELECT command is used to 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. Query output is shown only for the more complex commands to illustrate formatting.
The following list summarizes the example SELECT commands:- List administrator user ID passwords that are authenticated with an external LDAP directory server
- List available tables
- List client nodes and administrative clients that are currently locked from server access
- List client nodes and administrative clients that have not specified the correct password lately
- List nodes in the standard policy domain that are not associated with the daily backup schedule DAILYBACKUP
- List the administrators that have policy authority
- List type E (ERROR) or W (WARNING) messages that have been issued in the time period for which activity log records have been maintained
- List the administrative schedules that have been defined or altered by administrator JAKE
- List the relative administrative schedule priorities
- List the management classes that have an archive copy group with a retention period greater than 365 days
- List the client nodes that are in each policy domain
- Count how many files have been archived from each node
- List the clients that are using space management
- Determine how many volumes would be reclaimed if the reclamation threshold is changed to 50 percent for storage pool TAPE
- Determine how many backup files would be affected for each node if the DAILY management class in the STANDARD policy domain is changed or deleted
- For all active client sessions, determine how long have they been connected and their effective throughput in bytes per second
- Determine how long the current background processes have been running and determine their effective throughput in time and files per second
- Count the number of client nodes are there for each platform type
- Count the number of file spaces each client node has and list the client nodes ascending order
- Obtain statistical information for calculating the number of off-site volumes that have their space reclaimed during reclamation of a storage pool
- Obtain PVU estimate detail records
- Obtain information about the node roles
- Obtain information about status
Example: List administrator user IDs that authenticate to the Tivoli Storage Manager server
List all the administrator user IDs whose passwords authenticate with the Tivoli Storage Manager server:select admin_name from admins where
authentication=local
Example: List available tables
List all the tables available for querying the IBM Tivoli Storage Manager database.select * from syscat.tables
ABSCHEMA: 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
Example: List client nodes and administrative clients that are currently locked from server access
select node_name from nodes where locked='YES'
select admin_name from admins where locked='YES'
Example: List client nodes and administrative clients that have not specified the correct password lately
select node_name from nodes where invalid_pw_count <>0
select admin_name from admins where invalid_pw_count <>0
Example: List nodes in the standard policy domain that are not associated with the daily backup schedule DAILYBACKUP
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')
Example: List the administrators who have policy authority
select admin_name from admins where
upper(system_priv) <>'NO'
or upper(policy_priv) <>'NO'
Example: List type E (ERROR) or W (WARNING) messages that have been issued in the time period for which activity log records have been maintained
select date_time,msgno,message from actlog
where severity='E' or severity='W'
Example: List the administrative schedules that have been defined or altered by administrator JAKE
select schedule_name from admin_schedules
where chg_admin='JAKE'
Example: List the relative administrative schedule priorities
select schedule_name,priority from admin_schedules order
by priority
Example: List the management classes that have an archive copy group with a retention period greater than 365 days
select domain_name,set_name,class_name from ar_copygroups
where retver='NOLIMIT' or cast(retver as integer) >365
Example: List the management classes that specify more than five backup versions
select domain_name,set_name,class_name from bu_copygroups
where verexists ='NOLIMIT' or
cast(verexists as integer)>5
Example: List the client nodes that are using the client option set named SECURE
select node_name from nodes where option_set='SECURE'
Example: List the client nodes that are in each policy domain
select domain_name,num_nodes from domains
Example: Count how many files have been archived from each node
select node_name,count(*) from archives
group by node_name
Example: List the clients that are using space management
select node_name from auditocc where spacemg_mb <>0
Example: Determine how many volumes would be reclaimed if the reclamation threshold is changed to 50 percent for storage pool TAPE
select count(*) from volumes where stgpool_name='TAPE'
and upper(status)='FULL' and pct_utilized < 50
Example: Determine how many backup files would be affected for each node if the DAILY management class in the STANDARD policy domain is changed or deleted
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
Example: 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(second(current_timestamp-start_time) as decimal(18,0)))
as "Bytes sent/second",
(cast(bytes_received as decimal(18,0)) /
cast(second(current_timestamp-start_time) 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
Example: Determine how long the current background processes have been running and determine their effective throughput in time and files per second
select process_num as "Number",
process,
current_timestamp-start_time as "Elapsed Time",
(cast(files_processed as decimal(18,0)) /
cast(second(current_timestamp-start_time) as decimal(18,0)))
as "Files/second",
(cast(bytes_processed as decimal(18,0)) /
cast(second(current_timestamp-start_time) 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
Example: Count the number of client nodes for each platform type
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
Linux 20
Example: Count the number of file spaces each client node has and list the client nodes ascending order
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
Example: Obtain statistical information for calculating the number of off-site volumes that have their space reclaimed during reclamation of a storage pool.
select * from summary where activity='OFFSITE RECLAMATION'
START_TIME: 2004-06-16 13:47:31.000000
END_TIME: 2004-06-16 13:47:34.000000
ACTIVITY: OFFSITE RECLAMATION
NUMBER: 4
ENTITY: COPYPOOL
COMMMETH:
ADDRESS:
SCHEDULE_NAME:
EXAMINED: 170
AFFECTED: 170
FAILED: 0
BYTES: 17821251
IDLE: 0
MEDIAW: 0
PROCESSES: 2
SUCCESSFUL: YES
VOLUME_NAME:
DRIVE_NAME:
LIBRARY_NAME:
LAST_USE:
COMM_WAIT:
NUM_OFFSITE_VOLS: 2
Example: Identify which storage pools contain data that was deduplicated by clients
select stgpool_name,has_client_dedup_data from stgpools
STGPOOL_NAME HAS_CLIENT_DEDUP_DATA
-------------------- --------------------
ADPOOL NO
ARCHIVEPOOL NO
BACKUPPOOL NO
COPYDEDUP NO
COPYNODEDUP NO
FILEPOOL YES
FILEPOOL2 NO
LANFREEFILEPOOL YES
SPACEMGPOOL NO
Example: Obtain information about the database
select * from db
DATABASE_NAME: TSMDB1
TOT_FILE_SYSTEM_MB: 2048000
USED_DB_SPACE_MB: 12576
FREE_SPACE_MB: 1576871
TOTAL_PAGES: 983044
USABLE_PAGES: 982908
USED_PAGES: 977736
FREE_PAGES: 5172
BUFF_HIT_RATIO: 96.2
TOTAL_BUFF_REQ: 53967
SORT_OVERFLOW: 0
LOCK_ESCALATION: 0
PKG_HIT_RATIO: 70.0
LAST_REORG: 2010-07-15 17:32:55.000000
FULL_DEV_CLASS: OUTFILE
NUM_BACKUP_INCR: 0
LAST_BACKUP_DATE: 2010-01-21 10:37:59.000000
PHYSICAL_VOLUMES: 0
PAGE_SIZE:
NUM_BACKUP_STREAMS: 4
Example: Obtain PVU estimate detail records
Generate the PVU estimate for a node named ACCTSRECSRV, which is used by the IBM Tivoli Storage Manager Extended Edition product.
select * from pvuestimate_details where node_name='ACCTSRECSRV'
PRODUCT: PRODTSMEE
LICENSE_NAME: MGSYSLAN
NODE_NAME: ACCTSRECSRV
LAST_USED: 2008-01-20 16:12:24.000000
TRYBUY: FALSE
PROC_VENDOR: IBM
PROC_BRAND: POWER5+ QCM
PROC_TYPE: 4
PROC_MODEL:
PROC_COUNT: 2
ROLE: SERVER
ROLE_OVERRIDE: USEREPORTED
ROLE_EFFECTIVE: SERVER
VALUE_UNITS: 50
VALUE_FROM_TABLE: YES
PVU: 100
SCAN_ERROR : NO
API_CLIENT: NO
PVU_AGNOSTIC: NO
HYPERVISOR: VMWARE
GUID: 01.2e.1c.80.e5.04-
.11.da.aa.ab.00.-
15.58.0b.d9.47
VERSION: 6
RELEASE: 3
LEVEL: 1
VENDOR_D: IBM(R)
BRAND_D: POWER5(TM) QCM
TYPE_D: Quad-core Module
MODEL_D: All Existing
Field descriptions
- PRODUCT
- Rollup of license types into products at the level presented in the QUERY PVUESTIMATE command. Possible values are PRODTSMEE, PRODTSM, PRODSSAM, PRODMAIL, PRODDB, PRODSYSB, PRODSPACE, PRODSAN, PRODERP, or blank.
- LICENSE_NAME
- The license assigned to this node.
- NODE_NAME
- The node name.
- LAST_USED
- Date and time the identified node last connected to the system under this license.
- TRYBUY
- Indicates if running under try and buy mode. Possible values are TRUE or FALSE.
- PROC_VENDOR
- Processor vendor name as reported by the client.
- PROC_BRAND
- Processor brand name as reported by the client.
- PROC_TYPE
- Processor type as reported by the client. This value also reflects the number of cores. Example values are 1=SINGLE CORE, 2=DUO CORE, and 4=QUAD CORE.
- PROC_MODEL
- Processor model as reported by the client.
- PROC_COUNT
- Processor quantity.
- ROLE
- Node role. Possible values are CLIENT, SERVER, or OTHER.
- ROLE_OVERRIDE
- Override value specified in the UPDATE NODE command.
- ROLE_EFFECTIVE
- Actual role based on the values in the ROLE and ROLE_OVERRIDE fields.
- VALUE_UNITS
- Assigned processor value unit (PVU) for the processor.
- PVU
- Calculated
PVU value.
where the processor type represents the number of cores, and the pvu value is the value defined for the processor type in the IBM PVU table.PVU per node = number of processors per node * processor type * pvu value
- VALUE_FROM_TABLE
- Flag that indicates whether the PVU was calculated based on the IBM PVU table. Possible values are YES or NO. If NO, a value of 100 is applied for each node defined as a server. If no role is defined for a node, the role of server is assumed for purposes of PVU calculation.
- SCAN_ERROR
- Flag that indicates whether license information was reported by client. Possible values are YES or NO.
- API_CLIENT
- Flag that indicates an API application. Possible values are YES or NO.
- PVU_AGNOSTIC
- Flag indicating that the client version release level is earlier than Tivoli Storage Manager V6.3. If the version is earlier than 6.3, valid PVU metrics are not expected. Possible values are YES or NO.
- HYPERVISOR
- Name of the virtual machine software as reported by the client.
- GUID
- Globally Unique Identifier (GUID) of the computer where the node is located. The GUID is obtained from the node table.
- VERSION
- Version of client.
- RELEASE
- Release of client.
- LEVEL
- Level of client.
- VENDOR_D
- Processor vendor display value from the PVU table.
- BRAND_D
- Processor brand display value from the PVU table.
- TYPE_D
- Processor type display value from the PVU table.
- MODEL_D
- Processor model display value from the PVU table.
Example: Obtain role and PVU-related information
The following example shows partial results for a selected node, including PVU-related information and role information. Possible roles are CLIENT, SERVER, or OTHER. PVU is calculated only for nodes defined as servers.
select * from nodes
ROLE: CLIENT
ROLE_O: USEREPORTED
PVENDOR: INTEL
PBRAND: INTEL
PTYPE: 4
PMODEL:
PCOUNT: 1
HYPERVISOR:
PAPI: NO
SCANERROR: NO