Database administration for fun and profit

Helping the DB2 optimizer, well, optimize: SELECTIVITY clause.

The DB2 (we’re talking DB2 for LUW here) query optimizer usually does a pretty good job at choosing a good execution plan, and normally you would not want to coerce it, with optimization profiles or weird search predicates, to go where it does not want to. However, once in a while one does feel the urge to give it a good kick in the right direction.

Imagine a query like this:

SELECT something FROM a_table WHERE start_time BETWEEN ? AND ?

How is the optimizer supposed to know what would be the selectivity of such a predicate? It can’t, so it will take a guess, which in some cases will be wrong (although you can’t really blame it, can you). Suppose a_table contains some sort of log records for the past three years, start_time is a timestamp, and you’re only interested in a range of few hours at a time. How can you explain to the optimizer that you only expect about a hundred of rows as a result, not a hundred million, and it better choose the index on start_time for access?

Simple, you tell it:

SELECT something FROM a_table WHERE start_time BETWEEN ? AND ? 

Read More

Enabling automatic database backup in DB2 for LUW

DB2 for LUW since version 9.5 offers, among other autonomic features, automatic database backup. It is not enabled by default, and relying on automatic backup may not always be desirable, particularly in complex environments where the backup process needs to fit into a chain of other scheduled jobs, requires customization (for example, tablespace level backup), or relies on components outside DB2 control (e.g. split mirror backup).

However, in many cases automatic backup can help you reduce the routine work of scheduling it outside DB2 and managing space utilized by backup images and archived logs. It can work well in non-production environments, with small scale applications and departmental databases.

Here is how you can quickly set up automatic backup of a DB2 database.

Read More

Automatic startup and shutdown of DB2 instances on Linux

The default approach to enabling automatic startup of DB2 instances on Linux operating systems relies on the DB2 Fault Monitor. During the installation a line like this:

fmc:2345:respawn:/opt/IBM/db2/V8.1/bin/db2fmcd #DB2 Fault Monitor Coordinator

is added to the /etc/inittab file. The fault monitor then takes care of starting and restarting the instances which were enabled for autostart by db2iauto on.

There seem to be several problems with that approach. Firstly, the use of /etc/inittab to start programs is deprecated in the modern Linux versions. Secondly, one may want to have a more flexible tool to manage the lifecycle of DB2 instances on the system, such as that provided by the System V init scripts.

I’ve created a couple of scripts — one for the Red Hat Linux, another for SuSE — that can be used in place of the default startup control. They can be downloaded from GitHub.

The installation is easy: drop the appropriate for your server version of the script into /etc/init.d, naming it “db2”. Change the file permissions to allow execution. Run chkconfig --add db2 to enable it.

At this point the scripts respond to three commands: start, stop, and status.

The start command causes the script to check all instances listed in the DB2 global registry file and start those for which the startatboot flag is set to “1”.

The stop command, on the other hand, will stop all running DB2 instances, regardless of their startatboot flag value.

To modify the flag you can use the command:

db2greg -updinstrec instancename=db2inst1!startatboot=0

Obvisously, you’ll need to replace “db2inst1” with your instance name. Set the flag to “1” to enable autostart; set it to “0” to disable it.

For more information on the global registry file and the db2greg utility check the manual, as always. Running db2greg -h also provides much useful information.

Column-organized tables in DB2? Why not! Check out the IBM DB2 Technology Preview

A technology preview program for the potential new features in IBM DB2 was announced today: IBM DB2 Technology Preview

You can explore what DB2 developers are working on and provide direct feedback. In this installment: column-organized tables, a feature that may benefits performance of analytical and other data warehouse-type workloads.

In a column-organized table values of a single table column are stored sequentially in a particular page on disk. This is contrary to the regular, row-organized tables, where all columns of each row are stored sequentially, and one or more complete rows are placed on a particular page.

Columnar organization of a table benefits queries that access a significant portion or all rows in the table but only reference few columns.

Of course, there is no certainty about when or even whether this will actually be a part of DB2 Data Server, but the technology preview program gives you the opportunity to influence future directions of DB2.