Data loading tool

Patent metadata

Field	Value
Publication number	US-9773027-B2
Application number	US-201514851184-A
Country	US
Kind code	B2
Filing date	Sep 11, 2015
Priority date	Oct 19, 2012
Publication date	Sep 26, 2017
Grant date	Sep 26, 2017

Abstract

In an exemplary embodiment of this disclosure, a method for loading data from a backup image of a database includes selecting a subset statement defining a subset of the data in the database. Tables of the database are identified based on metadata of the database. A target database is written having the structure but not the data of the identified tables. One or more table statements are constructed, by a computer processor, defining a subset of each identified table based on the subset statement. Selected data is unloaded from a backup image into the target database using respective table statements as filters.

First claim

What is claimed is:

1. A method for loading selective data from a database, the database comprising a plurality of tables that are structured in a particular topology and the database contains data in the tables, the method comprising:
receiving, by a processor, a request to access a subset of the data in the database;
in response, writing, by the processor, an empty target database having the topology of the tables in the database;
identifying, by the processor, a subset of the tables referenced in the subset of the data corresponding to the request;
determining, by the processor, an unload order to unload the subset of the data from the tables, the unload order specifying an order of the tables according to which data is unloaded, wherein the unload order facilitates unloading the data within a predetermined duration by identifying tables in the database having the lowest expected number of rows to be unloaded;
unloading, by the processor, the subset of the data from a backup image of the database into the target database in the determined unload order, wherein the processor unloads the subset of the data by filtering one or more rows of the subset of the tables by constructing one or more table statements based on the subset of the data requested; and
loading, by the processor, the filtered one or more rows into the target database.

2. The method of claim 1 , further comprising:
receiving, by the processor, a selection of the backup image to unload the subset of the data from; and
selecting, by the processor, the backup image of the database according to the selection.

3. The method of claim 1 further comprising, identifying, by the processor, the topology of the tables in the database based on metadata of the database.

4. The method according to claim 1 , wherein the unload order unloads the tables in the database that are associated with respective filter predicates specified in the request before other tables in the database.

5. The method according to claim 1 , wherein the unload order processes tables that are in a single file of the backup image in a single scan of the backup image.

6. The method according to claim 1 , wherein determining the unload order further comprises:
and ordering the tables with the lowest expected number of rows to be unloaded first.

7. The method according to claim 1 , further comprising:
receiving, by the processor, database constraints for the target database; and
configuring, by the processor, the target database according to the database constraints.

8. A computer program product comprising a non-transitory computer readable storage medium having computer executable instructions embodied thereon, the computer readable storage medium comprises instructions to:
receive a data selection query that selects a subset of data in a source database, the source database comprising the data structured in rows of tables;
identify a topology of the tables in the source database based on metadata of the source database;
create an empty target database according to the topology of the source database;
determine a first row of a first table and a second row of a second table from the source database being referenced by the data selection query;
construct a table statement that identifies the first row and the second row;
determine an unload order to unload the contents of the first row and the second row, the unload order specifying an order of the tables according to which data is unloaded wherein the unload order unloads the contents of the first table before the second table in response to the first table containing a lower number of rows than the second; and
unload contents of the first row of the first table and the second row of the second table from a backup image of the source database for loading in the target database.

9. The computer program product of claim 8 , wherein the non-transitory computer readable storage medium further comprises instructions to:
receive a selection of a version of the backup image to use for copying contents; and
use a latest version of the backup image in absence of the selection.

10. The computer program product of claim 8 , wherein the unload order facilitates the contents to be unloaded within a predetermined duration.

11. The computer program product of claim 8 , wherein the unload order unloads the contents of the first table before the second table in response to the data selection query specifying a filter predicate for the first table.

12. The computer program product of claim 8 , wherein the non-transitory computer readable storage medium further comprises instructions to unload the contents of the first table and the second table in a single scan of the backup image in response to the first table and the second table being in a single file of the backup image.

13. A system for unloading data from a backup image of a database, the system comprising:
a source database comprising data structured as rows of tables, the tables related to each other according to a predetermined topology;
a database repository comprising a plurality of backup images of the source database, wherein a backup image stores the data in files;
a server configured to:
receive a data selection query to access a subset of data in the source database;
create an empty target database according to the topology of the source database;
determine a subset of the tables in the source database referenced by the data selection query;
determine an unload order to unload data from the subset of the tables, the unload order specifying an order of the tables according to which data is unloaded, the unload order specifying that the tables having the lowest expected number of rows are processed first;
unload contents of the subset of the tables from the backup image of the source database by filtering one or more rows of the subset of the tables; and
load the filtered one or more rows to corresponding rows of the tables in the target database.

14. The system of claim 13 , wherein the server is further configured to identify the predetermined topology of the source database based on metadata of the source database.

15. The system of claim 13 , wherein the server is further configured to construct one or more table statements defining a subset of each table in the subset of tables based on the data selection query; and
determine an unload order to unload the contents of the source data according to the table statements; and
unload the contents from the backup image into the target database using respective table statements as filters in the determined unload order.

16. The system of claim 15 , wherein the server is further configured to:
receive, prior to determining the unload order:
a modification to the data selection query; and
a modification to a set of database constraints associated with the target database; and

in response, determine the unload order according to a modified data selection query and a modified target database.

17. The system of claim 15 , wherein the unload order unloads the contents from the backup image to the target database within a predetermined duration.

18. The system of claim 13 , wherein the unload order further specifies that the identified tables existing in a single backup file will be processed in the same scan of the backup image.

Assignees

• IBM

Inventors

• Abed Sami
• Clifford Austin
• Emanowicz Konrad
• Jenkins Gareth

Classifications

• G06F2201/84

  Using snapshots, i.e. a logical point-in-time copy of the data · CPC title

• G06F11/1451

  by selection of backup contents · CPC title

• G06F16/21 Primary

  Design, administration or maintenance of databases · CPC title

• G06F16/245

  Query processing · CPC title

• G06F16/2282

  Tablespace storage structures; Management thereof · CPC title