Datum 08.04.2005 MM/DD/YYYY Keywords REORG times out in the switch phase

How can I ensure that objects are still available to my applications when online REORG times out in the switch phase?

Solution
Use the TIMEOUT TERM option when submitting the REORG utility in DB2® in the z/OS® and OS/390® environments. This automatically terminates (TERMs) the utility if it times out in the switch phase, which ensures that objects are still available to your applications.

You might also choose to use TIMEOUT ABEND, which is the default to allow for a restart of the online REORG, especially if it has been running on very large objects for a long time (days).

Instead, you might decide to terminate the failed online REORG manually with the -TERM UTIL command (instead of a doing a RESTART) to make the objects available to applications. In this case, it might no longer be necessary to re-drain the objects on the -TERM UTIL. This drain avoidance is an enhancement that was introduced by APAR PQ91245.




PQ91245 APAR status
Closed as program error.


Error description
Customer submits an Online Reorg while there is a SQL reader on same tablespace being Reorged. The Reorg cannot terminate because in the Switch phase it needs to do a DRAIN ALL.
The Reorg is suspended and finally is timed out:
Reorg ends with:
DSNT500I    DSNUGBAC - RESOURCE UNAVAILABLE
                      REASON 00C200EA
                      TYPE 00000200
                      NAME DAVIDE  .TSDAV A -DIS UTIL shows the utility is STOPPED in the Switch phase.
A -DIS DB shows the tablespace is left in UTRO state.
Up to this point all is working as expected, but issuing a -TERM we are suspended behind the claim of the SQL reader and then we DSNT500I  +DB1L DSNUGDIS RESOURCE UNAVAILABLE
           REASON 00C200EA
           TYPE 00000200
           NAME DAVIDE  .TSDAV
DSNU114I  +DB1L DSNUGCCC - UTILITY COMMANDS SUBSYSTEM FAILURE, REASON=X'00E40030'
DSN9023I  +DB1L DSNUGCCC '-TERM UTIL' ABNORMAL COMPLETION Also a -START DB(...) SPACE(..) ACCESS(FORCE) does not work:
DSNI002I  +DB1L DSNISTFO UNABLE TO START
           OBJECT-TYPE 00000200
           OBJECT-NAME DAVIDE  .TSDAV
           REASON 00C900A8
           TYPE 00000200
           NAME DAVIDE  .TSDAV
DSN9023I  +DB1L DSNTDDIS 'START DATABASE' ABNORMAL COMPLETION Of course when the SQL reader (the select) has gone, we are able to terminate the utility .
Local fix
Use TIMEOUT TERM in Reorg job.
Problem summary
****************************************************************
* USERS AFFECTED: All DB2 UDB for OS/390 or z/OS utility users *
****************************************************************
* PROBLEM DESCRIPTION: A TERM UTIL command specified for a     *
*                      stopped REORG SHRLEVEL CHANGE utility   *
*                      resulted in timeout message             *
*                      MSGDSNT500I REASON RC00C200EA           *
*                      TYPE 00000210 being issued.             *
****************************************************************
* RECOMMENDATION:                                              *
****************************************************************
A REORG SHRLEVEL CHANGE utility was run without specifying the TIMEOUT TERM option.  This utility failed when trying to drain all readers in the SWITCH phase.  After the failure an attempt is made to clean up the stopped utility with the TERM UTIL command.  This command also fails because it attempts to drain all readers.
Problem conclusion
When TERM UTIL is handled for a stopped utility which never successfully drained readers and set objects in a UTUT state, cleanup of shadows is now done without attempting to drain all readers from the target objects.
Temporary fix
Comments
APAR information
APAR number PQ91245
Reported component name 5740 IBM DATABA
Reported component ID 5740XYR00
Reported release 710
Status CLOSED PER
PE NoPE
HIPER NoHIPER
Submitted date 2004-07-12
Closed date 2004-07-28
Last modified date 2004-09-03

APAR is sysrouted FROM one or more of the following:

APAR is sysrouted TO one or more of the following:
UQ91050 UQ91053

Modules/Macros
DSNUGTER


Fix information
Fixed component name 5740 IBM DATABA
Fixed component ID 5740XYR00

Applicable component levels
R710 PSY UQ91050    UP04/08/12 P F408
R810 PSY UQ91053    UP04/08/12 P F408


P.S. Please pass my address to anyone interested in DB2 HOTLINE - thank you.

With kind regards
Michael Dewert, Software GroupTechnical Sales DB2