Yet another monthly database extract
A client was rolling out an application one division at a
time. With one division on the application, a monthly process ran for 8 hours. In
converting the second division onto the application, this process ran for 24 hours before they cancelled it
and backed out the implementation. It was a large extract process. One large cursor was opened
repeatedly. It had several IN clauses. A review of the explain results showed DB2 stopped
using index columns after the second IN clause, but had other matching index columns that could have been
used after it.
One of the two IN clauses was for the fiscal year, specifying the current and previous
year. We made this into a union of two selects, each one naming one year in an = clause. The original 8
hour runtime was cut to 2 hours. We were heroes.
However, another review of the explain results showed that DB2 stopped using
matching index columns at the next IN clause in both selects. This one had three values specified, so we made
our union of two selects into a union of six selects, with all of them using = clauses. The two
hour runtime was reduced to 5 minutes...