JP2007219920A - Data collection management system - Google Patents

Abstract

<P>PROBLEM TO BE SOLVED: To provide a data collection management system wherein trouble of newly manually creating a DB table incorporated in a PC is dispensed with every time a variable collected by an SPU on a PLC side is changed or increased/reduced or every time a DB software product used by a user is changed to improve change flexibility of collection data on the PSU side and adoption flexibility of the DB software product. <P>SOLUTION: A computer is provided with: a database table creation means creating a table having a corresponding structure inside a database based on a setting file acquired from a data collection device; and a database storage means assembling a corresponding SQL based on a record data file acquired from the data collection device, and makes the database additionally store the corresponding data. <P>COPYRIGHT: (C)2007,JPO&INPIT

Description

  The present invention relates to a personal computer (PC) incorporating a data collection unit (hereinafter referred to as SPU) incorporated in a programmable controller (hereinafter referred to as PLC) and commercially available database software (hereinafter referred to as DB software). ) To the data collection management system that collects and manages various variables (including bit variables and analog variables) that are handled by the PLC in a DB built in the PC. The present invention relates to a data collection management system that is applied to manufacturing equipment, other industrial manufacturing apparatuses, and the like, and is suitable for collecting various data relating to production.

  A personal computer (hereinafter referred to as PC) incorporating a data collection unit (hereinafter referred to as SPU) incorporated in a programmable controller (hereinafter referred to as PLC) and a commercially available database software (hereinafter referred to as DB software). Data collection management systems that collect and manage various variables (including bit variables and analog variables) that are handled by the PLC in a DB built in the PC are known for the past. (See Patent Document 1).

According to such a data collection management system, data collection from the PLC memory (input / output memory, data memory, etc.) to the SPU is performed periodically (for example, the PLC, regardless of the communication state between the PC and the PSU). Therefore, even if communication between the PC and the PSU is temporarily interrupted, no data is lost on the PC side. Is taken in, an SQL statement corresponding to the table structure of the DB is created (see Patent Document 2), and the fetched data is stored in a commercially available DB. In this case, there is an advantage that various variables handled in the PLC can be easily used on the PC side.
JP 2005-128721 A JP 2003-114908 A

  However, in such a conventional data collection and management system, the relationship between the file structure of data read from the SPU via communication or the like and the table structure in which data is to be registered in the DB is uniquely fixed. Therefore, whenever the variables collected by the SPU are changed or increased on the PLC side, or the DB software product used by the user is changed, the table structure in the DB incorporated in the PC is manually changed. There was a problem that it had to be recreated.

  The present invention has been made paying attention to the above-mentioned problems. The purpose of this invention is to change or increase / decrease variables collected by the SPU on the PLC side or to change the DB software product used by the user. Data collection that eliminates the need to manually recreate the DB table built into the PC each time, improving the degree of freedom of adopting DB software products and the degree of change of collected data on the PSU side To provide a management system.

  Other objects and effects of the present invention will be easily understood by those skilled in the art by referring to the following description of the specification.

  The data acquisition management system of the present invention employs the following configuration in order to achieve the above object.

  That is, the data acquisition management system of the present invention includes a data collection device that is connected to the PLC and collects and stores data stored in the memory of the PLC, and a computer incorporating a commercially available database, The data collection device is provided with a setting file that holds setting information about data to be collected, and a recording data file that includes data collected from the memory of the PLC according to the setting information of the setting file, Based on the setting file obtained from the data collection device, the computer creates database table creation means for creating a table having a corresponding structure in the database, and based on the recording data file obtained from the data collection device. Assemble the SQL for Database storage means for additionally storing data in the database, and further, between the data collection device and the computer, from the data collection device side to the computer side, the setting file and the recording data file The delivery means for delivering is provided, It is characterized by the above-mentioned.

  According to such a configuration, even if the variables collected by the SPU on the PLC side are changed or increased / decreased, it is not necessary to manually re-create the DB table incorporated in the PC, and the collection on the PSU side is unnecessary. The degree of freedom of data change can be improved.

  At this time, if the database table creation means creates a table corresponding to each database product in the database based on a database definition file that defines the characteristics of one or more commercially available databases, the PLC side This eliminates the need to manually re-create the DB table built into the PC each time the DB software product used by the user is changed as well as when the variables collected by the SPU are changed or increased or decreased. Thus, the degree of freedom of adopting the DB software product and the degree of freedom of changing the collected data on the PSU side can be improved.

  The delivery means can adopt either delivery by communication or delivery by a recording medium. However, if communication is adopted, a maintenance-free database system can be constructed.

  According to the present invention, even if the variables collected by the SPU are changed or increased / decreased on the PLC side, there is no need to manually recreate the DB table incorporated in the PC. The degree of freedom of change can be improved.

  Hereinafter, an embodiment of a data collection management system according to the present invention will be described in detail with reference to the accompanying drawings.

  A functional configuration diagram of the entire system of the present invention is shown in FIG. As shown in the figure, the data collection management system 100 includes a personal computer (PC) 1, a data collection unit (SPU) 2, and a programmable controller (PLC) 3.

  The PC 1 and the SPU 2 are configured to be communicable via a network such as Ethernet (registered trademark). The SPU 2 and the PLC 3 are connected via a bus (not shown) on the backplane.

  Here, the PLC 3 and the SPU 2 will be described in detail. In this example, the PLC 3 is configured in a building block type, and the system bus on the backplane and the system bus on the backplane are respectively configured. And a plurality of device units connectable by connectors.

  As these device units, although not shown, a CPU unit, an input / output unit (I / O unit), a communication unit, and the like can be cited. As is well known to those skilled in the art, a CPU unit includes a microprocessor, a system program memory, a user program memory, an input / output memory (I / O memory), a data memory, a working memory, and an ASIC for executing a user program. Etc. are built-in.

  The microprocessor executes an I / O refresh process, a user program execution process, and a peripheral service process as one cycle by executing a system program, and performs an operation of repeatedly executing the cycle.

  Here, in the I / O refresh process, input data (bit variables, analog variables) taken from an external input device (various sensors, etc.) via the I / O unit is stored in the corresponding area of the I / O memory or data memory. A process of storing the output data stored in the corresponding area of the I / O memory and sending it to an external output device (such as various actuators) via the I / O unit is executed.

  Further, in the user program execution process, each user instruction is sequentially read out from the user program memory and executed while referring to the contents of the I / O memory or the data memory through the ASIC or by the microprocessor itself. At the same time, a process of rewriting the contents of the corresponding area of the I / O memory with the data obtained as a result of the execution is executed.

  In peripheral service processing, user programs can be uploaded and downloaded by exchanging I / O data with other PLCs via the network, or by communicating with the tool device via the network. Various peripheral service processes are executed.

  On the other hand, the data collection unit (SPU) 2, which is the main part of the present invention, is similar to the system bus to which the input / output unit (CPU unit), the I / O unit, the communication unit, etc. are connected as described above. Connector is connected. In the I / O refresh process described above, predetermined I / O data (bit variables, analog variables) is read from the I / O memory and the data memory via the system bus in synchronization with the cycle time. (Collect) and store it in the internal memory. At this time, the contents of the collected I / O data (bit variables, analog variables) are determined by the setting file F2 built in the SPU2.

  As will be described later, this I / O data is saved in the file format as one or a plurality of files (recording data file F1), and after waiting for a read command to arrive from the PC 1 side, the collected results of the plurality of times. The recording data file F1 is transmitted to the PC1 side.

  Next, the configuration on the PC 1 side will be described in detail. As shown in FIG. 1, the PC 1 and the PSU 2 are connected via a network, and the recording data file F1 and the setting file F2 stored in the PSU 2 can be acquired. The PC 1 includes a data management device 11, a database 12, and an application 13.

  The data management device 11 includes a DB table creation unit 11a, a file copy unit 11b, and a database storage unit (DB storage unit) 11c, which are realized by software by installing predetermined programs on the PC 1, respectively. Has been. Further, the database (DB) 12 is a main part of the present invention, but is actually composed of commercially available DB software as will be described later. Furthermore, the application 13 means a user-side application for processing data stored in the DB 12.

  FIG. 2 shows a flowchart schematically showing the entire PC-side process. It should be noted that this flowchart omits pre-processing on the PC side such as SPU 2 setting processing and various control file creation processing.

  As shown in the figure, the CPU of the PC first activates a program corresponding to the DB table creation unit 11a in order to create a table for storing data (step 201). The DB table creation unit 11a is usually realized as a setting tool having a display screen. Then, the DB table creation unit 11a acquires the setting file F1 from the SPU 2, and recognizes the format of the recording data, the data type information, and the like based on the acquired setting file F1. Based on these pieces of recognition information, a data storage table is created in the DB 12. At this time, the operation setting of the DB storage unit 11c is also executed simultaneously (step 202).

  In the CPU of the PC 1, programs corresponding to the file copy unit 11b and the DB storage unit 11c are started (step 203). The file copy unit 11b monitors whether or not there is a predetermined recording data file F1 to be copied as a result of data collection in the SPU 2 (step 204). Here, if the existence of the recording data file F1 is confirmed (YES in step 204), the recording data file F1 is copied from the SPU2 to the PC1 (step 205).

  The DB storage unit 11c reads out each record from the recording data file F1 thus copied, and stores data corresponding to each record in a specified table in the DB 12 (step 206).

  While the series of operations (steps 204 to 206) are repeated, a predetermined end command is given to complete the series of operations (YES in step 207).

  Next, details of each component necessary for the series of operations (steps 201 to 207) described above will be described below.

  First, the configuration related to the DB table creation unit 11a will be described in detail. The DB table creation unit 11a has a function of acquiring the setting file F2 stored in the SPU2. In the setting file F2, data to be collected is defined and held as “variables”, and a set of variables to be collected is defined as “collection patterns”. The settings of “variable” and “collection pattern” are stored in the SPU 2 as a text file described in XML, which is an extensible markup language.

  In order to set and register “variables” and “collection patterns” in the SPU 2, dedicated setting tool software installed and operated on the PC 1 side is used.

  An image diagram of a screen example of the setting tool of the data collection unit (SPU) 2 is shown in FIG. As shown in the figure, when this setting tool is activated, a list of variables held in the memory in the PLC 3 is displayed on the screen of the PC 1, and at the same time, a selection target of the sampling pattern is guided and displayed. Therefore, the user can set and register any “variable” and “sampling pattern” to be sampled as text files described in XML in the SPU 2 while performing predetermined dialog processing on the premise of the display. it can.

  FIG. 4 shows a table of item tags and their contents necessary for variable definition, and FIG. 5 shows examples of XML documents of variable definitions described using such item tags.

  As shown in FIG. 4, seven item tags (Variable, Description, Address, SystemDataType, DataSize, ArrayUBound, and PLCDDataFormat) each having the meaning content shown in the figure are used to define the “variable”. Then, by using these item tags, as shown in FIG. 5, a “variable” (in this example, “variable 0000”) to be collected is defined. Note that “variable 0000” on the setting screen of FIG. 3 does not correspond to “variable 0000” on the sentence example of FIG.

  FIG. 6 shows a table of item tags and their meanings necessary for defining the collection pattern, and FIG. 7 shows an example XML document of the collection pattern definition described using such item tags.

  As shown in FIG. 6, five item tags (DataCollection, Journal, Trigger, Items, Item) each having the meaning content shown in the figure are used to define the “collection pattern”. By using these item tags, a “collection pattern” (in this example, “sampling pattern 1”) at the time of collecting data corresponding to a variable is defined as shown in element 7.

  Next, a configuration related to the DB table creation unit 11a will be described in detail. The DB table creation unit 11a automatically creates a table for storing the data of the recording data file F1 acquired from the data collection unit 2 in the database (DB) 12 based on the setting file F2 acquired from the data collection unit 2. The function to create automatically.

  As shown in FIG. 8, the DB table creation unit 11a has a function of creating three tables, that is, a management table 12a, a data storage table 12b, and a variable table 12c in the database (DB) 12.

  The data storage table 12b is a table for storing data collected by the data collection unit 2. One DB 12 can have a plurality of data storage tables.

  The structure of the data storage table is shown as a table in FIG. As shown in the figure, in the data storage table 12b, there are “header fields” (RECORD_INDEX, TIME_STAMP, TIMESTAMP_NANO, SAMPLING_INDEX) such as indexes and “data fields” (DATA_0 to DATA_n) for storing actual data. Is provided.

  The variable table 12c is a table that stores attribute information of variable data stored in the data field of the data storage table 12b. A single DB 12 can have a plurality of data variable tables, which correspond one-to-one with the data storage table 12b. Thereby, it is possible to know from the application what data is stored in the data storage table 12b. The structure of the variable table is shown as a table in FIG.

  The management table 12a is a table that manages the correspondence between the data storage table 12b and the variable table 12c. There can be only one management table in one DB 12. Thereby, the application can know what kind of data storage table 12b exists in the DB 12 by referring to the management table 12c. The structure of the management table 12c is shown as a table in FIG.

  Here, the correspondence to the difference for each type of database (DB) will be described. Commercially available database systems have different specifications for each product, and the types of fields in the database table differ for each product. Therefore, when the DB table creation unit 11a creates the tables 12a to 12c, it is necessary to create a table according to the type of the target database product.

  In the present invention, a DB definition file F3 for defining the difference is prepared for each type of database product to be supported. When creating the data storage table 12b, the DB table creation unit 11a refers to the DB definition file F3 and creates an appropriate data storage table 12b for each database product.

  Item tags and contents necessary for DB definition are shown in a table in FIG. 12, and XML document examples of DB collection files described using such item tags are shown in FIG.

  As shown in FIG. 12, the definition of “DB” uses five item tags (Database, ConnectionFormat, DataTypes / Data Type, CreateTable / EDMS_SYSTEM, CreateTable / VARS) each having the semantic content shown in the figure. Then, by using these item tags, as shown in FIG. 13, the unique features (difference information) of the “database product” used (in this example, “Access (registered trademark)”) are defined. .

  Next, the configuration related to the file copy unit 11b will be described in detail. The file copy unit 11b monitors the data collection unit 2 to check whether or not the collected recording data file F1 exists. If there is, the recording data file F1 is transferred to an external storage device such as a hard disk of the personal computer (PC) 1. make a copy.

  That is, the data collection unit 2 reads the contents of the real I / O memory of the PLC 3 according to the setting, and records the read data in the recording data file F1. In addition to the collected data, an index and a collected time (time stamp) are simultaneously recorded in the recording data file F1. Each data is expressed as text separated by commas “,”, and one record is recorded for each collection. An example of the recording data file F1 is shown in FIG. Such recording data F1 is copied to an external storage device such as a hard disk of the personal computer (PC) 1 by the action of the file copy unit 11b.

  Next, a configuration related to the DB storage unit 11c will be described in detail. A flowchart showing processing for realizing the function of the DB storage unit is shown in FIG. When processing is started in the figure, first, the value of the record pointer is set to the first record (step 1501), and then one record is read from the recording data file F1 copied by the file copy unit 11b (step 1502). Based on the position of “,”, each record is decomposed into one record (step 1503), an SQL statement that is a database operation language is created (step 1504), and the data storage table 12b of the database (DB) 12 is stored. The process of adding data (step 1505) is continuously repeated while incrementing the record pointer (step 1507) until the last record is reached (NO in step 1506).

  As described above, according to the above embodiment, the recording data file F1 and the setting file F2 are transferred from the SPU 2 on the PLC 3 side to the PC 1 side at an appropriate timing, and based on the setting file F 2 thus transferred, Since a table suitable for the structure of each collected data is automatically created in the database 12 in a form suitable for the DB software product being used, the variables collected by the SPU on the PLC side can be changed or increased or decreased. Even in this case, there is no need to manually recreate the DB table incorporated in the PC, and the degree of freedom for changing the collected data on the PSU side can be improved.

  In the above embodiment, network communication such as Ethernet (registered trademark) is used as means for transferring the recording data file F1 and the setting file F2 from the SPU 2 to the PC 1, but a CD-ROM, USB memory, Of course, it may be replaced with delivery by a memory medium such as a CF card memory.

  According to the present invention, even if the variables collected by the SPU are changed or increased / decreased on the PLC side, there is no need to manually recreate the DB table incorporated in the PC. The degree of freedom of change can be improved.

It is a functional block diagram of the whole system which concerns on this invention. It is the general flowchart which shows the whole PC side process roughly. It is an image figure of the example of a screen of the setting tool of a data collection unit. It is a figure which shows the item tag required for the definition of a variable, and its meaning content as a table | surface. It is a figure which shows the XML document example of a variable definition. It is a figure which shows the item tag required for the definition of a collection pattern, and the meaning content as a table. It is a figure which shows the XML document example of a collection pattern definition. It is a figure which shows the structure of a database (DB) table. It is a figure which shows the structure of a data storage table as a table | surface. It is a figure which shows the structure of a variable table as a table | surface. It is a figure which shows the structure of a management table in a table | surface. It is a figure which shows the item tag required for DB definition, and its meaning content. It is a figure which shows the example of an XML document of a DB collection file. It is a figure which shows the example of a recording data file. It is a flowchart which shows the process for implement | achieving the function of a database storage part.

Explanation of symbols

1 Personal computer (PC)
2 Data collection unit (SPU)
3 Programmable controller (PLC)
DESCRIPTION OF SYMBOLS 11 Data management apparatus 11a DB table preparation part 11b File copy part 11c DB storage part 12 Database (DB)
12a management table 12b data storage table 12c variable table 13 application 100 data collection management system

Claims (3)

A data collection device connected to the programmable controller for collecting and storing data stored in the memory of the programmable controller;
A computer with a commercially available database incorporated therein,
In the data collection device,
A setting file that holds setting information related to data to be collected, and a recording data file that includes data collected from the memory of the programmable controller according to the setting information of this setting file are provided.
The computer includes
Database table creating means for creating a table having a corresponding structure in the database based on the setting file acquired from the data collection device;
Database storage means for assembling corresponding SQL data based on the recording data file acquired from the data collection device and additionally storing the corresponding data in the database; and
Furthermore, between the data collection device and the computer, a delivery means for delivering the setting file and the recording data file from the data collection device side to the computer side is provided.
A data acquisition management system characterized by that.   The database table creation means creates a table corresponding to each database product in the database based on a database definition file that defines the characteristics of one or more commercially available databases. The data acquisition management system described.   The data acquisition management system according to claim 1 or 2, wherein the delivery means is delivery by communication or delivery by a recording medium.

Images