datori

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 ? 
SELECTIVITY 0.00001

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.

Populate a DB2 database table with pseudo-random BLOBs

How do we quickly populate a database table with arbitrary LOB data? This question often comes up when we need to generate some data for performance testing or tuning. We can, of course, write a little utility in our favourite programming language, such as Java or Perl.

The Java approach can be found in DB2 documentation, for example, here. It shows a stored procedure, but we can see how this idea can be used in a standalone application as well. All we need to do now is write the code, compile it hoping there are no errors (as if!), and wait while a few hundred thousand of LOBs get inserted over a JDBC connection. Fun!

But is there a quicker way? How about three simple Bash commands? Read More