FAILURE RECOVERY AND ERROR CORRECTION TECHNIQUES FOR DATA LOADING IN INFORMATION WAREHOUSES

• US 20080307011A1
• Filed: 06/05/2008
• Published: 12/11/2008
• Est. Priority Date: 06/07/2007
• Status: Active Grant

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Chen, Ying, Wang, Rui, He, Bin, Rhodes, James J., Lelescu, Ana
• Granted Patent: US 9,218,377 B2
• Time in Patent Office: Days
• US Class Current: 707/202
• 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:

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.

2. The method of claim 1, wherein the checkpointing maintains dependencies among multiple database tables affected by the data loading.

3. The method of claim 1, wherein the recovering comprises restoring the information warehouse to a checkpoint.

4. The method of claim 1, wherein the recovering comprises resuming the data loading from a checkpoint to which the information warehouse has been restored.

5. The method of claim 1, wherein:

the recovering comprises restoring the information warehouse to a checkpoint; and

the restoring removes incomplete records from the information warehouse.

6. The method of claim 1, wherein:

the recovering comprises resuming the data loading from a checkpoint to which the information warehouse has been restored so that the resuming avoids duplicate data loading.

7. A method comprising:

performing versioning concurrently with data loading into an information warehouse; and

processing an undo operation of the loaded data from a current version to a previous version using the versioning.

8. The method of claim 7, further comprising:

processing a redo operation of the loaded data from the current version to a later version using the versioning.

9. The method of claim 7, wherein:

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

10. The method of claim 7, wherein the data loading comprises loading a plurality of source files, and the versioning comprises:

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

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

11. The method of claim 10 wherein the undo operation comprises:

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

leaving the maximum version number for the source file unchanged.

12. The method of claim 11 wherein the redo operation comprises:

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

13. A method comprising:

performing failure recovery without starting a data load from the beginning of a source file but rather from a latest checkpoint for data loading using a checkpoint process performed at an information warehouse level, and characterized by a state transition diagram having a plurality of states; and

tracking state transitions among the states using a system state table.

14. The method of claim 13, further comprising:

updating a set of checkpoint logs to record checkpoints; and

updating a set of pending logs to record pending updates to the information warehouse.

15. The method of claim 13, wherein:

the state-transition diagram includes checkpoint states and pending states; and

each state-transition is based on a data loading activity.

16. The method of claim 15, wherein:

the checkpoint states are chosen from the group consisting of;

empty, added, and synced.

17. The method of claim 15, wherein:

the pending states are chosen from the group consisting of;

add pending and sync pending.

18. The method of claim 15, wherein:

the data loading activities are chosen from the group consisting of;

beginning, completing, aborting, undoing, syncing, sync-aborting, sync-completing, and sync-undoing.

19. An information warehouse system comprising:

a source file version table;

a plurality of individual data base (DB) tables each of which contains versioning information in addition to data base information; and

a versioning mechanism that accesses the source file version table and any combination of the DB tables to perform update, undo, and redo operations of data loading for an information warehouse.

20. The information warehouse system of claim 19, wherein:

the versioning mechanism updates information in the source file version table and the DB tables according to at least one scheme selected from the group consisting of a first scheme and a second scheme, the scheme being chosen according to different workload characteristics encountered by the information warehouse system.

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

in the first scheme, a valid version for each record is calculated using a creation version number information from the DB tables and a current version number information from the source file version table; and

in the second scheme, a valid version for each record is recognized according to a flag information from the DB tables.

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

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

a current version number of the source file; and

a maximum version number equal to the maximum value that the current version number for the source file has been assigned.

23. The information warehouse system of claim 19, wherein the versioning information in the DB tables includes information for:

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

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

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

24. The information warehouse system of claim 19, wherein:

in response to the system performing update, undo, and redo operations on a source file, 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.

25. A computer program product for use with an information warehouse, the computer program product comprising a computer useable medium including a computer readable program, wherein the computer readable program when executed on a computer causes the computer to:

process a data loading task according to a state transition diagram that has checkpoint states;

track state transitions among states of the state transition diagram using a system state table; and

wherein in response to a data loading failure;

completing the data loading task does not require entirely repeating the data loading task; and

completing the data loading task comprises reloading data from a most recent checkpoint state.

26. The computer program product of claim 25, wherein:

the system state table includes states of the data loading task wherein each state of the data loading task is a state of the state transition diagram.

27. The computer program product of claim 25, wherein:

the system state table includes information to locate data base (DB) tables that the loading task affects;

the system state table includes information to locate DB table attributes of the DB tables that have a dependent relationship;

the system state table includes information to locate a source file loaded by the data loading task; and

the system state table includes a record loading count for each of the DB tables.

28. The computer program product of claim 25, wherein:

the system state table includes information to locate DB tables that are dependent; and

the tracking includes recording a checkpoint state of the data loading task only after all dependent DB tables are updated consistently.

29. The computer program product of claim 25, wherein in response to a data loading failure:

completing the data loading task comprises examining a checkpoint log and the system state table to identify a checkpoint state of the data loading task recorded previous to the data loading failure.

30. The computer program product of claim 25, wherein in response to a data loading failure completing the data loading task comprises:

resolving inconsistencies among records loaded by the data loading task; and

resuming the data loading task from the most recent checkpoint state without generating duplicate records.

31. A computer program product for use with an information warehouse, the computer program product comprising a computer useable medium including a computer readable program, wherein the computer readable program 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;

current version information for each of the source data files;

maximum version information for each of the source data files;

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

perform update, undo, and redo operations of data loading using the source file version table and the DB tables.

32. The computer program product of claim 31, 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.

33. The computer program product of claim 32 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.

34. The computer program product of claim 31, 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.

35. The computer program product of claim 31, 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”

.

36. The computer program product of claim 31, wherein:

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

37. The computer program product of claim 31, 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”

.