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

A method comprising:

• loading data from a plurality of source files into an information warehouse, the data loading being assigned a state of data chosen from the group consisting of:
• beginning, completing, aborting, undoing, syncing, sync-aborting, sync-completing, and sync-undoing;
• maintaining dependencies among multiple database tables affected by the data loading in said information warehouse by tracking a state of database table transition, said state of database table transition being selected as one of:
• empty, add pending, added synced and sync-pending, wherein the state of database table transition transitions from one state to another state in response to a condition of said data loading;
• performing checkpointing and versioning concurrently with said data loading, the checkpointing ensuring consistency among said multiple database tables, wherein checkpoints are maintained in a checkpoint log file and said 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;
• tracking a number of records associated with each data stored in each of the multiple database tables;
• recovering from a failure in the data loading using the checkpointing by restoring the information warehouse to at least one of said checkpoints, wherein said restoring comprises;
• avoiding duplicate data loading by determining a data item having a consistent number of records in each of said multiple database tables from data associated with said at least one checkpoint,
• removing data from said multiple database tables having an inconsistency in their corresponding number of records;
• and
• resuming the data loading from a point to which the information warehouse has been restored;
• and
• processing an undo operation of the loaded data from a current version to a previous version in response to an error in data loading using said versioning wherein said 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.

An information warehouse system comprising:

• a source file version table, including 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;
• a plurality of individual data base (DB) tables each of which contains versioning information in addition to data base information, the versioning information including:
• 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;
• 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, 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.

A computer program product for use with an information warehouse, the computer program product comprising a computer readable 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, 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;
• information to locate data base (DB) tables that the loading task affects and are dependent;
• information to locate DB table attributes of the DB tables that have a dependent relationship;
• information to locate a source file loaded by the data loading task;
• and
• a record loading count for each of the DB tables, said tracking includes recording a checkpoint state of the data loading task only after all dependent DB tables are updated consistently
• 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:
• resolving inconsistencies among records loaded by the data loading task;
• and
• reloading data from a most recent checkpoint state without generating duplicate records, while not requiring repeating the data loading task.