Skip to main content
To homepage To homepage
Solved

Limit historic table

  • May 15, 2024
  • 4 replies
  • 77 views
J
Contributor
Forum|alt.badge.img

When having an historic snapshot table in TimeXtender MDW, based on a table insert, I need to limit the table somehow to prevent it from growing too large. I want to limit the [Date] field for only 1 year of data. Because it's based on a table insert, there are no fields available in the table to use for a table “Data selection rule”. I can only place a data filter on the table insert which only has data of 1 day.

My historic table works like this: 

  • Actual table (snapshot of today)
  • Historic table (table insert, daily, of actual table, so it stacks up the daily snapshots)

(TX version 20.10.43.64)

Best answer by Christian Hauggaard

Hi @juriaan.hensbroek 

Perhaps you can add a stored procedure as a post-step that deletes the records that are older than 1 year?

In general we recommend using the native history table functionality in TimeXtender as opposed to a manual build up of history using table inserts

4 replies

Christian Hauggaard
Community Manager
Forum|alt.badge.img+5

Hi @juriaan.hensbroek 

Perhaps you can add a stored procedure as a post-step that deletes the records that are older than 1 year?

In general we recommend using the native history table functionality in TimeXtender as opposed to a manual build up of history using table inserts

J
Contributor
Forum|alt.badge.img

Ok, I will try to make it without a table insert. If this doesn't work I will solve it with some post-step stored procedure.

Thomas Lind
Community Manager
Forum|alt.badge.img+5
  • Thomas Lind
  • Community Manager
  • May 15, 2024

Hi @juriaan.hensbroek 

I normally set up my History table with a custom field that has the following setup.

So you add a custom field that has the date data type and adds a custom rule as GetDate() and sets it to be Type II in the history settings. All other fields should be Type I except the Natural keys.

Then it will generate a snapshot of each day and not delete anything unless you use an external script of some sort.

J
J
Contributor
Forum|alt.badge.img

@Thomas Lind This also looks like a table insert. How do you prevent the table for becoming too large? I assume you don't have any fields available to filter on either. Therefore I now made a separate table without a table insert, but just a regular Hist table, for testing first.

Terms and ConditionsCookie settingsAccessibility statement