Creation of change-based data integration jobs

• US 9,305,067 B2
• Filed: 07/19/2013
• Issued: 04/05/2016
• Est. Priority Date: 07/19/2013
• Status: Expired due to Fees

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Gorelik, Alexander, Padmanabhan, Sriram K., Spyker, James D.
• Primary Examiner(s): Ly, Anh
• Application Number:
  US13/946,335
• Publication Number:
  US 20150026115A1
• Time in Patent Office: 991 Days
• Field of Search: 707/602, 707/E17.048, 707/E17.017, 707/714, 707/748, 707/716, 707/684, 707/704, 707/E17.007, 707/E17.005, 707/640, 707/651, 707/610, 707/803, 707/E17.044, 714/49, 714/E11.024, 718/100, 718/102, 718/103, 718/104, 718/106, 717/104, 717/109, 717/115, 709/203, 709/217, 709/218, 709/219, 709/223, 709/230, 709/231, 715/234
• US Class Current: 1/1
• CPC Class Codes: G06F 16/00 Information retrieval; Database structures therefor; File system structures therefor
• CPC Class Codes: G06F 16/2358 Change logging, detection, and notification replication G06F16/27
• CPC Class Codes: G06F 16/254 Extract, transform and load [ETL] procedures, e.g. ETL data flows in data warehouses
• CPC Class Codes: G06F 16/27 Replication, distribution or synchronisation of data between databases or within a distributed database system; Distributed database system architectures therefor
• CPC Class Codes: G06F 9/4881 Scheduling strategies for dispatcher, e.g. round robin, multi-level priority queues
• CPC Class Codes: G06F 9/5005 to service a request

Abstract

A computer software implemented method for transforming a first extract transform load (ETL) job having at least some unload transform load (UTL) portions. The method includes the following steps: (i) decomposing the first ETL job into an intermediate set of one or more jobs; and (ii) for each job of the intermediate set, transforming the job into a transactionally equivalent job to yield a final set of one or more jobs. The decomposing is performed so that each job of the intermediate jobs set is a Simple UTL job. The transforming is performed so that each job of the final set includes no UTL portions.

Claims

1. A method for transforming a first extract transform load (ETL) job having at least one unload transform load (UTL) portion, the method comprising:

decomposing the first ETL job into an intermediate set of one or more Simple UTL jobs;

for each Simple UTL job of the intermediate set, transforming the job into a transformationally equivalent job to yield a final set of one or more jobs that are a set of change-based transform load (CTL) job(s);

wherein;

the transforming includes the following (not necessarily in the following order);

replacing an input stage with a change capture stage programmed to receive change data from a database and to generate an output table including an indicator column that includes an indication of an insert type action or a delete type action,

delivering at least one update type action on a source database as a delete type action followed by an insert type action, and

performing at least one of the following;

inserting a row in a target database table or deleting a row in a target database, and

running the set of CTL job(s) on a source database to yield a target database;

wherein;

each Simple UTL job of the intermediate set jobs meets the following criteria;

a single input action retrieves the contents of a single table, or view on a single table, in a database,

contains no actions that operate on the entirety of data for a job,

contains no actions that rely on ordering of data, and

at least one output stage operates on a database.

2. A computer program product method for transforming a first extract transform load (ETL) job having at least one unload transform load (UTL) portion, the computer program product comprising software stored on a software storage device in a non-transitory manner, the software comprising:

first program instructions programmed to decompose the first ETL job into an intermediate set of one or more Simple UTL jobs;

second program instructions programmed to, for each Simple UTL job of the intermediate set;

transform the job into a transformationally equivalent job to yield a final set of one or more jobs that are a set of change-based transform load (CTL) job(s);

replace an input stage with a change capture stage programmed to receive change data from a database and to generate an output table including an indicator column that includes an indication of an insert type action or a delete type action;

deliver at least one update type action on a source database as a delete type action followed by an insert type action; and

perform at least one of the following;

inserting a row in a target database table or deleting a row in a target database;

third program instructions programmed to run the set of CTL job(s) on a source database to yield a target database;

wherein;

each Simple UTL job of the intermediate set jobs meets the following criteria;

a single input action retrieves the contents of a single table, or view on a single table, in a database,

contains no actions that operate on the entirety of data for a job,

contains no actions that rely on ordering of data, and

at least one output stage operates on a database; and

the software is stored on a software storage device in a manner less transitory than a signal in transit.

3. A computer system for transforming a first extract transform load (ETL) job having at least one unload transform load (UTL) portion, the computer system comprising:

a processor(s) set; and

a software storage device;

wherein;

the processor set is structured, located, connected or programmed to run software stored on the software storage device; and

the software comprises;

first program instructions programmed to decompose the first ETL job into an intermediate set of one or more Simple UTL jobs,

second program instructions programmed to, for each Simple UTL job of the intermediate set;

transform the job into a transformationally equivalent job to yield a final set of one or more jobs that a set of change-based transform load (CTL) job(s),

replace an input stage with a change capture stage programmed to receive change data from a database and to generate an output table including an indicator column that includes an indication of an insert type action or a delete type action,

deliver at least one update type action on a source database as a delete type action followed by an insert type action, and

perform at least one of the following;

inserting a row in a target database table or deleting a row in a target database, and

third program instructions programmed to run the set of CTL job(s) on a source database to yield a target database;

wherein;

each Simple UTL job of the intermediate set jobs meets the following criteria;

a single input action retrieves the contents of a single table, or view on a single table, in a database,

contains no actions that operate on the entirety of data for a job,

contains no actions that rely on ordering of data, and

at least one output stage operates on a database.