[DB2] Reorganising table partitions in a loop ends with SQLSTATE 24501

Setup

You write a SQLPL procedure/function to reorganise table partitions and loop through the partitions having read the metadata in a cursor.

Problem

After the first pass, execution terminate in an exception with SQLSTATE 24501. If you catch the exception to propagate the error to a log table - e. g., the error message you get from the diagnostics stack might be like:

SQL0501N The cursor specified in a FETCH statement or CLOSE statement is not open or a cursor variable in a cursor scalar function reference is not open. SQLSTATE=24501

Cause

DB2 closes the loop cursor with the execution of the reorg statement no matter if you specify HOLD in the cursor definition.

Solution

If you place the actual reorg statement into a different function/procedure and define that as autonomous (transaction), the cursor remains open.

Note

Some say, that it is not recommended doing that in a loop. They argue that it takes long time for big tables. This certainly is true however, there is no real other choice. One can reorganise the table as a whole which will end up with more or less the same time needed as it is the same amount of data being reorganised. Apparently, reorganising partition by partition takes less resources to do.