Replicating structured query language (SQL) in a heterogeneous replication environment

• US 10,360,236 B2
• Filed: 09/25/2015
• Issued: 07/23/2019
• Est. Priority Date: 09/25/2015
• Status: Active Grant

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Spyker, James D., Szabo, Victor L., Yao, Yongfeng
• Primary Examiner(s): Thomas, Ashish
• Assistant Examiner(s): Mahmood, Rezwanul
• Application Number:
  US14/865,559
• Publication Number:
  US 20170091298A1
• Time in Patent Office: 1,397 Days
• CPC Class Codes: G06F 16/2246 Trees, e.g. B+trees
• CPC Class Codes: G06F 16/258 Data format conversion from or to a database
• CPC Class Codes: G06F 16/273 Asynchronous replication or reconciliation

Abstract

A processor-implemented method for replicating structured query language (SQL) in a heterogeneous replication environment is provided. The method includes receiving a first statement for a multi-row operation, whereby the multi-row operation is directed to one or more tables of a database, and whereby the first statement includes a text of a SQL statement in a DBMS transaction log. The method also provides for evaluating whether to create a source tree structure from the received first statement. In response to creating the source tree structure, the method evaluates whether the source tree structure is transformable. The method transforms the source tree structure to a target tree structure in response to evaluating that the source tree structure is transformable.

Claims

1. A computer program product for replicating structured query language (SQL) in a heterogeneous replication environment comprising:

a database management system (DBMS) embodied on a computer readable storage medium, the DBMS including program instructions executable by a processor, the program instructions comprising;

program instructions to receive, by a database replication component of a database management system (DBMS), a delete or update first SQL statement that was executed on a source DBMS and is directed to one or more tables of a target DBMS having an architecture that is non-compatible with the source DBMS, being of different operating system architectures, different DBMS versions, different host server architectures, or different DBMS vendors;

program instructions to evaluate whether to create a source tree structure from the received first SQL statement based on complexity of the first SQL statement wherein the first SQL statement comprises;

     1) a column in a source table not corresponding to a column in a target table;

     2) the first SQL statement including a concatenation of columns;

or

     3) the first SQL statement including a user-defined function;

in response to creating the source tree structure, program instructions to evaluate whether the source tree structure is transformable;

based on the source tree structure being transformable, program instructions to transform the source tree structure to a target tree structure, wherein the transforming comprises mapping one or more columns in the source tree structure to corresponding columns in at least one target table by substituting values associated with columns of the source table into corresponding columns of the target table, and wherein the values comprise constant data and expressions using other columns, the values being connected using relational operators, and grouped using brackets and parentheses, but when the source tree structure is not transformable, indicating to the database replication component to apply default row level replication strategy instead of performing data transformation; and

program instructions to generate a target SQL statement for execution at the target DBMS.

2. The computer program product of claim 1, wherein the source DBMS executes the default row level replication strategy based on the DBMS being unable to create the source tree structure from the first SQL statement.

3. A computer system for replicating structured query language (SQL) in a heterogeneous replication environment comprising:

one or more processors, one or more computer-readable storage devices, and a plurality of program instructions stored on at least one of the one or more storage devices for execution by at least one of the one or more processors, the plurality of program instructions comprising;

program instructions to receive, by a database replication component of a database management system (DBMS), a delete or update first SQL statement that was executed on a source DBMS and is directed to one or more tables of a target DBMS having an architecture that is non-compatible with the source DBMS, being of different operating system architectures, different DBMS versions, different host server architectures, or different DBMS vendors;

program instructions to evaluate whether to create a source tree structure from the received first SQL statement based on complexity of the first SQL statement wherein the first SQL statement comprises;

     1) a column in a source table not corresponding to a column in a target table;

     2) the first SQL statement including a concatenation of columns;

or

     3) the first SQL statement including a user-defined function;

in response to creating the source tree structure, program instructions to evaluate whether the source tree structure is transformable;

based on the source tree structure being transformable, program instructions to transform the source tree structure to a target tree structure, wherein the transforming comprises mapping one or more columns in the source tree structure to corresponding columns in at least one target table by substituting values associated with columns of the source table into corresponding columns of the target table, and wherein the values comprise constant data and expressions using other columns, the values being connected using relational operators, and grouped using brackets and parentheses, but when the source tree structure is not transformable, indicating to the database replication component to apply default row level replication strategy instead of performing data transformation; and

program instructions to generate a target SQL statement for execution at the target DBMS.

4. The computer system of claim 3, wherein the source DBMS uses a configurable heuristic of a number of rows affected by the target SQL statement to determine whether to execute the default row level replication strategy, or to transform the first SQL statement for replication.