• Please help support our sponsors by considering their products and services.
    Our sponsors enable us to serve you with this high-speed Internet connection and fast webservers you are currently using at ADSM.ORG.
    They support this free flow of information and knowledge exchange service at no cost to you.

    Please welcome our latest sponsor Tectrade . We can show our appreciation by learning more about Tectrade Solutions
  • Community Tip: Please Give Thanks to Those Sharing Their Knowledge.

    If you receive helpful answer on this forum, please show thanks to the poster by clicking "LIKE" link for the answer that you found helpful.

  • Community Tip: Forum Rules (PLEASE CLICK HERE TO READ BEFORE POSTING)

    Click the link above to access ADSM.ORG Acceptable Use Policy and forum rules which should be observed when using this website. Violators may be banned from this website. This notice will disappear after you have made at least 3 posts.

Select command with multiple tables

#1
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.
 

alimirza

ADSM.ORG Senior Member
#2
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:

PJ

ADSM.ORG Senior Member
#5
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
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.
 
#8
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?
 

PJ

ADSM.ORG Senior Member
#9
Just delete the unwanted DISTINCT from the command.

PJ

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

jideck

ADSM.ORG Member
#10
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
 

Advertise at ADSM.ORG

If you are reading this, so are your potential customer. Advertise at ADSM.ORG right now.

UpCloud high performance VPS at $5/month

Get started with $25 in credits on Cloud Servers. You must use link below to receive the credit. Use the promo to get upto 5 month of FREE Linux VPS.

The Spectrum Protect TLA (Three-Letter Acronym): ISP or something else?

  • Every product needs a TLA, Let's call it ISP (IBM Spectrum Protect).

    Votes: 9 20.5%
  • Keep using TSM for Spectrum Protect.

    Votes: 23 52.3%
  • Let's be formal and just say Spectrum Protect

    Votes: 8 18.2%
  • Other (please comement)

    Votes: 4 9.1%

Forum statistics

Threads
31,055
Messages
132,235
Members
21,274
Latest member
ctauber
Top