Image via WikipediaThis is an excellent question. DB2 can indeed take control of several performance-related configuration settings once DB2 has been authorized (enabled) for that. Regarding compression it is interesting to see that DB2 compresses temporary tables automatically once a compression license has been applied and DB2 determines that it makes sense performance-wise. This same capability applied to regular data and indexes is what the question is about. Why not autonomous decisions for that?
The answer relates to several aspects: The scope of a decision and the business strategy. Let's start with the scope. For a temporary table DB2 has an idea of what to expect. It knows the query and has (rough) statistics about the data and expected intermediate result sets. Hence it estimate the impact, good or bad, more or less precisely and decide on whether it makes sense to apply compression. For regular data and indexes, the decision and the background knowledge are different. DB2 does not know which queries to expect, how the data might grow over time, what other concurrent activities might be planned, what resources would be available to compress and reorganize the data. The impact of the decision is by far of a much bigger scope and wide reaching.
The business side of the question whether to compress or not are also nothing DB2 knows about. Service level agreements, storage costs, costs for CPU cycles, response times, throughput goals, business priorities, and many more are something DB2 does not know about. Smart humans are needed to decide on what is best, not just for a single table or index, but for the overall design and how it fits into the business requirements and (cost) strategy.
And that is where skilled DBAs come into the picture. Excellent question, some answers, and overall good news for DBAs...
