Configuring 2 databases connected to same ERP: one AI on, one with AI off

[guardian]

Hello,

We have a custom rebate module that we added to Infor's SX.Enterprise. We have a process that allows the user to recalculate rebate transactions for an entire year. This process basically deletes and then recreates ALL of the rebate transactions. This involves well over a million rebate records.

Because of this, we have been having after-imaging archiving issues due to space on the system. This process is simply creating too many after-image extents.

One possible solution we are considering is to move the rebate tables to a different database that will have after-imaging turned off. We would not need after-imaging because we can rerun the process to recreate. In addition, we will not have to implement 2phase commit because we do not have any transactions that will update both databases at once.

Does anyone see any issues with having 2 databases: one with AI turned on, one with AI turned off?

Or does anyone have any other suggestions. I have, by the way, suggested that we rewrite the rebate calc to avoid all of the deleting/recreating.

FYI we are at Progress version 10.ob05.

Thanks for your time,
Patrick Maloney

 

[TomBascom]

That seems like a reasonable approach.

You might also like to ensure that the rebate tables are in their own type 2 storage area -- that way you can just "truncate area" to delete them. That's much faster than "for each ...: delete..."

Beyond that -- there are things that you should do to optimize large updates that help with bi and ai space as well as overall performance. Your coders may already be doing it but one of the really key things is to "chunk" transactions into groups of records rather than doing it all as one big transaction or doing each update as an independent transaction -- both extremes are problematic for different reasons. A "chunk" size between 100 and 1,000 records is usually a good balance.

 

[guardian]

Thanks for the response, Tom.

I've been testing your "chunking of transactions into groups of records" idea and am fairly amazed by the results. I think that may be our solution.

Patrick Maloney