Rajendran_
New Member
Hello everyone,
I have an issue with a batch process program that takes a bunch of orders as input, allocates the required order line items, and creates a delivery flow and exports them as a report. When dealing with orders with huge order lines, I see a huge amount of records being locked. I've included my code structure below.
Consider the below:
internalCustProcedure - this procedure updates the order count on the customer table and loads user info in a temp-table
internalOrdProcedure - allocates the item to the order lines and loads the order and order line info in a temp-table
generateFlowAndReport - This procedure will determine on which flow the order has to be delivered and also generate the output report for reference
I tried adding the "transaction" keyword to the repeat statement, but it didn't fix the issue.
I'm experiencing an issue when I load approximately 100 orders, each with 500 order lines. This causes about 100 customer records and 100 * 500 = 500000 order line records to be locked in the repeat block until I exit the do block. I can't move generateFlowAndReport inside the repeat because I need all 500 orders to be processed before I can execute the roadmap flow for them. If something goes wrong with the delivery mapping flow, I'll have to undo the entire transaction.
What would be the better approach to undo the entire do block if something goes wrong in generateFlowAndReport and also release record locks in the repeat block?
What is the best way to minimize these record locks? I used the VST table below to determine the number of locks.
I have an issue with a batch process program that takes a bunch of orders as input, allocates the required order line items, and creates a delivery flow and exports them as a report. When dealing with orders with huge order lines, I see a huge amount of records being locked. I've included my code structure below.
Code:
doTrans:
do transaction:
...
repeat while not hQuery:query-off-end:
/* updates made to customer table and required data's are loaded into temp-table */
run internalCustProcedure.
/* updates made to order and orderline table and required data's are loaded into temp-table */
run internalOrdProcedure.
end. /* repeat */
/* Create picking records with above gathered data. */
run generateFlowAndReport(temp-table).
if error then undo doTrans, leave doTrans.
end. /* do transaction */
Consider the below:
internalCustProcedure - this procedure updates the order count on the customer table and loads user info in a temp-table
internalOrdProcedure - allocates the item to the order lines and loads the order and order line info in a temp-table
generateFlowAndReport - This procedure will determine on which flow the order has to be delivered and also generate the output report for reference
I tried adding the "transaction" keyword to the repeat statement, but it didn't fix the issue.
I'm experiencing an issue when I load approximately 100 orders, each with 500 order lines. This causes about 100 customer records and 100 * 500 = 500000 order line records to be locked in the repeat block until I exit the do block. I can't move generateFlowAndReport inside the repeat because I need all 500 orders to be processed before I can execute the roadmap flow for them. If something goes wrong with the delivery mapping flow, I'll have to undo the entire transaction.
What would be the better approach to undo the entire do block if something goes wrong in generateFlowAndReport and also release record locks in the repeat block?
What is the best way to minimize these record locks? I used the VST table below to determine the number of locks.
Code:
for first _DbStatus no-lock:
display _DbStatus._DbStatus-NumLocks .
end.
Last edited: