Reducing the transaction size.

[ank123]

define buffer bcust for customer.
--
--
MAINBLK:
do transaction on-error, undo  MAINBLK:
 
  for each bcust exclusive-lock where bcust.id = 10 and
                                                   bcust.rcode = 5:
 
    assign bcust.discount = 1.5
            ----
           ----.
  end.
 
end.

Above query will find the 100k records to be updated. So currently all the records will be updated under one transaction in one go.

But i want only 1000 records to be updated in one go.
Kindy suggest me the different ways to get my program updated for my requirement.

TIA

 

[medu]

you might try something along the line...

DEFINE VARIABLE numRec  AS INTEGER NO-UNDO.

DEFINE QUERY qry FOR customer.

OPEN QUERY qry FOR EACH customer.

qry_block:
DO WHILE TRUE:
    DO TRANSACTION:
        DO numRec = 1 TO 5:
            GET NEXT qry EXCLUSIVE-LOCK.
            IF NOT AVAILABLE(customer) THEN
                LEAVE qry_block.
            customer.discount = 10.
        END.
        PAUSE 5.
    END.
    
END.

 

[ank123]

Hi Medu,

thanks for the reply.

Do we need to use define query .. to get this done? Can we not do using for each ..End as in my original query?

Thanks

 

[medu]

no, you can't... the exclusive lock option will raise the transaction level to the for each block and once in that block you can't get out, well you can but then you have to get back from where you left somehow.

query is the easiest option, either static or dynamic but you can use the good old find if you like it better

qry_block:
DO WHILE TRUE:
    DO TRANSACTION:
        DO numRec = 1 TO 5:
            FIND NEXT customer WHERE state EQ 'WA' EXCLUSIVE-LOCK NO-WAIT NO-ERROR.
            IF NOT AVAILABLE(customer) THEN
                LEAVE qry_block.
            customer.discount = 77.
        END.
        PAUSE 5.
    END.
    
END.

 

[jhdandliai]

try this code

find first bcust bcust.id = 10 and bcust.rcode = 5 no-error.
if avail bcust then do:
repeat:
assign bcust.discount = 1.5.
find next bcust bcust.id = 10 and bcust.rcode = 5 no-error.
if not avail bcust then leave.
end.
end.