Wednesday, September 29, 2010

Automatic compression in DB2? Good question and some answers

Compression in DB2 9.7Image via Wikipedia
During the DB2 bootcamp I was teaching I was asked, why DB2 does not offer automatic compression. There are a lot of automatic and autonomous features inside DB2. Why could DB2 not automatically determine that compression makes sense and compress the data?

This 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...

Monday, September 27, 2010

Just in time - autonomics in action

Last week I was teaching a DB2 Bootcamp. The class room had big window fronts to the South-West and to the North-West and shortly after lunch the sun started to shine inside. First, it was only very bright, then even I in the front could notice how the temperature inside the room started to increase. After a few minutes the outside sun shades started to come down, finally stopping in a position that still left the nice daylight inside, but blocking the sun.

About half an hour later, it was time for me to start the presentation about "Practical Autonomics in DB2". They let you keep focused on your actual high-value job by taking care of mostly routine tasks. Autonomic Building Maintenance or Building Automation is exactly the same. I didn't need to worry about regulating the temperature or light. Sensors and some "smart algorithms" took care of it, so that I could concentrate on a more valuable job, delivering a presentation and transferring skills.

Do autonomics always give the best, the optimal results? No, but they get close to it without any human intervention. For the class room, it would have required keeping an eye on the temperature and pressing buttons to control the sun shades. Most of all, it would have required to take focus off the teaching and worrying about things a computer, in most of the cases, could do better. The same for autonomics in DB2.

Friday, September 3, 2010

Obtaining information about the installed DB2 version (level)

Somehow, I had a mental blackout earlier today when I tried to obtain information about the installed DB2 version and couldn't remember the command. Of course, you can always look into the db2diag.log file. Because DB2 writes an entry with version information and data about the system it is running on to that diagnostic file whenever it starts up.

However, the command I couldn't come up with is db2level. This command prints out the same information. And then, for those who need to obtain that information using plain SQL, they can utilize a special administrative view, ENV_INST_INFO.

How does the output look like?

From my db2diag.log:

DATA #1 : Build Level, 128 bytes
Instance "hloeser" uses "32" bits and DB2 code release "SQL09071"
with level identifier "08020107".
Informational tokens are "DB2 v9.7.0.1", "s091114", "IP23033", Fix Pack "1".

Output from db2level:
DB21085I  Instance "hloeser" uses "32" bits and DB2 code release "SQL09071"
with level identifier "08020107".
Informational tokens are "DB2 v9.7.0.1", "s091114", "IP23033", and Fix Pack
Product is installed at "/opt/ibm/db2/V9.7".

I spare you the output from "select * from sysibmadm.env_inst_info". And why did I want to look at it? I was checking whether I already had applied the fixpack 2.