With the FORCE keyword, Reorg Plus for DB2 will CANCEL any threads in DB2 which are holding locks on participating objects and preventing a DRAIN from succeeding. This function is available via PTF.
The FORCE function is in effect any time a DRAIN is taken during the course of the utility. Here is the syntax for the FORCE keyword:
FORCE NONE | READERS | ALL
FORCE_AT START | RETRY | LASTRETRY
FORCE NONE (default) disables the FORCE function, enabling the utility to work as before. When FORCE NONE (default) is in effect, the utility will wait for any in-flight transactions to complete. If the in-flight transactions do not complete within the DRAIN_WAIT time limitation, the DRAIN will fail, and may RETRY if enabled via user options.
FORCE READERS enables the FORCE function to eliminate any threads which are not performing any kind of update and are holding only IS (Intent Share) or S (Share) locks on participating objects. FORCE READERS will functionally eliminate READ queries, while allowing WRITE queries to complete normally. When FORCE READERS is in effect, DRAINS may still fail if the WRITE queries do not complete within the DRAIN_WAIT time limitation.
FORCE ALL enables the utility to eliminate all threads holding any kind of lock on participating objects. When FORCE ALL is in effect, the FORCE processor will CANCEL any thread which is in-flight when the DRAIN begins, subject to the FORCE_AT specification.
BMC’s implementation is unique in that it offers control over when to begin the FORCE process. This is accomplished with the FORCE_AT parameter.
FORCE_AT START (default) instructs the utility to begin the FORCE process as soon as the first DRAIN attempt begins. This approach plows the way for the DRAIN function and insures the utility will complete in the least time. In the case of LOGFINAL, when FORCE_AT START is in effect, the LOGFINAL outage is diminished to the least possible time since there should be no wait time for thread completion and no DRAIN RETRIES.
FORCE_AT RETRY instructs the utility to wait for the first DRAIN RETRY before beginning the FORCE process. This allows transactions to complete normally if they can within the DRAIN_WAIT time limitation. But, if any threads exceed DRAIN_WAIT in duration then they will be eliminated when the second DRAIN attempt begins.
FORCE_AT LASTRETRY is offered for compatibility with the IBM FORCE functionality. Just as it sounds, FORCE_AT LASTRETRY waits until the FINAL DRAIN RETRY (based on the user DRAIN_WAIT RETRY option). So, if syntax DRAIN_WAIT 5 RETRY 3 is used with FORCE_AT LASTRETRY then the FORCE process will be invoked on the last (fourth) DRAIN attempt.
Can a thread be “Too big to fail?” Well, something like that. It may be more like being too big to get out of the way in time. Even with FORCE in effect, a badly behaving transaction which has not COMMITed in a very long time may not be FORCE-ABLE due to the ROLLBACK time required. In other words, if DRAIN_WAIT 10 is in effect, but it takes 12 seconds for a thread to ROLLBACK, then the DRAIN will still fail and require a RETRY. The RETRY should succeed in this case, and FORCE remains in effect as well. Nonetheless, this scenario should be very rare in a production environment where long running update transactions which do not perform COMMITs are rarely tolerated. READER transactions do not have this consideration since they require no ROLLBACK interval.
Enabling the FORCE function requires application of the two following PTFs:
(ARU) REORG PLUS FOR DB2 10.1 BPU3948
(SCC) SOLUTION COMMON CODE 10.1 BPJ0472
Applying either of the above PTFs without the other is not harmful, but the FORCE function will not be enabled until both are applied.
Thanks to Ken Kornblum from BMC Software for his contributions to this article.