# US 11,442,823 B2 · Transaction consistency query support for replicated data from recovery log to external data stores > Agents: this data is available as JSON from the patentgrep API and MCP server — see https://patentgrep.com/llms.txt - Number: US 11,442,823 B2 - Document ID: US11442823B2 - Status: Granted - Kind: B2 - Application number: 15/173,502 - Filed: 2016-06-03 - Published: 2017-12-07 - Granted: 2022-09-13 - Earliest claimed: 2016-06-03 - Assignees: International Business Machines Corporation - Applicants: International Business Machines Corporation - Inventors: Serge Bourbonnais; Austin F. M. D'Costa; Yat On Lau; Xiao Li; Hong Min; Gong Su; Jonathan W. Wierenga; Christian Zentgraf; Kan Zhang - Web page: https://patentgrep.com/patent/US11442823B2 ## CPC - G06F 11/1474: in transactions (updating of structured data in databases G06F16/23) - G06F 16/27: Replication, distribution or synchronisation of data between databases or within a distributed database system; Distributed database system architectures therefor - G06F 11/2094: {Redundant storage or storage space (G06F11/2056 takes precedence)} - G06F 2201/80: Database-specific techniques - G06F 2201/82: Solving problems relating to consistency - G06F: ELECTRIC DIGITAL DATA PROCESSING (computer systems based on specific computational models G06N) ## Abstract Transaction consistency query support is available for replicated data from recovery log to external data stores. An external data store is populated with records using entries of a change data table. The change data table has entries for each transaction that has committed and is to be replicated, and each of the entries stores information for each log entry in a recovery log from a database management system. Each log entry identifies a transactional change of data and a transaction completion indicator of one of commit and abort. In response to receiving a query about a transaction of the transactions, a set of records are retrieved from the external data store for the transaction. From the set of records, records whose sequence identifier values are larger than a maximum transaction commit sequence identifier are removed. From the set of records, remaining records having transaction consistency are returned. ## Claims ### Claim 1 A computer program product, the computer program product for transaction consistency query support for replicated data from a recovery log to an external data store and comprising a computer readable storage medium having program code embodied therewith, the program code executable by at least one processor to perform operations for: populating a replicated transformation table in an external data store with records using entries of a change data table in a database management system, by: with sequential publishing, for each of the entries: determining whether that entry is a last entry for a transaction by checking a last transaction message indicator; and in response to determining that the entry is the last entry for the transaction, setting a maximum transaction commit sequence identifier value for the transaction to a sequence identifier of the last entry; and with parallel publishing, for each of the entries, determining whether that entry is the last entry for the transaction by checking the last transaction message indicator; and in response to determining that the entry is the last entry for the transaction, setting a maximum transaction commit sequence identifier value to a last consecutive applied sequence identifier; and in response to receiving a query at the external data store, retrieving a set of records from the replicated transformation table in the external data store; determining whether partial changes have been applied, wherein the partial changes are in records of in-flight transactions that have not committed yet in the external data store; in response to determining that the partial changes have been applied, removing, from the set of records, the records of the in-flight transactions whose sequence identifier values are larger than the maximum transaction commit sequence identifier value; and returning, from the set of records, remaining records having transaction consistency; and in response to determining that the partial changes have not been applied, returning the set of records having transaction consistency. ### Claim 2 (depends on 1) The computer program product of claim 1, wherein, in response to receiving an abort transaction completion indicator, discarding the entries for the transaction from an in-memory queue. ### Claim 3 (depends on 1) The computer program product of claim 1, wherein the remaining records contain records for committed transactions and do not contain records for the in-flight transactions. ### Claim 4 (depends on 1) The computer program product of claim 1, wherein information from a log entry includes a sequence identifier, a commit time, and a data loss indictor, wherein the sequence identifier and the commit time are used to determine a source commit time for which transactions to that point have been inserted into the external data store, and wherein the sequence identifier and the data loss indicator are used to detect data loss and corruption. ### Claim 5 (depends on 1) The computer program product of claim 1, wherein a Software as a Service (SaaS) is configured to perform the operations of the computer program product. ### Claim 6 (depends on 1) The computer program product of claim 1, wherein the change data table is populated by: storing information from log entries of a recovery log into entries of an in-memory queue; in response to receiving a commit transaction completion indicator, reconstructing a transaction by grouping entries of the in-memory queue that have a same transaction identifier; and storing the grouped entries for the reconstructed transaction in the change data table. ### Claim 7 A computer system for transaction consistency query support for replicated data from a recovery log to an external data store, comprising: one or more processors, one or more computer-readable memories and one or more computer-readable, tangible storage devices; and program instructions, stored on at least one of the one or more computer-readable, tangible storage devices for execution by at least one of the one or more processors via at least one of the one or more computer-readable memories, to perform operations comprising: populating a replicated transformation table in an external data store with records using entries of a change data table in a database management system by: with sequential publishing, for each of the entries: determining whether that entry is a last entry for a transaction by checking a last transaction message indicator; and in response to determining that the entry is the last entry for the transaction, setting a maximum transaction commit sequence identifier value for the transaction to a sequence identifier of the last entry; and with parallel publishing, for each of the entries, determining whether that entry is the last entry for the transaction by checking the last transaction message indicator; and in response to determining that the entry is the last entry for the transaction, setting a maximum transaction commit sequence identifier value to a last consecutive applied sequence identifier; and in response to receiving a query at the external data store, retrieving a set of records from the replicated transformation table in the external data store; determining whether partial changes have been applied, wherein the partial changes are in records of in-flight transactions that have not committed yet in the external data store; in response to determining that the partial changes have been applied, removing, from the set of records, the records of the in-flight transactions whose sequence identifier values are larger than the maximum transaction commit sequence identifier value; and returning, from the set of records, remaining records having transaction consistency; and in response to determining that the partial changes have not been applied, returning the set of records having transaction consistency. ### Claim 8 (depends on 7) The computer system of claim 7, wherein, in response to receiving an abort transaction completion indicator, discarding the entries for the transaction from an in-memory queue. ### Claim 9 (depends on 7) The computer system of claim 7, wherein the remaining records contain records for committed transactions and do not contain records for the in-flight transactions. ### Claim 10 (depends on 7) The computer system of claim 7, wherein a Software as a Service (SaaS) is configured to perform the operations of the computer system. ### Claim 11 (depends on 7) The computer system of claim 7, wherein information from a log entry includes a sequence identifier, a commit time, and a data loss indictor, wherein the sequence identifier and the commit time are used to determine a source commit time for which transactions to that point have been inserted into the external data store, and wherein the sequence identifier and the data loss indicator are used to detect data loss and corruption. ### Claim 12 (depends on 7) The computer system of claim 7, wherein the change data table is populated by: storing information from log entries of a recovery log into entries of an in-memory queue; in response to receiving a commit transaction completion indicator, reconstructing a transaction by grouping entries of the in-memory queue that have a same transaction identifier; and storing the grouped entries for the reconstructed transaction in the change data table.