Modules/DBArch

The module is designed for archiving messages and values of OpenSCADA on a database supported by OpenSCADA.

Any SCADA system provides the ability to archive the acquired data, i.e. formation of history of the changes (dynamics) of processes. Archives conditionally can be divided into two types: archives of messages and archives of values.

A specific of the message archives is that so-called events are archived. A characteristic sign of the event is its time of occurrence. The message archives are usually used for archiving messages of the program, i.e. conducting of logs and reports. Depending on the source the messages can be classified according to different criteria. For example, this may be: reports of emergency situations, reports of actions of the operators, reports of the connection failures and other.

A specific of the value archives is their periodicity, which is determined by the time interval between two adjacent values. The value archives are used for archiving the history of continuous processes. Since the process is continuous, it can be archived only by introducing the concept of quantization of the polling time, otherwise we will receive archives of infinite dimensions because of the continuity of the very nature of the process. In addition, practically, we can get value from the time limited by the data sources. For example, a fairly high-quality data sources in the industry, are rarely allowed to receive data at a frequency of more than 1kHz. And this is without taking into account of the sensors themselves, which have even less qualitative characteristics.

For conducting the archives in OpenSCADA, the subsystem "Archives-History" is provided. This subsystem, according to the types of archives, consists two parts: the message archive and the value archives. The subsystem, in general, is a modular one that allows you to create archives based on different nature and methods of data storage. This module provides a mechanism for archiving to the file system for both the flow of messages and values.

1 Message archiver

Archives of messages are formed by archivers, which can be many and with individual settings, which allows separating archiving of different classes of messages.

The message archiver of this module stores data in a database table, which is named "DBAMsg_{ArchID}", where:

• ArchID — identifier of the message archiver.

The module provides additional settings for the archiving process, Figure 1.

Fig.1. Additional settings of the message archiving process.

These additional parameters:

• Archive size, days — determines the size of the archive over time. After exceeding the limit the old records will be deleted! Set to 0 to disable this limit and to rise some the performance.
• To form time as a string — store messages time in the readable form. Only for databases that support such by means of specific data types like "datetime" in MySQL. This option is incompatible, that is, if you change it, you will lose your current archives.
• Unique and non duple messages for time and category only — in the primary key there are used the fields MIN, TM, TMU and CATEG; otherwise the field MESS is included also to the primary key and is limited in 255 symbols.

The database table for the message archiver has a structure {MIN, TM, TMU, CATEG, MESS, LEV}, where:

• MIN — UTC time, in minutes, uses to accelerate while reading in minutes.
• TM — UTC time of the message, seconds from (01.01.1970). Here you can use a special type for the option "To form time as a string" if it is supported by the database.
• TMU — microseconds of the time.
• CATEG — category of the message.
• MESS — text of the message.
• LEV — level of the message.

2 Value archiver

Archives of values, by default, are formed by the value archivers individually for each registered archive. Archivers can have plenty of individual settings that allow you to split archives according to different parameters, for example, in accuracy and depth. The archives of the parameters of one archiver can be grouped together — one table, with the specified restriction of the number of parameters in the group. Grouping can greatly improve the performance of archiving by sending one query to the database with parameter values in the group.

Archive of values is an independent component, which includes buffer processed by the archivers. The main parameter of the value archive is a data source. As the data source role may be the attributes of the subsystem "Data acquisition" as well as other external data sources (passive mode). Other data sources could be: network archivers of remote OpenSCADA, programming environment of OpenSCADA etc. No less important parameters are the parameters of the archive buffer. From the buffer parameters the opportunity of working of archivers depends on. So, the periodicity of values in the buffer should be no more than the periodicity of the fastest archiver, and the buffer size is not less than double the size for the slowest archiver. Otherwise possible data loss.

General schema of archiving values is clearly shown in Figure 2.

Fig.2. General schema of archiving values process.

Value archiver of this module stores data in a database table, which is named "DBAVl_{ArchivatorID}_{ArchiveID}", for the single mode, and "DBAVl_{ArchivatorID}_<GRP>{N}", for the group mode, where:

• ArchivatorID — identifier of the value archiver;
• ArchiveID — identifier of the archive;
• N — group number, omitted for the first one

The module provides additional settings for the archiving process, Figure 3.

Fig.3. Additional settings of the value archiving process.

These additional parameters:

• Archive size, days — determines the size of the archive over time. After exceeding the limit the old records will be deleted! Set to 0 to disable this limit and to rise some the performance.
• To form time as a string — store messages time in the readable form. Only for databases that support such by means of specific data types like "datetime" in MySQL. This option is incompatible, that is, if you change it, you will lose your current archives.
• Grouping limit of the parameters — non-zero value enables the group archiving and determines of the limit to parameters number per group/table.

The database table for the value archiver has a structure {MARK, TM, VAL), for the single mode, and {MARK, TM, {PRM1}, {PRM2}, {PRMN}}, for the grouping mode, where:

• MARK — fast accessing/reading mark of the archive, {TM}/(10*{period}).
• TM — UTC time of the value, seconds from (01.01.1970). Here you can use a special type for the option "To form time as a string" if it is supported by the database.
• VAL — parameter value into the single mode, the value type determines to type of the column (Integer, Real, String).
• PRM1...PRMN — parameter value with the identifier into the column name, into the grouping mode, the value type determines to type of the column (Integer, Real, String).

3 Informational table of the archival tables

To store the begin, end and other service information of archives in the archival tables, the informational table with the name of the module "DBArch" is created. This table has the structure {TBL, BEGIN, END, PRM1, PRM2, PRM3), where:

• TBL — name of the archive table.
• BEGIN — begin of the data in the archive. Seconds for messages and microseconds for values from UNIX epoch (01.01.1970).
• END — end of the data in the archive. Seconds for messages and microseconds for values from UNIX epoch (01.01.1970).
• PRM1 — optional parameter 1: values periodicity, in microseconds.
• PRM2 — optional parameter 2: type of parameter values, into the single mode, or list of parameters into the group {Type}:{ArchiveId}, for the group mode.
• PRM3 — optional parameter 3.

According to the information into the table, for the value archivers the archive objects restoring and creating are supported.

4 Efficiency

When designing and implementing this module, special mechanisms for improving the efficiency of the archiving process were not laid out due to the objective restrictions of the databases itself and the interfaces of access to them. Accordingly, the effectiveness of archiving in the database is mainly related to the database itself and the interface to access it. From the more effective interfaces and approaches to improving productivity, the following should be noted:

• Reading from DB of several records not by a single/concrete command SELECT, but the grouping SELECT requests, that is for all DB minimum in ten times faster. For that behavior using, the layer of accessing to DB into OpenSCADA, in the request "dataSeek()", is expanded to support preloading to full of all records of the respond to the request. The behavior also now is used by the module and allowed to get data often faster then it is processed further, although giving way to the archiving to the file system.
• Writing to DB of different column also significant faster then different record of the table. The behavior is used by the module in a part of values archiving and the grouping mode, that is values of each signal write to different column.

Results of measurement of the archiving performance placed to the following table:

5 Links

• Diagram: General schema of archiving values process.