Efficient bulk loading multiple rows or partitions for a single target table

Patent metadata

Field	Value
Publication number	US-11520781-B2
Application number	US-202017023490-A
Country	US
Kind code	B2
Filing date	Sep 17, 2020
Priority date	Sep 17, 2020
Publication date	Dec 6, 2022
Grant date	Dec 6, 2022

Abstract

A computer-implemented system and method process insertions into a target table of a target database system. A single query language INSERT statement is received that is related to a source partition of a source table in a source database system that identifies a block of data (BOD), having a partition identifier (PID), to be inserted into the target table. The INSERT statement contains no literal PID. Responsive to receiving the INSERT statement, a processor receives the BOD from the source table via a data stream, and inserts the BOD into the target table. This reduces the overhead associated with using an INSERT statement to insert multiple rows of data into a target table.

First claim

What is claimed is:

1. A method of processing insertions into a target table of a target database system, comprising, with a processor:
receiving a single query language INSERT statement related to a source partition of a source table in a source database system that identifies a block of data (BOD), having a partition identifier (PID), to be inserted into the target table, wherein the INSERT statement contains no literal PID;
responsive to receiving the INSERT statement, receiving the BOD from the source table via a data stream; and
inserting the BOD into the target table to result in a target table containing the BOD,
wherein the data stream comprises a plurality of data streams that are demultiplexed on the source database system.

2. The method of claim 1 , wherein a PID of each row of the BOD is inserted into a column of said each row after receiving the INSERT statement but prior to inserting said each row into the target table.

3. The method of claim 2 , wherein the BOD is organized into a plurality of rows and each row is modified to contain a partition ID value so that the partition ID value is injected in the data stream.

4. The method of claim 1 , further comprising:
specifying a shared memory location that contains a current PID; and
writing the current PID to the shared memory location, wherein the current PID is readable by the target database system.

5. The method of claim 1 :
wherein the PID is specified in the data stream itself such that each batch of the BOD is preceded with a batch PID and a quantity of rows that the batch PID is applicable to;
the method further comprising:
reading and storing the batch PID and the quantity of rows; and
reading a number n of rows corresponding to the quantity of rows from the data stream and processing each read row with the read batch PID.

6. The method of claim 1 , further comprising performing the demultiplexing, by the source database system, of rows in the BOD utilizing an unload utility that is started on the source database system in order to extract the BOD from the source table and populate the plurality of data streams.

7. The method of claim 6 , further comprising:
determining, for all logical nodes in the target database system, unload criteria that identity all rows that go into each respective logical node; and
starting the unload utility with the unload criteria.

8. The method of claim 1 , further comprising:
recombining the plurality of data streams into a single data stream, wherein:
in the single data stream, granularity is on blocks or batches of rows instead of single rows; and
each batch has a header and the header indicates for which target node the block or batch is.

9. The method of claim 1 , further comprising sending directly to the target node on the target database system, the data streams resulting from the demultiplexing of the data on the source database system.

10. A system for processing insertions into a target table of a target database system, comprising:
a memory; and
a processor, that is configured to:
receive a single query language INSERT statement related to a source partition of a source table in a source database system that identifies a block of data (BOD), having a partition identifier (PID), to be inserted into the target table, wherein the INSERT statement contains no literal PID;
responsive to the receipt of the INSERT statement, receive the BOD from the source table via a data stream; and
insert the BOD into the target table to result in a target table containing the BOD;
wherein the data stream comprises a plurality of data streams that are demultiplexed on the source database system.

11. The system of claim 10 , wherein the processor is further configured to insert a PID of each row of the BOD into a column of said each row after receiving the INSERT statement but prior to an insert of said each row into the target table.

12. The system of claim 11 , wherein the BOD is organized into a plurality of rows and the processor is configured to modify each row to contain a partition ID value so that the partition ID value is injected in the data stream.

13. The system of claim 10 , wherein the processor is further configured to:
specify a shared memory location that contains a current PID; and
write the current PID to the shared memory location, wherein the current PID is readable by the target database system.

14. The system of claim 10 , wherein the PID is specified in the data stream itself such that each batch of the BOD is preceded with a batch PID and a quantity of rows that the batch PID is applicable to;
wherein the processor is further configured to:
read and store the batch PID and the quantity of rows; and
read a number n of rows corresponding to the quantity of rows from the data stream and process each read row with the read batch PID.

15. The system of claim 10 , wherein a processor of the source database system is configured to demultiplex rows in the BOD utilizing an unload utility that is started on the source database system in order to extract the BOD from the source table and populate the plurality of data streams.

16. The system of claim 15 , wherein the processor is further configured to:
determine, for all logical nodes in the target database system, unload criteria that identity all rows that go into each respective logical node; and
start the unload utility with the unload criteria.

17. A computer program product for processing insertions into a target table of a target database system, the computer program product comprising a computer readable storage medium having computer-readable program code embodied therewith to, when executed on a processor:
receive a single query language INSERT statement related to a source partition of a source table in a source database system that identifies a block of data (BOD), having a partition identifier (PID), to be inserted into the target table, wherein the INSERT statement contains no literal PID;
responsive to the receipt of the INSERT statement, receive the BOD from the source table via a data stream; and
insert the BOD into the target table to result in a target table containing the BOD;
wherein the data stream comprises a plurality of data streams that are demultiplexed on the source database system.

18. The computer program product of claim 17 , wherein the instructions further cause the processor to insert a PID of each row of the BOD into a column of said each row after receiving the INSERT statement but prior to an insert of said each row into the target table.

Assignees

• IBM

Inventors

• Stolze Knut
• Beier Felix
• Oliveira Lizardo Luis Eduardo
• Geiselhart Reinhold

Classifications

• G06F16/2386 Primary

  Bulk updating operations (data conversion details G06F16/258) · CPC title

• G06F16/24554

  Unary operations; Data partitioning operations · CPC title

• G06F16/24568

  Data stream processing; Continuous queries · CPC title

• G06F16/2282

  Tablespace storage structures; Management thereof · CPC title