JP5836422B2 - Method and system for data matching - Google Patents

Description

  The present invention relates generally to techniques for data matching, and more particularly to a method and system for fast data matching between a source database and a replicate database.

  In a database system including a source database and a duplicate database, data matching between the source database and the duplicate database is important.

  FIG. 4 is a conceptual diagram showing a conventional data matching method.

  That is, according to the method shown in FIG. 4A, the first data record is acquired from the source database, and the acquired first data record is written in the duplicate database. When the writing of the first data record to the duplicate database is completed in this way, the second data record is acquired from the source database and similarly written to the duplicate database.

  Such retrieval / write processing is performed sequentially for each data record until writing of the last data record is completed, thereby matching the data between the source database and the duplicate database. Is achieved.

  However, in such a sequential method, access to the source database to obtain data records and access to the duplicate database to write data records are repeated a number of times equal to the number of data records. Therefore, there is a problem that it takes a very long time before the last data record is written in the duplicate database.

  One method for solving such a problem is a bulk copy method as shown in FIG. In this method, a large amount (for example, 100,000 records) of data records are collectively acquired from the source database, and thereafter, are collectively written in the duplicate database. According to this method, it is not necessary to repeat the access to the source database for obtaining the data record and the access to the duplicate database for writing the data record, the number of times equal to the number of records. The time required for the last data record to be written to the duplicate database is greatly reduced compared to the method shown in FIG.

  As an example, when 100,000 data records are copied from a source database to a duplicate database, in the sequential copy method shown in FIG. 4A, acquisition and writing are looped 100,000 times, While it takes about 10 hours, the bulk copy method shown in FIG. 4B requires only about 34 minutes.

  As for the bulk copy, as shown in Non-Patent Document 1, a general component called SQL Bulk Copy is prepared that batch-writes large-sized data at a high speed in a Microsoft SQL Server table.

  Non-Patent Document 2 provides a general part called OracleBulkCopy that batch-writes large data at a high speed to an Oracle Database table.

  Non-Patent Document 3 provides a general component called DB2BulkCopy that batch-writes large-size data at a high speed into the IBM DB2 Database table.

Microsoft Developer Network home page, http://msdn.microsoft.com/en-us/library/7ek5da1a(v=vs.110).aspx Oracle Data Provider for .NET Developer's Guide home page, http://docs.oracle.com/cd/E16635_01/win.111/e06104/OracleBulkCopyClass.htm IBM DB2 Database for Linux, UNIX, and Windows Information Center home page, http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=%2Fcom.ibm.swg.im.dbclient. adonet.ref.doc% 2Fdoc% 2FDB2BulkCopyClass.html

  However, such a conventional bulk copy method has the following problems.

  That is, in the bulk copy method, as described above, a large amount (for example, 100,000 records) of data records are acquired from the source database at a time. FIG. 5 is a conceptual diagram for explaining a process in which a large amount of data records are acquired by the server 62 from the source database 60 and written to the duplicate database 70 in this way.

  The server 62 connected to the source database 60 and the duplicate database 70 includes a data acquisition unit 64, a memory 66, and a data writing unit 68. The data record stored in the source database 60 is acquired by the data acquisition unit 64, transferred from the data acquisition unit 64 to the memory 66, and held in the memory 66. When all the data records acquired by the data acquisition unit 64 are stored in the memory 66, the data records stored in the memory 66 are retrieved by the data writing unit 68 and written to the duplicate database 70.

  That is, how much data records can be obtained from the source database 60 at a time depends on the capacity of the memory 66.

  If the memory 66 has sufficient capacity, all data records could be obtained from the source database 60 at once. However, there is a problem that preparing a memory 66 having a sufficient capacity causes an increase in cost. Even if the capacity of the memory 66 is sufficient at the present time, there is always a problem that if the data size stored in the source database 60 increases in the future, it may be necessary to increase the capacity of the memory. Let's go.

  On the other hand, if the memory 66 does not have a sufficient capacity, all the data records stored in the source database 60 cannot be held at one time. And the process of writing the acquired data record to the duplicate database 70 must be repeated several times. In that case, there is a problem that it takes a long time to complete writing of all data records in the duplicate database 70.

  The present invention has been made in view of such circumstances, and when copying data records stored in a source database to a duplicate database, even if the number of data records stored in the source database increases. Another object of the present invention is to provide a data matching method and system capable of performing copying to a duplicate database at high speed without the need for increasing the memory capacity.

  In order to achieve the above object, the present invention takes the following measures.

That is, the invention of claim 1 is a system for matching data between a source database and a duplicate database, and sequentially transmits a plurality of data records stored in the source database while sequentially acquiring them. An acquisition unit; a storage unit that sequentially receives and sequentially stores the data records sent by the acquisition unit; and when the data record is stored in the memory, the stored data record Writing means is provided for writing to the duplicate database even when the record is being stored in the memory, and deleting the data record from the memory after the writing is completed. Then, the writing means repeats the writing of the data record stored in the memory to the duplicate database and the deletion from the memory after the writing is completed until there is no data record stored in the memory. The data records stored in the source database and the data records stored in the duplicate database are matched.
Here, the acquisition unit and the storage unit are connected via a communication network, and the acquisition unit is a Web server.
Further, a Web server is provided between the source database and the writing means so that the source database and the writing means are not directly connected.

  The invention according to claim 2 is the data matching system according to claim 1, wherein the obtaining means, the storing means, and the writing means operate independently.

The invention of claim 3 is a method for data matching between a source database and a replica database, wherein a Web server sequentially acquires a plurality of data records stored in the source database and sequentially transmits them. Then, sequentially sent data records are sequentially received and stored in the memory, and when the data record is stored in the memory, the stored data record is stored in the subsequent data record in the memory. Even in the middle, data is written to the duplicate database, and after the data is written, this data record is deleted from the memory. Then, by repeatedly writing the data record stored in the memory to the duplicate database and deleting it from the memory after the writing is completed, the data record stored in the memory does not exist. Matching the stored data records with the data records stored in the duplicate database.
Also, a Web server is provided between the source database and the memory so that the source database and the memory are not directly connected.

  According to the method and system for data matching according to the present invention, when data records stored in a source database are copied to a duplicate database, the number of data records stored in the source database increases. However, it is possible to complete copying to the duplicate database at high speed without having to increase the memory capacity.

It is a conceptual diagram which shows the structural example of the system by which the data matching method which concerns on embodiment of this invention was applied. It is a flowchart which shows operation | movement of the system which concerns on embodiment of this invention. It is a figure which shows the timing of acquisition of the data record from the source database by the system which concerns on embodiment of this invention, and the writing of the data record to a replication database. It is a conceptual diagram which shows the conventional data matching method. It is a conceptual diagram for demonstrating the conventional bulk copy system.

  The best mode for carrying out the present invention will be described below with reference to the drawings.

  FIG. 1 is a conceptual diagram showing a configuration example of a system to which a data matching method according to an embodiment of the present invention is applied.

  This system 10 is a system for data matching between a source database 12 and a duplicate database 14, and in addition to the source database 12 and the duplicate database 14, for example, a data acquisition unit 16 that is a Web server, The data writing unit 18 is provided. The data acquisition unit 16 and the data writing unit 18 are connected by a communication network 19 such as the Internet.

  Such a communication network 19 includes a LAN such as Ethernet (registered trademark) or a WAN to which a plurality of LANs are connected through a public line or a dedicated line. In the case of a LAN, it is composed of a number of subnets via routers as necessary. In the case of a WAN, a firewall or the like for connecting to a public line is provided as appropriate, but illustration and detailed description thereof are omitted here.

  The data acquisition unit 16 is connected to the source database 12, and sequentially sends out a plurality of data records stored in the source database 12 to the data writing unit 18 via the communication network 19. . In other words, the data acquisition unit 16 sequentially transmits the already acquired data records to the data writing unit 18 even while the subsequent data records are being acquired.

  The data writing unit 18 includes a receiving unit 20, a memory 22, and a writing unit 24. Then, the receiving unit 20 sequentially receives the data records sequentially transmitted by the data acquisition unit 16 and sequentially stores them in the memory 22. When a data record (for example, data record # 1) is stored in the memory 22, the writing unit 24 performs subsequent data records (for example, data records # 2, # 3, # 4,...). ) Is stored in the memory 22, the stored data record (data record # 1) is written to the replication database 14. Then, when a data record (data record # 1) is written in the duplicate database 14, this data record (data record # 1) is deleted from the memory 22.

  Thereafter, the writing unit 24 performs the same operation for the next data record (for example, data record # 2). The writing unit 24 writes all the data records stored in the memory 22 in the duplicate database 14 by continuously repeating such an operation until the last data record stored in the memory 22.

  Next, the operation of the system according to the embodiment of the present invention configured as described above will be described with reference to the flowchart of FIG.

  In the system 10 according to the present embodiment, a plurality of data records stored in the source database 12 are sequentially acquired by a data acquisition unit 16 such as a Web server and sequentially transmitted to the data writing unit 18 ( Step S1).

  The data records sequentially transmitted in step S1 are sequentially acquired by the receiving unit 20 of the data writing unit 18 and sequentially stored in the memory 22 (step S2).

  When a data record (for example, data record # 1) is stored in the memory 22, subsequent data records (for example, data records # 2, # 3, # 4,...) Are stored in the memory 22. Even during the process, the stored data record (data record # 1) is written into the replication database 14 by the writing unit 24 (step S3).

  When the data record (data record # 1) is thus written in the duplicate database 14, the data record (data record # 1) is deleted from the memory 22 by the writing unit 24 ( Step S4).

  Thereafter, the writing unit 24 performs the same processing as in step S3 and step S4 for the next data record (for example, data record # 2). Such an operation is continuously repeated until the last data record stored in the memory 22 (step S5), whereby all the data records stored in the memory 22 are written in the duplicate database 14.

  FIG. 3 is a diagram showing the timing of data record acquisition from the source database 12 by the system 10 according to the present embodiment and writing of the data record to the duplicate database 14 in comparison with the prior art. is there. 3A shows the sequential copy method shown in FIG. 4A, and FIG. 3B shows the bulk copy method shown in FIG. 4B.

  Steps S1 and S2 correspond to the data record acquisition process a in FIG. 3C, and steps S3 and S4 correspond to the data record writing process b in FIG. 3C. As shown in FIG. 3C, in the system 10 according to the present embodiment, when even one data record is stored in the memory 22, writing of this data record to the duplicate database 14 is started. Therefore, even if the data record acquisition process a is not completed, the data record writing process b is started. That is, when a data record is stored in the memory 22 in step S2, the operation of step S3 is started, and thereafter, the operations of step S1 and step S2 and the operations of step S3 and step S4 are independent. Executed.

  The time required for the writing of the last data record to the duplicate database 14 after the first data record is acquired from the source database 12 by such an independent operation is as shown in FIG. This is much shorter than the bulk copy method shown in

  As an example, when 100,000 data records are copied from the source database 12 to the duplicate database 14, the sequential copy method shown in FIG. 3A requires about 10 hours, whereas FIG. As described above, the bulk copy method shown in FIG. 3 only requires about 34 minutes, but the system 10 according to the present embodiment shown in FIG. 3C requires only about 18 minutes. That is, according to the system 10 according to the present embodiment, the processing time required for the conventional bulk copy method is further reduced to about half. Moreover, as described in step S4, when the data record is written in the duplicate database 14, it is deleted from the memory 22 in sequence, so that the memory 22 does not require a very large capacity.

  Such an operation can also be realized by the above-described SQL Bulk Copy, Oracle Bulk Copy, and DB2 Bulk Copy. For example, it is assumed that SqlBulkCopy is used in a data writing unit 68 of a server 62 directly connected to a source database 60 and a duplicate database 70 as shown in FIG. However, the source database 60 and the data writing unit 68 are directly connected between the source database 60 and the server 62 because of a limitation of a network or the like, or a data processing process instead of a simple copy between tables. If the connection cannot be established, all data must be temporarily stored in the memory 66 by the data acquisition unit 64 in order to be read by the SQL Bulk Copy used by the data writing unit 68. Therefore, when the SQL Bulk Copy is applied as it is, a large amount (for example, 100,000 records) of data records from the source database 60 are stored in the memory 66 as described above, so that the memory 66 has a sufficient capacity. Need to be.

  However, as shown in FIG. 1, if a data acquisition unit 16 such as a Web server is provided and a data writing unit 18 that is not directly connected to the source database 12 is applied with SqlBulkCopy, the data 22 is stored in the memory 22. When a record (for example, data record # 1) is stored, a subsequent data record (for example, data record # 2, # 3, # 4,...) Is being stored in the memory 22. Even so, the stored data record (data record # 1) is written to the duplicate database 14, and then the data record (data record # 1) is deleted from the memory 22. The system 10 according to the present embodiment is realized.

  As described above, in the system 10 according to the present embodiment, when the data record stored in the source database 12 is copied to the duplicate database 14 by the operation as described above, the data record stored in the source database 12 is stored. Even if the size of the data record increases, copying to the replication database 14 can be completed at high speed without the need to increase the capacity of the memory 22.

  If the data acquisition unit 16 and the data writing unit have sufficient memory capacity, a plurality of bulk copies may be performed in parallel. The best mode for carrying out the present invention has been described above with reference to the accompanying drawings, but the present invention is not limited to such a configuration. Within the scope of the invented technical idea of the scope of claims, a person skilled in the art can conceive of various changes and modifications. The technical scope of the present invention is also applicable to these changes and modifications. It is understood that it belongs to.

  For example, in the present embodiment, as illustrated in FIG. 1, the configuration example in which the data acquisition unit 16 and the data writing unit 18 are connected via the communication network 19 such as the Internet has been described. The system and method are not limited to such a configuration, and the data acquisition unit 16 and the data writing unit 18 may be directly connected without the communication network.

  The system and method for data matching according to the present invention comprises a source database and a replica database. For example, a database system for resident data management of a local government, a financial institution, a securities company, an insurance company The present invention is also preferably applied to a database system such as

DESCRIPTION OF SYMBOLS 10 System 12 Source database 14 Replication database 16 Data acquisition part 18 Data writing part 19 Communication network 20 Reception part 22 Memory 24 Writing part 60 Source database 62 Server 64 Data acquisition part 66 Memory 68 Data writing part 70 Replica database

Claims (3)

A system for achieving data consistency between a source database and a duplicate database using bulk copy ,
Obtaining means for sequentially sending out a plurality of data records stored in the source database;
Storage means for sequentially receiving the data records sequentially sent by the obtaining means and sequentially storing them in a memory;
When a data record is stored in the memory, the stored data record is written to the duplicate database even when a subsequent data record is being stored in the memory. Writing means for deleting the data record from the memory;
The writing means writes the data record stored in the memory to the duplicate database and deletes the data record from the memory after the writing is completed until there is no data record stored in the memory. By repeating, the data record stored in the source database and the data record stored in the duplicate database are matched,
Said acquisition means, said storage means being connected through a communication network, said acquiring means, Ri Oh the Web server,
A data matching system in which the Web server is provided between the source database and the writing means so that the source database and the writing means are not directly connected .   The data matching system according to claim 1, wherein the acquisition unit, the storage unit, and the writing unit operate independently. A method for achieving data matching of between the source database and the duplicate database using bulk copy,
The Web server sequentially sends out a plurality of data records stored in the source database,
Sequentially receiving and sequentially storing the sequentially sent data records in memory;
When a data record is stored in the memory, the stored data record is written to the duplicate database even when a subsequent data record is being stored in the memory. Delete this data record from the memory,
By repeating the writing of the data record stored in the memory to the duplicate database and the deletion from the memory after completion of the writing until the data record stored in the memory does not exist, the source The data records stored in the database are matched with the data records stored in the duplicate database ;
A data matching method in which the Web server is provided between the source database and the memory so that the source database and the memory are not directly connected .

Images