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...

~~~~~