Data loading tool

• US 9,471,607 B2
• Filed: 08/14/2013
• Issued: 10/18/2016
• Est. Priority Date: 10/19/2012
• Status: Active Grant

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Abed, Sami, Clifford, Austin, Emanowicz, Konrad, Jenkins, Gareth
• Primary Examiner(s): Mofiz, Apu
• Assistant Examiner(s): Nguyen, Cindy
• Application Number:
  US13/966,360
• Publication Number:
  US 20140114924A1
• Time in Patent Office: 1,161 Days
• US Class Current: 1/1
• CPC Class Codes: G06F 11/1451 by selection of backup contents
• CPC Class Codes: G06F 16/21 Design, administration or maintenance of databases
• CPC Class Codes: G06F 16/2282 Tablespace storage structures; Management thereof
• CPC Class Codes: G06F 16/245 Query processing
• CPC Class Codes: G06F 2201/84 Using snapshots, i.e. a logical point-in-time copy of the data

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.

Claims

1. A method for loading data from a backup image of a database, the method comprising:

selecting a subset statement defining a subset of the data in the database;

identifying tables of the database based on metadata of the database;

writing a target database having a structure but not the data of the identified tables;

constructing, by a computer processor, one or more table statements defining a subset of each identified table based on the subset statement, wherein the subset of each identified table includes particular rows of a corresponding table;

determining an unload order to unload the table statements in the least time, wherein the unload order specifies unloading the identified tables having filter predicates specified in the subset statement before other tables and the unload order specifies that unloading the identified tables having the lowest expected number of rows are processed first; and

unloading selected data from the backup image into the target database in the determined unload order using respective table statements as filters.

2. The method according to claim 1, wherein the unload order further specifies that the identified tables existing in a single backup file are processed in the same scan of the backup image.

3. The method according to claim 1, wherein the unload order further specifies that the identified tables having the lowest expected number of rows are processed first.

4. The method according to claim 1, wherein the determined unload order is determined by examining a query strategy from the database based on the subset statement.

5. The method according to claim 1, further comprising prompting a user to modify the subset statement.

6. The method according to claim 1, further comprising:

prompting a user to modify a set of database constraints; and

configuring an empty database target with the database constraints.

7. The method according to claim 1, further comprising using a latest image of the database as the backup image by default.

8. The method according to claim 7, further comprising providing an interface for a user to choose a database image other than the latest image as the backup image.

9. A computer program product comprising a non-transitory computer readable storage medium having computer readable program code embodied thereon, wherein the computer readable program code is executable by a processor to perform a method comprising:

selecting a subset statement defining a subset of data in a database;

identifying tables of the database based on metadata of the database;

writing a target database having a structure but not the data of the identified tables;

constructing one or more table statements defining a subset of each identified table based on the subset statement, wherein the subset of each identified table includes particular rows of a corresponding table;

determining an unload order to unload the table statements in the least time, wherein the unload order specifies unloading the identified tables having filter predicates specified in the subset statement before other tables and the unload order specifies that unloading the identified tables having the lowest expected number of rows are processed first; and

unloading selected data from a backup image into the target database using respective table statements as filters in the determined unload order.

10. The computer program product according to claim 9, 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.

11. The computer program product according to claim 9, wherein the unload order further specifies that the identified tables having the lowest expected number of rows are processed first.

12. The computer program product according to claim 9, wherein the determined unload order is determined by examining a query strategy from the database based on the subset statement.

13. A data loading apparatus for loading data from a backup image of a database, the apparatus comprising:

a selector configured to select a subset statement defining a subset of the data in the database;

an identifier configured to identify tables of the database based on metadata of the database;

a writer configured to write a target database having a structure but not the data of the identified tables;

a builder configured to construct one or more table statements defining a subset of each identified table based on the subset statement, wherein the subset of each identified table includes particular rows of a corresponding table;

a determination engine configured to determine an unload order to unload the table statements in the least time, wherein the unload order specifies unloading the identified tables having filter predicates specified in the subset statement before other tables and the unload order specifies that unloading the identified tables having the lowest expected number of rows are processed first; and

an unloader configured to unload selected data from the backup image into the target database using respective table statements as filters in the determined unload order.

14. The data loading apparatus according to claim 13, further comprising a user interface configured to prompt a user to modify the subset statement.

15. The data loading apparatus according to claim 13, further comprising a user interface configured to prompt a user to modify a set of database constraints, wherein the set of database constraints are used to configure the database target.

16. The data loading apparatus of claim 13, the builder being further configured to use a latest image of the database as the backup image by default.

17. The data loading apparatus according to 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.

18. The data loading apparatus according to claim 13, wherein the unload order further specifies that the identified tables having the lowest expected number of rows are processed first.