Post-migration validation of ETL jobs and exception management

Patent metadata

Field	Value
Publication number	US-9449060-B2
Application number	US-201313960048-A
Country	US
Kind code	B2
Filing date	Aug 6, 2013
Priority date	Aug 6, 2013
Publication date	Sep 20, 2016
Grant date	Sep 20, 2016

Abstract

Handling extract-transform-load (ETL) job mismatches as “exceptions.” Exception handling may include the following steps: (i) determining a mismatch while running an extract-transform-load job with the mismatch being a mismatch of at least one of the following types: design time information mismatch, and/or operational metadata mismatch; and (ii) responsive to determining the mismatch, handling the mismatch as an exception.

First claim

What is claimed is:

1. A method comprising:
running a first instance of a first extract-transform-load (ETL) job on a first system, with the running of the first instance including generation of first job runtime operational metadata;
running a second instance of the first ETL job on a second system, with the running of the second instance including generation of second job runtime operational metadata;
responsive to the running of the first and second instances of the first ETL job, determining existence of a job runtime issue with the running of the second instance on the second system;
responsive to the determination of existence of the job runtime issue of the second instance on the second system; and
correcting the runtime issue with respect to the second system based on intelligent analysis of design and runtime operational metadata;
wherein the correction of the runtime issue with respect to the second system comprises:
parsing a first runtime log generated by running the first instance and a second runtime log generated by running the second instance,
determining information about the runtime issue based on the parsing of the first and second runtime logs, and identifying the failure, and
finding out a root cause of the failure based upon the information determined about the runtime issue,

wherein the root cause of the failure is one of the following: an environment variable failure, or an output folder failure.

2. The method of claim 1 wherein the correction of the runtime issue with respect to the second system comprises:
finding a relevant fix is a fix repository; and
applying the relevant fix.

3. The method of claim 1 further comprising:
generating a new fix for correcting the runtime issue with respect to the second system based on the intelligent analysis of design and runtime operational metadata; and
adding the new fix to a fix repository along with information identifying the runtime issue with respect to the second system that lead to generation of the new fix.

4. A computer program product comprising:
a software storage device comprising a processor; and
computer readable program instructions stored on the software storage device; wherein the program instructions include:
first program instructions programmed to run a first instance of a first extract-transform-load (ETL) job on a first system, with the running of the first instance including generation of first job runtime operational metadata;
second program instructions programmed to run a second instance of the first ETL job on a second system, with the running of the second instance including generation of second job runtime operational metadata;
third program instructions programmed to, responsive to the running of the first and second instances of the first ETL job, determine existence of a job runtime issue with the running of the second instance on the second system;
fourth program instructions programmed to, responsive to the determination of existence of the job runtime issue of the second instance on the second system, and
fifth program instructions programmed to correct the runtime issue with respect to the second system based on intelligent analysis of design and runtime operational metadata;

wherein the correction of the runtime issue with respect to the second system comprises:
parsing a first runtime log generated by running the first instance and a second runtime log generated by running the second instance, determining information about the runtime issue based on the parsing of the first and second runtime logs, and identifying the failure, and finding out a root cause of the failure based upon the information determined about the runtime issue;

wherein the root cause of the failure is one of the following: an environment variable failure, or an output folder failure.

5. The computer program product of claim 4 wherein the correction of the runtime issue with respect to the second system comprises:
finding a relevant fix is a fix repository; and
applying the relevant fix.

6. The computer program product of claim 4 further comprising:
generating a new fix for correcting the runtime issue with respect to the second system based on the intelligent analysis of design and runtime operational metadata; and
adding the new fix to a fix repository along with information identifying the runtime issue with respect to the second system that lead to generation of the new fix.

7. A computer system comprising:
a set of processor(s);
a software storage device; and
computer readable program instructions executable on the set of processor(s) and stored on the software storage device;
wherein the program instructions include:
first program instructions programmed to run a first instance of a first extract-transform-load (ETL) job on a first system, with the running of the first instance including generation of first job runtime operational metadata,
second program instructions programmed to run a second instance of the first ETL job on a second system, with the running of the second instance including generation of second job runtime operational metadata,
third program instructions programmed to, responsive to the running of the first and second instances of the first ETL job, determine existence of a job runtime issue with the running of the second instance; on the second system,
fourth program instructions programmed to, responsive to the determination of existence of the job runtime issue of the second instance on the second system, and
fifth program instructions programmed to correct the runtime issue with respect to the second system based on intelligent analysis of design and runtime operational metadata;

wherein the correction of the runtime issue with respect to the second system comprises:
parsing a first runtime log generated by running the first instance and a second runtime log generated by running the second instance,
determining information about the runtime issue based on the parsing of the first and second runtime logs, and identifying the failure, and
finding out a root cause of the failure based upon the information determined about the runtime issue;

wherein the root cause of the failure is one of the following: an environment variable failure, or an output folder failure.

8. The computer system of claim 7 wherein the correction of the runtime issue with respect to the second system comprises:
finding a relevant fix is a fix repository; and
applying the relevant fix.

9. The computer system of claim 7 further comprising:
generating a new fix for correcting the runtime issue with respect to the second system based on the intelligent analysis of design and runtime operational metadata; and
adding the new fix to a fix repository along with information identifying the runtime issue with respect to the second system that lead to generation of the new fix.

Assignees

• IBM

Inventors

• Dhayapule Raghavendra R
• Gupta Ritesh K
• Sood Ajay

Classifications

• G06F11/079

  Root cause analysis, i.e. error or fault diagnosis (in a hardware test environment G06F11/22; in a software test environment G06F11/36) · CPC title

• G06F16/254 Primary

  Extract, transform and load [ETL] procedures, e.g. ETL data flows in data warehouses · CPC title

• G06F16/245

  Query processing · CPC title

• G06F16/2365

  Ensuring data consistency and integrity · CPC title

• G06F17/30563 Primary

  Physics · mapped topic