Backup Report with Occupancy

chad_small

ADSM.ORG Moderator
Joined
Dec 17, 2002
Messages
2,262
Reaction score
52
Points
0
Location
Gilbert, AZ
Website
www.tsmadmin.com
PREDATAR Control23

I am trying to create a 30 day report that gathers the sum of the data for specific nodes that shows the following

NODE NAME, FILES BACKED UP, TOTAL MB BACKED UP, TOTAL FILES IN TSM, CURRENT OCCUPANCY IN TSM

I am using the following select and the sum of the NUM_FILES and LOGICAL_MB is not accurate due to my join being off. If anyone knows what else I could do to make it accurate then I'd appreciate the help.

select -
varchar(a.node_name,45) as entity, -
cast(sum(b.bytes/1073741824) as decimal(12,2)) AS GB_BACKED_UP, -
cast(sum(b.affected)as dec(12,0)) as AFFECTED, -
cast(sum(a.NUM_FILES)as dec(15,0)) as TOTAL_FILES_STORED, -
cast(sum(a.LOGICAL_MB/1024)as dec(12,2)) as GB_TSM_OCCUPANCY -
from occupancy a, summary b -
where -
a.node_name=b.entity -
and a.node_name in (select node_name from nodes where domain_name<>'MN-STD-POL-DOM') -
and b.end_time>=(current_timestamp - 30 days) -
and b.activity='BACKUP' -
group by a.node_name -
order by a.node_name

I've tried different orders for the tables and used entity instead of node_name but nothing I adjust changes the incorrect results. (It's summing more records than it should for the occupancy fields).
 
PREDATAR Control23

Hi Chad,

Could you try this ?

select -
varchar(a.node_name,45) as entity, -
cast(sum(b.bytes/1073741824) as decimal(12,2)) AS GB_BACKED_UP, -
cast(sum(b.affected)as dec(12,0)) as AFFECTED, -
cast(sum(a.NUM_FILES)as dec(15,0)) as TOTAL_FILES_STORED, -
cast(sum(a.LOGICAL_MB/1024)as dec(12,2)) as GB_TSM_OCCUPANCY -
from occupancy a, summary b -
where -
a.node_name=b.entity -
and a.node_name in (select node_name from nodes where domain_name<>'MN-STD-POL-DOM') -
and b.end_time>=(current_timestamp - 30 days) -
and a.type='Bkup' -
and a.stgpool_name in (select stgpool_name from stgpools where pooltype='PRIMARY') -
and b.activity='BACKUP' -
group by a.node_name -
order by a.node_name
 
PREDATAR Control23

You would think that would work but check this out. Here are the results (which match my previous select).

ENTITY GB_BACKED_UP AFFECTED TOTAL_FILES_STORED GB_TSM_OCCUPANCY
---------------------------------------------- --------------- --------------- ------------------- -----------------
...
GSD1X133S.BLAH.BLAH.BLAH 5527.00 12755 186707690 115310712.17
GSD1X134S.BLAH.BLAH.BLAH 17756.00 13336 54044140 77390946.05
GSD1X135S.BLAH.BLAH.BLAH 16080.00 8340 23406210 46529469.69
...

tsm: CLTTSM40>select node_name, sum(logical_mb) AS TOTAL_MB from occupancy where node_name like 'GSD1X13%ORA' group by node_name

NODE_NAME TOTAL_MB
------------------------------------------------------------------ ----------------------------------
GSD1X133S.BLAH.BLAH.BLAH 9257402.53
GSD1X134S.BLAH.BLAH.BLAH 11884872.34
GSD1X135S.BLAH.BLAH.BLAH 11425941.72


So the query is calculating more than just the nodes occupancy or its doing it more than once for some reason. It's calculating it more for the node with less data too. Weird!
 
PREDATAR Control23

Hello Chad,

Unfortunately, you're right.
I've made some further testing but I still got those erroneous values.
We certainly miss something...
 
PREDATAR Control23

The results you get are expected. Every row of the left tabled will be joined to every row of the right table basically giving you the Cartesian product. Summing is done over the resulting table. This will only give you valid results if either or both sides of the join contain only one row for which the conditions are true. The solution is splitting it into two sub selects and using those as the left and right tables of the join.

Code:
select o.entity, s.GB_BACKED_UP, s.AFFECTED, o.TOTAL_FILES_STORED, o.GB_TSM_OCCUPANCY -
from -
(select -
varchar(node_name,45) as entity, -
cast(sum(NUM_FILES)as dec(15,0)) as TOTAL_FILES_STORED, -
cast(sum(LOGICAL_MB/1024)as dec(12,2)) as GB_TSM_OCCUPANCY -
from occupancy -
where -
node_name in (select node_name from nodes where domain_name<>'MN-STD-POL-DOM') -
group by node_name) o, -
(select -
varchar(entity,45) as entity, -
cast(sum(bytes/1073741824) as decimal(12,2)) AS GB_BACKED_UP, -
cast(sum(affected)as dec(12,0)) as AFFECTED -
from summary  -
where -
entity in (select node_name from nodes where domain_name<>'MN-STD-POL-DOM') -
and end_time>=(current_timestamp - 30 days) -
and activity='BACKUP' -
group by entity -
order by entity) s -
where -
o.entity=s.entity
 
PREDATAR Control23

The results you get are expected. Every row of the left tabled will be joined to every row of the right table basically giving you the Cartesian product. Summing is done over the resulting table. This will only give you valid results if either or both sides of the join contain only one row for which the conditions are true. The solution is splitting it into two sub selects and using those as the left and right tables of the join.

Code:
select o.entity, s.GB_BACKED_UP, s.AFFECTED, o.TOTAL_FILES_STORED, o.GB_TSM_OCCUPANCY -
from -
(select -
varchar(node_name,45) as entity, -
cast(sum(NUM_FILES)as dec(15,0)) as TOTAL_FILES_STORED, -
cast(sum(LOGICAL_MB/1024)as dec(12,2)) as GB_TSM_OCCUPANCY -
from occupancy -
where -
node_name in (select node_name from nodes where domain_name<>'MN-STD-POL-DOM') -
group by node_name) o, -
(select -
varchar(entity,45) as entity, -
cast(sum(bytes/1073741824) as decimal(12,2)) AS GB_BACKED_UP, -
cast(sum(affected)as dec(12,0)) as AFFECTED -
from summary  -
where -
entity in (select node_name from nodes where domain_name<>'MN-STD-POL-DOM') -
and end_time>=(current_timestamp - 30 days) -
and activity='BACKUP' -
group by entity -
order by entity) s -
where -
o.entity=s.entity

Well I know DB2 supports outer and inner joins but will TSM?
 
PREDATAR Control23

Afaik it fully supports all types of join operations. The comma style join above is in fact an inner join.

select * from (sub_sel1) s1, (sub_sel2) s2 where s1.entity=s2.entity
...is the same to TSM as...
select * from (sub_sel1) s1 inner join (sub_sel2) s2 on s1.entity=s2.entity
 
PREDATAR Control23

Well yeah its an inner join but I haven't played around enough with DB2 level of querries to know if TSM will support the syntax. TSM 5.x was limited in its SQL/PL abilities so the question has been does the DB2 version support full DB2 level queries from the TSM command line or do I have to use a DB2 prompt?
 
PREDATAR Control23

You can run this from the command line. Probably best to move the casting, order by, etc to the main select.

Code:
select -
varchar(o.node_name,45) ENTITY, -
cast(s.SUM_BYTES/1073741824 as decimal(12,2)) GB_BACKED_UP, -
cast(s.SUM_AFFECTED as dec(12,0)) AFFECTED, -
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) SUM_BYTES, -
sum(affected) SUM_AFFECTED -
from summary  -
where -
end_time>=(current_timestamp - 30 days) -
and activity='BACKUP' -
group by entity) s -
where o.node_name=s.entity -
and ENTITY in (select node_name from nodes where domain_name<>'MN-STD-POL-DOM') -
order by ENTITY
 
PREDATAR Control23

Hmmm....so I tried it and it looks better except the sum of the bytes backed up for the month is 0 across the board.

BTW - I appreciate the help and will use this as an example of what I can do in the future. Love learning new ways and processes, especially with SQL.
 
PREDATAR Control23

check again plz.. i changed it after posting it. accidentally left "/1073741824" in the second sub select.
 
PREDATAR Control23

CoMaboy

Awesome. I actually caught the extra division when I looked more closely. This is great and you have opened up my eyes to all sorts of possiblities I didn't know TSM could handle. Thanks so much!
 
PREDATAR Control23

Hi Chad

Which part is the extra division? I try to execute the select statement and receive this error:

ANR2908E Unexpected SQL punctuation token - '('.

|
...............................V...............................
c(12,2)) GB_TSM_OCCUPANCY from (select node_name, sum(NUM_FILES

ANS8001I Return code 3.
 
Top