How To
Summary
You can use SQL Select statements to query the IBM Spectrum Protect server database and to create custom Operations Center email reports. The following examples can help you create SQL queries that are tailored to your storage environment.
Tip: You must configure container storage pools on the server for the following custom report examples to work.
Steps
For more information, see the following resources:
Space saved through compression and data deduplication
- Nodes with the best overall savings
- Best compressed nodes
- Worst compressed nodes
- Best deduplicated nodes
- Worst deduplicated nodes
- Storage pool space savings
- Storage pool space saving percentages
Use SELECT statements to view information about data deduplication and compression savings for a specific point in time. These queries provide the following information:
- Clients that are successfully deduplicating and compressing data.
- Storage pools with the highest amount of compressed or deduplicated data.
Reports the top ten space saving nodes by total savings percentage.
Minimum IBM Spectrum Protect server version:
7.1.5.000
SQL Select statement
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.comp_savings)+SUM(s.dedup_savings))/1024/1024/1024 AS DECIMAL(12,2))) AS OVERALL_SAVINGS_GB,COALESCE((CAST(FLOAT(SUM(s.dedup_savings)+SUM(s.comp_savings))/FLOAT(SUM(s.bytes_protected ))*100 AS DECIMAL(5,2))),0) AS OVERALL_SAVINGS_PCT from summary_extended s WHERE activity='BACKUP' or activity='ARCHIVE' GROUP BY S.ENTITY ORDER BY OVERALL_SAVINGS_PCT DESC FETCH FIRST 10 ROWS ONLY
Example output: Nodes with the best overall savings
Reports the top ten compressed nodes by compression percentage.
Minimum IBM Spectrum Protect server version:
7.1.5.000
SQL Select statement
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,COALESCE((CAST(FLOAT(SUM(s.comp_savings))/FLOAT(SUM(s.bytes_protected)-SUM(s.dedup_savings))*100 AS DECIMAL(5,2))),0) AS COMP_PCT from summary_extended s WHERE activity='BACKUP' or activity='ARCHIVE' GROUP BY S.ENTITY ORDER BY COMP_PCT DESC FETCH FIRST 10 ROWS ONLY
Example output: Best compressed nodes
Reports the ten nodes with the least compression savings by percentage.
Minimum IBM Spectrum Protect server version:
7.1.5.000
SQL Select statement
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 activity='BACKUP' or activity='ARCHIVE' GROUP BY S.ENTITY ORDER BY COMP_PCT ASC FETCH FIRST 10 ROWS ONLY
Example output: Worst compressed nodes
Reports the top ten deduplicated nodes by deduplication percentage.
Minimum IBM Spectrum Protect server version:
7.1.5.000
SQL Select statement
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
Example output: Best deduplicated nodes
Reports the ten nodes with the least deduplication savings by percentage.
Minimum IBM Spectrum Protect server version:
7.1.5.000
SQL Select statement
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
Example output: Worst deduplicated nodes
Reports all storage pools that are on a single server. The table is sorted by total space saved.
Minimum IBM Spectrum Protect server version:
7.1.5.000
SQL Select statement
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
Example output: Storage pool space savings
Reports the space that is saved for all storage pools for servers that are monitored by the Operations Center (TSMGUI_ALLSTG_GRID).
Minimum IBM Spectrum Protect server version:
7.1.5.000
SQL Select statement
select name, SPACE_SAVED_PCT, SPACE_SAVED_MB, USED_SPACE, DEDUP_SAVED_MB, DEDUP_SAVED_PCT, COMP_SAVED_MB, COMP_SAVED_PCT from TSMGUI_ALLSTG_GRID where STG_TYPE=101 or STG_TYPE=100 order by SPACE_SAVED_PCT DESC
Example output: Storage pool savings with percentages
Use SELECT statements to view information about nodes during a specific time period. These queries provide the following information:
- Clients that are not compressing or deduplicating data well.
- General overview of recent client activity.
Reports all backups or archived files for each node in the last 24 hours. The table is sorted by compression space saved.
Minimum IBM Spectrum Protect server version:
7.1.5.000
SQL Select statement
SELECT ENTITY as NODE_NAME, s.START_TIME, (CAST(FLOAT((s.bytes_protected))/1024/1024 AS DECIMAL(12,2))) AS PROTECTED_MB, (CAST(FLOAT((s.bytes_written))/1024/1024 AS DECIMAL(12,2))) AS WRITTEN_MB, (CAST(FLOAT((s.dedup_savings))/1024/1024 AS DECIMAL(12,2))) AS DEDUPSAVINGS_MB, (CAST(FLOAT((s.comp_savings))/1024/1024 AS DECIMAL(12,2))) AS COMPSAVINGS_MB, (CAST(FLOAT((s.dedup_savings))/FLOAT((s.bytes_protected))*100 AS DECIMAL(5,2))) AS DEDUP_PCT from summary s WHERE s.bytes_protected<>0 and (activity='BACKUP' or activity='ARCHIVE') and s.START_TIME>=(current_date - 1 days) order by COMPSAVINGS_MB DESC
Example output: All node backups in the last 24 hours
Reports a summary of backups or archived files for each node in the last 24 hours. The table is sorted by compression space saved.
Minimum IBM Spectrum Protect server version:
7.1.5.000
SQL Select statement
select Entity as NODE_NAME, (CAST(FLOAT(SUM(s.bytes_protected))/1024/1024 AS DECIMAL(12,2))) AS PROTECTED_MB, (CAST(FLOAT(SUM(s.bytes_written))/1024/1024 AS DECIMAL(12,2))) AS WRITTEN_MB, (CAST(FLOAT(SUM(s.dedup_savings))/1024/1024 AS DECIMAL(12,2))) AS DEDUPSAVINGS_MB, (CAST(FLOAT(SUM(s.comp_savings))/1024/1024 AS DECIMAL(12,2))) AS COMPSAVINGS_MB, (CAST(FLOAT(SUM(s.dedup_savings))/FLOAT(SUM(s.bytes_protected))*100 AS DECIMAL(5,2))) AS DEDUP_PCT from summary s WHERE s.bytes_protected<>0 AND (activity='BACKUP' OR activity='ARCHIVE') AND s.START_TIME>=(current_date - 1 days) GROUP BY ENTITY order by COMPSAVINGS_MB DESC
Example output: Summarized node backups in the last 24 hours
Reports the total daily client ingest for the server. Includes total data protected as well as compression and deduplication savings. Sorted by most recent date.
Minimum IBM Spectrum Protect server version:
7.1.5.000
SQL Select statement
SELECT DATE(s.START_TIME) AS Date, (CAST(FLOAT(SUM(s.bytes_protected))/1024/1024/1024 AS DECIMAL(12,2))) AS PROTECTED_GB, (CAST(FLOAT(SUM(s.bytes_written))/1024/1024/1024 AS DECIMAL(12,2))) AS WRITTEN_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 s WHERE activity='BACKUP' or activity='ARCHIVE' GROUP BY DATE(S.START_TIME) ORDER BY DATE DESC
Example output: Daily ingest
Document Location
Worldwide
Was this topic helpful?
Document Information
Modified date:
24 March 2020
UID
ibm15692136