We are running 10.2B and are prepping for a migrations. As a part of that we are purging a large number of records.
The way the purge currently goes is:
DEF VAR min-id AS INT NO-UNDO.
DEF VAR max-id AS INT NO-UNDO.
DEF VAR num-records AS INT INITIAL 0 NO-UNDO.
DEF VAR trans-active AS INT INITIAL no NO-UNDO.
DISABLE TRIGGERS FOR LOAD OF big-table.
DEFINE BUFFER buf-4-big-table FOR big-table.
FOR EACH big-table
WHERE min-id <= big-table.id AND big-table.id <= max-id NO-LOCK:
DO TRANSACTION:
FIND buf-4-big-table WHERE
RECID(buf-4-big-table) = RECID(big-table) EXCLUSIVE-LOCK.
DELETE big-table.
END.
END.
END.
I'd like to make it to not start a new transaction for every delete, but rather every 10, 100, ...
Something in the spirit of:
START WISHFUL CODE >>>>
FOR EACH big-table
WHERE min-id <= big-table.id AND big-table.id <= max-id NO-LOCK:
num-records = num-records + 1.
IF numre-cords MOD 10 = 0 THEN
DO:
IF trans-active = yes THEN
END TRANSACTION.
DO TRANSACTION.
END.
FIND buf-4-big-table WHERE
RECID(buf-4-big-table) = RECID(big-table) EXCLUSIVE-LOCK.
DELETE big-table.
END.
END.
IF trans-active = yes THEN
END TRANSACTION.
<<<<< END WISHFUL CODE.
The reason is that we only have small amount time to run this purge in the early am when nobody is on the system. Sometimes the purge runs long and risk to interfere with business and we want to be able to clobber the process without a MEGA rollback taking place. But we also don't want to BEGIN/END a transaction for every single record.
Anyone any ideas?
Cheers /Pinne
The way the purge currently goes is:
DEF VAR min-id AS INT NO-UNDO.
DEF VAR max-id AS INT NO-UNDO.
DEF VAR num-records AS INT INITIAL 0 NO-UNDO.
DEF VAR trans-active AS INT INITIAL no NO-UNDO.
DISABLE TRIGGERS FOR LOAD OF big-table.
DEFINE BUFFER buf-4-big-table FOR big-table.
FOR EACH big-table
WHERE min-id <= big-table.id AND big-table.id <= max-id NO-LOCK:
DO TRANSACTION:
FIND buf-4-big-table WHERE
RECID(buf-4-big-table) = RECID(big-table) EXCLUSIVE-LOCK.
DELETE big-table.
END.
END.
END.
I'd like to make it to not start a new transaction for every delete, but rather every 10, 100, ...
Something in the spirit of:
START WISHFUL CODE >>>>
FOR EACH big-table
WHERE min-id <= big-table.id AND big-table.id <= max-id NO-LOCK:
num-records = num-records + 1.
IF numre-cords MOD 10 = 0 THEN
DO:
IF trans-active = yes THEN
END TRANSACTION.
DO TRANSACTION.
END.
FIND buf-4-big-table WHERE
RECID(buf-4-big-table) = RECID(big-table) EXCLUSIVE-LOCK.
DELETE big-table.
END.
END.
IF trans-active = yes THEN
END TRANSACTION.
<<<<< END WISHFUL CODE.
The reason is that we only have small amount time to run this purge in the early am when nobody is on the system. Sometimes the purge runs long and risk to interfere with business and we want to be able to clobber the process without a MEGA rollback taking place. But we also don't want to BEGIN/END a transaction for every single record.
Anyone any ideas?
Cheers /Pinne