Database consolidation tool

• US 9,514,163 B2
• Filed: 11/17/2005
• Issued: 12/06/2016
• Est. Priority Date: 11/17/2005
• Status: Active Grant

• Current Assignee: Kyndryl Incorporated
• Original Assignee: International Business Machines Corporation
• Inventors: Farr, James A., Ledbetter, Billy Patrick, Morgan, Timothy S.
• Primary Examiner(s): Boccio, Vincent
• Application Number:
  US11/282,444
• Publication Number:
  US 20070112834A1
• Time in Patent Office: 4,037 Days
• Field of Search: 707/7, 707/101, 707/803, 707/610, 707/714, 707/756
• US Class Current: 1/1
• CPC Class Codes: G06F 16/214 Database migration support
• CPC Class Codes: G06F 16/24544 Join order optimisation
• CPC Class Codes: G06F 16/2456 Join operations

Abstract

A method for consolidating a database in a Seibel CRM environment is presented. Bypassing the Business Object Layer (BOL) of the Seibel CRM environment, primary keys in a consolidated table are realigned such that a consolidated child table, created from two distinct databases, is able to point to reference items in a single parent table.

Claims

1. A computer-implementable method for consolidating a plurality of databases, the method comprising:

associating a child target table with a parent target table, wherein each row in the child target table includes a first foreign key pointer to a target primary key in the parent target table, wherein the parent target table provides a first administrative descriptor of each data field in the child target table;

associating a child source table with a parent source table, wherein each row in the child source table includes a second foreign key pointer to a source primary key in the parent source table, wherein the parent source table provides a second administrative descriptor of each data field in the child source table;

merging the child target table with the child source table to form a consolidated child table;

creating a temporary parent source table by copying the parent source table and adding a target row ID column used to track completion states of Foreign Key Realignment (FKR);

reading, from a foreign key table control file, a script that associates the target primary key and the source primary key with a single reference item found in both the parent target table and the temporary parent source table to produce, in an interim source/target reference table, one logical record per each reference item;

reading, from a foreign key realignment control file, a script to determine which child columns in the consolidated child table require realignment such that each same reference item has a same primary key;

reading each defined child column that requires alignment from the foreign key realignment control file;

creating a realigned consolidated child table and a common set of reference data, based on replacing each instance of the second foreign key pointer with the first foreign key pointer, in the consolidated child table, for all rows retrieved from the interim source/target reference table, wherein the first foreign key pointer is selected based on matching the first administrative descriptor with the second administrative descriptor; and

pointing each entry in the realigned consolidated child table to the parent target table.

2. The method of claim 1, wherein the database is a Siebel CRM database system that includes a business object layer (BOL) and wherein further the method avoids using the BOL.

3. The method of claim 1, wherein the step of creating a realigned consolidated child table utilizes a Structured Query Language (SQL) to populate the consolidated child table.

5. The method of claim 1, further comprising:

creating a new temporary parent source table in the target database, wherein the temporary parent source table is a copy of the parent source table from the source database, with an addition of a target row ID column used to track completion states of Foreign Key Realignment (FKR); and

merging the child target table and child source table to form a consolidated child table, which points to both parent target table as well as temporary parent source table.

6. The method of claim 1, further comprising:

reading a row from a Parent Table User Key Control File, which includes information that identifies which tables match up and coordinates a target table name with a target column; and

joining the target table and source temp table together via the column identified from the Parent Table User Key Control File to create an interim logical source/target reference table, which identifies which primary keys were used to identify reference items in the tables.

7. The method of claim 1, further comprising:

creating the realigned consolidated child table by consulting with an interim Source/Target reference Table.

8. The method of claim 1, further comprising:

invoking a script for the parent target table that is to ultimately have a related child table realigned (S_BU);

reading a record from the source temporary table; and

retrieving one or more user key(s) for the S_BU table for the parent table of the children to be realigned.

9. The method of claim 1, further comprising:

dynamically building a cursor to retrieve a matching target parent row;

applying the cursor to retrieve a matching target row ID, wherein an output includes the target row ID and source row ID;

consulting the database to find a match;

in response to finding a match;

reading the first mapped child table and column for the parent table (S_BU) from the control file;

replacing all occurrences of the source row id with the target row id for a given child table/column, through reference to a database table; and

updating the target row ID to the source row ID in the temporary table.

10. The method of claim 1, further comprising:

importing reference/admin data from target database to source database by;

extracting selected columns for Foreign Key Realignment (FKR) required parent data from the target database;

loading the selected columns into the source database, wherein each selected column includes a parent identifier column, which contains a pointer referenced in the child tables, along with required user keys utilized in the lookup; and

adding a custom extension column for each parent table; and

updating a target row ID to a source row ID in the temporary table.

11. The method of claim 1, wherein:

said creating a realigned consolidated child table utilizes a Structured Query Language (SQL) to populate the consolidated child table;

the importing of parent tables is completed via a build script to build the temp tables, an extract script to retrieve the target database tables, and a load script to load data from delimited files into the temp tables in the source database; and

invoking Foreign Key Realignment (FKR) for specific parent tables is completed via two control files, as well as a ksh (script) file, where a first control file contains a list of parent tables and associated child tables and columns which contain foreign key pointers to the particular parent table and a second control file contains user key definitions for the parent tables.

4. The method of clam 1, further comprising:

migrating reference (parent) tables from the target database to the source database prior to invoking Foreign Key Realignment;

storing the migrated data in temporary tables for later use by the Foreign Key Realignment application to perform lookups of matching reference data between the two databases, wherein foreign key pointers in a source customer data to source reference data can be realigned to point to corresponding target reference/admin items; and

in response to completion of the Foreign Key Realignment, migrating customer data from the source database to the target database utilizing a migration tool which takes an input comprising a list of tables that are required for migration;

wherein Foreign Key Realignment and migration each use a common engine, and a community of Foreign Key pointers to align and tables to migrate can be set in control files, and do not have to be individually configured.

12. A system comprising:

a hardware processor;

a data bus coupled to the hardware processor;

a memory coupled to the data bus; and

a computer readable storage medium embodying computer program code, the computer program code comprising instructions executable by the hardware processor and configured to;

associate a child target table with a parent target table, wherein each row in the child target table includes a first foreign key pointer to a target primary key in the parent target table;

associate a child source table with a parent source table, wherein each row in the child source table includes a second foreign key pointer to a source primary key in the parent source table;

merge the child target table with the child source table to form a consolidated child table;

create a temporary parent source table by copying of the parent source table and adding a target row ID column used to track completion states of Foreign Key Realignment (FKR);

read, from a foreign key table control file, a script that associates the target primary key and the source primary key with a single reference item found in both the parent target table and the temporary parent source table to produce, in an interim source/target reference table, one logical record per each reference item;

read, from a foreign key realignment control file, a script to determine which child columns in the consolidated child table require realignment such that each same reference item has a same primary key;

read each defined child column that requires alignment from the foreign key realignment control file;

create a realigned consolidated child table and a common set of reference data, based on replacing each instance of the second foreign key pointer with the first foreign key pointer, in the consolidated child table, for all rows retrieved from the interim source/target reference table, wherein the first foreign key pointer is selected based on matching the first administrative descriptor with the second administrative descriptor; and

point each entry in the realigned consolidated child table to the parent target table.

13. The system of claim 12, wherein the database is a Siebel CRM system having a business object layer (BOL) and wherein further the method avoids using a BOL in the Siebel CRM system.

14. A non-transitory computer readable storage medium embodying computer program code, the computer program code comprising computer executable instructions configured to cause a data processing system to:

associate a child target table with a parent target table, wherein each row in the child target table includes a first foreign key pointer to a target primary key in the parent target table;

associate a child source table with a parent source table, wherein each row in the child source table includes a second foreign key pointer to a source primary key in the parent source table;

merge the child target table with the child source table to form a consolidated child table;

create a temporary parent source table by copying the parent source table and adding a target row ID column used to track completion states of Foreign Key Realignment (FKR);

read, from a foreign key table control file, a script that associates the target primary key and the source primary key with a single reference item found in both the parent target table and the parent source table to produce, in an interim source/target reference table, one logical record per each reference item;

read, from a foreign key realignment control file, a script to determine which child columns in the consolidated child table require realignment such that each same reference item has a same primary key;

read each defined child column that requires alignment from the foreign key realignment control file;

create a realigned consolidated child table and a common set of reference data, based on replacing each instance of the second foreign key pointer with the first foreign key pointer, in the consolidated child table, for all rows retrieved from the interim source/target reference table, wherein the first foreign key pointer is selected based on matching the first administrative descriptor with the second administrative descriptor; and

point each entry in the realigned consolidated child table to the parent target table.

15. The non-transitory computer readable storage medium of claim 14, wherein the database is a Siebel CRM system having a business object layer (BOL) and wherein further the computer executable instructions perform database consolidation while avoiding use of the Business Object Layer (BOL) in the Siebel CRM system.

16. The non-transitory computer readable storage medium of claim 14, wherein the computer program code is deployed to a client computer from a server at a remote location.

17. The non-transitory computer readable storage medium of claim 14, wherein the computer program code is provided by a service provider to a customer on an on-demand basis, wherein the on-demand basis is determined by evaluating a Central Processing Unit (CPU) capacity of the client computer, and wherein if sufficient CPU capacity at the client computer is not available to receive the computer program code, allocating additional CPU capacity from another CPU in the client computer to receive and process the computer program code.