Why doesn't Recversion always change on record update?

(0) Share

Report

Posted on by Community Member

I've noticed that Recversion on some records in the database aren't updating as I'd expect. What I'm trying to do is to rework how my company is moving data into our Data Warehouse for reporting purposes. Up until this point, the tables we use for reports are refreshed each night with data from our production environment. Data Warehouse tables are truncated, then reloaded fresh.

Naturally, as our production database grows, this is becoming less and less ideal. We're using SQL Server 2005 for our database, so we don't have access to the new SQL "Merge" statement, but we were able to achieve a similar effect. We match records between the databases by RecId, and are using Recversion to test whether a Data Warehouse record needs to be updated from production in the case of changes to the record (inserts are handled based on not finding a matching record in the data warehouse by RecId).

So the problem is, if Recversion isn't being updated all the time, this method is useless since we aren't accurately capturing all updates. The table we've noticed a problem with is InventSum. The only thing I can see different from most other tables is the concurrency setting; InventSum is set to use Pessimistic concurrency. Would this affect the behavior of when the Recversion value changes? What else might cause this value not to update regularly?

The test:

I tested this out by recording the Recversion value of an InventSum record on a particular item in a given warehouse. I then created a sales line for 100,000 of the item, which in turn updated the InventSum.ReservPhysical value. Despite the change in the reserved amount on InventSum, the Recversion remained unchanged from its original value. Picking the quantity also did not update the Recversion. Posting a packing slip DID cause the Recversion to change.

So why the difference?

*This post is locked for comments

I have the same question (0)

All responses (11)

Answers (1)

Suggested answer

Albert Akuamoah 260 on at

Like (0)

Report

RE: Why doesn't Recversion always change on record update?

This is very late but perhaps someone will benefit form it. RecVersion is used to handle Optimistic Concurrency (OCC) in AX. I believe all your observations help answer this question. Reversion is even more useful now than it was when you asked this question because the AIF framework has gain popularity with AX2012. When a request for an update comes into AX, the recversion and recid pair will have to match a record in AX or else the understanding is that the record has already been updated. InventSum behaved differently in your experiment because of its concurrency settings.

Take care,

Albert.

Was this reply helpful? Yes No
Mariano Gracia on at

Like (0)

Report

RE: Why doesn't Recversion always change on record update?

I think your problem is that sometimes dynamics updates InventSum table through sql directly, you can see in the method sqlUpdateInventSumStr from InventUpdateOnHand class, in those cases RecVersion is not updated

Was this reply helpful? Yes No
Verified answer

Brandon Wiese 17,788 on at

Like (0)

Report

RE: Why doesn't Recversion always change on record update?

InventSum is unusually special in this case. Because it is a candidate for significant locking, in order to maintain performance all changes to InventSum (typically by writing to InventTrans records) within a TTS block are stored up in another table InventSumDelta. When the TTS commits, all of the InventSumDelta records are collected and those changes are committed to InventSum. As indicated by another post, sometimes these changes are done through direct SQL which apparently fails to update the RecVersion.

To support your needs, you might consider just fixing that problem. The class responsible for this behavior is InventUpdateOnhand, and the class that hooks into the TTS scope is InventUpdateOnhandGlobal. I suspect you could tack on a RecVersion field to the SET statement.

Good luck!

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: Why doesn't Recversion always change on record update?

Aha, that makes sense. Sadly will be of little practical use at this point, since the issue has long since passed relevance for my current work. Regardless, I appreciate the insight. Thanks guys.

Was this reply helpful? Yes No
Brandon Wiese 17,788 on at

Like (0)

Report

RE: Why doesn't Recversion always change on record update?

Wow, I just noticed the year of 2010.

Was this reply helpful? Yes No
Ax2009 User on at

Like (0)

Report

RE: Why doesn't Recversion always change on record update?

Hi Justin,

Please can I ask what workaround you used for your data-warehouse purpose when you experienced this issue, as I assume InventSum was not the only table that would have given this issue?

Thanks

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: Why doesn't Recversion always change on record update?

I don't believe it was very elegant. I think for that table we still did a full truncate / load from the transactional DB, but we still did use the improvised merge procedure for the bulk of the tables we captured. Overall it was still a massive increase in performance, dropping the execution time by about 65%.

Was this reply helpful? Yes No
Ax2009 User on at

Like (0)

Report

RE: Why doesn't Recversion always change on record update?

Thanks for your response Justin.

Was this reply helpful? Yes No
Ax2009 User on at

Like (0)

Report

RE: Why doesn't Recversion always change on record update?

Hi Justin, can you confirm if you still remember as I see your post is a few years old already whether you still used Recid and Recversion in your Merge statement to determine incremental updates for the load of the bulk of the tables. There is no index on Recversion and from the blogs I have seen it is not recommended adding an index on Recversion because it is updated so frequently. Would you be able to post your merge statement?thanks

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: Why doesn't Recversion always change on record update?

We did use RecId for the matching, and RecVersion to see if the record needed to be updated. We did not add an index on RecVersion. Unfortunately I no longer have access to the statement so I cannot provide that. Sorry.

Was this reply helpful? Yes No