Method And Apparatus for Propagating Tables While Preserving Cyclic Foreign Key Relationships

• US 20080005183A1
• Filed: 06/30/2006
• Published: 01/03/2008
• Est. Priority Date: 06/30/2006
• Status: Active Grant

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Kaemmerer, John P., Forlenza, Randolph M., Kalyanaraman, Raghuraman, Bostick, James E.
• Granted Patent: US 8,285,677 B2
• Time in Patent Office: Days
• US Class Current: 707/200
• CPC Class Codes: G06F 16/217 Database tuning G06F16/2282 takes precedence; database performance monitoring G06F11/3409
• CPC Class Codes: G06F 16/284 Relational databases

Abstract

The invention meeting the need identified above is the “Enhanced Database Propagation Program” or (EDPP). EDPP uses a cyclic member table that identifies a first source table in a cyclic relationship with a second source table. The first source table, identified in the cyclic member table, propagates first. EDPP replaces all foreign keys with “NULL” in the first target table. After the second source table in the subscription set has propagated to a second target table, EDPP updates first target table with the foreign keys from the first source table.

Claims

1. A computer implemented process for propagating database tables with cyclic foreign keys, the computer implemented process comprising:

identifying a first source database table and a second source database table with a cyclic relationship;

copying the first source database table to a first target database table, wherein all foreign keys referencing the second database table are replaced with “

NULL”

;

copying the second source database table to a second target database table; and

updating the first target database table with the foreign keys referencing the second database from the first source database table.

2. The computer implemented process of claim 1 further comprising:

identifying a plurality of identified source database tables wherein each of the identified source database tables have a cyclic relationship;

copying a set of identified source database tables to a plurality of target database tables, wherein all cyclic foreign keys are replaced with “

NULL” and

wherein the set of identified source database tables comprise all but a last source database table;

copying the last source database table to a last target database table; and

updating the plurality of target database tables with the cyclic foreign keys from the set of identified source database tables.

3. The computer implemented process of claim 2 wherein a member table identifies the set of identified source database tables.

4. The computer implemented process of claim 3 wherein the member table proscribes copying the set of identified source database tables in a particular sequence.

5. The computer implemented process of claim 4 wherein the set of identified source database tables are copied in the particular sequence.

6. The computer implemented process of claim 5 wherein the set of identified source database tables are updated in a reverse order than the particular sequence.

7. An apparatus for propagating database tables with cyclic foreign keys, the apparatus comprising:

a processor;

a memory connected to the processor;

a first source database table and a second source database table in the computer memory;

an enhanced database propagation program in the memory operable to;

identify the first source database table and a second source database table with a cyclic relationship;

copy the first source database table to a first target database table, wherein all foreign keys referencing the second database table are replaced with “

null”

;

copy the second source database table to a second target database table; and

update the first target database table with the foreign keys referencing the second database from the first source database table.

8. The apparatus of claim 7 further comprising:

a plurality of identified source database tables with a cyclic relationship in the memory, wherein the enhanced database propagation program in the memory is further operable to;

identify the plurality of identified source database tables with a cyclic relationship;

copy the set of identified source database tables to a plurality of target database tables, wherein all cyclic foreign keys are replaced with “

NULL” and

wherein the set of identified source database tables comprise all but a last source database table;

copy the last source database table to a last target database table; and

update the plurality of target database tables with the cyclic foreign keys from the set of identified source database tables.

9. The apparatus of claim 8 further comprising:

a member table in the memory, wherein the member table identifies the set of identified source database tables.

10. The apparatus of claim 9 wherein the member table proscribes copying the set of identified source database tables in a particular sequence.

11. The apparatus of claim 10 wherein the enhanced database propagation program in the memory is further operable to:

copy the set of identified source database tables in the particular sequence.

12. The apparatus of claim 11 wherein the enhanced database propagation program in the memory is further operable to:

update the set of identified source database tables in a reverse order than the particular sequence.

13. A computer readable memory containing a plurality of instructions to cause a computer to propagate database tables with cyclic foreign keys, the plurality of instructions comprising:

a first instruction to identify a plurality of identified source database tables, wherein each of the identified source database tables have a cyclic relationship;

a second instruction to copy a set of identified source database tables to a plurality of target database tables, wherein all cyclic foreign keys are replaced with “

NULL” and

wherein the set of identified source database tables comprise all but a last source database table;

a third instruction to copy the last source database table to a last target database table; and

a fourth instruction to update the plurality of target database tables with the cyclic foreign keys from the set of identified source database tables.

14. The computer readable memory of claim 14 further comprising a member table in the memory, wherein the member table identifies the set of identified source database tables.

15. The computer readable memory of claim 14 wherein the member table proscribes copying the set of identified source database tables in a particular sequence.

16. The computer readable memory of claim 15 wherein the third instruction copies the set of identified source database tables in the particular sequence.

17. The computer readable memory of claim 16 wherein the fourth instruction updates the set of identified source database tables in a reverse order than the particular sequence.