Failure recovery and error correction techniques for data loading in information warehouses

• US 9,218,377 B2
• Filed: 06/05/2008
• Issued: 12/22/2015
• Est. Priority Date: 06/07/2007
• Status: Expired due to Fees

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Chen, Ying, He, Bin, Lelescu, Ana, Rhodes, James J., Wang, Rui
• Primary Examiner(s): Saeed, Usmaan
• Assistant Examiner(s): Zhao, Yu
• Application Number:
  US12/134,065
• Publication Number:
  US 20080307011A1
• Time in Patent Office: 2,756 Days
• Field of Search: 707/695, 707999001-999005
• US Class Current: 1/1
• CPC Class Codes: G06F 11/1469 Backup restoration techniques
• CPC Class Codes: G06F 11/1471 involving logging of persistent data for recovery
• CPC Class Codes: G06F 16/219 Managing data history or versioning querying versioned data G06F16/2474; querying temporal data G06F16/2477

Abstract

A method of data loading for large information warehouses includes performing checkpointing concurrently with data loading into an information warehouse, the checkpointing ensuring consistency among multiple tables; and recovering from a failure in the data loading using the checkpointing. A method is also disclosed for performing versioning concurrently with data loading into an information warehouse. The versioning method enables processing undo and redo operations of the data loading between a later version and a previous version. Data load failure recovery is performed without starting a data load from the beginning but rather from a latest checkpoint for data loading at an information warehouse level using a checkpoint process characterized by a state transition diagram having a multiplicity of states; and tracking state transitions among the states using a system state table.

Claims

1. A method comprising:

maintaining a source file version table including;

source data file information for data loading of a plurality of source data files, wherein the source data files are .xml files;

current version information for each of the source data files; and

maximum version information for each of the source data files with respect to maximum version numbers used in the past;

maintaining a plurality of individual data base (DB) tables each of which includes creation version information for each record of each source data file;

maintaining a system table containing system information of the DB tables, including pending status and creation time of the DB tables;

performing update, undo, and redo operations of data loading using the source file version table and the DB tables, wherein the data loading includes splitting each of the source data files into multiple blocks, and loading the multiple blocks in a bulk loading operation;

tracking a tuple count and match the tuple count between the DB tables;

matching a plurality of keys between the DB tables;

aborting the data loading after a partial completion of the data loading, and resuming the data loading without restarting to a beginning of the data loading;

tracking incomplete records in the information warehouse using a state transition diagram which diagrams load progress states of the multiple blocks in the information warehouse;

removing all the tracked incomplete records in the information warehouse;

determining whether a modification has been made to one of the source data files; and

deleting a non-current version of one of the DB tables after the undo operation in response to determining that a modification was made to said one of the source data files.

2. The method of claim 1, wherein:

the undo operation is processed in response to an error in the data loading.

3. The method of claim 1, wherein the data loading comprises loading the source data files, and the versioning comprises:

assigning a current version number to each of the source data files; and

keeping track of a maximum version number for each of the source data files that is a maximum of the assigned current version numbers for the source file.

4. The method of claim 3 wherein the undo operation comprises:

updating the current version number of one of the source data files to a chosen version number less than the maximum version number for one of the source data files; and

leaving the maximum version number for the one of the source data files unchanged.

5. The method of claim 4 wherein the redo operation comprises:

if no changes have been made to the one of the source data files for which the current version number was updated to the chosen version number, reverting the updated current version number of the one of the source data files from the chosen version number to the current version number.

6. An information warehouse system comprising:

a computer processor;

a source file version table stored on the computer processor including;

source data file information for data loading of a plurality of source data files, wherein the source data files are .xml files;

current version information for each of the source data files; and

maximum version information for each of the source data files with respect to maximum version numbers used in the past;

a plurality of individual data base (DB) tables each of which includes creation version information for each record of each source data file;

a system table containing system information of the DB tables, including pending status and creation time of the DB tables;

wherein the computer processor is configured to;

perform update, undo, and redo operations of data loading using the source file version table and the DB tables, wherein the data loading includes splitting each of the source data files into multiple blocks, and loading the multiple blocks in a bulk loading operation;

track a tuple count and match the tuple count between the DB tables;

match a plurality of keys between the DB tables;

abort the data loading after a partial completion of the data loading, and resume the data loading without restarting to a beginning of the data loading;

track incomplete records in the information warehouse using a state transition diagram which diagrams load progress states of the multiple blocks in the information warehouse;

remove all the tracked incomplete records in the information warehouse;

determine whether a modification has been made to one of the source data files; and

delete a non-current version of one of the DB tables after the undo operation in response to determining that a modification was made to said one of the source data files.

7. The information warehouse system of claim 6, wherein in response to a runtime query:

a valid version for each record of each of the source data files is recognized according to a flag information from the DB tables.

8. The information warehouse system of claim 6, wherein the source file version table includes information for:

a source file name for each of the source data files of the information warehouse;

a current version number of one of the source data files; and

a maximum version number equal to the maximum value that the current version number for the one of the source data files has been assigned.

9. information warehouse system of claim 6, wherein the versioning information in the DB tables includes information for:

a source file name for each of the source data files of the information warehouse;

a creation version number for each record of the source data files; and

a deletion flag for a creation version of each record of the source data files.

10. The information warehouse system of claim 6, wherein:

in response to the system performing update, undo, and redo operations on the source data files, the versioning mechanism updates information in the source file version table and the DB tables according to which of update, undo, and redo the operation is.

11. A computer program product stored on a non-transitory computer-readable medium for use with an information warehouse, wherein the computer program product when executed on a computer causes the computer to:

maintain a source file version table including;

source data file information for data loading of a plurality of source data files, wherein the source data files are .xml files;

current version information for each of the source data files; and

maximum version information for each of the source data files with respect to maximum version numbers used in the past;

maintain a plurality of individual data base (DB) tables each of which includes creation version information for each record of each source data file;

maintain a system table containing system information of the DB tables, including pending status and creation time of the DB tables;

perform update, undo, and redo operations of data loading using the source file version table and the DB tables, wherein the data loading includes splitting each of the source data files into multiple blocks, and loading the multiple blocks in a bulk loading operation;

track a tuple count and match the tuple count between the DB tables;

match a plurality of keys between the DB tables;

abort the data loading after a partial completion of the data loading, and resuming the data loading without restarting to a beginning of the data loading;

track incomplete records in the information warehouse using a state transition diagram which diagrams load progress states of the multiple blocks in the information warehouse;

remove all the tracked incomplete records in the information warehouse;

determine whether a modification has been made to one of the source data files; and

delete a non-current version of one of the DB tables after the undo operation in response to determining that a modification was made to said one of the source data files.

12. The computer program product of claim 11, wherein:

a runtime query is processed by;

1) examining the current version information of the source file version table and the creation version information of the DB tables, and

2) selecting a version of each record for which the creation version number of the selected version of the record is the maximum of creation version numbers for versions of the record for which the creation version number is less than or equal to the current version number of the source data file for the record.

13. The computer program product of claim 12 wherein:

if the deletion flag of the selected version of the record is 0, the query result is the selected version of the record, and

if the deletion flag of the selected version of the record is 1, the query result for the selected version of the record is NULL.

14. The computer program product of claim 11, wherein:

the maintaining of a plurality of individual DB tables includes, for each record of each source data file, setting and updating a flag having a value chosen from the group consisting of;

“

active”

, “

inactive”

, and “

deleted”

, each of which indicates a status of the record.

15. The computer program product of claim 11, wherein:

a runtime query is processed by;

1) examining the current version information of the source file version table and the creation version information of the DB tables; and

2) selecting a version of each record for which the creation version number of the selected version of the record is less than or equal to the current version number of the source data file for the record and a flag of the selected version of the record has a value of “

active”

.

16. The computer program product of claim 11, wherein:

the undo operation comprises changing a version number in the source file version table.

17. The computer program product of claim 11, wherein:

the undo operation comprises;

1) changing a flag for a current version of each record of a source data file to “

inactive”

; and

2) changing the flag for a previous version of each record of the source data file to “

active”

.