Thingworx CWC Clean History

[TomerH]

We are using CWC as a solution to provide work instructions to employees
Over 1 year there is a table that growing very fast called workdefinitionhistory (reaching the 1TB soon)

What will be the effect if this table will be cleaned?

Is there a PTC way/best practice to clean the table?

my plan is to archive and move the data to a different storage.

[mstarnaud]

Hi TomerH

This table is the list of changes happening to the executions of Serial-Step combination. So in a simple example, if you have 2 serial numbers and you execute a step (schedule, start, complete) then there should be 6 records in the table. I'm very surprised that this table is reaching this size. A quick check with my test data suggests that to reach 1TB it would require around 3 billion records. We really don't expect the table to grow this big. For a factory that uses CWC a lot, I would expect this table to reach 1 million records after many weeks, maybe even a year, but you seem to be at 1000 times this amount. Do you know why the table is growing this big for you? We have never seen a scenario like this so far. There may be a bug on your server, or some custom tool that does data changes that it shouldn't do.

As for purging, the only officially supported purge that I know of is for the Valuestream table (in the Thingworx DB), every minute it deletes records that were processed. If you delete data in other tables (like WorkDefinitionHistory) it would be at your own risk. Now having said that, as far as I know there is no code that looks at this table, its only purpose is to have access to old values for debugging purposes. So I expect the only issue you might have would be related to performance and disk space while doing the purge. I suggest you proceed with a database expert who has an experience with database shrinks and index rebuilding, as I expect this will be required during/after the purge and doing these operations on 1TB of data can take some time. Also, a database expert could help asking the decision on how to remove the data : truncating the table, deleting in small chunks, etc.

[TomerH]

Hi,

Thank you for the quick reply.

From the record i see in the table it is not just schedule, start, complete, 
It contains every change made in the work definition (step).
for example: user change manual step value it is adding and update value. if he completes it adding insert value.

even reopen step, bypass, verification check is all there.

I just want to make sure losing this data won't brake CWC.

i want to transfer it to another location than to view the old data in history view dashboard if needed.

[mstarnaud · Accepted solution]

Hi

Yes, it should be most changes that cause records to appear. Schedule-Start-Complete was just an example. I think entering values in Attributes (Smarttool, Manual Entry and Part Validation types) is the only change that does not create a record. If you want to see what changes caused records to be created, you can look at all records with the same WorkDefinitionUid and order by Modified and check the columns that changed value.

My concern is that getting billions of records in just 1 year is way more than a normal scenario. Are you really sure that the changes make sense, that this is not a bug? Also I am concerned that even if you completely empty the table today, in 1 year you will have 1TB of new data again.

For the deletion : this is not officially supported, we can't give an official guarantee. But unofficially I can tell you that this table (WorkDefinitionHistory) is not used by CWC, there should be no place that looks at this table, it's only there for debugging, so yes you should be able to remove the records without causing issues. Well, I say without causing issues, but like I mentioned in my previous message please have a database expert to review the situation : there may be performance issues while the delete is happening, and it may use even more disk space temporarily (database logs), and you will probably need to rebuild the index after.

[TomerH]

Thank you, it helped.

Not sure yet what next step will be decided but I have the information I was looking for.

[mstarnaud]

Thanks.

By the way, if/when you remove all this data, I suggest that you come here and give feedback. You could confirm that CWC still works, and also specify which steps you decided to take to prevent issues during the deletion (performance & disk space). This could be useful for future people trying to do the same thing. And seeing as you had 1TB in 1 year, "future people" will probably be you next year unless you find a way to reduce the amount of data that comes in.

[TomerH]

It might take some time. In the meantime, we have added 1TB of space so we can focus on more pressing matters.

When decided to bring a DBA to handle moving the table data, i will update.