Methods, systems, and computer programs for loading data into a target database system.

Abstract

The method includes identifying a query view of the target database system that enables access to the first source table stored in the first target table; executing a load program for a current version of the first source partition of the first source table, where executing the load program includes loading the first source partition into a second load target partition of the first target table; modifying the query view to select records of the first target table; executing an update program to assign records of the first replication target partition to the first load target partition; selecting an operation mode for the replication program based on determining whether execution of the update program has finished; running the replication program in accordance with the selected operation mode; and modifying the query view to further select records of the first replication target partition.

Description

[Technical Field]

[0001] The present invention relates to the field of digital computer systems, and more particularly to a method for loading data into a target database system using different synchronization programs.

[0002] Data loading is one of the most frequently performed operations in a database system. Improving data loading can enhance the overall performance of the database system. However, controlling the time required to perform such data loading can be a challenging task. [Overview of the Initiative]

[0003] Various embodiments provide methods for loading data into a target database system using different synchronization programs, computer systems, and computer program products, as described by the subject matter of the independent claims. Advantageous embodiments are described in the dependent claims. Embodiments of the present invention can be freely combined with one another, provided they are not mutually exclusive.

[0004] As disclosed herein, a computer implementation method for data synchronization between a source database system and a target database system includes identifying a query view in the target database system that enables access to the final version of a first source table stored in a first target table, wherein the query view is configured to select records in the first target table, and the first target table has a first load target partition having a first load partition ID and a first replication target partition having a first replication partition ID; and executing a load program for the current version of the first source partition of the first source table, wherein executing the load program loads the first source partition into a second load target partition of the first target table. The method includes, the second load target partition having the second load partition ID, executing a query view to select records in the first target table having the second load partition ID, executing an update to assign records in the first replication target partition to the first load target partition, determining whether the execution of the update has finished in response to receiving a replication request for one or more current records in the first source partition of the first source table, selecting an operating mode for the replication program at least partially based on determining whether the execution of the update has finished, executing the replication program according to the selected operating mode, and modifying the query view to further select records in the first replication target partition. Computer program products and computer systems corresponding to the method are also disclosed. [Brief explanation of the drawing]

[0005] [Figure 1] A diagram of a data analysis system according to an example of the subject matter of the present invention.

[0006] [Figure 2] A flowchart of a method for loading data into a target database system according to an example of the subject matter of the present invention.

[0007] [[ID=​​​​​​​​​​​​​​​​​​​​​​​​​​​​​ [Figure 5] A flowchart of a method for executing a replication program according to a second operation mode.

[0010] [Figure 6] A representation diagram of a computerized system suitable for implementing one or more method steps involved in the subject matter of the present invention.

Embodiments for Carrying Out the Invention

[0011] The description of various embodiments of the present invention is presented for illustrative purposes and is not intended to be exhaustive or limited to the disclosed embodiments. Many modifications and variations will be apparent to those skilled in the art without departing from the scope and spirit of the described embodiments. The terms used herein are selected to best explain the principles of the embodiments, their practical applications, or the technological improvements found in the marketplace, or to enable other skilled artisans in the art to understand the embodiments disclosed herein.

[0012] A data analysis system includes a source database system and a target database system. The data analysis system may be, for example, a data warehousing system or a master data management system. The data analysis system can enable data warehousing, or master data management, or another technology using the source and target database systems, where the target database system comprises a target database configured to receive / contain a copy of the contents of the corresponding source database in the source database system. The source database system may be, for example, a transaction engine, and the target database system may be an analytical engine. For example, the source database system may be an online transaction processing (OLTP) system, and the target database system may be an online analytical processing (OLAP) system. The source database system may contain source datasets, and the target database system may contain target datasets. Source datasets may be part of the source database, and target datasets may be part of the target database. Source and target datasets may be stored in the same format or in different formats. The formats may differ in encryption, compression, row-oriented or column-oriented storage, etc. For example, the source dataset may be stored in a row-oriented format, while the target dataset may be stored in a column-oriented format. In other words, the target dataset may be stored in "columns" rather than "rows." The contents of the source dataset can be modified by one or more database transactions.

[0013] The source table consists of one or more partitions called source partitions SP1…SP NThis includes, where N≧1. A source table can be horizontally partitioned, thereby allowing complete rows to be placed in specific partitions. Each source partition can contain each set of rows in the source table. Each source partition in a source table can have an identifier (ID) called a source partition ID. The same type of source partition ID may be used for source partitions. Source partition SP1…SP N For example, each of these is the source partition ID.

number

number

number

number

number

number

[0014] However, to avoid data inconsistencies within the target table, the load program may handle rows that have been specifically replicated by the replication program for performing a new load of data. This can be achieved by performing an update (e.g., an UPDATE in the SQL statement) to assign the last load partition ID of the final bulk load to the replication target partition of the target table.

[0015] Therefore, synchronization between source and target tables involves the execution of three programs: a replication program, a load program, and an update program. As a result, the contents of at least one source partition of a source table may be stored in two target partitions, each having a load partition ID and a replication partition ID, respectively. The execution of the load program loads the entire source partition into the target partition having its load partition ID, and then the replication program can be executed on that source partition to replicate the records of the source partition to the target partition having the replication partition ID.

[0016] Therefore, at the current time t0, each source partition SP1…SP N The record may be stored in at least one of the target partitions. For example, source partition K SP1…SP K Each source partition is associated with two target partitions, each having a replication partition ID and a load partition ID, where K ≤ N. This means that the replication program runs on these K source partitions following the execution of the load program. For example, each source partition SP i (where i=1,...K) is the M target partition TP1...TP M Replication (R) and Load (L) Target Partitions

number

Number

Number

Number

number

[0017] However, at a later point in time t1, the load program is run again, and the source partition SP1…SP of the source table is accessed. N Each load target partition of the target table

number

number

number

number

number

number

number

number

[0018] For example, after the execution of the load program at point t1, the source partition SP1…SP of K K One or more source partitions SP1…SP JIf one or more records (referred to as modified records) are modified or inserted in the source partition, the replication program will modify such J source partition SP1…SP J This can be performed on (however, 1 ≤ J ≤ K). Therefore, it may be necessary to determine whether the update process has finished. Upon completion of the update, the K replication target partition...

number

number

number

number

number

[0019] The subject of this invention is the ability to visualize already fully loaded partitions while ensuring correct results for queries running concurrently before and after partition completion. Replication latency may be reduced. Therefore, the subject of this invention can enable efficient synchronization based on the timing of the execution of the three programs described above.

[0020] According to one embodiment, J source partition SP1…SP J The records of each load target partition

number

number

[0021] According to one embodiment, each load target partition

number

[0022] A replication program can be run against a replication view. For example, a replication view is separate from a query view. For example, a replication view may be a query view such that the replication program is run against the query view.

[0023] According to one embodiment, changing the query view further includes deselecting the first load partition ID and the first replication partition ID, and executing the replication program according to the default operating mode is performed after the queries using the initially changed query view have finished.

[0024] According to one embodiment, the load partition ID is greater than the replication partition ID, and the query view includes selecting records whose load partition ID is less than the maximum load partition ID.

[0025] Figure 1 is a block diagram of a data analysis system 100 according to an example of the subject matter of the present invention. The data analysis system 100 may be configured for data synchronization between a source database system 101 and a target database system 103 according to an example of the subject matter of the present invention. The source database system 101 may be, for example, an online transaction processing (OLTP) system. The target database system 103 may be, for example, an online analytical processing (OLAP) system. The source database system 101 and the target database system 103 may be connected via a network such as a fixed wireless network, a wireless local area network (WLAN), a wireless wide area network (WWAN), a personal area network (PAN), a virtual private network (VPN), an intranet, or other suitable network system. For example, communication between the source database system 101 and the target database system 103 may be performed via the TCP / IP communication layer.

[0026] The source database system 101 consists of one or more source tables 105 of the source database and a transaction recovery log 106. The source tables 105 may be relational tables of any number of database systems. Entries or log records in the transaction recovery log 106 describe changes to rows or records in the source tables 105 in the source database system 101. Figure 1 shows an example of the contents of log record 130. Log record 130 may include a timestamp, a log record sequence number (LRSN), and attribute changes. More specifically, log records in the transaction recovery log 106 may include, for example, information defining (1) the table being changed, (2) the values ​​of the key columns of the row being changed, (3) the old and new values ​​of all columns in the changed row, and (4) the transaction (unit of work) that caused the change. By definition, an insertion is a new data record and therefore does not have old values. In a delete change, there is no new data record, only the old data record exists. Therefore, the transaction log record for an inserted row may contain only the new column values, and the transaction log record for a deleted row may contain only the old column values. The transaction log record for an updated row may contain both the old and new values ​​for all rows and columns. The order of log records in a transaction recovery log may reflect the order of the transaction's modification operations, and the order of transaction commit records may reflect the order in which the transactions were completed. The type of row operation in a transaction log record can be, for example, deletion, insertion, or update.

[0027] The source database system 101 includes a log reader 104. The log reader 104 can read log records from the transaction recovery log 106 and provide the modified records to the replication program 108 on the target database system 103. The log reader 104 may be configured to perform log shipping of the transaction recovery log 106 to the target database system 103 based on an incremental update strategy. This shipping is performed, for example, by sending a stream of log records formed from the log records of the transaction recovery log 106 to the replication program 108. The log stream being shipped may be associated with a stream ID, for example. The stream of log records may be a stream of merged log records, for example. This enables efficient processing of log records on the target database system. The target database system 103 may have a log streaming interface for receiving log streams from the source database system 101. The replication program 108 may be configured to receive a stream of log records via a log streaming interface. The replication program 108 can then apply the changes indicated in the change records to the target table 113 corresponding to the source table 105 where the changes occurred.

[0028] In another example, a bulk load of an entire partition of source table 105 may be performed. To do this, the target database system 103 includes a load program 110 that receives the partition to be loaded and loads it into the target table 113 corresponding to source table 105.

[0029] Figure 2 is a flowchart of a method for data synchronization in a data analysis system. For ease of explanation, the method described in Figure 2 can be implemented in the system shown in Figure 1, but is not limited to this implementation. The method in Figure 2 can be performed, for example, by the target database system 103. Synchronization can be explained, for example, by referring to a source table and its corresponding target table. The changes in the contents of the target table are shown in Figures 3A to 3G. Figure 3A shows the initial state of the source table and target table. The source table is N source partition SP1…SP N Includes. The target table includes the M target partition. Each source partition SP1…SP N The record is stored in at least one of the target partitions. For example, source partition K SP1…SP K Each source partition SP i (Here i=1,...K) are the replication (R) and load (L) target partitions of the M target partition.

number

number

number

number

number

number

number

[0030] After the initial state shown in Figure 3A, N source partition SP1…SP N Therefore, the load program can be executed in step 201. The load program is N source partition SP1…SP N Each of these, ID

number

number

number

number

[0031] The update is ID

number

number

number

[0032] K J J

[0033] J i

[0034]

[0035] J J

[0036] ( J

[0037]

[0038]

[0039] <replication-view>AS SELECT * FROM <target-table>WHERE

number

number

[0040] A replication program may include an insert command to insert modified records into a replication view. For example, the insert command is an SQL INSERT statement to a replication view, such as the following: INSERLIFE INTO <replication-view>

number

number

number

[0041] This method further allows switching between the default operating mode and a second operating mode. To this end, in step 401, triggers such as temporary INSTEAD OF triggers can be defined for the replication view. A trigger can be a database object associated with a replication view that is activated when the replication program starts while the update is not yet complete. The trigger may only be needed while the update is still in progress. Once the update is complete and committed, the trigger can be dropped. Therefore, there is no long-term negative impact on performance. Furthermore, there may be no impact on query performance, because triggers only exist for replication-specific views that do not need to be used in queries. An INSTEAD OF trigger can be defined, for example, as follows: CREATE TRIGGER <trigger-name>INSTEADOF INSERT ON <replication-view> 将新的AS引用为n 对于每一行 设置n.partitionID = 当n.partitionID为

数

数

[0042] The execution of the replication program in step 403 may include executing an INSERT command defined against the replication view. While the update is still in progress, the replication program may be executed according to a second operating mode using triggers. The target database system can receive an INSERT command, such as an SQL INSERT statement. The target database system may parse the INSERT command and compile it into some internal representation. During this phase, the target database system may check for the existence of the replication view. The target database system may further check, for example, whether the user executing the INSERT command has the necessary privileges. In addition, the target database system may know about triggers against the replication view. Therefore, the trigger is compiled into an internal representation of the statement. In particular, this compilation into an internal representation of the statement may lead to the inclusion of the trigger's additional SET statement. Once the internal representation is processed, the values ​​to be inserted are first retrieved, then the SET statement (originally from the trigger) is processed, and finally the values ​​are inserted into the target table, i.e., the base table on which the replication view is constructed. Thus, in the second operating mode, the replication program ID

number

number

number

number

number

number

number

[0043] Figure 5 is a flowchart of a method for executing a replication program according to a second operating mode. The method in Figure 5 provides an exemplary implementation of step 215 in Figure 2. For illustrative purposes, the method described in Figure 5 is implemented in the system illustrated in Figure 1, but is not limited to this implementation. The method in Figure 5 can be executed, for example, by the target database system 103. A replication view can be defined for the target table as shown in Figure 3B as follows: CREATE VIEW <replication-view>AS SELECT * FROM <target-table> WHERE

number

number

[0044] The method in Figure 5 describes an alternative to the method in Figure 4. No triggers are used for the replication view. Instead, in step 501, the J source partitions SP1…SP are used respectively. J The modified records are each ID via the staging table.

number

[0045] In step 503, you can modify the replication view definition to cover the staging table as follows: CREATE VIEW <replication-view>AS SELECT * FROM <target-table> WHERE

number

number

[0046] A staging table can be used to hold all newly replicated rows for a partition that has just been reloaded. Source partition ID

number

number

[0047] The query view in expression Eq(2), like replication views, can be adapted to cover the staging table. This may allow newly replicated rows to be available in new queries. Depending on the view definition, the database system may have partition IDs.

number

number

[0048] After the update is complete, you can gradually stop using the staging table to restore normal replication and the default operating mode. This may require moving all rows from the staging table to the target table. INSERT / DELETE statements can be used in combination because all other methods (using multiple SQL statements) can produce inaccurate results for concurrently executed queries. INSERLIFE INTO <target-table> SELECT CASE ID WHEN

Number

Number

[0049] The data movement via INSERT / DELETE described above will be filtered by the WHERE clause of the replication view for the moved rows, therefore the partition ID

number

number

number

number

[0050] Figure 6 shows a typical computerized system 600 suitable for implementing at least some of the method steps relating to this disclosure.

[0051] It should be understood that the methods described herein are at least partially non-interactive and automated by computerized systems such as servers and embedded systems. However, in exemplary embodiments, the methods described herein can be implemented in (partially) interactive systems. These methods can further be implemented in software 612, 622 (including firmware 622), hardware (processor) 605, or a combination thereof. In exemplary embodiments, the methods described herein are implemented in software as executable programs and run by specialized or general-purpose digital computers such as personal computers, workstations, minicomputers, or mainframe computers. Thus, the most common system 600 includes a general-purpose computer 601.

[0052] In exemplary embodiments, from a hardware architecture perspective, as shown in Figure 6, the computer 601 includes a processor 605, memory (main memory) 610 coupled to a memory controller 615, and one or more input or output (I / O) devices (or peripherals) 10, 645 or both, communicatively coupled via a local input / output controller 635. The input / output controller 635 may be, but is not limited to, one or more buses or other wired or wireless connections, as known in the art. The input / output controller 635 may have additional elements, omitted for simplicity, such as controllers, buffers (caches), drivers, repeaters, and receivers, to enable communication. Furthermore, the local interface may include address, control, or data connections or a combination thereof to enable proper communication between the aforementioned components. As described herein, the I / O devices 10, 645 may generally include generalized cryptographic cards or smart cards known in the art.

[0053] The processor 605 is a hardware device for executing software, particularly software stored in memory 610. The processor 605 can be a custom-made or commercially available processor, a central processing unit (CPU), an auxiliary processor among multiple processors associated with computer 601, a semiconductor-based microprocessor (in the form of a microchip or chipset), or any device in general for executing software instructions. The processor 605 may, for example, be a multi-core processor.

[0054] Memory 610 may include one or a combination of volatile memory elements (e.g., random access memory (RAM such as DRAM, SRAM, SDRAM, etc.)) and non-volatile memory elements (e.g., ROM, erasable programmable read-only memory (EPROM), electronically erasable programmable read-only memory (EEPROM), programmable read-only memory (PROM)). Note that memory 610 may have a distributed architecture in which various components are located geographically apart but are accessible by processor 605.

[0055] The software in memory 610 may include one or more separate programs, each including an ordered enumeration of executable instructions for implementing logical functions, in particular functions related to embodiments of the present invention. In the example in Figure 6, the software in memory 610 includes instructions 612 for managing a database, such as a database management system.

[0056] The software in memory 610 typically also includes a suitable operating system (OS) 411. The OS 611 essentially controls the execution of other computer programs, such as software 612 for implementing the methods described herein, if possible.

[0057] The methods described herein may take the form of a source program, an executable program (object code), a script, or any other entity consisting of a set of instructions 612 to be executed. In the case of a source program, it is necessary to translate the program through a compiler, assembler, interpreter, etc., so that it can function properly with respect to the OS 611, and these may or may not be contained in memory 610. Furthermore, the methods may be written as an object-oriented programming language having data classes and methods, or as a procedural programming language having routines, subroutines, functions, or a combination thereof.

[0058] In exemplary embodiments, a conventional keyboard 650 and mouse 655 can be coupled to the input / output controller 635. Other output devices, such as I / O device 645, include, but are not limited to, input devices such as printers, scanners, and microphones. Finally, I / O devices 10, 645 may further include, but are not limited to, devices that communicate both input and output, such as network interface cards (NICs) or modulators / demodulators (for accessing other files, devices, systems, or networks), radio frequency (RF) or other transceivers, telephone interfaces, bridges, routers, etc. I / O devices 10, 645 may be common cryptographic cards or smart cards known in the art. System 600 may further include a display controller 625 coupled to a display 630. In exemplary embodiments, system 600 may further include a network interface for coupling to a network 665. Network 665 may be an IP-based network for communication between computer 601 and any external servers, clients, etc., via a broadband connection. Network 665 transmits and receives data between computer 601 and external systems 30, and these systems may be involved in performing some or all of the steps of the method described herein. In exemplary embodiments, network 665 may be a managed IP network operated by a service provider. Network 665 may be implemented wirelessly using wireless protocols and technologies such as WiFi, WiMAX, etc. Network 665 may also be a packet-switched network such as a local area network, wide area network, metropolitan area network, internet network, or other similar type of network environment.Network 665 may be a fixed wireless network, a wireless local area network (WLAN), a wireless wide area network (WWAN), a personal area network (PAN), a virtual private network (VPN), an intranet, or other suitable network system, and may include equipment for transmitting and receiving signals.

[0059] If computer 601 is a PC, workstation, or intelligent device, the software in memory 610 may further include a Basic Input / Output System (BIOS). The BIOS is a set of basic software routines that initialize and test the hardware at startup, start the OS 611, and support data transfer between hardware devices. The BIOS is stored in ROM so that it can be executed when computer 601 starts up.

[0060] When computer 601 is operating, processor 605 is configured to execute software 612 stored in memory 610, communicate data with memory 610, and generally control the operation of computer 601 according to the software. The methods and OS 611 described herein, in whole or in part, typically the latter, are read by processor 605, possibly buffered within processor 605, and then executed.

[0061] As shown in Figure 6, when the systems and methods described herein are implemented in software 612, the methods can be stored in any computer-readable medium, such as storage 620, for use by or associated with any computer-related system or method. Storage 620 may include disk storage such as HDD storage. For example, program code implementing the method may reside on one or more computer-readable storage media. System 600 includes, for example, a target database system 103 or a source database system 101. The computer program code / method can be executed on, for example, one or more processors, particularly processors of different systems such as the source database system and the target database system. One or more processors can receive elements of the program code / method as instructions.

[0062] The present invention may, in any possible level of technical detail, be a system, method, or computer program product, or a combination thereof. A computer program product may include one or more computer-readable storage media having computer-readable program instructions for causing a processor to perform an aspect of the present invention. As used herein, “processor” includes electronic components capable of executing programs or machine-executable instructions or computer-executable code. A processor may be, for example, a multi-core processor. A processor may also refer to a collection of processors within a single computer system or distributed across multiple computer systems. Computer-executable code may be executed by multiple processors within the same computing device or distributed across multiple computing devices.

[0063] A computer-readable storage medium can be a tangible device capable of holding and storing instructions used by an instruction execution device. A computer-readable storage medium can be, but is not limited to, electronic storage devices, magnetic storage devices, optical storage devices, electromagnetic storage devices, semiconductor storage devices, or any suitable combination thereof. A non-exhaustive enumeration of more specific examples of computer-readable storage media includes: portable computer diskettes, hard disks, random-access memory (RAM), read-only memory (ROM), erasable programmable read-only memory (EPROM or flash memory), static random-access memory (SRAM), portable compact disk read-only memory (CD-ROM), digital versatile disks (DVDs), memory sticks, floppy disks, mechanically encoded devices such as punched cards or grooved structures on which instructions are recorded, and any suitable combination thereof. As used herein, computer-readable storage media should not be construed as transient signals themselves, such as radio waves or other freely propagating electromagnetic waves, electromagnetic waves propagating through waveguides or other transmission media (e.g., light pulses passing through optical fiber cables), or electrical signals transmitted through wires.

[0064] The computer-readable program instructions described herein can be downloaded from a computer-readable storage medium to an individual computing / processing device, or to an external computer or external storage device via a network, such as the Internet, a local area network, a wide area network, or a wireless network, or a combination thereof. The network may consist of copper transmission cables, optical transmission fibers, wireless transmissions, routers, firewalls, switches, gateway computers, or edge servers, or a combination thereof. The network adapter card or network interface of each computing / processing device receives computer-readable program instructions from the network and transfers the computer-readable program instructions for storage on a computer-readable storage medium within the individual computing / processing device.

[0065] The computer-readable program instructions for performing the operations of the present invention may be source code or object code written in any combination of one or more programming languages, including assembler instructions, instruction set architecture (ISA) instructions, machine instructions, machine-dependent instructions, microcode, firmware instructions, state setting data, configuration data for integrated circuits, or object-oriented programming languages ​​such as Smalltalk(R) and C++, and procedural programming languages ​​such as the "C" programming language or similar programming languages. The computer-readable program instructions may be executed entirely on the user's computer, some as a standalone software package on the user's computer, some on the user's computer and some on a remote computer, or all on a remote computer or on a server. In the latter scenario, the remote computer may be connected to the user's computer via any type of network, including a local area network (LAN) or a wide area network (WAN), or the connection may be to an external computer (for example, via the Internet using an Internet service provider). In some embodiments, for example, an electronic circuit including a programmable logic circuit, a field-programmable gate array (FPGA), or a programmable logic array (PLA) can be personalized by executing computer-readable program instructions by utilizing state information of computer-readable program instructions in order to perform aspects of the present invention.

[0066] Aspects of the present invention will be described herein with reference to flowcharts or block diagrams, or both, of methods, apparatus (systems), and computer program products according to embodiments of the present invention. It will be understood that each block in a flowchart or block diagram, or both, and combinations of blocks in a flowchart or block diagram, or both, are implemented by computer-readable program instructions.

[0067] These computer-readable program instructions may be provided to a computer or other programmable data processing device processor to create a machine, which is executed via the processor of the computer or other programmable data processing device, in order to create means for implementing functions / operations specified in one or more blocks of a flowchart or block diagram or both. These computer-readable program instructions may also be stored in a computer-readable storage medium that stores and holds the instructions, instructing a computer, programmable data processing device, or other device or combination thereof to function in a particular way, so as to provide a manufactured article containing instructions for implementing modes of function / operation specified in one or more blocks of a flowchart or block diagram or both.

[0068] Computer-readable program instructions may also be loaded onto a computer, other programmable data processing device, or other device to create a computer implementation process in which instructions executed on a computer, other programmable device, or other device implement functions / operations specified in one or more blocks of a flowchart or block diagram, or both, causing the computer, other programmable device, or other device to execute a series of operable steps.

[0069] The flowcharts and block diagrams in the drawings illustrate the architecture, functionality, and operation of possible implementations of the system, method, and computer program product according to various embodiments of the present invention. In this regard, each block in a flowchart or block diagram may represent a module, segment, or portion of instructions, containing one or more executable instructions for implementing a specified logical function. In some alternative implementations, the functions shown in a block may occur in a different order than shown in the drawings. For example, two consecutively shown blocks may actually be performed as a single step, simultaneously, substantially simultaneously, partially or entirely in overlapping time, or blocks may sometimes be performed in reverse order depending on the function involved. It should also be noted that each block in a block diagram or flowchart, or both, and combinations of blocks in a block diagram or flowchart, or both, may be implemented by a special-purpose hardware-based system that performs a specified function or operation, or executes a combination of special-purpose hardware and computer instructions. SET [number] here [number]. The target table obtained after the update program has finished running is shown in Figure 3C. In this figure, for example, the load target partition associated with source partition SP1 contains the contents [number] and [number]. A replication request can be received in step 207. The replication request contains one or more modified records of source partition SP1…SP, where 1 ≤ J ≤ K holds for modified or inserted records. This may require running the replication program on such J source partition SP1…SP. To this end, step 209 includes determining whether the update program has finished running (step 209). If the update program has finished running, in step 211 the replication program can be run according to its default operating mode to copy the modified records of J source partition SP1…SP to their respective replication target partitions, each having an ID [number]. The status of the resulting target table is shown in Figure 3D. The contents of the modified records of source partition SP (where i=1,..J) are indicated by [number]. In this case as well, the query view of expression Eq(2) may be changed or updated in step 213 to the current contents of the target table indicated by version number v1, as shown in Figure 3D. The change may be made to cancel the deselection of ID [number], which would result in the query view looking like this: [number]. The query view may be updated, for example, before the start of the replication program execution. The replication program execution can start after any queries still running against the query view of expression Eq(2) have completed. The replication program may include, for example, an insert command to insert the changed records into the replication view, which is a query for the view of expression Eq(3).Figure 3D shows the status of the target table after the replication program has finished, with the update completed before the replication program started. Figure 3C shows the status of the target table after the update has finished and before the replication program started. If the update has not yet finished running, in step 215, the replication program can be run according to the second operating mode to copy the changed records of J source partition SP1…SP to the load target partition having ID [number] (directly or indirectly). However, replication of records from J source partition SP1…SP may or may not finish before the update program finishes. Figures 3E and 3F show the status of the target table in Figure 3B if replication is not completed by the time the update program finishes. Figure 3E shows the intermediate state of the target table, and Figure 3F shows the final state of the target table. Figure 3E shows the status of the target table after the update program has run and after replication has run according to the second operating mode. Figure 3F shows the status of the target table after the update program has run and replication has run according to both the default operating mode and the second operating mode has finished. Figure 3G shows the status of the target table when replication is not completed by the end of the update. For example, consider a scenario where the update finishes before the replication program, which operates according to the second operating mode, has finished running. For instance, consider a case where, before the update finishes, only a portion of the target partition (e.g., F target partition [number]) is copied to each load target partition with ID [number]. In this case, once the update is complete, the replication program returns to its default operating mode and can copy the remaining change records of the source partition SPF+1)...SP to the replication target partition with ID [number].The resulting target table is shown in Figure 3F. In this case, the query view of expression Eq(2) can be updated to obtain the query view of Eq(3) to select the record of version v1 of the target table in Figure 3F, showing the rows newly inserted by the replication program according to the default operating mode. If the replication program finishes running according to the second operating mode before the update is complete, the J target partition [number] is copied to each load target partition with ID [number]. The resulting target table is shown in Figure 3G. In this case as well, the query view of Eq(2) can be used, but to select the record of version v1 of the target table in Figure 3G. As mentioned above, the execution of the replication program may or may not require modification of the (initial) query view of expression Eq(2). If the query view of expression Eq(2) must be adapted, the replication program can only start after all queries still running against the query view of expression Eq(2) have completed. Figure 4 is a flowchart of a method for executing a replication program according to a second operating mode. The method in Figure 4 provides an exemplary implementation of step 215 in Figure 2. For illustrative purposes, the method described in Figure 4 is implemented in the system illustrated in Figure 1, but is not limited to this implementation. The method in Figure 4 can be executed, for example, by the target database system 103. A replication view can be defined for the target table in Figure 3B as follows: CREATE VIEW.

Claims

1. A computer-based information processing method performed for data synchronization between a source database system and a target database system, wherein the method is: Identifying a query view of the target database system that enables access to the final version of the first source table stored in the first target table, wherein the query view is configured to select records from the first target table, and the first target table has a first load target partition having a first load partition ID, a first replication target partition having a first replication partition ID, and a second load target partition having a second load partition ID. Executing a load program for the current version of the first source partition of the first source table, wherein executing the load program includes loading the first source partition into the second load target partition, the second load target partition having the second load partition ID, Modify the query view to select records from the second load target partition, Perform an update that assigns the load partition ID of the first load target partition to the record of the first replication target partition, In response to receiving a replication request for one or more modified records of the first source partition of the first source table, it is determined whether the execution of the update program has finished, Based on the above determination, the operating mode of the replication program is determined to be: In response to the determination that the execution of the update program has finished, the default operating mode copies the one or more modified records to the first replication target partition, or An alternative operating mode in which, in response to the determination that the execution of the update program has not finished, the one or more modified records are stored in a staging table different from the first target table. Choosing and The replication program is executed according to the selected operating mode, A method comprising: modifying the query view to further select the one or more modified records of the first replication target partition if the default operating mode is selected; and modifying the query view to further select the one or more modified records stored in the staging table if the alternative operating mode is selected.

2. The method according to claim 1, wherein executing the replication program according to the default operating mode includes copying the one or more modified records to the first replication target partition.

3. The replication program is executed according to the default operating mode. Copying the modified records from the source partition, Associating the aforementioned source partition with one or more load target partitions and replication target partitions. The method according to claim 2, including the method described in claim 2.

4. The method according to claim 1, wherein the target database system includes the first target table associated with the first source table of the source database system, the first source table includes at least one source partition, and the first target table includes the first load target partition, the first replication target partition, and the second load target partition.

5. The method according to claim 2, which provides a replication view including the query view, further comprising providing a replication program including an insert command to the replication view for inserting the modified records, and performing the replication program in accordance with the default operating mode is performed by modifying the replication view.

6. The replication program is executed according to the alternative operating mode. The one or more modified records are stored in a staging table different from the first target table, Modify the query view to further select one or more modified records from the staging table. The method according to claim 1, including the method described in claim 1.

7. The method according to claim 6, further comprising verifying that the update program has finished, copying the one or more modified records stored in the staging table to the second load target partition.

8. Executing the replication program according to the aforementioned alternative operating mode is The method according to claim 1, comprising implementing a trigger to switch the replication program from its default operating mode to an alternative operating mode, the trigger including an SQL INSTEAD OF trigger which executes another command to copy the one or more modified records to a second load target partition, skipping the copy of the one or more modified records to the first replication target partition, and the trigger being invoked in response to a determination that the update has not finished.

9. To verify that the aforementioned update program has finished, Deleting the aforementioned trigger, The method according to claim 8, further comprising completing the execution of the replication program in accordance with the default operating mode.

10. The method according to claim 7, further comprising modifying the query view to further select the one or more modified records of the second load target partition.

11. The method according to claim 1, wherein changing the query view further includes deselecting the first load partition ID and the first replication partition ID, and executing the replication program according to the default operating mode is performed after the query that first used the changed query view has finished.

12. The method according to claim 1, wherein the query view includes selecting records having an ID smaller than the maximum load partition ID of the load partition ID.

13. The aforementioned load program, Loading one or more distinct versions of the source partitions of the source tables of a source database system into distinct target partitions of the corresponding target tables of a target database system, wherein each of the distinct target partitions has a unique load partition ID. The method according to claim 1, including the method described in claim 1.

14. The method according to claim 1, wherein the update includes an UPDATE of an SQL statement.

15. The method according to claim 5, wherein the replication view is separate from the query view, or is the query view.

16. A computer program that causes a computer to perform the method according to any one of claims 1 to 15.

17. A computer-readable storage medium storing the computer program described in claim 16.

18. A computer system configured to perform the method described in any one of claims 1 to 15.

Images