A Better Way to Manage Remote Connections

Have you ever had a DB2 utility job fail due to some remote connection holding locks? Or maybe you couldn't make a table change due to a faulty remote connection. Or maybe you're new to DB2 and you're wondering how to cancel the connection that's holding the lock so you can continue to run jobs in your maintenance window. The STOP DDF command is often used to resolve such issues. This terminates all DB2 connections, even those that aren't using the tables you're trying to maintain. Of course, the brute force of STOP DDF has its drawbacks, so over the years many DBAs have written REXX routines to interrogate DISPLAY THREAD output and terminate specific connections.

However, DB2 10 brings a new DDF command. MODIFY DDF provides the capability to halt specific connections by defining an alias that your application will connect to. For instance, by using the stop parameter--MODIFY DDF ALIAS(alias-name) STOP--MODIFY DDF tells DB2 to stop accepting new connections. Current active connections are unaffected and inactive connections are closed, and a stopped alias is marked ineligible for auto startup when DDF starts. Then once you've completed the maintenance window, use the start parameter--MODIFY DDF ALIAS(alias-name) START--to start the alias.

What about the current active connections that are impacting your maintenance window? These can be forced off the system with the cancel parameter -- MODIFY DDF ALIAS(alias-name) CANCEL. Cancel is like the stop parameter, in that it stops new connections, marks the alias as ineligible for start and cancels current active connections.

The difference between these two MODIFY DDF parameters is basically when they're used. Schedule the stop job--MODIFY DDF ALIAS (alias-name) STOP--to run first. Then, after about five minutes, and just before you kick off the maintenance jobs, run the cancel--MODIFY DDF ALIAS(alias-name). This gives the current running jobs time to wrap up and keeps new jobs from coming in.

If you've used MODIFY DDF in your shop, please go to Comments and offer some feedback on your experience.