Thank you all for the comments about last week’s blog. With the queries from last week you were able to see the various settings within your PLAN and PACKAGE environment and some were surprised with what they found. Some of you discovered extreme or a few rogue situations within your plan and package settings.
This week let’s concentrate on saving disk space through the DB2 table compression option that is commonly exploited with DB2 for z/OS and an optional feature enabled in DB2 LUW. Compression has saved some of my client’s huge amounts of disc storage, up to 85% for some data warehouses. Most of the time compression also saved CPU through better I/O efficiencies. Before you order more disk storage, check out leveraging DB2 compression in your environment.
Unfortunately, compression is not for every database table and there are some simple guidelines to follow. First only compress tables that have a better than 3 to 1 ratio of SELECT activity to UPDATE, and INSERT activity. The compression process can be expensive and this 3 to 1 ratio makes sure that the compression savings in SELECT activities pays for overhead costs of compression. This also makes sure the compression processing is minimized within the table for the overall workload.
On tables where the INSERTs and UPDATEs are extremely active, try to separate the new and old data into separate partitions to isolate which partitions are INSERT and UPDATE active. Once the partition data ages, a different partition is utilized with all the INSERT and UPDATE activity and diminishes within other partitions. Once the INSERT and UPDATE workload diminishes under the 3 to 1 ratio within a partition, compression can be enable on that partition and reorganized to save disk space.
Sometimes a definition mistake can enable compression within a table where it should not be and cost CPU overhead for compression. In addition sometimes compression is defined where the CPU overhead, the cost of the compression dictionary and the compression activity is not a 3 to 1 SELECT to INSERT and UPDATE activity causing overall negative compression.
So look at your DB2 z/OS and LUW environments with the following queries to determine whether your system is implementing DB2 compression on the right or wrong tables and where your system is experiencing negative compression. It is sometimes surprising how many tables are found where compression is actually hurting your overall performance. Adjust the number of rows in Queries #2 and #3 to your threshold for enabling compression. Standards vary and some shops compress every table or tables that only hold 50,000 rows.
–Query1 – Find Tables with over 500,000 rows that are Compression Candidates within DB2 z/OS
SELECT DBNAME, TSNAME, PARTITION, CARD, PQTY*4/720 AS CYLS, STATSTIME
FROM SYSIBM.SYSTABLEPART
WHERE COMPRESS = ‘ ‘
AND CARD > 500000
AND DBNAME NOT LIKE ‘TMP%’
AND DBNAME NOT LIKE ‘WRK%’
ORDER BY 4 DESC
WITH UR;
–Query2 – Find Negative Compression with z/OS
SELECT DBNAME, TSNAME, PARTITION, PAGESAVE, CARD,
PQTY*4/720 AS CYLS, STATSTIME
FROM SYSIBM.SYSTABLEPART
WHERE COMPRESS = ‘Y’
AND PAGESAVE < 0
ORDER BY 1,2,3
WITH UR;
–Query3 – Find Tables with over 500,000 rows that are Compression Candidates within DB2 LUW
SELECT CREATOR, NAME, CARD, COMPRESSION, AVGROWSIZE
FROM SYSIBM.SYSTABLES
WHERE COMPRESSION = ‘N’
AND AVGROWSIZE > 50
AND CARD > 500000
WITH UR;
DB2 Performance Maintenance
Remember the other huge benefit of DB2 compression. By reducing the amount of disk storage used for your DB2 tables you reduce the processing time for every database table backup and also reduce your potential recovery time. Any of those nasty queries that are doing table space scans or other large scans of the tables will also run faster, reducing your overall query time and their CPU consumption. So use these queries to learn about your database table disk storage requirements and where DB2 compression might make sense to reduce your disk storage costs and overall processing costs.
Leave a Reply