I am working on setting up a system to be able to import historical data from other systems into Ignitions sqltag history tables. During this process, I have come across a couple of columns in the sqlth_partitions table that I am not sure the purpose of.
Id appreciate if someone could explain the purpose of these columns.
Also, I noticed that my sqlth_sce table has quite a few entries. I would like to get a better understanding of the purpose of having multiple entries for every scan class id in this table and what role the start_time and end_time have, if any, when inserting data into the history tables.
Are there any special considerations I need to take when inserting data regarding dataintegrity in the sqlt_data tables? Can I just use 192 for all of my entries?
I am assuming that I will need to modify the “created” column in the sqlth_te table to be before my first entry of data that I make? If a tagpath has multiple entries in the sqlth_te table, should I make sure that any data that I import in falls into the correct created/retired time spans for each entry or can I just import all data using the id of the current tagpath that is not retired?
Thanks for your help.
I don’t have those details, but 7.7.3 added a new call, system.tag.storeTagHistory, which might help.
Thanks. Pretty cool that they added that. They listen!
Unfortunately its still going to be a little while before I make the jump from 7.5 to 7.7.
The Ignition Programmers Guide, found in the Module SDK, will have most, if not all, of the answers you are looking for.
The blocksize is the size, in milliseconds, of time covered by each entry. This is used by “pre-processed” partitions, and would be 0 for normal data partitions.
The flags are the additional flas that affect how the partition is used.
When inserting your data into the database I would suggest giving the dataintegrity a value of 192 if you know the data is of good quality.
The sce table can have multiple entries for the same scan class. This usually isn’t a problem until there are several records in the table. And by several I mean thousands. In the sce table the latest row for the scan class is updated with the most recent execution time as the end_time. However, if the previous end_time is more than SCRATE*STALE_THRESHOLD (scan class rate time the stale threshold defined in the history provider settings), a new row is inserted. The start_time is usually the amount of time to go back into history for a particular scan class, historically speaking. If you were to clear out the sce table it should insert a new record for each scan class that executes. Once there are records present you can see the end_time updating. If you were to go to your trends you would only see the most recent historical data. This is because the start_time is more recent. If you were to set the start_time to 0 it would now pull historical values from the database as far back as you have available records.
As for the created column, yes, you will want to set this value to that of something earlier than your oldest records. However, you may want to adjust your oldest records created date in the te table for a given tagpath. The te table can contain multiple records of a given tagpath. As tags are modified they are retired and a new entry is made giving the tag a new id, this id corresponds to the historical data.