# US 2019/0377737 A1 · Incremental and collocated redistribution for expansion of online shared nothing database

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

- Number: US 2019/0377737 A1
- Document ID: US20190377737A1
- Status: Published application
- Kind: A1
- Application number: 16/460,919
- Filed: 2019-07-02
- Published: 2019-12-12
- Earliest claimed: 2013-12-13
- Assignees: International Business Machines Corporation
- Applicants: International Business Machines Corporation
- Inventors: Enzo Cialini; Austin Clifford; Garrett Fitzsimons
- Web page: https://patentgrep.com/patent/US20190377737A1

## CPC

- G06F 16/24554: {Unary operations; Data partitioning operations}
- G06F 16/2282: {Tablespace storage structures; Management thereof}
- G06F 16/2365: {Ensuring data consistency and integrity}
- G06F 16/24558: {Binary matching operations}
- G06F 16/27: Replication, distribution or synchronisation of data between databases or within a distributed database system; Distributed database system architectures therefor
- G06F 16/278: {Data partitioning, e.g. horizontal or vertical partitioning}
- G06F: ELECTRIC DIGITAL DATA PROCESSING (computer systems based on specific computational models G06N)

## Abstract

Provided are a system, method and computer program product for redistribution of data in an online shared nothing database, said shared nothing database comprising a plurality of original partitions and at least one new partition. Said method comprising: a collocation-set identifier for identifying two or more collocated tables in the original partitions to be redistributed over the original partitions and at least one new partition, said tables having data rows, each data row residing on a partition governed by a distribution key; a table factory for creating corresponding new tables in the new partition; a view creator for creating a UNION ALL view of all the data from both the original and new tables using the check constraints, said check constraints in turn based on the distribution keys; a counter for defining a loop of intervals over the distribution key range; a data mover for performing data movement of each data row over the current distribution key interval from the original tables to the new tables; a data changer for altering the check constraints for each distribution key interval so that the UNION ALL view is preserved thereby ensuring collocation and data integrity is maintained; a loop finisher for looping back for the next distribution key interval until all the distribution key intervals have been processed such that all the data has been moved to the new table whereby collocation and data integrity is maintained, redistribution is transparent for queries performed on the database, and tables are fully available during redistribution for read and write.

## Claims

### Claim at position 1

1-22. (canceled)

### Claim 23

A computer-implemented method for redistribution of data in an online shared nothing database, wherein the online shared nothing database comprises original partitions and at least one new partition, the method comprising:
identifying original tables that are collocated in the original partitions to be redistributed over the original partitions and the at least one new partition, wherein the original tables store data rows storing data, and wherein each of the data rows resides on a partition of the original partitions governed by a distribution key;
creating new tables in the at least one new partition corresponding to the original tables;
adding an inflight column with a default value in the original tables and a NULL value in the new tables;
creating a UNION ALL view of data from the original tables and the new tables using check constraints, wherein the check constraints are based on the distribution key;
defining distribution key intervals over a distribution key range for the distribution key; and
looping through the distribution key intervals by:
updating the default value of the inflight column to NULL for the original tables based on a current distribution key interval; and
performing data movement of each of the data rows over the current distribution key interval from the original tables to the new tables based on determining that the check constraints on the original tables are violated using the inflight column, wherein the data is moved from the original tables to the new tables while maintaining collocation; and
in response to the data movement completing for the distribution key intervals, dropping the inflight column from the original tables and the new tables.

### Claim 24 (depends on 23)

The computer-implemented method according to claim 23, wherein the data is relocated between the original tables and the new tables by using a row movement feature of the UNION ALL view.

### Claim 25 (depends on 23)

The computer-implemented method according to claim 23, further comprising:
creating a new partition group spanning the original partitions and the at least one new partition, with an associated new partition map wherein individual partitions are defined as one of a sender and a receiver of the data being moved by:
extracting a data definition of an existing partition group;
modifying the data definition to incorporate the at least one new partition; and
executing the modified data definition.

### Claim 26 (depends on 25)

The computer-implemented method according to claim 25, wherein two or more sets of collocated tables are located on two or more partitions within the existing partition group.

### Claim 27 (depends on 23)

The computer-implemented method according to claim 23, wherein each distribution key interval is small relative to an entire original table of the original tables.

### Claim 28 (depends on 23)

The computer-implemented method according to claim 23, wherein each of the data rows is moved from the original tables to the new tables incrementally and committed in batches.

### Claim 29 (depends on 23)

The computer-implemented method according to claim 23, wherein new rows inserted for the current distribution key interval are diverted to a new table of the new tables.

### Claim 30

A computer program product for redistribution of data in an online shared nothing database, wherein the online shared nothing database comprises original partitions and at least one new partition, the computer program product comprising a computer readable storage medium having computer-readable program code embodied therewith, the computer-readable program code executable by at least one processor to perform:
identifying original tables that are collocated in the original partitions to be redistributed over the original partitions and the at least one new partition, wherein the original tables store data rows storing data, and wherein each of the data rows resides on a partition of the original partitions governed by a distribution key;
creating new tables in the at least one new partition corresponding to the original tables;
adding an inflight column with a default value in the original tables and a NULL value in the new tables;
creating a UNION ALL view of data from the original tables and the new tables using check constraints, wherein the check constraints are based on the distribution key;
defining distribution key intervals over a distribution key range for the distribution key; and
looping through the distribution key intervals by:
updating the default value of the inflight column to NULL for the original tables based on a current distribution key interval; and
performing data movement of each of the data rows over the current distribution key interval from the original tables to the new tables based on determining that the check constraints on the original tables are violated using the inflight column, wherein the data is moved from the original tables to the new tables while maintaining collocation; and
in response to the data movement completing for the distribution key intervals, dropping the inflight column from the original tables and the new tables.

### Claim 31 (depends on 30)

The computer program product according to claim 30, wherein the data is relocated between the original tables and the new tables by using a row movement feature of the UNION ALL view.

### Claim 32 (depends on 30)

The computer program product according to claim 30, wherein the computer-readable program code is executable by the at least one processor to perform:
creating a new partition group spanning the original partitions and the at least one new partition, with an associated new partition map wherein individual partitions are defined as one of a sender and a receiver of the data being moved by:
extracting a data definition of an existing partition group;
modifying the data definition to incorporate the at least one new partition; and
executing the modified data definition.

### Claim 33 (depends on 32)

The computer program product according to claim 32, wherein two or more sets of collocated tables are located on two or more partitions within the existing partition group.

### Claim 34 (depends on 30)

The computer program product according to claim 30, wherein each distribution key interval is small relative to an entire original table of the original tables.

### Claim 35 (depends on 30)

The computer program product according to claim 30, wherein each of the data rows is moved from the original tables to the new tables incrementally and committed in batches.

### Claim 36 (depends on 30)

The computer program product according to claim 30, wherein new rows inserted for the current distribution key interval are diverted to a new table of the new tables.

### Claim 37

A computer system for redistribution of data in an online shared nothing database, wherein the online shared nothing database comprises original partitions and at least one new partition, the computer system comprising:
one or more processors; and
a tangible medium coupled to the one or more processors, wherein the tangible medium stores computer-readable program code for execution by at least one of the one or more processors, to perform:
identifying original tables that are collocated in the original partitions to be redistributed over the original partitions and the at least one new partition, wherein the original tables store data rows storing data, and wherein each of the data rows resides on a partition of the original partitions governed by a distribution key;
creating new tables in the at least one new partition corresponding to the original tables;
adding an inflight column with a default value in the original tables and a NULL value in the new tables;
creating a UNION ALL view of data from the original tables and the new tables using check constraints, wherein the check constraints are based on the distribution key;
defining distribution key intervals over a distribution key range for the distribution key; and
looping through the distribution key intervals by:
updating the default value of the inflight column to NULL for the original tables based on a current distribution key interval; and
performing data movement of each of the data rows over the current distribution key interval from the original tables to the new tables based on determining that the check constraints on the original tables are violated using the inflight column, wherein the data is moved from the original tables to the new tables while maintaining collocation; and
in response to the data movement completing for the distribution key intervals, dropping the inflight column from the original tables and the new tables.

### Claim 38 (depends on 37)

The computer system according to claim 37, wherein the data is relocated between the original tables and the new tables by using a row movement feature of the UNION ALL view.

### Claim 39 (depends on 37)

The computer system according to claim 37, wherein the data is relocated between the original tables and the new tables by using a row movement feature of the UNION ALL view.

### Claim 40 (depends on 37)

The computer system according to claim 37, wherein the tangible medium stores the computer-readable program code for execution by the at least one of the one or more processors, to perform:
creating a new partition group spanning the original partitions and the at least one new partition, with an associated new partition map wherein individual partitions are defined as one of a sender and a receiver of the data being moved by:
extracting a data definition of an existing partition group;
modifying the data definition to incorporate the at least one new partition; and
executing the modified data definition.

### Claim 41 (depends on 40)

The computer system according to claim 40, wherein two or more sets of collocated tables are located on two or more partitions within the existing partition group.

### Claim 42 (depends on 37)

The computer system according to claim 37, wherein each distribution key interval is small relative to an entire original table of the original tables.

### Claim 43 (depends on 37)

The computer system according to claim 37, wherein each of the data rows is moved from the original tables to the new tables incrementally and committed in batches.
