Skip to main content
D
dbologna13-Aquamarine
13-Aquamarine
January 17, 2025
Solved

ValueStream cleaning

  • January 17, 2025
  • 1 reply
  • 1462 views

Hello everyone, I am using the Thingworx 9.6 platform, on which I use the purge functions ‘PurgeSelectedPropertyHistory’/‘PurgeAllPropertyHistory’ on ValueStream to manage data retention. I often get the following error
ERROR[java.lang.RuntimeException: com.thingworx.common.exceptions.DataAccessException: [1,018] Data store unknown error: [Error occurred while accessing the data provider.] The message is returned to me after 602 seconds. Can anyone help me it is important to keep only a small amount of data active in order to avoid a db overflow.
Thanks
Dimitri

 

Best answer by slangley

Hi @dbologna 

 

From a review of your case, it appeared that you made some updates to your purge script, possibly a timeout setting.  If that is correct, please confirm by marking this response as the Accepted Solution.  If you made other settings changes, please post an update and mark that as the Accepted Solution for the benefit of others in the community.

 

Regards.

 

--Sharon

1 reply

N
nmutter16-Pearl
16-Pearl
January 17, 2025

PTC has this article regarding valueStream cleanup: https://www.ptc.com/en/support/article/CS271772

 

I assume the failure you get is that the timeout for DB queries is 600 seconds. And as you have so much data the deletion query just needs very long.

 

You can change this timeout or change your purge mechanism (depending on amount of data you have). 1. Maybe purge multiple times with different start times (first run purge all data older than 10 months, second run all data older than 9 months, ...) so that each run only deletes like 1 month of data.

 

2. You can configure query timeout in "ThingWorxPersitenceProvider"-Entity:

nmutter_0-1737133893805.pngnmutter_1-1737133900188.png

But this setting applies for all queries to database. It may impact platform performance in certain conditions. So better alternative is to fine tune your purge mechanism 🙂

 

A 3. option would be to implement a purge in database level. Without TWX functionality. Like execute a custom query "DELETE * From table where timestamp > 1.1.2025" (not working like this). But I do not have such a query.

 

There's unfortunate no feature from the platform itself for this.

    Rocko
    Rocko19-Tanzanite
    19-Tanzanite
    January 20, 2025

    on option 3) I would assume if it takes that long using PurgeSelectedPropertyHistory, the DELETE FROM will also take very long. I would recommend option 1), make smaller chunks, and also to call it more often (e.g. weekly instead of monthly) for the same effect, less data to delete in one chunk.

     

    If the valuestream is very large, also consider moving the data for this effected thing to its own valuestream. It will end up in the same database table but might allow for less deletion time if a different index is used.

      D
      dbologna13-AquamarineAuthor
      13-Aquamarine
      January 20, 2025

      Hi Rocko,

      the cleaning takes place daily and is related to the data of a single day that is identified by the retention.

      Each Thing has its own ValueStream and on average every day the number of data is about 20000 rows (calculated with QueryNumberPropertyHistory). Now instead of deleting all the data with a single request I have created a loop and perform the cleaning for each hour of the day, but the problem does not change.
      Thanks
      Dimitri

      Terms & ConditionsCookie settingsAccessibility statement