Solved

Limit historic table

  • 15 May 2024
  • 4 replies
  • 40 views

Badge

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)

icon

Best answer by Christian Hauggaard 15 May 2024, 10:40

View original

4 replies

Userlevel 6
Badge +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

Badge

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.

Userlevel 6
Badge +5

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.

Badge

@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.

Reply