BMC REORG - SHRLEVEL REFERENCE Issue

Problem:

  We migrated from IBM to BMC REORG, we saw nearly 70% CPU saving and 40% elapse time saving.  We use online REORG (SHRLEVEL CHANGE) except one for which we use SHRLEVEL REFERENCE as we need to discard data as part of archival flow. Recently, we had REORG job failure on the SHRLEVEL REFERENCE job interesting when REORG Job failed it put the DB2 object in STOP status.

Reason:

  A application user had run a SPUFI Query that that gone for index space scan on a large table during the REORG Window. BMC REORG job issues a STOP command on the object during switch phase. As the object was in use, DB2 object went into STOPP pending status until the SPUFI query running. And the BMC REORG failed as well so it was unsuccessful in stopping the DB2 job and REORG failed. And when the SPUFI Query ended the DB2 object went into STOP STATUS.  It would have been IBM REORG only the REORG job would be failed and DB2 Object would have been available.

Fix:

We started the DB2 Object , terminated the hanging utility and Rerun the REORG job successfully.

Change d the REORG as SHRLEVEL CHANGE as we were sure that the old data that are to be discard will not be changed by any user or by any application process. 

 Lesson Learnt:

BMC provides an option STOP@CMT, This option applies to SHRLEVEL NONE and SHRLEVEL REFERENCE only.  The STOP@CMT option specifies whether to add the 'AT (COMMIT)' parameter to all DB2 STOP commands that REORG PLUS issues. YES adds the parameter and NO does not.

By using STOP@CMT=YES, you might avoid this problem, but application threads might be affected. When a thread performs a commit, DB2 might stop the object, although the thread continues to run.

SHRLEVEL CHANGE can be used for discarding old data since V8, for data that will not be changed during the REORG duration.