Reorg with a SWITCHTIME option

Steve Reznikov

Reorg with a SWITCHTIME option

Folks,

Can someone tell me what am I doing wrong. I added a SWITCHTIME parameter to my Reorg job as you can see below and I was surprised to see that it started draining before the specified time.

Your help is appreciated.

REORG TABLESPACE LIST REORLIST
SHRLEVEL CHANGE
MAPPINGTABLE xxxxx
COPYDDN(SYSCOPY)
FASTSWITCH YES
SORTDEVT SYSDA
SORTNUM 100
SWITCHTIME 2018-02-07-20.30.00.000000
STATISTICS TABLE ALL INDEX ALL KEYCARD UPDATE ALL

 

 

38 18:04:06.19 DSNURLOG - DRAIN ALL WITH START TIME 2018-02-07-18.02.53.360240

18:04:07.01 DSNURLGD - FINAL LOG ITERATION STATISTICS. NUMBER OF LOG RECORDS =
18:04:07.01 DSNURLGD - LOG PHASE STATISTICS. NUMBER OF ITERATIONS = 2, NUMBER O
18:04:07.01 DSNURLGD - LOG PHASE COMPLETE, ELAPSED TIME = 00:07:44

Bill Gallagher

Reorg with a SWITCHTIME option
(in response to Steve Reznikov)
From the Utilities Guide, for SWITCHTIME:

If MAXRO DEFER is not specified, REORG enters the final log iteration of the LOG phase before the specified SWITCHTIME value if the specified or defaulted MAXRO criteria is met. When MAXRO DEFER is specified, REORG
does not attempt to enterto the final log iteration until the specified SWITCHTIME is met or affected by an external ALTER UTILITY command in the changing of its MAXRO value.

Bill Gallagher
DB2 Database Administrator
State of Connecticut


From: Steve Reznikov [mailto:[login to unmask email]
Sent: Thursday, February 08, 2018 3:46 PM
To: [login to unmask email]
Subject: [DB2-L] - Reorg with a SWITCHTIME option


Folks,

Can someone tell me what am I doing wrong. I added a SWITCHTIME parameter to my Reorg job as you can see below and I was surprised to see that it started draining before the specified time.

Your help is appreciated.

REORG TABLESPACE LIST REORLIST
SHRLEVEL CHANGE
MAPPINGTABLE xxxxx
COPYDDN(SYSCOPY)
FASTSWITCH YES
SORTDEVT SYSDA
SORTNUM 100
SWITCHTIME 2018-02-07-20.30.00.000000
STATISTICS TABLE ALL INDEX ALL KEYCARD UPDATE ALL





38 18:04:06.19 DSNURLOG - DRAIN ALL WITH START TIME 2018-02-07-18.02.53.360240

18:04:07.01 DSNURLGD - FINAL LOG ITERATION STATISTICS. NUMBER OF LOG RECORDS =
18:04:07.01 DSNURLGD - LOG PHASE STATISTICS. NUMBER OF ITERATIONS = 2, NUMBER O
18:04:07.01 DSNURLGD - LOG PHASE COMPLETE, ELAPSED TIME = 00:07:44

-----End Original Message-----

Steve Reznikov

RE: Reorg with a SWITCHTIME option
(in response to Bill Gallagher)

Bill,

Actually I tried adding MAXRO DEFER, but the job started draining well ahead of the specified SWITCHTIME.

Steve.

Michael Hannan

RE: Reorg with a SWITCHTIME option
(in response to Steve Reznikov)

Steve,

What you report seems completely normal to me.

DRAIN ALL is the Default, right. So during the LOGAPPLY, instead of draining only the writers, it drains readers as well. It cannot start the final LOGAPPLY iteration until the writers are drained.

This is obviously before the SWITCH phase. O.K. SWITCHTIME is actually specifying a time for final LOGAPPLY with exceptions, but the Drain has to happen before that. The manual does not say SWITCHTIME is the Drain Writers time. No clue how the Drain duration could be estimated.

P.S.Draining is not immediate. It can take extended time.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Edited By:
Michael Hannan[Organization Members] @ Feb 09, 2018 - 11:59 AM (Europe/Berlin)
Michael Hannan[Organization Members] @ Feb 09, 2018 - 12:08 PM (Europe/Berlin)

Pete Woodman

RE: Reorg with a SWITCHTIME option
(in response to Michael Hannan)

Steve,

I think you need to code MAXRO DEFER LONGLOG CONTINUE and SWITCHTIME to achieve what you want.

Pete

Steve Reznikov

RE: Reorg with a SWITCHTIME option
(in response to Michael Hannan)

Michael,

The Reorg went into the SWITCH phase immediately after the DRAIN. Take a look at the time:

38 18:04:06.19 DSNURLOG - DRAIN ALL WITH START TIME 2018-02-07-18.02.53.360

18:04:07.01 DSNURLGD - FINAL LOG ITERATION STATISTICS. NUMBER OF LOG RECORD
18:04:07.01 DSNURLGD - LOG PHASE STATISTICS. NUMBER OF ITERATIONS = 2, NUMB
18:04:07.01 DSNURLGD - LOG PHASE COMPLETE, ELAPSED TIME = 00:07:44
38 18:04:07.69 DSNUGPAM - APPLYING PENDING DEFINITION CHANGES COMPLETE FOR
38 18:04:07.69 DSNURSWD - SOME PARTITION STATISTICS MAY HAVE BECOME OBSOLET
18:04:08.04 DSNURSWT - SWITCH PHASE COMPLETE, ELAPSED TIME = 00:00:01

Pete Woodman

RE: Reorg with a SWITCHTIME option
(in response to Steve Reznikov)

Steve,

I think you need to code MAXRO DEFER LONGLOG CONTINUE and SWITCHTIME to achieve what you want.

Pete

Steve Reznikov

RE: Reorg with a SWITCHTIME option
(in response to Pete Woodman)

Pete,

I tried that, but it didn't change the behavior. 

Thanks.

Pete Woodman

RE: Reorg with a SWITCHTIME option
(in response to Steve Reznikov)

Steve,

Very odd ... this is exactly what I ran and you can see the message showing the switch:

 

REORG TABLESPACE LIST TSLIST

     COPYDDN        (SCOPY)

     SHRLEVEL CHANGE

     MAPPINGDATABASE DPZW

     AUX NO

     STATISTICS

     TIMEOUT TERM

     DRAIN_WAIT 5  RETRY 90 RETRY_DELAY 5  DRAIN ALL

     FORCE READERS

     MAXRO DEFER LONGLOG CONTINUE

     SWITCHTIME CURRENT_TIMESTAMP + 30 MINUTES

 

 

 

LOG - ALTER OF MAXRO DEFER TO 2147483647 DETECTED

LOG - DRAIN ALL WITH START TIME 2018-02-09-13.09.36.564180 HAS COMPLETED

 

- FINAL LOG ITERATION STATISTICS. NUMBER OF LOG RECORDS = 0

- LOG PHASE STATISTICS. NUMBER OF ITERATIONS = 320, NUMBER OF LOG RECORDS = 0

- LOG PHASE COMPLETE, ELAPSED TIME = 00:26:45

- SWITCH PHASE COMPLETE, ELAPSED TIME = 00:00:00

Steve Reznikov

RE: Reorg with a SWITCHTIME option
(in response to Pete Woodman)

I know Pete.

I think it's time to open a PMR...

Michael Hannan

RE: Reorg with a SWITCHTIME option
(in response to Steve Reznikov)

Steve,

I don't see what your point is. The Drain takes significant time, when other transactions need to release their claim, then the final Log Apply was very quick. It would vary depending  how many updates occurred during the 2nd last log apply (concurrent with later part of the drain. Switch was also fast. Seems that you want to control when the Drain starts, but instead you specified target time for the Last Log Apply according to the manual. The last Log Apply happened just before switch (if low outstanding updates), but the Drain happened before that and took much longer.

Michael Hannan
 
In Reply to Steve Reznikov:

Michael,

The Reorg went into the SWITCH phase immediately after the DRAIN. Take a look at the time:

38 18:04:06.19 DSNURLOG - DRAIN ALL WITH START TIME 2018-02-07-18.02.53.360

18:04:07.01 DSNURLGD - FINAL LOG ITERATION STATISTICS. NUMBER OF LOG RECORD
18:04:07.01 DSNURLGD - LOG PHASE STATISTICS. NUMBER OF ITERATIONS = 2, NUMB
18:04:07.01 DSNURLGD - LOG PHASE COMPLETE, ELAPSED TIME = 00:07:44
38 18:04:07.69 DSNUGPAM - APPLYING PENDING DEFINITION CHANGES COMPLETE FOR
38 18:04:07.69 DSNURSWD - SOME PARTITION STATISTICS MAY HAVE BECOME OBSOLET
18:04:08.04 DSNURSWT - SWITCH PHASE COMPLETE, ELAPSED TIME = 00:00:01

 

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Edited By:
Michael Hannan[Organization Members] @ Feb 10, 2018 - 01:31 PM (Europe/Berlin)
Michael Hannan[Organization Members] @ Feb 10, 2018 - 01:32 PM (Europe/Berlin)