ChannelDB2

This screencast demonstrates compression technology and its enhancements in DB2 9.7.

Views: 1584

Comment

You need to be a member of ChannelDB2 to add comments!

Join ChannelDB2

Comment by Radim Kolar on August 14, 2009 at 2:13pm
These estimated table functions are very useful. But they should be more consistent such as pct_pages_saved for index vs pages_saved_percent for tables. They are much easier to use that INSPECT command.

I really like moving from command based administration to stored procedure/views based. It is easier to automate and more flexible.
Comment by Rav Ahuja on August 14, 2009 at 12:08pm
Transcript:

DB2 9.7 Compression

In this short video we'll show you how lower the cost of storage and at the same time improve the performance of your database using DB2 row compression. DB2 provides comprehensive compression for almost every type of database object including table data, indexes, temporary tables, XML documents, LOB columns and backup images. DB2 even keeps the data compressed in the bufferpools and log files reducing the memory consumption of your database.

As part of this video you will see how to estimate the savings from row compression,
how to enable row compression and why DB2 compression can mean faster performance.

This video uses the Technology Explorer, a demonstration system connected to a live DB2 data base.

For the purpose of this demonstration we will create a sample table and populate it with several hundred thousand rows of data joined together from several catalog tables that simulate customer address records.

Before enabling compression, you can see the size of our address table by running a query on the SYSCAT.TABLES system view.

You can also estimate the storage savings by using a table function called ADMIN_GET_TAB_COMPRESS_INFO and ADMIN_GET_INDEX_COMPRESS_INFO. In this case we are going to reduce the storage required for the table by around 66% and for the indexes by around 68%.

You can enable compression on existing tables using the ALTER TABLE and ALTER INDEX commands and then reorganizing the data and indexes. Usually, the table should have at least 1Mb of raw data to get the benefit of compression.

The actual compression turns out to be almost exactly the same as the estimate. You can choose to compress only the tables and indexes that give you the most benefit and understand your cost saving before enabling compression.

You can also turn on compression as soon as you create a table using the automatic compression option in the CREATE TABLE command. All new indexes will be automatically compressed as well.

The compression dictionary is created as soon as there is enough data in the table.

This step has the same result as the previous one except there was no need to reorganize the existing data.

Using the two table we created - one with compression and one without we will run some performance tests.

The first two statements compare SELECTs with an ORDER BY clause. Including the ORDER BY insures that each row in the table is loaded into the buffer pool for sorting and that each row is uncompressed. Even in this case the table with compression performs better than one without.
While there is slightly more time taken by the CPU to process the compressed table the number of IO page reads is dramatically lower. Even in this small example this means overall faster performance for compressed tables. The benefit only increases with larger tables and IO intensive queries, especially those found in business intelligence.

For our last test we will create indexes on our compressed an uncompressed tables.


Index compression can also help performance. Just as compression reduced IO in the last test, compression can also help the performance of indexes. In this case we are running a SELECT with a WHERE clause against each table. The WHERE clause insures that the indexes are used. In the same five second period the statement ran X times against the uncompressed table and index, while it ran Y times for the compressed table and compressed index.

This video demonstrated DB2 data row compression as well as index compression. You can easily estimate compression savings and enable "set and forget" automatic compression. You also saw how row compression can mean faster performance. DB2 compression can save you money by saving time and storage.

Featured Downloads

Try BLU Acceleration on Cloud

© 2020   Created by channeldb2.   Powered by

Badges  |  Report an Issue  |  Terms of Service