Method for migrating data records from a source database to a target database

The present disclosure relates to a method using a database engine for migrating data records from a source database to a target database, where said data records are arranged in a sorted source table of the source database in accordance with a clustered-base-table order following the Hilbert-Filling-Curve algorithm. The method comprises configuring the database engine for storing the CBT order of each data record of the source table in a reference table to said source table or a in a column of said source table. A reading step may be performed to read said data records from said source table, wherein in said reading step said stored CBT order is used. The read data and the stored CBT order may be transferred to the target database in accordance with the stored CBT order. The transferred data may be written at the target database in accordance with the CBT order.

BACKGROUND

The present invention relates to the field of digital computer systems, and more specifically, to a method using a database engine for migrating data records from a source database to a target database.

SUMMARY

Various embodiments provide a method using a database engine for migrating data records from a source database to a target database, computer system and computer program product 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 each other if they are not mutually exclusive.

In one aspect, the invention relates to a method using a database engine for migrating data records from a source database to a target database, where said data records are arranged in a sorted source table of the source database in accordance with a clustered-base-table order (“CBT order”) following the Hilbert-Filling-Curve algorithm. The method comprises: configuring the database engine for storing the CBT order of each data record of the source table in a reference table to said source table or a in a column of said source table; performing a reading step to read said data records from said source table, wherein in said reading step said stored CBT order is used; transferring the read data records and the stored CBT order to the target database in accordance with the stored CBT order; and writing at the target database the transferred data to said target table in accordance with the CBT order using the stored CBT order.

In another aspect, the invention relates to a computer program product comprising a computer-readable storage medium having computer-readable program code embodied therewith, the computer-readable program code configured to implement all of steps of the method according to preceding embodiments.

In another aspect, the invention relates to a computer system for migrating data records from a source database to a target database, where said data records are arranged in a sorted source table of the source database in accordance with a clustered-base-table order (“CBT order”) following the Hilbert-Filling-Curve algorithm. The computer system is configured for: storing the CBT order of each data record of the source table in a reference table to said source table or a in a column of said source table; performing a reading step to read said data records from said source table, wherein in said reading step said stored CBT order is used; transferring the read data records and the stored CBT order to the target database in accordance with the stored CBT order; and writing at the target database the transferred data to said target table in accordance with the CBT order using the stored CBT order. The computer system may for example be part of a database engine.

DETAILED DESCRIPTION

One of the typical problems during migration of the data from one database to another is the change of a physical order of the data. It may become more problematic when dealing with massively parallel processing (MPP) databases as the source data is organized by Clustered Base Tables (CBT) methods. In such case, after migration, the data are spread differently. This may reduce the performance of the target database compared the source database.

The CBT order is an order that results from applying the Hilbert-Filling-Curve algorithm to order the records of the source table. The CBT order is the physical order in which the records are stored in extents. The term “CBT order” is used for naming purpose to indicate that the ordered records that result from the Hilbert-Filling-Curve algorithm may be clustered and stored in nearby extents in accordance with the CBT algorithm. But, it is not limited to that way of storage and other ways of storing the ordered records may be used with the present disclosure.

The present method may enable consistency in data content between the source and the target databases by maintaining a consistent ordering of data records in the source and target databases. After migration, the data is maintained in the same CBT order and thus the performance of the target database can be similar to the performance of the source database.

The term “data table” or “table” as used herein refers to a collection of data that may be presented in a tabular form. Each column in the data table may represent a particular variable or attribute. Each row in the data table may represent a given member, record (or data record) or entry of the data table.

According to one embodiment, transferring the data records is performed using one or more threads. Using the threads may provide concurrency within a process and may allow utilization of multiprocessor architectures to a greater scale and efficiency.

According to one embodiment, the method further comprises splitting the data records into chunks of consecutive subsets of ordered data records, wherein each thread is configured to transfer a respective subset of the data records. For example, assuming that the source table comprises records r1 to r6. The CBT ordering following the Hilbert-Filling-Curve algorithm may result in this order of the records: r2→r4→r6→r5→r3→r1. In this case, two chunks may for example be created, one chunk having records r2→r4→r6 and the second subsequent chunk comprises r5→r3→r1. One thread may be used to process the first subset of records r2→r4→r6 and another thread may be used for processing the other subset of records r5→r3→r1. This may enable an efficient processing of records in accordance with the CBT order.

According to one embodiment, the method further comprises determining a storage order in which the data records are stored by the threads in the target database, wherein the reading step further comprises: in case the storage order is different from the CBT order applying an operation to order the data records in accordance with the storage order such that the writing of the data records can be performed in accordance with the CBT order. For example, with a conventional method, the records on the target side may be stored as they arrive at the target side e.g. if record r3 arrives at the target side before r6, r3 may be stored before storing r6 so that r3 is ordered before r6. However, this order is different from the CBT order e.g. where r3 is ordered after r6. On the target side one may thus not benefit from the CBT order. This embodiment may solve this problem by taking into account the storage order.

According to one embodiment, the applying of the operation to order the data records comprises applying a modulo operation to perform the ordering of the data records. The modulo operation may be advantageous as it may enable to make use of existing database management tools. The modulo operation may for example be implemented using a SQL MOD( ) function.

According to one embodiment, the method further comprises providing a first named pipe and second named pipe, wherein the result of said reading step is placed or stored in the first named pipe, wherein the second named pipe is configured for storing said data records after being transferred to a target system of the target database. The target system comprises the target database. The method further comprises buffering and synchronizing said data for the writing. A named pipe (or a pipe) may, for example, be a section of a memory that is used for communication. A named pipe may be configured to operate as a first in, first out (FIFO) memory such that the inputs that enter first will be read or output first.

According to one embodiment, the buffering and synchronizing comprises: a) buffering in a buffer a current received subset of records of a given thread; and b) in case the data records of the current received subset are the first ordered data records of the source table, storing the first subset and any subsequent stored subset of records in the buffer; otherwise waiting for another subset of records of another thread and repeating steps a) and b) for the other received subsets until all subsets are received. Following the above example, if the subset r5→r3→r1 arrives at the target side before the subset r2→r4→r6, the subset r5→r3→r1 may be buffered and the system may wait until it receives the subset r2→r4→r6. Once the subset r2→r4→r6 is received it may be determined that the two subsets are subsequent to each other and that are the first ordered ones and thus may be stored in accordance with the CBT order.

According to one embodiment, the storing of the CBT order comprises storing the CBT order in a hidden column. This may provide an implementation that is transparent to the user. For example, the user may not realize that there is any additional column. This embodiment may seamlessly be integrated in existing systems.

FIG. 1is a block diagram for a data processing system100for a database engine (e.g. such as a hybrid DBMS) suited for implementing method steps as involved in the disclosure. The data processing system100comprises a first computer system101connected to a second computer system121.

Processor102may represent one or more processors (e.g. microprocessors). The memory103can include any one or combination of a volatile memory element (e.g., random access memory (RAM)), nonvolatile memory element (e.g., ROM), erasable programmable read only memory (EPROM), electronically erasable programmable read only memory (EEPROM), and programmable read only memory (PROM). Note that the memory103can have a distributed architecture, where various components are situated remote from one another, but can be accessed by the processor102.

Memory103in combination with persistent storage device107may be used for local data and instruction storage. Storage device107includes one or more persistent storage devices and media controlled by I/O circuitry104. Storage device107may include magnetic, optical, magneto optical, or solid-state apparatus for digital data storage, for example, having fixed or removable media. Sample devices include hard disk drives, optical disk drives and floppy disks drives. Sample media include hard disk platters, CD-ROMs, DVD-ROMs, BD-ROMs, floppy disks, and the like.

Memory103may include one or more separate programs e.g. database management system DBMS1109, each of which comprises an ordered listing of executable instructions for implementing logical functions, notably functions involved in embodiments of this invention. The software in memory103shall also typically include a suitable operating system (OS)108. The OS108essentially controls the execution of other computer programs for implementing at least part of methods as described herein. DBMS1109comprises a DB application111and a query optimizer110. The DB application111may be configured for processing data stored in storage device107. The query optimizer110may be configured for generating or defining query plans for executing queries e.g. on source database112. The source database112may for example comprise a source table190. An example content of the source table190is shown inFIG. 2.

Processor122may represent one or more processors (e.g. microprocessors). The memory123can include any one or combination of a volatile memory element (e.g., random access memory (RAM)), nonvolatile memory element (e.g., ROM), erasable programmable read only memory (EPROM), electronically erasable programmable read only memory (EEPROM), and programmable read only memory (PROM). Note that the memory123can have a distributed architecture, where various components are situated remote from one another, but can be accessed by the processor122.

Memory123in combination with persistent storage device127may be used for local data and instruction storage. Storage device127includes one or more persistent storage devices and media controlled by I/O circuitry104. Storage device127may include magnetic, optical, magneto optical, or solid-state apparatus for digital data storage, for example, having fixed or removable media. Sample devices include hard disk drives, optical disk drives and floppy disks drives. Sample media include hard disk platters, CD-ROMs, DVD-ROMs, BD-ROMs, floppy disks, and the like.

Memory123may include one or more separate programs e.g. database management system DBMS2129, each of which comprises an ordered listing of executable instructions for implementing logical functions, notably functions involved in embodiments of this invention. The software in memory123shall also typically include a suitable OS128. The OS128essentially controls the execution of other computer programs for implementing at least part of methods as described herein. DBMS2129comprises a DB application131and a query optimizer130. The DB application131may be configured for processing data stored in storage device127. The query optimizer130may be configured for generating or defining query plans for executing queries e.g. on a target database132.

First computer system101and second computer system121may be independent computer hardware platforms communicating through a high-speed connection142or a network141via network interfaces105,125. The network141may for example comprise a local area network (LAN), a general wide area network (WAN), and/or a public network (e.g., the Internet). Every computer system101and121is responsible for managing its own copies of the data.

Although shown inFIG. 1as separate systems, the first and second computer systems may belong to a single system e.g. sharing a same memory and processor hardware, while each of the first and second computer systems is associated with a respective DBMS and datasets e.g. the two DBMSs may be stored in the shared memory. In another example, the two database management systems DBMS1and DBMS2may form part of a single DBMS that enables communications and methods performed by DBMS1109and DBMS2119as described herein. The first and second datasets may be stored on a same storage or on separate storages.

The storages107and127may comprise extents. The term “extent” may refer to a storage unit (e.g. a contiguous area of storage) for storing the data of the data table190. The records of the data table190may be stored in one or more extents.FIG. 2describes an example of how the records can be stored in the extents.

A database engine (not shown) may be configured to perform data migration or data transfer in accordance with the present disclosure. The database engine may be part of the first computer system101and/or second computer system121(e.g. the database engine may for example comprise at least part of the DBMS1109and/or DBMS2119). In another example, the database engine may be or may comprise a separate computer system that is configured to connect to the data processing system100, wherein the database engine may be configured to control the data processing system100to perform at least part of the present method.

The transfer of data between the source database112and the target database132may be performed using one or more threads. Two pipes may, for example, be provided as predefined memory areas of memories103and123respectively. One first pipe of the memory103may be used for the reading step such that an output from the reading step is placed in the pipe and one second pipe of memory123may be used for writing after transferring the data via the network. The transfer process of data records from the source database112to the target database132may for example comprise a read process for reading the records of the source database112. The read data records are stored in the first pipe. The data records are then transferred over network141by the one or more threads. At the computer system121, the received data records are stored in the second pipe before being processed by a write process. The read process may for example be performed by a reading application of the DBMS1109(the reading application may be, in another example, a separate component of DBMS1109) and the write process may be performed by a retriever. The retriever may retrieve data records of the second pipe for storing them in the target database132. The retriever may for example be part of DBMS2119. In another example, the retriever may be a dedicated program embedding JDBC or ODBC connector which is responsible for reading to the data coming from the second pipe on the target side.

FIG. 2shows an example content of the source data table190. The source data table190may comprise two attributes or columns C1and C2. The column C1has character values A, B, C and D and C2has values 1, 2, 3 and 4. The source data table190may be ordered using the values of the two columns C1and C2.FIG. 2indicates the difference between a simple ordering and CBT ordering. The result of the simple ordering is shown in the source data table201, where the simple ordering may be based on alphabetical order in combination with numerical order. The source table201after performing the simple ordering has records annotated A1-A4, B1-B4, C1-C4and D0-D4(collectively referred to as A1-D4) which are ordered using columns C1and C2. For example, A1is a concatenation of the values of the two columns C1and C2to indicate that the record A1is a record having a value of C1which is ‘A’ and value of C2which is ‘1’. The source table290has records A1-D4which are ordered, using columns C1and C2, in accordance with the CBT order which is obtained after applying a re-arrangement of the data table using the Hilbert Filling Curve algorithm. The re-arrangement process may for example be performed during tasks like grooming tasks.FIG. 2further illustrates on which extent of the storage107the records of the source table290as well as the source table201are stored. The records of the source data table290are ordered using the Hilbert Filling Curve algorithm in order to enable the concept of the clustered base tables (CBT) which is introduced to minimize table scan in extents for databases by clustering the ordered records in same or nearby extents. The CBT order299is indicated inFIG. 2, where each record of the records A1-D4is associated with the respective CBT order. For example, record A1is associated with CBT order0which indicates the first order, while record D2is associated with CBT order6which indicates the seventh order. This may enable that the records of the source data table190may be arranged basing on defined keys (columns) in the same or nearby extents. This may benefit for queries with CBT columns used in WHERE conditions or join statements. The middle box203illustrates how the Hilbert curve covers the set of the data (e.g. with a 2-dimensional key). Each square inside box203represents a row or record having a value of C1and C2.

After the migration of the data table190to the target database132, especially when the data are moved in more than one thread, the CBT order may be changed by the way the threads reach the target database132. For example, using four threads to transfer the records of the four extents EXT0-3of the source data table190may result in one thread arriving before the other threads (e.g. leading to the fact that the records of the EXT2of the source database are stored in EXT1of the target database131and that the records of the EXT1of the source database are stored in EXT2of the target database131). This may not make use of the CBT order at the target database.FIG. 3Aprovides a method that may solve this problem.

FIG. 3Ais a flowchart of a method using a database engine (e.g. database engine of the data processing system100) for migrating data records A1-D4from source data table290of a source database112to a target table of target database132. As described inFIG. 2, the data records A1-D4of the source data table290are arranged in or sorted in accordance with the CBT order following the Hilbert-Filling-Curve algorithm. For example, at least part of the method steps performed on the source database112may be performed by DBMS1and at least part of the method steps performed on the target database132may be performed by DBMS2e.g. by the retriever of DBMS2. In case of a separate database engine, the execution of the method steps may, for example, be controlled or triggered by the database engine e.g. the database engine may be configured to control the data processing system100to perform the method steps e.g. via control commands.

In step301, the database engine, e.g. DBMS1109, may be configured for storing the CBT order299of each data record A1-D4of the source table290in a reference table of said source table290or in a column of said source table290.

For example, as shown inFIG. 3Ba column301may be added to the source table290to obtain source table390. The values of the CBT order299may be stored in the added column301so that each record303.1-16comprises in addition to records A1-D4, the CBT order299associated with records A1-D4. For example, record303.1of the source table390comprises values of record A1in addition to the CBT order0. The column301may be a hidden column. The hidden column may be a column that is not displayed (or is not displayable) with other columns of the source table390when displaying the source table390.

In another example, the reference table may be used to store the CBT order299. The source and/or reference tables may be configured such that the reference table may be used to determine the CBT order of each record of the source table290.

In step303, a reading step may be performed in order to read the data records303.1-16from said source table390, wherein in said reading step said stored CBT order is used. For example, the records303.1-16are read following the CBT order299e.g. record303.1is first read followed by record303.2and so on. The CBT order may be read from the additional column301or from the additional reference table. A reading application (e.g. of DBMS1) may perform the reading step and may store the read data in a first pipe of system101(e.g. the first pipe may be an area of memory103). The thread used for transferring data may be configured to read the data from the first pipe for transferring it over the network.

In step305, the read data records including the stored CBT order may be transferred to the target database132in accordance with the stored CBT order299. For example, if a single thread is used to transfer the records A1-D4, the following SQL instructions may be used for reading the records A1-D4based on the CBT order in order to be transferred.

In case a column (named ‘orderHFC’) is added to the source table290(named ‘T_CBT’) the following SQL instructions may be used: select T_CBT .*, orderHFC from T_CBT order by orderHFC.

In case of the additional reference table (named ‘ref’) of the source table290, the following SQL instructions may be used: select cbt.*, ref.orderHFC from T_CBT cbt inner join T_REF_CBT ref on cbt.rowID=ref.fk_RowID order by ref.orderHFC.

FIGS. 4 and 5describe another example for transferring and writing the data in accordance with the present disclosure.

In step307, the transferred data may be written to the target table of the target database132in accordance with the CBT order using the stored CBT order e.g. for each record A1-D4to be copied in the target data table, the corresponding CBT order or value in column301may be read and based on that read CBT order, the record may be stored accordingly in the target data table. Using the stored CBT order for storing data may be advantageous in particular as the transferred records may not reach the target database132in the CBT order e.g. record D2may reach the target database132before record C1. The resulting data table (after storing all records A1-D4) may for example be the source table290. The writing of the transferred data may, for example, be performed by the retriever. The transferred data may for example be placed by the thread in a second pipe of the system121(e.g. the second pipe may be part of memory123) and the retriever may read the data from the second pipe for performing the writing. The writing may be performed either directly after reading data of the second pipe to the target table or via the buffer as described below inFIGS. 5A-D. The data read from the second pipe are buffered before being written into the target table. The first and/or second pipes may optionally be used with the present method.

FIG. 4is a flowchart of a method for transferring and writing the data records A1-D4using the source table390. For example, threads may be used to transfer respective records of the records A1-D4.

In step401, a storage order in which the data records A1-D4are stored by the threads in the target database may be determined. For example, without the present method, the transfer and writing of the data records A1-D4may result in data records A-D4being stored in the target database following an order that is not the same as the CBT order299e.g. as described above withFIG. 2.

In case the storage order is different from the CBT order, an operation may be applied in step403(e.g. at the reading step303) to order the data records A1-D4in accordance with the storage order before transferring and writing the data records A1-D4to the target database. The ordering is performed such that the writing of the data records can be performed in accordance with the CBT order. For example, as shown with the code ofFIG. 4, a modulo operation may be performed in order to change the CBT order to the storage order.

For example, assuming that two threads are provided, wherein each thread is configured to transfer a single set of four records. E.g. each of the threads may be configured to transfer the set of records of a given extent, EXT0-3. For example, thread1may be used to transfer records303.1-303.4of extent EXT0, thread2may be used to transfer records303.5-303.8of extent EXT1(after transferring the records of extents EXT0and EXT1, the threads may be used to transfer the remaining records of the extents EXT2and EXT3in the same way). It may happen e.g. due to the network connections, that records303.5-303.6(comprising C1and D1) of the second thread2arrive at the target database132before the records303.3-303.4(comprising B2and B1) of the thread1and thus may be stored in that order e.g. A1followed by A2followed by C1and D1. However, this order is different from the CBT order. Using the method ofFIG. 4e.g. via a modulo operation, this different order may be taken into account when assigning records to the two threads in order that the records reach the target database and thus be stored in the CBT order. Following the above example, thread1may be assigned records A1-A2-C1-D1and the second thread2may be assigned records B2-B1-D2-C2. In this way, the records B2-B1of the second thread would arrive at the target database before the records C1-D1of the first thread and may thus be stored after A1-A2i.e. in this order A1-A2-B2-B1which is the CBT order.

The following SQL instructions may be used to perform the modulo operation described above for reordering the records before being transferred for a) the case of an additional column301and for b) the case of an additional reference table:

Where optimalRow4Chunk is an optimal number of records in a single chunk. The value may be set as a compromise between an optimal time for reading/transferring and buffering/sorting, and may additionally be related to the size of buffer memory dedicated for the retriever. maxThreadNO is the number of threads in which transfer is realized and currentThread is the current transfer thread.

FIGS. 5A-Ddepict diagrams illustrating a method for transferring and writing records A1-D4of the source table390into the target database132. Two threads are used in the example ofFIGS. 5A-D.

In a first step as illustrated inFIG. 5A, the two successive sets of records, namely (A1, A2, B2, B1) and (C1, D1, D2, C2) of the extents EXT0and EXT1are assigned to the threads thread1and thread2respectively.

The records of each of the two sets are buffered in a buffer501that is used by the retriever for buffering data records before storing them in the target database132. The records are buffered in association with their CBT order. Since the two sets of records are successive and are the first ordered sets of the source table390, they may be stored, after they are buffered, following the CBT order in the target data table. The retriever may be configured to store (e.g. in the buffer501) an indication of the CBT order of the previously stored records (e.g. the retriever may store the CBT order7, which is the order of the last ordered and stored record, as an indication of the CBT order of the stored two sets of records (A1, A2, B2, B1) and (C1, D1, D2, C2).

Each thread that completed the task of the transfer of records may be used again to transfer the remaining records. As shown inFIG. 5B, thread2is used to transfer the set of records of the extent EXT3. Thread1is not used yet for the transfer of the remaining records. For example, this is due to the fact that thread1was suspended (e.g. due to a network issue) and only thread2transfers the data.

In this case ofFIG. 5B, the set of records (B4, B3, A3, A4) is buffered in the buffer501. However, using the stored order indication, the retriever may be configured to determine that there is a gap (8-11) in the CBT orders that are processed when receiving the set of records (B4, B3, A3, A4). Since this set of records is not (immediately) subsequent to the two previously stored sets of records (A1, A2, B2, B1) and (C1, D1, D2, C2), the retriever may wait until the set of records (C3, D3, D4, C4) is stored in the buffer501. This is for example indicated inFIG. 5C, where thread1is restarted for transferring the data. As shown inFIG. 5C, thread1transfers the set of records (C3, D3, D4, C4) which is then stored in the buffer after the set of records (B4, B3, A3, A4). However, there is a need to resort the buffered records of the buffer because their order is different from the CBT order. Thus, as indicated inFIG. 5D, the two sets of records (B4, B3, A3, A4) and (C3, D3, D4, C4) are ordered in accordance with the CBT order. Since the two sets of records that are currently in the buffer501ofFIG. 5Dare immediately following or subsequent of the previously stored sets of records (A1, A2, B2, B1) and (C1, D1, D2, C2) they may be stored in the target data table.

In another example, a method using a database engine for migrating data from a first database is provided, where said data is arranged in a sorted source table having a column-based sort order, to a second database, where said data is arranged in a sorted target table having a clustered-base-table order following the Hilbert-Filling-Curve algorithm. The method comprises the steps: introducing into said database engine a functionality of storing the order obtained from calculating said clustered-base-table order following the Hilbert-Filling-Curve algorithm, by adding a reference table or a hidden column to said source table, performing a reading step to read said data from said source table, wherein in said reading step said stored order is used, applying a modulo operation to ensure that said data maintained in relative proximity, passing the result of said reading step to a pipe, retrieving said data in a retrieving step using a dedicated pipe on the side of said target table, buffering and synchronizing said data for writing it to said target table.