IBM Tivoli Storage Manager, Version 7.1

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.

To help you find what information is available, Tivoli Storage Manager 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.

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, see the DB2 Information Center: http://pic.dhe.ibm.com/infocenter/db2luw/v10r5. Search on Select-statement for usage guidelines.

Important: The appropriate syntax for the timestamp Select statement is:

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:

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

Attention: This command might take a long time to complete.
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

Note: This command takes significant time and resources to complete.
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

Note: Expiration does not report the number of bytes processed.
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. 
PVU per node = number of processors per node * processor type * 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.
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
Parent topic: Administrative commands

Feedback