Method and apparatus for loading data into multi-table tablespace
AssigneeBMC Software, Inc.
InventorPatricia L. Yingst
Life of this patent
Estimating the term…
Abstract
A relational database load operation is disclosed for loading data into a multi-table tablespace, such as a segmented tablespace in a DB2® environment. In one aspect, the load operation allows one or more tables to be replaced in a single pass of the tablespace without modifying, loading or replacing other tables in the tablespace. Thus, the disclosed operation can replace one or more tables in a single pass of the tablespace without the need to unload and reload tables not targeted for replacement. In another aspect, the load operation allows for a combined operation where one or more tables are replaced and one or more tables are resumed in a single pass of the multi-table tablespace without modifying other tables already residing in the tablespace.
Claims54
A relational database load operation for a segmented tablespace of an IBM Database 2 (DB2) environment, the segmented tablespace having at least a first table and a second table, the load operation comprising:
- receiving a request to replace data in the first table with replacement data and to resume original data in the second table with new data, the first table and the second table being stored in separate segments of the segmented tablespace, and the replacement data differing from the data in the first table;
- creating a copy of original spacemap pages for the tablespace;
- determining reclaimable segments in the tablespace, the reclaimable segments comprising segments assigned to the first table but not segments assigned to the second table;
- replacing the data in the first table by loading the reclaimable segments in the tablespace with the replacement data, while the original data of the second table remains in place in the tablespace without being resumed or replaced, updating the copy of the spacemap pages as the reclaimable segments are loaded;
- and
- adding the new data to the second table, so the second table includes the original data and the new data,
- wherein the acts of replacing, maintaining, and resuming are performed in a single pass through the segmented tablespace.
A non-transitory computer-readable medium having instructions to perform a relational database load operation for a segmented tablespace of an IBM Database 2 (DB2) environment, the load operation implementable on a programmable control device and comprising:
- receiving a request to replace data in a first table with replacement data and to resume original data in a second table with new data, the first table and the second table being stored in separate segments of the segmented tablespace, and the replacement data differing in at least one key from the data in the first table;
- creating a copy of original spacemap pages for the tablespace;
- determining reclaimable segments in the tablespace, the reclaimable segments comprising segments assigned to the first table but not segments assigned to the second table;
- loading the reclaimable segments with the replacement data, while the original data of the second table remains in place in the tablespace without being resumed or replaced, updating the copy of the spacemap pages as the reclaimable segments are loaded;
- and
- adding the new data to the second table, so the second table includes the original data and the new data,
- wherein the acts of loading and adding are performed in a single pass through the segmented tablespace.
A relational database load operation, comprising:
- identifying a first table and a second table in a multi-table tablespace, the first table having first original data and the second table having second original data;
- creating a copy of a map governing data structures for the tablespace;
- identifying reclaimable data structures in the tablespace, reclaimable data structures comprising data structures assigned to the first table and excluding structures assigned to the second table;
- replacing the first original data in the first table with first data by loading the reclaimable data structures with the first data, the first data differing in at least one key from the first original data, updating the copy of the map as the reclaimable data structures are loaded;
- and
- adding second data to the second table,
- wherein the acts of replacing and resuming are performed in a single pass through the tablespace and without the second original data being replaced or resumed with itself.
A non-transitory computer-readable medium having instructions to perform a relational database load operation implementable on a programmable control device, the load operation comprising:
- identifying a first table and a second table in a multi-table tablespace, the first table having first original data and the second table having second original data;
- creating a copy of a map governing data structures for the tablespace;
- identifying reclaimable data structures in the tablespace, reclaimable data structures comprising data structures assigned to the first table and excluding data structures assigned to the second table;
- replacing the first original data in the first table with first data by loading the reclaimable data structures with the first data, the first data differing in at least one key from the first original data, updating the copy of the map as the reclaimable data structures are loaded with the first data;
- and
- adding second data to the second table,
- wherein the acts of replacing and adding are performed in a single pass through the tablespace and without the second original data being replaced or resumed with itself.
A relational database load operation, comprising:
- identifying a first table and a second table in a multi-table tablespace, the second table having second data;
- creating a copy of a map governing data structures for the tablespace;
- identifying reclaimable data structures in the tablespace, reclaimable data structures comprising data structures assigned to the first table but excluding data structures assigned to the second table;
- loading the reclaimable data structures with first data in a reclaim mode that terminates when there are no more reclaimable data structures or there is no unloaded first data, updating the copy of the map as the reclaimable data structures are loaded with the first data;
- determining whether an extend mode is needed;
- and
- when the extend mode is needed, adding new data structures to the tablespace and loading the new data structures with extended data,
- wherein, the second data is left in place during the reclaim mode and the extend mode.
A non-transitory computer-readable medium having instructions to perform a relational database load operation implementable on a programmable control device, the load operation comprising:
- identifying a first table and a second table in a multi-table tablespace, the second table having original second data;
- creating a copy of a map governing data structures for the tablespace;
- identifying reclaimable data structures in the tablespace, reclaimable data structures comprising data structures assigned to the first table but excluding data structures assigned to the second table;
- loading the reclaimable data structures with first data in a reclaim mode that terminates when there are no more reclaimable data structures or there is no unloaded first data, updating the copy of the map as the reclaimable data structures are loaded with the first data;
- determining whether an extend mode is needed;
- and
- when the extend mode is needed, adding new data structures to the tablespace and loading the new data structures with extended data,
- wherein, the original second data is left in place during the reclaim mode and the extend mode.
A relational database management system for a computer system, the computer system having a central processing unit operatively coupled to memory, the memory storing a multi-table tablespace having a plurality of tables, the management system comprising:
- a load operation implementable on the central processing unit for performing instructions, the instructions comprising:
- identifying a first table and a second table in the multi-table tablespace, the second table having second original data;
- creating a copy of a map governing data structures for the tablespace;
- identifying reclaimable data structures in the tablespace, reclaimable data structures comprising data structures assigned to the first table but excluding data structures assigned to the second table;
- replacing data in the first table by loading the reclaimable data structures with first data in a reclaim mode that terminates when there are no more reclaimable data structures, updating the copy of the map as the reclaimable data structures are loaded with the first data;
- determining that first data remains to be loaded in the first table;
- adding new data structures in an extend mode;
- and
- loading the new data structures with the remaining first data,
- wherein the second original data is left in place during the reclaim mode and the extend mode.
Dependencies are inferred from the claim text.