I was wondering about the best way to automatically archive and delete old records from the FSQL tables. I see that there are options on objects and groups to delete records older than # of days, but don’t see an option to archive these records. This is tank information that we want to keep around for a long period so we can go back and review it, but we don’t want our db tables too large. I was wondering if this was something better handled as a batch action every few months on all the tables or something we could automate through FSQL.
How to archive data is really under the purview of the database administrator, not FactorySQL. There are typically multiple different archiving strategies for each flavor of database. So, I’d have a meeting with your DB admin discussing how they want archiving to take place. If the archiving can be implemented as a stored procedure, you could easily automate the process using FactorySQL simply by calling the proc.
Here is an article that might get you started
vyaskn.tripod.com/sql_archive_data.htm
I do something along these lines, although it’s technically not an archive, I take my history data and move it to a separate table. The reason I do this is to speed up the load time of trends, it takes less time to search through 30 days worth of data as appose to a years worth of data.
Basically what I do is once every day I search my history_tank table for records that are older than 30 days, I insert these records into my archive_tank table and finally delete these records from my history_tank table.
The first thing you need to do is to duplicate the history_tank table schema and name it archive_tank. Make sure you turn off Auto Increment in the index column in the archive_tank table.
Next create a Group in FactorySQL, I called mine archive_tank. Now you need to find a way to trigger the Group. Add a OPC tag, I used _Time_Hour24 from my OPC server, and set it to Read-Only under Item Properties. Next set the Group to execute on this trigger. Now you need to add 3 Action Items, ndx, INSERT, and DELETE and use the following SQL statements for the Action Items. Note - the following SLQ statements are for MySQL.
ndx Action Item is –
SELECT MAX(history_tank_ndx) FROM history_tank WHERE t_stamp < (NOW() - INTERVAL 30 day)INSERT Action Item is –
INSERT INTO archive_tank SELECT * FROM history_ tank
WHERE history_tank _ndx <= {ndx} ORDER BY history_tank_ndx
DELETE Action Item is –
DELETE FROM history_tank WHERE history_tank_ndx <= {ndx}