# US 2017/0286472 A1 · Change stream analytics for data replication systems

> Agents: this data is available as JSON from the patentgrep API and MCP server — see https://patentgrep.com/llms.txt

- Number: US 2017/0286472 A1
- Document ID: US20170286472A1
- Status: Published application
- Kind: A1
- Application number: 15/090,633
- Filed: 2016-04-05
- Published: 2017-10-05
- Earliest claimed: 2016-04-05
- Assignees: International Business Machines Corporation
- Applicants: International Business Machines Corporation
- Inventors: Paul M. Cadarette; James D. Spyker
- Web page: https://patentgrep.com/patent/US20170286472A1

## CPC

- G06F 16/2358: {Change logging, detection, and notification (replication G06F16/27)}
- G06F 16/162: {Delete operations (erasing in storage systems G06F3/0652)}
- G06F 16/221: {Column-oriented storage; Management thereof}
- G06F 16/23: Updating
- G06F 16/2365: {Ensuring data consistency and integrity}
- G06F 16/2438: {Embedded query languages}
- G06F 16/24568: {Data stream processing; Continuous queries}
- G06F 16/27: Replication, distribution or synchronisation of data between databases or within a distributed database system; Distributed database system architectures therefor
- G06F 16/273: {Asynchronous replication or reconciliation}
- G06F: ELECTRIC DIGITAL DATA PROCESSING (computer systems based on specific computational models G06N)

## Abstract

A computer-implemented method includes, by a streaming analytics component, identifying a source database table and a target database table. The target database table includes one or more target database rows and one or more target database columns. The method further includes identifying a change stream including a plurality of row-level modifications that cause the target database table to replicate the source database table. The method further includes determining that each row-level modification affects one or more common columns of the target database columns, wherein the common columns exhibit one or more common values for those of the target database rows that are affected by the row-level modifications. The method further includes generating, based on the common values and the common columns, a reconstructed modification statement and sending the reconstructed modification statement to an apply component. A corresponding computer program product and computer system are also disclosed.

## Claims

### Claim at position 1

1-20. (canceled)

### Claim 21

A computer program product comprising, by a streaming analytics component:
a machine readable storage device; and
computer code stored on the machine readable storage device, with the computer code including instructions for causing a processor(s) set to perform operations including the following:
identifying a source database table and a target database table, said target database table comprising one or more target database rows and one or more target database columns,
identifying a change stream, said change stream comprising a plurality of row-level modifications, wherein application of said plurality of row-level modifications to said target database table causes said target database table to replicate said source database table,
determining that each of said plurality of row-level modifications affects one or more common columns of said one or more target database columns, wherein said one or more common columns exhibit one or more common values for those of said one or more target database rows that are affected by said plurality of row-level modifications,
generating, based on said one or more common values and said one or more common columns, a reconstructed modification statement, and
sending said reconstructed modification statement to an apply component.

### Claim 22 (depends on 21)

The computer program product of claim 21, wherein the computer code further includes instructions for causing the processor(s) set to perform the following operation:
determining that each of said plurality of row-level modifications comprises a DELETE operation for one of said one or more target database rows.

### Claim 23 (depends on 21)

The computer program product of claim 21, wherein the computer code further includes instructions for causing the processor(s) set to perform the following operation:
determining that each of said plurality of row-level modifications comprises an UPDATE operation for one of said one or more target database rows, wherein said UPDATE operation identifies an update value for one or more of said one or more target database columns.

### Claim 24 (depends on 23)

The computer program product of claim 23, wherein said update value is identical for said plurality of row-level modifications.

### Claim 25 (depends on 21)

The computer program product of claim 21, wherein determining that each of said plurality of row-level modifications affects said one or more common columns comprises:
generating a before image for said target database table, said before image comprising a state of said target database table with said plurality of row-level modifications not applied, and said before image comprising one or more before rows and one or more before columns;
generating a cardinality for each of said one or more before columns, based only on those of said one or more before rows that are identified by said plurality of row-level modifications; and
for any of said one or more before columns, responsive to said cardinality being below a threshold, identifying that of said one or more before columns as one of said one or more common columns.

### Claim 26 (depends on 21)

The computer program product of claim 21, wherein said reconstructed modification statement comprises an operational clause and a WHERE clause, and further comprising:
querying said target database table using said WHERE clause to yield a test payload;
generating a test row count for said test payload;
generating a batch row count for said plurality of row-level modifications; and
responsive to said test row count and said batch row count being equivalent to within a predetermined tolerance, applying said reconstructed modification statement to said target database table in place of said plurality of row-level modifications.

### Claim 27 (depends on 21)

The computer program product of claim 21, wherein said streaming analytics component operates asynchronously from both said source database table and said target database table.

### Claim 28

A computer program product comprising:
a machine readable storage device; and
computer code stored on the machine readable storage device, with the computer code including instructions for causing a processor(s) set to perform operations including the following:
receiving a plurality of row-level modifications, each of said plurality of row-level modifications being for said target database table,
receiving a reconstructed modification statement, wherein said reconstructed modification statement comprises an operational clause and a WHERE clause,
testing said reconstructed modification statement against said target database table to yield a test result,
comparing said test result with said plurality of row-level modifications to yield an equivalence determination, and
responsive to said equivalence determination being positive, applying said reconstructed modification statement to said target database table in place of said plurality of row-level modifications.

### Claim 29 (depends on 28)

The computer program product of claim 28, wherein the computer code further includes instructions for causing the processor(s) set to perform the following operation:
responsive to said equivalence determination being negative, applying said plurality of row-level modifications to said target database table in place of said reconstructed modification statement.

### Claim 30 (depends on 28)

The computer program product of claim 28, wherein testing said reconstructed modification statement against said target database table to yield a test result comprises querying said target database table based on said WHERE clause to yield a test payload.

### Claim 31 (depends on 28)

The computer program product of claim 28, wherein:
testing said reconstructed modification statement against said target database table to yield a test result comprises:
initiating a transaction, and
applying said reconstructed modification statement to said target database table; and
applying said reconstructed modification statement to said target database table in place of said plurality of row-level modifications comprises committing said transaction.

### Claim 32 (depends on 31)

The computer program product of claim 31, wherein the computer code further includes instructions for causing the processor(s) set to perform the following operation, responsive to said equivalence determination being negative:
rolling back said transaction; and
applying said plurality of row-level modification statements in place of said reconstructed modification statement.

### Claim 33 (depends on 28)

The computer program product of claim 28, wherein:
comparing said test result with said plurality of row-level modifications to yield an equivalence determination is performed synchronously with said target database table.

### Claim 34 (depends on 33)

The computer program product of claim 33, wherein comparing said test result with said plurality of row-level modifications to yield an equivalence determination comprises:
querying said target database table using said WHERE clause to yield a test payload;
generating a test row count for said test payload;
generating a batch row count for said plurality of row-level modifications; and
responsive to said test row count and said batch row count being exactly equal, determining said equivalence determination to be positive;
whereby said reconstructed modification statement is proven to be equivalent to said plurality of row-level modifications.

### Claim 35 (depends on 28)

The computer program product of claim 28, wherein said database management system comprises a change data replication apply component.

### Claim 36

A computer program product comprising:
a machine readable storage device; and
computer code stored on the machine readable storage device, with the computer code including instructions for causing a processor(s) set to perform operations including the following:
identifying a change stream, said change stream being transmitted from a source database to a target database, and said change stream comprising one or more change data rows,
for each row-level operation of said one or more change data rows:
determine that said row-level operation is for a target table, said target table being of said target database,
identify an operation type for said row-level operation, said operation type being either UPDATE or DELETE,
determining, for said target table and said operation type, whether a batch is open,
responsive to no said batch being open for said target table and said operation type, opening said batch for said target table and said operation type, and
adding said row-level operation to said batch;
for each said batch:
identifying, for said batch, a batch operation type and a batch table, said batch table comprising one or more batch table rows and one or more batch table columns,
generating a before image for said batch table, said before image comprising a first state of said batch table with said batch not applied to said batch table,
for each of said one or more batch table columns, determining a cardinality among those of said one or more batch table rows that are affected by said batch, based on said before image,
identifying one or more common columns, wherein each of said one or more common columns has said cardinality less than a predetermined threshold,
generating a WHERE clause, wherein said WHERE clause references each of said one or more common columns by a column phrase, and wherein each said column phrase disjunctively references all values for that of said one or more common columns that is exhibited for any of said one or more batch table rows that are affected by said batch,
responsive to said batch operation type being DELETE, generating a candidate modification statement for said batch, said candidate modification statement comprising a DELETE statement on said batch table using said WHERE clause,
responsive to said batch operation type being UPDATE:
generating an after image, said after image comprising a second state of said batch table with said batch applied to said batch table,
comparing, row-wise, said after image with said before image to identify one or more changed rows,
determining, for each of said one or more batch table columns, whether there exists a column update value, said column update value being common to all of said one or more changed rows,
responsive to said column update value not existing for any change data row of said batch, abandoning said batch,
responsive to said column update value existing, generating a candidate modification statement for said batch, said candidate modification statement comprising an UPDATE statement on said batch table using said WHERE clause and a SET clause that identifies each said column update value,
generating a hint, said hint comprising said candidate modification statement and a metadata as to said batch, and
sending said hint to an apply component.

### Claim 37 (depends on 36)

The computer program product of claim 36, wherein said predetermined threshold is a configurable parameter.

### Claim 38 (depends on 21)

The computer program product of claim 21 further comprising:
the processor(s) set;
wherein the computer program product is in the form of a system.

### Claim 39 (depends on 28)

The computer program product of claim 28 further comprising:
the processor(s) set;
wherein the computer program product is in the form of a system.

### Claim 40 (depends on 36)

The computer program product of claim 36 further comprising:
the processor(s) set;
wherein the computer program product is in the form of a system.