ibaAnalyzer provides a basic framework for database integration, i.e. it creates default tables, constraints and indices with a mouse click. This may be sufficient in small standalone environments, however even in environments like this, some implementations are recommended, such as data deletion or archiving, to ensure availability.
If you load high-resolution data into a database, archiving functions and, in particular, deletion functions are required. You must derive these deletion functions from the individual data management strategy.
In MySQL, DB2-UDB, MSSQL, PostgreSQL and Oracle databases, the cascading constraints ensure that when a row in the file table is deleted, the corresponding rows in the segment tables and channel headers are also deleted (referential integrity).
Example of a simple deletion strategy
Every night at 02:00, an automated job is executed that deletes all data older than 100 days.
For this purpose, a simple SQL command (MSSQL syntax) is processed every night, which
can be stored in a short SQL script (del_old_recs.sql):
A batch file (del_old_recs.bat) can be created in a Windows environment.
@echo off
SQLCMD -S dbhost\SQLEXPRESS -i C:\Backup\del_old_recs.sql
In the control panel, add a scheduled task "del_old_recs".
Note |
|
|---|---|
 |
Deleting data sets does not always lead to an increase in free disk space. Depending on the database provider, it may be necessary to execute additional maintenance commands or maintenance procedures. |
Data management strategies may also depend on third-party applications. For example, an MES application or QM application may provide the trigger for deleting or archiving entries that come from product-related files. In this case, a product ID reference is required in the file table.
Another part of the data management strategy concerns disk space requirements. How much disk space is required for the implementation of ibaAnalyzer (e.g. for 100 days of storage)? The various set lengths, index space requirements, etc. can be calculated based on the information provided by the database provider. In practice, it is better to simulate typical data processing and monitor the space requirements. A typical database extraction can be easily configured with a small amount of test data.
If you want to use the extracted measurement data together with other data in a production environment or information system environment, you need additional indices for performance optimization or info columns filled by external procedures. All INSERT statements of ibaAnalyzer are "fully qualified". Therefore, adding NULL columns is not a problem. Adding indices is also possible and recommended. If database triggers are necessary, these may slow down load performance considerably, especially in the case of long segment tables.
A very efficient way to connect ibaAnalyzer tables with third-party tables is to define database views that hide the complexity of JOIN conditions.