JP2017027349A - Replication program - Google Patents

Abstract

PROBLEM TO BE SOLVED: To suppress data mismatch due to difference of handling of a specific character before and after replicating of a database.SOLUTION: A replication program of the invention is configured to convert zero byte length characters string and a null value on a replication source database, according to handling of the character strings on the replication source database.SELECTED DRAWING: Figure 1

Description

  The present invention relates to database replication.

  Database replication is a process of creating a duplicate database having exactly the same contents as a certain database, and is distinguished from so-called backup. By using replication, for example, the contents of the master database can be reflected in the slave database. This technique is used when making a database redundant.

  If the entire contents of the database are replicated every time replication is performed, a large amount of resources are required from the viewpoint of processing load and communication load. Therefore, as a practical method, it is conceivable to extract only the updated portion of the master database and reflect this in the slave database.

  The following Patent Document 1 describes a technique related to database system replication. In this document, a log-based algorithm and a trigger-based algorithm are exemplified as an algorithm for tracking data changes for transaction replication (see 0007 to 0008 in the same document).

JP 2005-301329 A

  Database replication may be performed between different types of host computers. Depending on the model of the host computer, the handling of specific characters used in the database may differ. Furthermore, even a host computer of the same model may have different handling of specific characters depending on the database management system (DBMS). If the specific character is not handled correctly in the replication destination database, the contents of the record are not correctly recognized, and therefore there is a possibility that consistency before and after the replication cannot be achieved.

  The technique described in Patent Document 1 focuses on maintaining transaction consistency even after replication, and considers problems caused by replicating databases between different models (or between different DBMSs). It has not been. However, in actual operation situations, for example, replication may be performed from a DBMS on an open platform (Windows (registered trademark), etc.) to a DBMS on a general-purpose machine, and therefore the handling of specific characters on each platform before and after replication is different there is a possibility. Even in such a case, a technique capable of maintaining the consistency of the database is required.

  The present invention has been made in view of the above-described problems, and an object of the present invention is to suppress data mismatch caused by different handling of specific characters before and after database replication.

  The replication program according to the present invention converts a zero-byte length character string and a null value in the replication source database according to the handling of these character strings in the replication destination database.

  According to the replication program of the present invention, it is possible to correctly perform replication by absorbing differences in handling of a zero-byte length character string and a null value for each database management system.

1 is a diagram showing a replication computer 100 that executes a replication program 120 according to Embodiment 1 and its peripheral configuration. FIG. It is a figure which shows the structure and data example of a conversion table. It is a figure which shows the structure and data example of the log | history table 230. FIG. FIG. 6 is a diagram for explaining the operation of a replication program 120. It is a figure which shows the structure and example of a data of the conversion table 140 in Embodiment 2. FIG. It is a figure which shows the structure and data example of the conversion table 140 in Embodiment 3. FIG.

<Embodiment 1>
FIG. 1 is a diagram showing a replication computer 100 that executes a replication program 120 according to the first embodiment of the present invention and its peripheral configuration. The replication computer 100 is a computer that replicates a first DB (DataBase) 220 to a second DB 320, and includes a CPU (Central Processing Unit) 110, a replication program 120, and a storage device 130.

  The CPU 110 executes the replication program 120. The replication program 120 is a program that implements a process of replicating a database. In the following, for convenience of description, the replication program 120 may be described as an operation subject. However, it is added that the CPU 110 actually executes this.

  The storage device 130 stores a conversion table 140. The conversion table 140 defines a rule for converting a specific character on the replication source database into a format on the replication destination database when the replication program 120 performs replication. A specific example of the conversion table 140 will be described later.

  The first DB computer 210 is a computer that executes a first database management system (not shown) that implements the first DB 220. The first DB computer 210 stores an update operation for the first DB 220 in the history table 230. A specific example of the history table 230 will be described later. The first DB computer 210 further uses the numbering data 240 when storing the update operation in the history table 230. The numbering data 240 will be described later.

  The second DB computer 310 is a computer that executes a second database management system different from the first database management system. The second database management system has a second DB 320 mounted.

<Embodiment 1: About database management system>
The database management system is supplemented below. The database is not simply a collection of data files. For example, (a) a transaction is accepted and executed, (b) a transaction log is recorded, and (c) a predetermined process is executed when a predetermined event occurs. (Trigger function). A program that implements these functions is generally called a database management system. How these functions are implemented depends on the individual database management system. Therefore, the function specifications may be different for each database management system.

  Each record held in the database has a data type. For example, a record or column that holds a character string record has a character string type such as a fixed-length character string type / variable-length character string type, and a record or column that holds a numeric record is a numeric type such as an integer type or a floating-point type Have

  Some database management systems may handle data types differently. For example, when a zero-byte length character string “” is stored in a variable-length character string type column, it is stored as a zero-byte length character string “” as defined in one database management system, whereas another database management. In the system, storing this as a null value (a special bit value indicating no data) has actually occurred.

  In general, a zero-byte length character string and a null value are completely different on the application. For example, a zero-byte character string may be handled as a character string, while a null value may be handled as an invalid data value. Then, for example, when the record stored in the first DB 220 and the application that reads and writes the record are migrated to the second DB computer 310 (the first DB 220 is replicated to the second DB 320), the application may not operate correctly. For example, when the application is processing a zero byte length character string on the first DB 220 as a character string variable, the application may not be able to process the same record on the second DB 320 as a character string. Specifically, the process of adding a character string to a zero-byte length character string may not operate normally as a process for an invalid data value.

  Therefore, in the present invention, in order to eliminate the above-mentioned problems associated with replication between different database management systems, processing is performed so that such problems do not occur for data that is handled differently between database management systems.

<Embodiment 1: Table configuration>
FIG. 2 is a diagram illustrating the configuration of the conversion table 140 and data examples. The conversion table 140 is a data table that defines data conversion rules before and after replication. The conversion table 140 includes a table name field 141, a column name field 142, a zero-byte length character string conversion rule field 143, and a null value conversion rule field 144.

  The table name field 141 and the column name field 142 are character strings that uniquely identify the table name and column name of the replication source database. If the source database has multiple databases, you can also specify the database name. The zero byte length character string conversion rule field 143 is a format for specifying what value is stored in the replication destination database when the column value of the zero byte length character string is stored in the replication source database. Indicates. The null value conversion rule field 144 indicates a format for designating what value is stored in the replicate destination database when the null value column value is stored in the replicate source database.

  In the data example of the first row shown in FIG. 2, when the “column1” column of the “table1” table of the replication source database stores a zero-byte length character string, it should be replaced with a space character at the replication destination. Is defined. Similarly, when a null value is stored in the same column, it is defined that this should be stored as a null value at the replicate destination. Columns for which no conversion rule is defined may be replicated as they are without performing special conversion.

  FIG. 3 is a diagram illustrating a configuration of the history table 230 and an example of data. The history table 230 is a data table that records update operations for the first DB 220. The history table 230 has an order field 231, a status field 232, and a command statement field 233.

  The order field 231 holds a serial number for which update processing has been performed on the first DB 220. The status field 232 indicates whether or not the record has been reflected on the second DB 320. The command statement field 233 indicates the command statement of the update operation performed on the replication source database. The update operation here is not limited to the UPDATE instruction in SQL, for example, but includes all write operations to the database including the INSERT instruction and the DELETE instruction.

  The history table 230 further has a field for holding a specified value for each column in the recorded update operation. For example, an INSERT instruction has a field for holding a value stored for each column, and a DELETE instruction has a field for holding a column value in a conditional statement for specifying a record to be deleted. . Since the fields of each table are different for each table, a history table 230 is provided for each table of the replication source database. For example, when a table in the first DB 220 has two columns, a field for recording operations for the two columns can be provided as shown in FIG.

  Update operations on the database are often performed across multiple tables. An update operation for a certain table may be performed with reference to a record in another table. In order to perform the update operation across the plurality of tables while maintaining the consistency between the tables, the order in which the update operations are performed becomes important. Therefore, in the first embodiment, the order in which the update operation is performed on the replication source database (here, the first DB 220) is recorded in the order field 231.

  As a means for detecting the occurrence of an update operation for the replication source database, for example, a trigger that is activated when the update operation is performed may be implemented in the replication source database. Many database management systems are equipped with a trigger mechanism, which is easy to implement.

  The numbering data 240 holds the current value of the order field 231. Each update operation is obtained by referring to the numbering data 240 at the start of each trigger, acquiring the current value, recording the update operation in the history table 230, and then incrementing the current value of the numbering data 240 by one. Can be recorded. In addition, even in the case of an update operation extending over a plurality of tables, the current value to be referred to when recording each update operation is aggregated into the numbering data 240, so that the order in which the update process across the tables should be performed is saved. Thus, consistency can be maintained even after replication.

  FIG. 4 is a diagram for explaining the operation of the replication program 120. Hereinafter, each step of FIG. 4 will be described.

(Figure 4: Step (1): Extract untransferred history records)
The replication program 120 extracts records whose status field 232 is “untransferred” from the records stored in the history table 230. When there are a plurality of history tables 230 (that is, when there are a plurality of replication source tables), records are extracted from all the history tables 230. The extracted records may be sorted in this step according to the order field 231 or may be sorted in a later step. Here, it is assumed that sorting is performed in this step.

(Figure 4: Step (2): Applying conversion rules)
The replication program 120 applies the conversion rule defined by the conversion table 140 to the record extracted in step (1), thereby each of the zero byte length character string and the null value is replicated to the destination database (here, the second DB 320). ) Convert to the above format. For example, since the record in the third line in FIG. 3 specifies that column 1 is updated to the zero-byte character string “”, when this is reflected in the second DB 320, the conversion in the first line in FIG. According to the rules, “” of the record is converted to “” (space character).

(FIG. 4: Step (3): Update the second DB: Part 1)
The replication program 120 generates an instruction to update the second DB 320 using each record after converting the zero-byte length character string and the null value in step (2). The type of update command to be executed for the second DB 320 is described in the command statement field 233. Each column value to be specified in the update command is described in a column value portion of each history table 230 (or a value after conversion when conversion is performed). Therefore, the replication program 120 can generate an update command to be executed on the second DB 320 by referring to these fields.

(FIG. 4: Step (3): Update the second DB: Part 2)
The replication program 120 reflects the contents of the history table 230 on the second DB 320 by executing the generated update instructions in the order specified by the order field 231. The replication program 120 changes the status field 232 to “transferred” for the reflected record.

(FIG. 4: Steps (2) to (3): Supplement)
The order in which these steps are performed may be switched. That is, the conversion rule may be applied after generating an update command for the second DB 320.

<Embodiment 1: Summary>
The replication program 120 according to the first embodiment converts the zero-byte length character string and the null value in the first DB 220 into the format in the second DB 320, and then replicates the record in the first DB 220 to the second DB 320. As a result, the application can be operated without data inconsistency even after replication.

  The replication program 120 according to the first embodiment converts the format of the zero byte length character string and the format of the null value in the second DB 320 for each column. As a result, the conversion can be performed only for the column to be converted, and the replication processing efficiency can be improved. Further, the above conversion can be performed without depending on the column structure of the table.

<Embodiment 2>
FIG. 5 is a diagram illustrating a configuration and data example of the conversion table 140 according to the second embodiment of the present invention. In the second embodiment, the conversion table 140 has a new extraction format field 145 in addition to the configuration described in the first embodiment. Other configurations are the same as those of the first embodiment.

  The extraction format field 145 specifies what data type the record is handled when extracting the record of the replication source database. For example, “TEXT” indicates that the original record is handled as a character string type, and “FLOAT3.2” indicates that it is handled as a floating-point type having a 3-digit integer part and 2-digit decimal part.

  When the replication program 120 extracts the replication source record, the replication program 120 takes measures such as truncating the portion that does not match the data type specified by the extraction format field 145 as appropriate. For example, if the extraction source column for which “FLOAT3.2” is specified has three or more decimal parts, the third and subsequent digits are rounded down or rounded off. Alternatively, when the extraction source column for which “FLOAT3.2” is designated is not a numerical value, the column value after extraction is set to “0.0”, for example.

<Embodiment 3>
In the first embodiment, it is assumed that replication is performed from the first DB 220 to the second DB 320. However, the present invention can also be used when performing replication from the second DB 320 to the first DB 220. In the third embodiment of the present invention, an example will be described.

  Even when the replication is performed from the second DB 320 to the first DB 220, the processing procedure similar to that of the first embodiment can be used because only the database to be processed is replaced. However, in view of the fact that the second DB 320 handles the zero byte length character string as a null value, a measure for substituting a space instead of the zero byte length character string is taken in advance by the replication program 120 or manual operation, and the second DB 320 that is the replication source is applied. May contain records. Therefore, in the third embodiment, when the replication source record is a space character string, how to handle it at the replication destination is defined in advance.

  FIG. 6 is a diagram illustrating a configuration and data example of the conversion table 140 according to the third embodiment. In the third embodiment, the conversion table 140 has a space conversion rule field 146 in addition to the configuration described in the first embodiment. Other configurations are the same as those of the first embodiment.

  The space conversion rule field 146 indicates a format for designating what value is stored in the replication destination database when the column value of the space character string is stored in the replication source database. According to the above assumption, the space character string should be stored as a zero-byte length character string in the replication destination database, so the record on the first line in FIG. 6 specifies that fact. However, there is a case where it is originally intended to store a space depending on the property of the column. For such a column, this field may not be specified and the replication source record may be replicated as it is. The record on the second line in FIG. 6 indicates that.

<Modification of the present invention>
The present invention is not limited to the above-described embodiments, and includes various modifications. For example, the above-described embodiments have been described in detail for easy understanding of the present invention, and are not necessarily limited to those having all the configurations described. Further, a part of the configuration of a certain embodiment can be replaced with the configuration of another embodiment, and the configuration of another embodiment can be added to the configuration of a certain embodiment. In addition, it is possible to add, delete, and replace other configurations for a part of the configuration of each embodiment.

  In the above embodiment, the replication program 120 may convert the replication source record into the character code used by the replication destination database when the DBs before and after the replication use different character codes.

  In the above embodiment, an example in which a zero-byte length character string is converted to a space character string has been shown. However, in order to avoid a problem that a zero-byte length character string is handled as a null value, any character string may be stored. Any suitable character string other than a space may be used. However, in view of the convenience of processing, it is desirable that the character or character string has some length of 1 byte or more.

  In the above embodiment, the SQL statement in the RDBMS (Relational Database Management System) is illustrated for ease of explanation, but the command to be implemented may be appropriately used according to the specification of the database management system, and is not necessarily SQL. It is not limited to imperative sentences.

  In the above embodiment, the database trigger is used as means for storing records in the history table 230, but the update operation for the replication source database may be recorded by other appropriate means.

  In the above embodiment, the database on the first DB computer 210 is replicated to the database on the second DB computer 310, but the present invention is used when performing replication between different database management systems on the same computer. You can also.

  100: Replication computer, 110: CPU, 120: Replication program, 130: Storage device, 140: Conversion table, 210: First DB computer, 220: First DB, 230: History table, 240: Numbering data, 310: Second DB Computer, 320: second DB.

Claims (5)

A replication program that causes a computer to execute a process of replicating a record held by a first database as a record held by a second database implemented using a database management system different from the first database, In the computer,
Obtaining history data describing an execution history of a first instruction for updating the first database;
Reading a conversion table defining a format of a zero byte length string on the second database and a format of null values on the second database;
Generating a second instruction for executing a process for performing the same record operation as the first instruction on the second database;
The part using the zero byte length character string of the second instruction is converted into the format on the second database according to the definition of the conversion table, and the part using the null value of the second instruction is converted into the conversion table. Converting to a format on the second database according to the definition;
Updating the second database by executing the second instruction converted by the converting step;
A replication program characterized by executing The first database and the second database are relational databases,
The conversion table defines the format of the zero byte length character string and the format of the null value for each column of the table of the first database,
In the conversion step, the computer
2. The replication program according to claim 1, wherein the conversion is performed for each portion corresponding to a column of a table included in the first database in the second instruction according to the definition of the conversion table. 3. The replication program further on the computer,
The replication program according to claim 1, wherein the step of converting the character code of the second instruction from the character code used by the first database to the character code used by the second database is executed. The conversion table defines a conversion rule that specifies conversion of a zero byte length character string on the first database into an alternative character string of 1 byte length or more on the second database;
The replication program causes the computer to convert a portion using a zero-byte length character string in the second instruction to the substitute character string in the conversion step according to the definition of the conversion rule. 1. The replication program according to 1. The conversion table defines a conversion rule that specifies conversion of the substitute character string on the first database into a zero-byte length character string on the second database;
The replication program, in the conversion step, causes the computer to convert a portion of the second instruction that uses the substitute character string into a zero-byte length character string according to the definition of the conversion rule. 4. The replication program according to 4.

Images