Select command with multiple tables

[scottgassTSM]

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]

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

 

[scottgassTSM]

Thank You!!!! Worked like a charm.

 

[scottgassTSM]

How would I add the number of nodes where lastacc_time >current_timestamp -2 days?

 

[PJ]

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

 

[scottgassTSM]

This would work, however, I want to keep the number of total nodes, but also report on the number of active nodes.

 

[scottgassTSM]

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.

 

[scottgassTSM]

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]

Just delete the unwanted DISTINCT from the command.

PJ

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

 

[jideck]

some practical difference how distinct works in reality:

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