overview
The summary action logging and aggregation function can support CSV files as well as ODBC databases.
If you are logging to a ODBC database, use the "Summary (ODBC)" action.
(Select from the action list)
In addition, in Summary (ODBC), the tables on the database side are automatically generated. Therefore, the only preparation required for the database is to prepare the area of the destination database and define the ODBC data source.
Below is a configuration example using logging by summary action as an example.
Configuration Example
Setting up the database
In this example, we will use Microsoft SQL Server 2017 and introduce a configuration example using Microsoft SQL Server Management Studio.
To set up the SQLServer side, create a database for summary actions from the Management Studio screen.
This completes the preparations on the SQL Server side.The necessary tables and fields will be created automatically by the summary action.
*Please create an actual database name according to your actual operations.
Tag configuration
Here, a virtual device is used instead of a PLC device as the tag to be logged (the tag to be actually logged should be defined according to the actual operation).
As an example, create "U01" as a virtual device unit and create 1000 consecutive tags under it, from "U01.F01.SD0000" to "U01.F01.SD0999".
Action Settings
■ Summary action master registration
Add "A01" as a summary action (ODBC) and register the tags ("SD0000" to "SD0999") and field names ("T000" to "T999") in the tag field master file.Furthermore, when you start a summary action, the field name specified here will become the field name in the table on the database (*The table and field on the database side will be generated automatically)..
For more information about tag field masters, see "Tag Field Master".
(Example of editing tag field master in Excel)
Also, write the following in the summary master file.
(Summary master definition example)
[COMMON] DaysOfHoldingData=365 OdbcDatabaseType=SQLSERVER
[TEST] TagFieldMaster=summary_fieldmaster_tag.csv TagFieldIndex=DB\summary_index_tag.txt OdbcTable=TESTTABLE OdbcMaxFields=250
|
The above is an example of the definition when logging. Among the parameters defined in the Summary Master, the parameters with the prefix "Odbc" are parameters for connecting to the database. For details on configuring the Summary Master, see "Summary Master".
Among the above, the name specified in OdbcTable will be the table name. Also, if the number of fields to be logged exceeds the number of columns specified in OdbcMaxFields, the table will be automatically split (in that case, a sequential number will be automatically added to the end of the table name).
|
Field and table names cannot contain SQL reserved words (such as "DATE"). |
■ ODBC settings for summary actions
Configure the "ODBC settings" to connect to the database.
ODBC connections are configured on the ODBC Settings tab of the Action Settings dialog.
When setting the data source here, be sure to specify the database name that you have prepared in advance on the DBMS side.
For more information about configuring the ODBC data source, see "ODBC Settings".
Event Settings
Create an event to call the summary action "A01".
In this example, create "E01" as Periodic event with a 10 second cycle and specify "A01" as the execution action.
Commentary
When you execute a summary action, the following tables and field names will be created automatically, and logging will begin. If the tables and fields have already been created, logging will begin immediately.
Table Name |
field |
TESTTABLE1 |
T000 to T249 |
TESTTABLE2 |
T250～T499 |
TESTTABLE3 |
T500 to T749 |
TESTTABLE4 |
T750 to T999 |
The table will be automatically split and generated according to the maximum number of fields specified in the Summary Master (OdbcMaxFields=250). When splitting, a sequential number will be automatically added after the table name specified in "OdbcTable=TESTTABLE". If you do not want to split automatically, delete the "OdbcMaxFields=250" line (in that case, all data will be stored in one table and sequential numbers will not be added to the table name).