1. Please help support our sponsors by considering their products and services.
    Our sponsors enable us to maintain high-speed Internet connection and fast webservers.
    They support this free information and knowledge exchange forum service at no cost to you.

    Please welcome our latest sponsor Tectrade . We can show our appreciation by learning more about Tectrade Solutions

Backup Report with Occupancy

Discussion in 'Scripting' started by chad_small, Nov 2, 2016.

  1. chad_small

    chad_small ADSM.ORG Moderator

    Joined:
    Dec 17, 2002
    Messages:
    2,253
    Likes Received:
    49
    Occupation:
    AIX/SAN/TSM
    Location:
    Gilbert, AZ
    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).
     
  2.  
  3. erwanns

    erwanns ADSM.ORG Senior Member

    Joined:
    Mar 29, 2006
    Messages:
    138
    Likes Received:
    9
    Location:
    France
    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
     
  4. chad_small

    chad_small ADSM.ORG Moderator

    Joined:
    Dec 17, 2002
    Messages:
    2,253
    Likes Received:
    49
    Occupation:
    AIX/SAN/TSM
    Location:
    Gilbert, AZ
    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!
     
  5. erwanns

    erwanns ADSM.ORG Senior Member

    Joined:
    Mar 29, 2006
    Messages:
    138
    Likes Received:
    9
    Location:
    France
    Hello Chad,

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

    CoMaboy ADSM.ORG Senior Member

    Joined:
    Jun 4, 2012
    Messages:
    73
    Likes Received:
    11
    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
    
     
  7. chad_small

    chad_small ADSM.ORG Moderator

    Joined:
    Dec 17, 2002
    Messages:
    2,253
    Likes Received:
    49
    Occupation:
    AIX/SAN/TSM
    Location:
    Gilbert, AZ
    Well I know DB2 supports outer and inner joins but will TSM?
     
  8. CoMaboy

    CoMaboy ADSM.ORG Senior Member

    Joined:
    Jun 4, 2012
    Messages:
    73
    Likes Received:
    11
    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
     
  9. chad_small

    chad_small ADSM.ORG Moderator

    Joined:
    Dec 17, 2002
    Messages:
    2,253
    Likes Received:
    49
    Occupation:
    AIX/SAN/TSM
    Location:
    Gilbert, AZ
    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?
     
  10. CoMaboy

    CoMaboy ADSM.ORG Senior Member

    Joined:
    Jun 4, 2012
    Messages:
    73
    Likes Received:
    11
    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
    
    
     
  11. chad_small

    chad_small ADSM.ORG Moderator

    Joined:
    Dec 17, 2002
    Messages:
    2,253
    Likes Received:
    49
    Occupation:
    AIX/SAN/TSM
    Location:
    Gilbert, AZ
    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.
     
  12. CoMaboy

    CoMaboy ADSM.ORG Senior Member

    Joined:
    Jun 4, 2012
    Messages:
    73
    Likes Received:
    11
    check again plz.. i changed it after posting it. accidentally left "/1073741824" in the second sub select.
     
    chad_small likes this.
  13. chad_small

    chad_small ADSM.ORG Moderator

    Joined:
    Dec 17, 2002
    Messages:
    2,253
    Likes Received:
    49
    Occupation:
    AIX/SAN/TSM
    Location:
    Gilbert, AZ
    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!
     
  14. zareyna

    zareyna Newcomer

    Joined:
    Jun 24, 2006
    Messages:
    1
    Likes Received:
    0
    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.
     

Share This Page