Click on the action buttons in the action panel to compute the compression size and get the query execution times for tables with and without compression and generate a graph comparing the same
Collects statistics for the FACT tables, retrieves their size and estimated compression ratio. The SQL used to collect this information are :
CALL ADMIN_CMD
('RUNSTATS ON TABLE SCHEMA.TABLE_NAME')
-- Determines the size of a table
SELECT CAST(CAST(NPAGES AS DOUBLE)/256 AS DECIMAL(5,3))
FROM SYSIBM.SYSTABLES
WHERE CREATOR= 'SCHEMA' AND NAME='TABLE_NAME'
-- Estimates the compression size and compression ratio of an uncompressed table
SELECT PAGES_SAVED_PERCENT, CAST (TABSIZE*(CAST(100 - PAGES_SAVED_PERCENT AS DOUBLE)/100) AS DECIMAL (5,3))
FROM TABLE(SELECT CAST(CAST(NPAGES AS DOUBLE)/256 AS DECIMAL(5,3)) AS TABSIZE FROM SYSIBM.SYSTABLES
WHERE CREATOR= 'SCHEMA' AND NAME='TABLE_NAME')
AS T1, TABLE (SYSPROC.ADMIN_GET_TAB_COMPRESS_INFO('SCHEMA', 'TABLE_NAME', 'ESTIMATE')) AS T2
Query execution time for tables with and without compression is computed
Results may vary depending on the volume of data. For optimal performance, use high volume of data.
To increase the volume of data, load the fact tables 'te_temp.subscriber_rated_usage' and 'te_temp.subscriber_dedicated_account_usage' with the corresponding .del files present in the LoadFiles directory.
The tables size is considerably reduced after compression (depending on the number of rows in the table - more the no. of rows, better the compression ratio) resulting in reduced storage costs
The query execution time would reduce even with compression enabled.
The graph here might show an increase in the query execution time with Compression. This is because the amount of data in the tables is very low. The cost of retrieval of the data from the compressed table is not compensated by the amount of data.