SELECT (Perform an SQL query of the IBM Storage Protect database)

Example: List administrator user IDs that authenticate to the IBM Storage Protect server

select admin_name from admins where 
authentication=local 

Example: List available tables

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, administrative clients, and servers that are using transitional session security

select node_name from nodes where session_security='Transitional'

select admin_name from admins where session_security='Transitional'

select server_name from servers where session_security='Transitional'

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 the administrators who are designated as approval administrators

select * from admins where cmd_approver='YES'

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 in each client node and list the client nodes in 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: Determine whether an object agent for object storage is on the server

select * from servers

       SERVER_NAME: SERVER1
          COMMMETH: TCPIP
        HL_ADDRESS: localhost
        LL_ADDRESS: 1500
       DESCRIPTION:
      ALLOWREPLACE: NO
         NODE_NAME:
      LASTACC_TIME: 2018-04-16 17:32:39.000000
            LOCKED: NO
       COMPRESSION: NO
        ARCHDELETE: YES
               URL:
         ORIG_DATE: 2018-04-16 17:32:39.000000
         REG_ADMIN: SERVER_CONSOLE
    LASTSESS_RECVD: 0
     LASTSESS_SENT: 0
LASTSESS_DURATION: 0.00000000000000E+000
LASTSESS_IDLEWAIT: 0.00000000000000E+000
LASTSESS_COMMWAIT: 0.00000000000000E+000
LASTSESS_MEDIAWAIT: 0.00000000000000E+000
  GRACE_DEL_PERIOD: 5
           PROFILE:
    SERVER_PWD_SET: No
SERVER_PSWET_TIME:
SERVER_INVALID_PWC:
    VVNODE_PWD_SET: No
     VV_PSWET_TIME:
    VV_INVALID_PWC:
  VALIDATEPROTOCOL: No
               SSL: No
  SESSION_SECURITY: Transitional
  TRANSPORT_METHOD: Unknown
    TRANSFERMETHOD: TCPIP
      OBJECT_AGENT: Yes 

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 Storage Protect Extended Edition product.

select * from pvuestimate_details where node_name='ACCTSRECSRV'

           PRODUCT: PRODEE
      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
         PRODUCT_D: IBM Storage Protect Extended Edition

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 

Example: Determine whether storage rules copy data from a source storage pool to a target storage pool

select * from stgrules where type='COPY'

Example: For Google Cloud Storage, determine the type of Google storage class

select * from stgpools where cloud type='GOOGLE'

                       STGPOOL_NAME: GOOGLEPOOL2
                           POOLTYPE: PRIMARY
                           DEVCLASS: 
                           STG_TYPE: CLOUD
                         CLOUD_TYPE: GOOGLE
                          CLOUD_URL: 
                           CLOUD_ID: 
                     CLOUD_LOCATION: OFFPREMISE
                  CLOUDSTORAGECLASS: DEFAULT 
       

Example: Obtain information about object client nodes

select NODE_NAME,NODETYPE,OBJECT_CLIENT_USERAGENT,OBJECT_CLIENT_TYPE from nodes

                   NODE_NAME: NODE4
                    NODETYPE: OBJECTCLIENT    
     OBJECT_CLIENT_USERAGENT: user-agent-string-spectrumprotectplus-node4
          OBJECT_CLIENT_TYPE: IBM Storage Protect Plus

Example: Obtain information about active background processes

select * from processes

     PROCESS_NUM: 4
         PROCESS: Replication Storage Rule REPLPHX
      START_TIME: 2021-06-23 14:03:09.000000
 FILES_PROCESSED: 8
 BYTES_PROCESSED: 52995
BYTES_TO_PROCESS:
           JOBID: 14
          STATUS: Storage Rule REPLPHX replicating to server PHOENIX-DR, target process 12,
                  target job 14 for node(s) NODE1, NODE2. File spaces complete: 3. File spaces
                  identifying and replicating: 0. File spaces replicating: 3. File spaces not
                  started: 0. Files current: 0. Files replicated: 8 of 22. Files updated: 0 of 0.
                  Files deleted: 0 of 0. Amount replicated: 52,995 bytes of 155 KB. Amount
                  transferred: 47,405 bytes. Elapsed time: 0 Days, 0 Hours, 3 Minutes.
  PROCESS_PARENT: