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

OC - deduplication reporting

Discussion in 'TSM Reporting & Monitoring' started by imoteb, Oct 27, 2017.

  1. imoteb

    imoteb ADSM.ORG Member

    Joined:
    Nov 18, 2008
    Messages:
    21
    Likes Received:
    0
    Location:
    Germany
    Hello,

    I'm testing client side deduplication and looking for a way to integrate reporting onto OC. The main issue now is to understand how dedupe rates are being collected. I only want a high level overview of saved space and a dedupe rate in % (manager ready).

    So, the information I have is:

    select * from stgpools where stgpool_name='BACKUP2'
    EST_CAPACITY_MB: 20870099.4
    PCT_UTILIZED: 0.1
    DEDUP_SPACE_SAVED_MB: 13499
    COMP_SPACE_SAVED_MB: 0
    SPACE_SAVED_MB: 13499

    q stg backup2 f=d
    Deduplication Savings: 13,499 M (54.55%)
    Compression Savings:
    Total Space Saved: 13,499 M (54.55%)

    To integrate that into OC, I need a valid SQL statement. So my question basically is: how on earth do they calculate the % value?
     
  2.  
  3. marclant

    marclant ADSM.ORG Moderator

    Joined:
    Jun 16, 2006
    Messages:
    2,606
    Likes Received:
    370
    Occupation:
    Accelerated Value Specialist for Spectrum Protect
    Location:
    Canada
    The OC uses a series of table that start with the name TSMGUI_:
    select tabname from tables where tabname like 'TSMGUI%'

    The data you are looking for is likely in:
    select name,dedup_saved_pct from TSMGUI_ALLSTG_GRID
     
    imoteb likes this.
  4. imoteb

    imoteb ADSM.ORG Member

    Joined:
    Nov 18, 2008
    Messages:
    21
    Likes Received:
    0
    Location:
    Germany

    That is exactly what I was looking for. Thank you very much!
     
  5. dicowins

    dicowins ADSM.ORG Member

    Joined:
    Nov 8, 2002
    Messages:
    17
    Likes Received:
    0
    select STGPOOL_NAME, (CAST(SPACE_SAVED_MB as FLOAT)/1024) as TOTAL_SAVED_GB, (CAST(DEDUP_SPACE_SAVED_MB as FLOAT)/1024) as DEDUP_SAVED_GB, (CAST(COMP_SPACE_SAVED_MB as FLOAT)/1024) as COMP_SAVED_GB, ((CAST(EST_CAPACITY_MB as FLOAT)/1024)*PCT_UTILIZED/100) as USED_SPACE_GB from stgpools where STG_TYPE='DIRECTORY' or STG_TYPE='CLOUD' order by TOTAL_SAVED_GB DESC
     
  6. dicowins

    dicowins ADSM.ORG Member

    Joined:
    Nov 8, 2002
    Messages:
    17
    Likes Received:
    0
    best deduped nodes

    SELECT SUBSTR(s.ENTITY,1,10) AS NODE, (CAST(FLOAT(SUM(s.bytes_protected))/1024/1024/1024 AS DECIMAL(12,2))) AS PROTECTED_GB, (CAST(FLOAT(SUM(s.dedup_savings))/1024/1024/1024 AS DECIMAL(12,2))) AS DEDUPSAVINGS_GB, (CAST(FLOAT(SUM(s.comp_savings))/1024/1024/1024 AS DECIMAL(12,2))) AS COMPSAVINGS_GB, COALESCE((CAST(FLOAT(SUM(s.dedup_savings))/FLOAT(SUM(s.bytes_protected))*100 AS DECIMAL(5,2))),0) AS DEDUP_PCT,(CAST(FLOAT(SUM(s.comp_savings))/FLOAT(SUM(s.bytes_protected)-SUM(s.dedup_savings))*100 AS DECIMAL(5,2))) AS COMP_PCT from summary_extended s WHERE DEDUP_SAVINGS<>0 and activity='BACKUP' or activity='ARCHIVE' GROUP BY S.ENTITY ORDER BY DEDUP_PCT DESC FETCH FIRST 10 ROWS ONLY
     
  7. dicowins

    dicowins ADSM.ORG Member

    Joined:
    Nov 8, 2002
    Messages:
    17
    Likes Received:
    0
    Worst deduped nodes

    SELECT SUBSTR(s.ENTITY,1,10) AS NODE, (CAST(FLOAT(SUM(s.bytes_protected))/1024/1024/1024 AS DECIMAL(12,2))) AS PROTECTED_GB, (CAST(FLOAT(SUM(s.dedup_savings))/1024/1024/1024 AS DECIMAL(12,2))) AS DEDUPSAVINGS_GB, (CAST(FLOAT(SUM(s.comp_savings))/1024/1024/1024 AS DECIMAL(12,2))) AS COMPSAVINGS_GB, (CAST(FLOAT(SUM(s.dedup_savings))/FLOAT(SUM(s.bytes_protected))*100 AS DECIMAL(5,2))) AS DEDUP_PCT,(CAST(FLOAT(SUM(s.comp_savings))/FLOAT(SUM(s.bytes_protected)-SUM(s.dedup_savings))*100 AS DECIMAL(5,2))) AS COMP_PCT from summary_extended s WHERE DEDUP_SAVINGS<>0 and activity='BACKUP' or activity='ARCHIVE' GROUP BY S.ENTITY ORDER BY DEDUP_PCT ASC FETCH FIRST 10 ROWS ONLY
     

Share This Page