1. Please help support our sponsors by considering their products and services.
    Our sponsors enable us to maintain high-speed Internet connection and fast webservers.
    They support this free information and knowledge exchange forum service at no cost to you.

    Please welcome our latest sponsor Tectrade . We can show our appreciation by learning more about Tectrade Solutions

Select command with multiple tables

Discussion in 'Scripting' started by scottgassTSM, Jun 27, 2007.

  1. scottgassTSM

    scottgassTSM ADSM.ORG Member

    Joined:
    Jul 30, 2006
    Messages:
    77
    Likes Received:
    0
    I'm looking for a select command that would pull the number of nodes from the table "nodes", but also include he total TB of the same TSM server from the table "occupancy". This is AIX TSM 5.4.
     
  2.  
  3. alimirza

    alimirza ADSM.ORG Senior Member

    Joined:
    Aug 2, 2005
    Messages:
    417
    Likes Received:
    8
    Occupation:
    Storage Admin
    select distinct(nodes.collocgROUP_name),distinct(volumeusage.volume_name) from nodes,volumeusage where nodes.collocgroup_name='UNIX-ORACLE-NODES'



    tsm: QPTSM1>select count(DISTINCT(nodes.node_name)) as "number of Nodes", sum(DISTINCT(occupancy.physical_mb))/1024/1024 AS "Terabytes Stored" from nodes,occup
    ancy
    ANR2963W This SQL query may produce a very large result table, or may require a
    significant amount of time to compute.

    Do you wish to proceed? (Yes (Y)/No (N)) Y

    number of Nodes
    ---------------
    254


    Terabytes Stored
    ---------------------------------
    536.55
     
    Last edited: Jun 28, 2007
  4. scottgassTSM

    scottgassTSM ADSM.ORG Member

    Joined:
    Jul 30, 2006
    Messages:
    77
    Likes Received:
    0
    Thank You!!!! Worked like a charm.
     
  5. scottgassTSM

    scottgassTSM ADSM.ORG Member

    Joined:
    Jul 30, 2006
    Messages:
    77
    Likes Received:
    0
    How would I add the number of nodes where lastacc_time >current_timestamp -2 days?
     
  6. PJ

    PJ ADSM.ORG Senior Member

    Joined:
    Nov 18, 2005
    Messages:
    1,071
    Likes Received:
    4
    Location:
    LU Germany
    select count(DISTINCT(nodes.node_name)) as "number of Nodes", sum(DISTINCT(occupancy.physical_mb))/1024/1024 as "Terabytes Stored" from nodes,occupancy where nodes.node_name in (select node_name from nodes where lastacc_time>current_timestamp-(2)days)

    PJ
     
  7. scottgassTSM

    scottgassTSM ADSM.ORG Member

    Joined:
    Jul 30, 2006
    Messages:
    77
    Likes Received:
    0
    This would work, however, I want to keep the number of total nodes, but also report on the number of active nodes.
     
  8. scottgassTSM

    scottgassTSM ADSM.ORG Member

    Joined:
    Jul 30, 2006
    Messages:
    77
    Likes Received:
    0
    I figured it out. I used the following command:

    select count(DISTINCT(nodes.node_name)) as "TOTAL NODES", count(DISTINCT(occupancy.node_name)) as "ACTIVE NODES", sum(DISTINCT(occupancy.physical_mb))/1024/1024 as "TOTAL TB" from nodes,occupancy where nodes.node_name in (select node_name from nodes where lastacc_time>current_timestamp-(2)days)

    Thanks to PJ for helping me out with the second half of the statement.
     
  9. scottgassTSM

    scottgassTSM ADSM.ORG Member

    Joined:
    Jul 30, 2006
    Messages:
    77
    Likes Received:
    0
    One more question on this command. When i run the command, it doesn't give me an accurate reading on Total TB. It appears when I run the command for Total TB alone with the DISTINCT, it reports 13.42 TB, and when I run the command without the DISTINCT, it reports 16.11 TB. Any ideas on how I can get the command to work without using DISTINCT?
     
  10. PJ

    PJ ADSM.ORG Senior Member

    Joined:
    Nov 18, 2005
    Messages:
    1,071
    Likes Received:
    4
    Location:
    LU Germany
    Just delete the unwanted DISTINCT from the command.

    PJ

    P.S. (I guess it slipped in there by mistake anyway ;) )
     
  11. jideck

    jideck ADSM.ORG Member

    Joined:
    Oct 10, 2006
    Messages:
    83
    Likes Received:
    0
    Occupation:
    Unix worker
    Location:
    Stockholm
    some practical difference how distinct works in reality:




      • without distinct : select server_name from status
        getting many lines because there is one status for every node so output will like
        TSM_INST1
        TSM_INST1
        TSM_INST1
        TSM_INST1
        TSM_INST1
        TSM_INST1

        TSM_INST1
        TSM_INST1
        TSM_INST1
      • And now with distinct:
        SELECT distinct(server_name) from status
        and answer will be

        SERVER_NAME
        ---------------------------------------------------------------------------------------------------------------------------------
        TSM_INST1 (only once )
      • And it works similar with all other cases however with count or sum of something You will need 'group by' to get right answer:
    Protect: TSM_INST1> SELECT distinct(status.SERVER_NAME),CAST(FLOAT(SUM(auditocc.BACKUP_MB))/1024/1024 as DEC (8,1)) ,' TB' from STATUS,auditocc group by status.SERVER_NAME

    and answer will look like (started with -tab option)
    TSM_INST1 1878.3 TB
     

Share This Page