Customizing queries using the SELECT command

With the SELECT command, you can customize a wide variety of queries.

About this task

This section shows two examples.

Example 1: Find the number of nodes by type of operating system by issuing the following command:

select platform_name,count(*) as "Number of Nodes" from nodes
group by platform_name

This command gives results like the following:

PLATFORM_NAME     Number of Nodes
-------------     ---------------
OS/2                           45
AIX                            90
Windows                        35

Example 2: For all active client sessions, determine how long they have 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

This command gives results like the following:

                 Session: 24
                  Client: ALBERT
                   State: Run
            Elapsed Time: 4445.000000
       Bytes sent/second: 564321.9302768451
   Bytes received/second: 0.0026748857944
 
                 Session: 26
                  Client: MILTON
                   State: Run
            Elapsed Time: 373.000000
       Bytes sent/second: 1638.5284210992221
   Bytes received/second: 675821.6888561849

Tip: When issuing SELECT * FROM DB statements, the output column PHYSICAL_VOLUMES is included for legacy compatibility purposes only. The number of physical volumes listed represents the DBSPACE locations defined to the server.

For example:

          DATABASE_NAME: mgsA62
      TOT_FILE_SYSTEM_MB: 511872
        USED_DB_SPACE_MB: 448
           FREE_SPACE_MB: 452802
               PAGE_SIZE: 16384
             TOTAL_PAGES: 32772
            USABLE_PAGES: 32636
              USED_PAGES: 24952
              FREE_PAGES: 768
          BUFF_HIT_RATIO: 99.7
          TOTAL_BUFF_REQ: 385557
           SORT_OVERFLOW: 0
         LOCK_ESCALATION: 0
           PKG_HIT_RATIO: 99.8
              LAST_REORG: 
          FULL_DEV_CLASS: 
         NUM_BACKUP_INCR: 0
        LAST_BACKUP_DATE: 
        PHYSICAL_VOLUMES: 1