How to use the DB2 Governor to force off long idle connections

Ok, so the DB2 Governor is deprecated with 9.7. But it’s only replacement is a pay-for-use tool – the workload manager. So I imagine I’ll be writing a script to do the very basic things that I do with the DB2 Governor when it’s gone.

The DB2 Governor has a lot of purposes. It can be used to change the priority or limit connections based on a variety of criteria, but the way I use it is very basic. The main way I use it is to prevent log file saturation caused by idle connections. For a definition of log file saturation if you’re not familiar with it, read the bottom of this post: Managing db2 transaction log files.

I use the DB2 Governor to force off connections that have been idle for more than 4 hours. I’ve seen a number of cases where a hung connection causes log file saturation by holding on to an older log file. In many cases, these connections are random one-off connections that are not intentionally idle – either a developer has left a connection open or massload or some other tool has failed without releasing all resources.

Most WebSphere Commerce connections have some activity every 10 minutes or more frequently. If you’re running some other app, you’d obviously have to consider what timing is appropriate for you. Some applications may actually require a connection to the database with a lot of idle time.

Creating the governor config file

The config file is pretty simple:

1  { Wake up once every three minutes, database name is sample }
2  interval 180; dbname sample;
3
4  desc "Force off java applications idle for more than 4 hours"
5  applname java,java.exe
6  setlimit idle 14400
7  action force;

The leading numbers are added for convenience here, and are  not a part of the file.

Line 1 is a comment line. Anything in curly brackets is a comment, and at least one line of comment is nice.

Line 2 simply sets the wake-up interval and database name. These clauses can be specified only once per file, so only one database and interval may be specified.

Line 4 is simply a descriptive line

Line 5 sets the application names that will be affected. In my case, I only want to affect java connections, not command line or other connections

Line 6 sets a limit for the idle time of 14,400 seconds or 4 hours.

Line 7 indicates that when the limit above is encountered, the connection in question will be forced off of the database.

Starting the governor

With the above in a file named whatever you like and in whatever location you like, you can then start the governor using this syntax:

db2gov start sample dbpartitionnum 0 /fully/qualified/path/db2gov.sample.cfg db2gov.sample.log

The database name in this example is ‘sample’. The dbpartionnum clause is required even on single partition databases to prevent some odd error messages. The log file is created in the instance home directory under sqllib/logs.

If you get this error:

GOV1007N Governor already flagged as running

Then you must stop the governor before the start will succeed.

Keeping the governor up

There is no autostart function for the governor that I am aware of. So that leaves us to solve the situations of starting the governor on db2start or reboot and also if it should happen to crash. My preferred solution here is to run a simple script every 15 minutes or so that checks and sees if the governor is up, and starts it if not. Such a script has to also be able to either handle the GOV1007N error, or to always stop the governor before starting it.

Stopping the governor

Stopping the governor is quite simple:

db2gov stop sample dbpartitionnum 0

The only details we have to specify are the database name and the same dbpartitionnum clause.

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *