Change propagation method using DBMS log files

Details Change propagation method using DBMS log files Change propagation method using DBMS log files

2000-08-07

2003-05-20

Breene, John (Department: 2177)

Data processing: database and file management or data structures

Database design

Data structure types

Reexamination Certificate

active

06567823

ABSTRACT:

FIELD OF THE INVENTION
Our present invention relates to change propagation processes and, more particularly, to the propagation of derivatives of changes made to a data base relying on information contained in a log file initiated by the data base management system.
BACKGROUND OF THE INVENTION
While there have been a variety of tools developed in recent years to support data replication and the propagation, in particular, of changes made in a data base relying on information contained in the log file created by the DBMS, there are problems which have been encountered. Earlier tools can support the replication of tables as such, starting with target tables which contain all of the rows and columns of an original table or only a subset of the rows and columns of an original table and terminating in migration processes which can exploit SQL capability including joins and aggregate functions in forming the result tables.
Such tools can be provided by DBMS vendors, usually for their own DB management systems, and by independent software developers, usually for a variety of data bases. Source tables may be large in some instances to the point that it may not be desirable to derive an entire target table whenever an updated copy is required, but rather to derive the target table only once and from that point on derive only changes. Such changes involve change propagation or change propagation processes and can be very complicated, especially where the replication relies on advanced SQL features like join and aggregate functions.
As a practical matter we are not aware of any simple and effective system which can support change propagation processes for migrations that use more than one source table instance for single table subcondition traceable migrations or for fixed environment traceable migrations or those which cannot rely on a stable version of a source table that may change, as these conditions are described below.
OBJECTS OF THE INVENTION
It is, therefore, the principal object of the present invention to provide an improved method of change propagation in a migration from at least one source table to a target table in a DBMS, utilizing the log file created by the DBMS whereby the drawbacks noted above are obviated.
Another object is to provide an improved change propagation process which is supported for migrations of the particular types outlined above for which each support has been lacking in earlier DBMS.
SUMMARY OF THE INVENTION
These objects and others which will become apparent hereinafter are attained, in accordance with the invention in a method of change propagation in a migration from at least one source table to a target table in a DBMS for a data base containing the source table and having a log in which each specific record has a data independent key enabling the finding of the specific record in that log and in which the data independent key is always larger for new records than for older records. This method is specifically intended for supporting the change propagation process for migrations that use more than one source table instance for single-table sub condition traceable migrations, for supporting the change propagation process for fixed environment traceable migrations without relying on a stable version of the source table that may change, and for supporting the change propagation process for single table instance migrations without relying on a stable version of the source table that may change.
The invention comprises the steps of:
(a) deriving from the log of the database containing the source table a last data-independent key representing an immediately-previous migration or change propagation process;
(b) for a particular migration definition and the respective last data-independent key derived in step (a), for a source table undergoing a change involving a migration, generating a condensed table containing at most two records for a record identifier of each data row which has changed in the source table after the completion of a previous migration or change propagation process, the two records being selected from before and after images of the data row which has changed in the source table or from a before image of a first pair of the images and an after image of a last pair of the images where the same data row is changed a number of times and a respective pair of images are formed with each change;
(c) for each source table appearing in the migration definition, eliminating redundant changes and from the migration definition and the condensed table, generating a file containing delete statements, a file containing update statements, an insert table and an ignore file containing instructions to ignore specific rows in a table;
(d) thereafter:
(d1) implementing deletes based upon the file containing delete statements and updating the target table correspondingly,
(d2) implementing updates based upon the file containing update statements and updating the target table correspondingly,
(d3) where a single source table is involved in the migration definition and the migration definition does not involve an SQL join step, implementing insert commands contained in the insert table and updating the target table correspondingly, and
(d4) where a plurality of source tables are involved in the migration definition and the migration definition includes an SQL join step, joining each insert table generated from one of the plurality of source tables with at least parts of the other source tables of the plurality of source tables based upon the migration definition, the ignore file, the respective source table and the insert table to generate a respective insert statement file; and
(e) updating the target table based upon commands in the insert statement file.
Preferably in step (d4), for source tables T
1
, T
2
, T
3
, . . . having respective insert tables i
1
, i
2
, i
3
, . . . each insert table i
1
, i
2
, i
3
, . . . is iteratively joined to that portion of the other source tables T
2
, T
3
, . . . , T
1
, T
3
, . . . , T
1
, T
2
, . . . ignoring rows in response to a respective instruction of the respective ignore file.


REFERENCES:
patent: 4498145 (1985-02-01), Baker et al.
patent: 5613111 (1997-03-01), Malatesta et al.
patent: 5781910 (1998-07-01), Gostanian et al.
patent: 6151608 (2000-11-01), Abrams
patent: 6167405 (2000-12-01), Rosensteel et al.
patent: 6334215 (2001-12-01), Barker et al.
patent: 6343296 (2002-01-01), Lakhamraju et al.
patent: 6370524 (2002-04-01), Witkowski
patent: 6430579 (2002-08-01), Furminger et al.

Affiliated with

Also associated with

Rating

Change propagation method using DBMS log files does not yet have a rating. At this time, there are no reviews or comments for this patent.

If you have personal experience with Change propagation method using DBMS log files, we encourage you to share that experience with our LandOfFree.com community. Your opinion is very important and Change propagation method using DBMS log files will most certainly appreciate the feedback.

Rate now

Comments { 0 }

Profile ID: LFUS-PAI-O-3016075