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

Backup Report with Occupancy

chad_small

ADSM.ORG Moderator
#1
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).
 

erwanns

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

chad_small

ADSM.ORG Moderator
#3
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!
 

erwanns

ADSM.ORG Senior Member
#4
Hello Chad,

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

CoMaboy

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

chad_small

ADSM.ORG Moderator
#6
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?
 

CoMaboy

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

chad_small

ADSM.ORG Moderator
#8
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?
 

CoMaboy

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

chad_small

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

chad_small

ADSM.ORG Moderator
#12
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!
 
#13
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.
 

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: 8 23.5%
  • Keep using TSM for Spectrum Protect.

    Votes: 17 50.0%
  • Let's be formal and just say Spectrum Protect

    Votes: 5 14.7%
  • Other (please comement)

    Votes: 4 11.8%

Forum statistics

Threads
30,927
Messages
131,583
Members
21,207
Latest member
Nur03