V4.5 Migration/Upgrade Part 3

[CDrozdowski]

Hi the third...

Again how to influence the commit count for ALTER_TABLE.POP

This time the mig31248 process:

From the log for Test DB1:

START of mig31248 23:34:53
Pop of SS_STATUS_HISTORY.SHIPMENT_STOP_NUM for value (SELECT STOP_SEQU
ENCE FROM IE_SS_STOP WHERE IE_SS_STOP.I_TRANSACTION_NO = SS_STATU...6 Hr 28 Mi
14 Sec
Pop of SS_STATUS_HISTORY.EVENT_LOCATION_GID for value (SELECT LOCATION
_GID FROM SHIPMENT_STOP STOP WHERE STOP.SHIPMENT_GID = SS_STATUS_...6 Hr 53 Mi
39 Sec
END of mig31248 Elapsed Time: 13 Hr 21 Mi 53 Sec
_



Tables: IE_SS_STOP 5.5 million
SS_STATUS_HISTORY Test DB1 5.5 million Test DB 2 12 million Prod: 6.5 million
SHIPMENT_STOP 1.3 million

Mig31248 code:

From: create_dbmigrate_45.sql

PROCEDURE mig31248 IS
BEGIN

ALTER_TABLE.POP('SS_STATUS_HISTORY','SHIPMENT_STOP_NUM',
'(SELECT STOP_SEQUENCE FROM IE_SS_STOP WHERE IE_SS_STOP.I_TRANSACTION_NO = SS_STATUS_HISTORY.I_TRANSACTION_NO)');

ALTER_TABLE.POP('SS_STATUS_HISTORY','EVENT_LOCATION_GID',
'(SELECT LOCATION_GID FROM SHIPMENT_STOP STOP WHERE STOP.SHIPMENT_GID = SS_STATUS_HISTORY.SHIPMENT_GID AND '||
'STOP.STOP_NUM = SS_STATUS_HISTORY.SHIPMENT_STOP_NUM)');

END mig31248;

much thanks,
catharine

[chrisplough]

Re: V4.5 Migration/Upgrade Part 3

Since the solution to this issue is essentially the same as v4.5 Migration/Upgrade Part 1, I'm going to link back to our conversation there, for the rest of the forum users:

v4.5 Migration/Upgrade Part 1

Thank you for identifying these additional bottlenecks in the migration process!

--Chris