====================================
Inner and Outer Join Example
====================================
Part 1 - Will return the columns from two tables into a third temporary table
Select -
varchar(o.node_name,45) ENTITY, -
cast(s.SUM_AFFECTED as dec(12,0)) AFFECTED,
cast(s.SUM_BYTES/1073741824 as decimal(12,2)) GB_BACKED_UP, -
cast(o.SUM_NUM_FILES as dec(15,0)) TOTAL_FILES_STORED, -
cast(o.SUM_LOGICAL_MB/1024 as dec(12,2)) GB_TSM_OCCUPANCY -
from -
Part 2 - Gather two columns from occupancy
select node_name, -
sum(NUM_FILES) SUM_NUM_FILES, -
sum(LOGICAL_MB) SUM_LOGICAL_MB -
from occupancy -
group by node_name
Part 3 - Gather three columns from summary for BACKUP and ARCHIVES for last 30 days
select varchar(entity,45) ENTITY, -
sum(bytes/1073741824) SUM_BYTES, -
sum(affected) SUM_AFFECTED -
from summary -
where -
end_time>=(current_timestamp - 30 days) -
and activity in ('BACKUP','ARCHIVE') -
group by entity
select -
varchar(o.node_name,45) ENTITY, -
cast(s.SUM_AFFECTED as dec(12,0)) AFFECTED, -
cast(s.SUM_BYTES as decimal(12,2)) GB_BACKED_UP, -
cast(o.SUM_NUM_FILES as dec(15,0)) TOTAL_FILES_STORED, -
cast(o.SUM_LOGICAL_MB/1024 as dec(12,2)) GB_TSM_OCCUPANCY -
from -
(select node_name, -
sum(NUM_FILES) SUM_NUM_FILES, -
sum(LOGICAL_MB) SUM_LOGICAL_MB -
from occupancy -
group by node_name) o, -
(select entity, -
sum(bytes/1073741824) SUM_BYTES, -
sum(affected) SUM_AFFECTED -
from summary -
where -
end_time>=(current_timestamp - 30 days) -
and activity in ('BACKUP','ARCHIVE') -
group by entity) s -
where ENTITY in (select node_name from nodes where domain_name<>'MN-STD-POL-DOM') -
and o.node_name=s.entity -
order by ENTITY
The generated report shows the total amount of files backed up, GB backed up for the last 30 days while also showing the total files stored in TSM and the nodes current total occupancy in GB.
ENTITY AFFECTED GB_BACKED_UP TOTAL_FILES_STORED GB_TSM_OCCUPANCY
---------- --------------- --------------- ------------------- -----------------
DORPORA05 920 47513.00 8354 73263.60
IXPFLDNAS 60792 27.00 15443947 9434.27
ILDNASJ 12680 5.00 1972680 715.60
ILDNASK 8 0.00 83004 197.48
ILDNASL 10739 4.00 1649434 574.51
ILDNASM 45448 25.00 1221367 1722.03
ILDNASM 4386 7.00 1122826 557.64
ILDNASN 5 0.00 99769 46.52
ILDNASO 1688 0.00 459683 138.16
ILDNASP 9369 11.00 1402332 1623.92
ILDNASR 28787 29.00 462479 457.15
ILDNASS 5 0.00 36771 17.51
ILDNAST 6329 21.00 982728 1038.08
ILDNASU 6848 19.00 1548369 671.68
ILDNASV 18197 48.00 999905 1333.92
ILDNASW 5 0.00 245366 116.89
RSD1W030S 59979 7.00 5207060 547.23
RSD1W031S 3444 2.00 5070450 473.15
RSD1W241S 6879 47.00 517358 1782.92
RSD1W242S 6478 51.00 995006 2109.45
RSD1W266S 4377 7.00 492810 184.69
RSD1W267S 5700 3.00 328391 100.95
RSD1W320C 40881 12.00 2692541 576.50
RSD1W341C 8194 55.00 456567 1120.11
RSD1W342C 17003 7.00 625487 281.84
RSD1W343C 4628 2.00 298699 79.63
RSD1W504SV 1941 12.00 42444 551.35
RSD1W505SV 1079 105.00 38524 4033.06
RSD1W5067S 683 64.00 22389 1343.82
RSD1W506SV 489 0.00 14349 4.53
RSD1W508SV 2662 31.00 79340 699.12
RSD1W509SV 4017 74.00 139073 1617.35
RSD1W510SV 698 544.00 18086 9161.23
RSD1W511SV 1365 295.00 65303 6588.06