Monday, December 15, 2014

Runaway Reorgs


I often run into issues with a customer who's DB2 reorgs get out of control.
I found a great Reference in DBA to DBA, which outlines how to view the status of the reorg.

Killing a reorg is a fairly simple matter, on the face of it, inasmuch as that you can just kill the reorg process and you're "done".

The problem is that you may have to rebuild indexes after this, for reasons that should be obvious.

Per Alexander Ashkenazi, If you cancel it during SORT or BUILD phase, your REORG will roll back. It shouldn't take long, because nothing has changed in your table at this point. 
If your REORG fails for whatever reason during "RECREATE ALL INDEXES" phase, all or some of your indexes will be gone, but DB2 will start automatically rebuilding them on the first usage of the table. 
Link to Alexander's response to a question on IT TOOLBOX
My advice would be to NOT mess with a reorg that you find in the "REPLACE" phase. That is where the database either copies the shadow copy back to the original, or drops the original and points the db at the shadow copy. For obvious reasons, all effort should be made to avoid interrupting this process.

No comments:

DBT-50000 when using DBCA.bat on Windows (Oracle 19.11)

I’ve been having some trouble getting DBCA to run in order to create databases. Thought I’d share it with you, and thus document it for la...