E9.2 SQL Server Change Data Capture (CDC)

[Michelle D.]

Hi all,
Is anyone using Change Data Capture on their SQL server database?

 

[Kim Schmidt]

Yes, my former shop (I recently retired) is using it to great effect. Not only for researching those "who done it" inquiries, but also for troubleshooting bugs. In an extreme case, it can also be used to restore mistakenly deleted data. To optimize its effectiveness, make sure you ALWAYS include the table audit fields when doing an Update operation in your custom code. Just keep in mind that a CDC tablespace can grow by leaps and bounds if you include all JDE tables. But I'd still recommend including transaction tables like F0411, F0911, et cetera. And if you use Accounts Payable, also include the temp tables F04571, F04572, and F04573 - you will thank me. Probably also F06116 if you use JDE Payroll, as it gets frequently changed during payroll processing.

 

[Michelle D.]

Yes, my former shop (I recently retired) is using it to great effect. Not only for researching those "who done it" inquiries, but also for troubleshooting bugs. In an extreme case, it can also be used to restore mistakenly deleted data. To optimize its effectiveness, make sure you ALWAYS include the table audit fields when doing an Update operation in your custom code. Just keep in mind that a CDC tablespace can grow by leaps and bounds if you include all JDE tables. But I'd still recommend including transaction tables like F0411, F0911, et cetera. And if you use Accounts Payable, also include the temp tables F04571, F04572, and F04573 - you will thank me. Probably also F06116 if you use JDE Payroll, as it gets frequently changed during payroll processing.

Thank you so much for the details! We would be doing it for a limited set of tables. We have a 3rd party tool that is reading a set of tables and they need to know when data changes. They have requested we start using CDC for this purpose.
What sort of cleanup process did you use to keep the change data under control?

 

[Kim Schmidt]

Hi Michelle, I was a developer, not a DBA, but I think an aging algorithm was used to purge the old CDC data. Your 3rd party application may be able to inform you of a suitable aging threshold. If you're a USA based publicly owned company and if your DBAs are doing a periodic offline backup, that should suffice for whatever Sarbanes-Oxley or other audit support requirements you may have.