Systems and methods for database synchronization

• US 7,580,970 B2
• Filed: 03/27/2006
• Issued: 08/25/2009
• Est. Priority Date: 03/31/2005
• Status: Expired due to Fees

• Current Assignee: UBS Ag
• Original Assignee: UBS Ag
• Inventors: Bank, Marcel, Loacker, Hansbeat
• Primary Examiner(s): VU, VIET DUY
• Application Number:
  US11/389,285
• Publication Number:
  US 20060222160A1
• Time in Patent Office: 1,247 Days
• Field of Search: 707/104.1, 707200-204, 707/100, 709/248, 709/201, 709/202, 709/223, 709/224, 379200-22108
• US Class Current: 709/202
• CPC Class Codes: G06F 16/275 Synchronous replication
• CPC Class Codes: Y10S 707/99945 Object-oriented database structure processing
• CPC Class Codes: Y10S 707/99948 Application of database or data structure, e.g. distributed, multimedia, or image
• CPC Class Codes: Y10S 707/99952 Coherency, e.g. same view to multiple users

Abstract

Computer network system for building and/or synchronising a second database from/with a first database, accesses by work units being carried out at least on the first database from at least one application workstation, to generate, change or delete contents of the database, with at least one first server to guide and maintain the first database, said server being connected to the at least one application workstation, at least one second server to guide and maintain the second database, at least one data connection which connects the two servers, a software program module being provided, and being set up and programmed to carry out a comparison between the first and second databases, to obtain a statement about the equality of the information content of the two databases, and starting from the data comparison, an error log file about the errored and/or missing records being produced, and an error detection and processing function correcting the errored and/or missing records.

Claims

1. A computer network system for building and/or synchronising a second database (DB2) from/with a first database (DB1), comprising:

1.1. multiple application computer workstations configured to initiate work units (UOW) that generate, change or delete contents of the first database (DB1),

1.2. at least one first server computer (S1) configured to receive the work units (UOW) from at least one of the multiple application workstations, the at least one first server computer (S1) comprising;

1.2.1. a first memory for storing the first database and storing a first set of computer executable instructions,

1.2.2. a first processing unit for accessing the first memory and executing computer executable instructions, the first set of computer executable instructions comprising;

1.2.3. an encapsulation module (KM) that performs the accesses by application software programs of the multiple application workstations by being configured to;

1.2.3.1. receive the work units (UOW),

1.2.3.2. provide access to the first database for accepted work units (UOW),

1.2.3.3. decompose each of the work units (UOW) into a sequence of one or more messages (M1 . . . Mn) associated with a given work unit (UOW), each of the messages (M1 . . . Mn) in a given sequence of one or more messages (M1 . . . Mn) comprising;

1.2.3.3.1. a work unit identifier that identifies the given work unit (UOW),

1.2.3.3.2. a sequence number that identifies a processing order of the message (M1 . . . Mn) in the given sequence of the one or more messages (M1 . . . Mn),

1.2.3.3.3. data associated with at least one database field, and

1.2.3.3.4. a database type that identifies a database update action for each of the at least one database field,

1.2.3.4. enter the given sequence of one or more messages (M1 . . . Mn) in the first database (DB1), and

1.2.3.5. send the given sequence of one or more messages (M1 . . . Mn) to the second database (DB2),

1.2.3.6. wherein the encapsulation module program (KM) is further configured to carry out all accesses by application software programs and other programs which change the first database instead of these programs, wherein these programs direct their change commands which are intended for the first database (DB1) to the encapsulation module program (KM), which instead carries out the actual accesses to the first database (DB1), and the encapsulation module program is further configured to enter the given sequence of one or more messages (M1 . . . Mn) in the first database (DB1), and send the given sequence of one or more messages (M1 . . . Mn) to the second database (DB2) in real time,

1.3. at least one second server computer (S2) comprising;

1.3.1. a second memory for storing the second database and storing computer executable instructions, and

1.3.2. a second processing unit for accessing the second memory and for executing computer executable instructions,

1.4. a software module stored in a third memory, the software module having computer executable instructions configured to;

1.4.1.1. compare information content of the first and second databases (DB1, DB2),

1.4.1.2. generate a statement of synchronisation that characterizes the equality of information content of the first and second databases (DB1, DB2) based on the comparison of the information content of the first and second databases (DB1, DB2),

1.4.1.3. generate an error log that identifies errored and/or missing records in at least one of the first and second databases (DB1, DB2), and

1.4.1.4. activate a detection and processing function that corrects and/or adds the errored and/or missing records to the appropriate database.

2. The computer network system according to claim 1, wherein the memory further comprises:

2.1. a data container with a control table and a data table, the data container having computer executable instructions configured to;

2.1.1. simulate a transaction bracket from the context of the first database in the context of the second database, and

2.1.2. write the errored/missing records identified in the error log to the data container.

3. The computer network system according to claim 2, wherein:

3.1. the software module further comprises an error detection and processing function that is a sub-function of synchronisation between the two databases, and is based on the error log file and data container, and

3.2. the computer executable instructions of the data container are further configured to write all messages to the data container during the synchronisation between the two databases.

4. The computer network system according to claim 3, wherein

4.1. computer executable instructions of the software module are further configured to identify an error that occurs during synchronisation of the first database and the second database, and

4.2. the computer executable instructions of the second server computer are configured to:

4.2.1. create a link from the data container to the error log file in response to data being identified as errored, and

4.2.2. display the errors on a display.

5. The computer network system according to claim 1, wherein the computer executable instructions of the software module further comprises:

6.1. a repeat function that carries out an immediate correction of an identified difference between information content of the first and second databases.

6. The computer network system according to claim 1, wherein the computer executable instructions of the software module further comprises:

7.1. a redelivery function configured to;

7.1.1. select an errored or missing record in the context of the second database (DB2) in a table, and

7.1.2. generate a change corresponding to the errored or missing record in the second database (DB2).

7. The computer network system according to claim 6, wherein

8.1. the redelivery function is configured to correct three possible errors defined by:

A record is absent from the first database (DB1), but present in the second database (DB2),

A record is present in the first database (DB1), but absent from the second database (DB2), or

A record is present in the first database (DB1), but present in the second database (DB2) with the wrong contents.

8. The computer network system according to claim 2, wherein

9.1. the software module has an error detection module, an error analysis module and an error correction module.

9. The computer network system according to claim 8, wherein the error detection module has computer executable instructions configured to:

10.1. withdraw and process the data from the two databases by calculating hash values for the information content of withdrawn data and comparing the calculated hash values,

10.2. fetch the data from the appropriate databases in response to detecting differences between the information content of the two databases,

10.3. compare in detail corrupted data from the first and second databases,

10.4. write the differences of the information content in the two databases to the error log file of synchronisation, and

10.5. write the data corresponding to the corrupted data to the data container.

10. The computer network system according to claim 9, wherein the computer executable instructions of the software module further comprises a repeat function configured to apply new data to the corresponding database from the data container.

11. The computer network system according to claim 1, wherein

14.1. a controller (HS) of the second database (DB2) has computer executable instructions configured to:

14.1.1. read the messages (M1 . . . Mn) which are sent to the second database (DB2) from an input wait queue (Qin),

14.1.2. determine whether all the messages (M1 . . . Mn) belonging to one work unit (UOW) have arrived in the input wait queue (Qin), and

14.1.3. execute appropriate changes in the second database (DB2) when all the messages (M1 . . . Mn) belonging to one work unit (UOW) have arrived in the input wait queue (Qin).

12. The computer network system according to claim 11, wherein

15.1. the encapsulation module program (KM) is further configured to:

15.1.1. decompose work units (UOW) coming from a batch processing run into corresponding messages (M1 . . . Mn) in response to reaching a predefined parameter, and

15.1.2. write the messages (M1 . . . Mn) to a transfer database (Q1) in response to reaching the predefined parameter, and

15.2. the first server computer (S1) further comprises a monitor software module that has computer executable instructions configured to transmit the content of the transfer database (Q1) to the second database (DB2) in response to the predefined parameter being reached.

13. The computer network system according to claim 12, wherein

16.1. the controller (HS) of the second database (DB2), has computer executable instructions further configured to feed with data a coexistence element program module for every database or application that receives data from the first database (DB1), the coexistence element program module having computer executable instructions configured to:

16.1.1. synchronise fed data for the relevant database or application program specifically, and

16.1.2. execute changes corresponding to the messages (M1 . . . Mn) belonging to one work unit (UOW) in the input wait queue (Qin) in the second database (DB2) or application program, or in the database which is associated with the relevant application program.

14. The computer network system according to claim 13, wherein

17.1. the controller (HS) has computer executable instructions further configured to store the information about which of the coexistence element programs is to be supplied with which contents from tables.

15. The computer network system according to claim 1, wherein the second server computer (S2) further comprises:

18.1. a sister software program component having computer executable instructions by which, in the case of a transaction which is initiated from one application workstation on the first database (DB1), a sister transaction is executed on the second database (DB2), and vice versa and is configured such that, from the point of view of the application workstation, the sister transaction on the side of the second database (DB2) behaves analogously to its counterpart on the side of the first database (DB1).

16. The computer network system according to claim 15, wherein the computer executable instructions of the sister software program component is configured such that:

19.1. functions, services and data which exist at the first database platform are made available in the context of the second database platform, essentially the same source programs being used, and

19.2. the sister transactions are activated in the context of the second database platform, such that the interfaces of/to the application software program(s) are essentially unchanged.

17. The computer network system according to claim 16, wherein

20.1. the interfaces of the sister transactions in the second database environment correspond to the original transactions in the first database environment, wherein the interfaces of the sister transactions are configured such that the original transactions in the first database environment or the sister transactions in the second database environment are useable interchangeably.

30. The computer network system according to claim 1, wherein the third memory is included on one of the first server computer, the second server computer or another computer.

18. A method for building and/or synchronising a second database (DB2) from/with a first database (DB1), accesses by work units (UOW) being carried out at least on the first database (DB1) from multiple application workstations, to generate, change or delete contents of the database (DB1), comprising:

21.1. guiding and maintaining the first database (DB1) with at least one first server computer (S1), connected to multiple application workstations,

21.2. guiding and maintaining the second database (DB2) with at least one second server computer (S2),

21.3. receiving the work units (UOW) at the encapsulation module program (KM),

21.4. providing access to the first database (DB1) with the work units (UOW) accepted by the encapsulation module program (KM),

21.5. decomposing each of the passed work units (UOW) into a sequence of one or more messages (M1 . . . Mn) associated with a given work unit (UOW), with the encapsulation module (KM), each of the messages (M1 . . . Mn) in a given sequence of one or more messages (M1 . . . Mn) comprising;

21.5.1. a work unit identifier that identifies the given work unit (UOW),

21.5.2. a sequence number that identifies a processing order of the message (M1 . . . Mn) in the given sequence of the one or more messages (M1 . . . Mn),

21.5.3. data associated with at least one database field, and

21.5.4. a database type that identifies a database update action for each of the at least one database field,

21.6. entering the given sequence of one or more messages (M1 . . . Mn) into the first database (DB1) with the encapsulation module (KM),

21.7. sending the given sequence of one or more messages (M1 . . . Mn) to the second database (DB2) with the encapsulation module (KM), wherein the encapsulation module program (KM) is configured to carry out all accesses by application software programs and other programs which change the first database instead of these programs, wherein these programs direct their change commands which are intended for the first database (DB1) to the encapsulation module program (KM), which instead carries out the actual accesses to the first database (DB1), and the encapsulation module program is further configured to enter the given sequence of one or more messages (M1 . . . Mn) in the first database (DB1). and send the given sequence of one or more messages (M1 . . . Mn) to the second database (DB2) in real time,

21.8. comparing the information content of first and second databases (DB1, DB2),

21.9. generating a statement for synchronisation that characterizes the equality of the information content of the two databases,

21.10. generating an error log file identifying the errored and/or missing records being produced based on the comparison of the first and second databases (DB1, DB2), and

21.11. correcting the errored and/or missing records through an error correction and processing function.

19. The method according to claim 18, further comprising

22.1. simulating a transaction bracket from the context of the first database in the context of the second database with a data container that includes a control table and a data table, and

22.2. writing errored records identified by the comparison of the database information content to the data container.

20. The method according to claim 19, wherein the error detection and processing function is a sub-function of the synchronisation between the two databases, and is based on the error log file and data container, the method further comprising:

23.1. writing all message to the data container, and

23.2. processing the messages at the data container.

21. The method according to claim 18, further comprising:

24.1. detecting and identifying errors occurring during the synchronisation between the two databases,

24.2. creating a link from the data container to the error log file, and

24.3. displaying the errors on a display.

22. The method according to claim 21, further comprising:

25.1. combining the software program components including at least the error log file, the data container, the error processing during synchronisation, the redelivery and data equalisation into one logical unit, and

25.2. providing at least one graphical user interface (GUI) that provides consolidated reporting of the synchronisation between the two databases, initial load and data equalisation components.

23. The method according to claim 18, further comprising

26.1. immediately correcting an identified difference between the first and second databases.

24. The method according to claim 18, further comprising

27.1. selecting, with a redelivery function, an errored or missing record in the context of the second database (DB2) in a table, and

27.2. generating a change corresponding to the errored or missing record in the second database with the redeliver function.

25. The method according to claim 24, wherein 28.1. the redelivery function corrects three possible errors defined by:

A record is absent from the first database (DB1), but present in the second database (DB2),

A record is present in the first database (DB1), but absent from the second database (DB2), or

A record is present in the first database (DB1), but present in the second database (DB2) with the wrong contents.

26. The method according to claim 18, wherein a data comparison system performs the comparing of the information content of first and second database (DB1, DB2), the data comparison system comprising:

29.1. an error detection component,

29.2. an error analysis component, and

29.3. the error correction and processing function.

27. The method according to claim 19, further comprising:

30.1. withdrawing and processing the data from the two databases,

30.2. calculating hash values the withdrawn data,

30.3. comparing the calculated hash values with each other to determine differences between the information content of the first and second databases,

30.4. fetching data from the appropriate databases that corresponds to the differences between the information content of the first and second databases,

30.5. comparing, in detail, corrupted data from the first and second databases,

30.6. writing the differences between the information content of the two databases to the error log file of synchronisation, and

30.7. writing the data associated with the differences between the information content of the two databases to the data container.

28. The method according to claim 27, further comprising applying new data to the corresponding database from the data container.

29. A computer readable medium having instructions for performing the method of claim 18.

31. A computer network system for synchronising a second database with a first database, comprising:

at least one first server computer configured to receive the work units from at least one of application workstation, the at least one first server computer comprising;

a memory for storing the first database and storing a set of computer executable instructions;

a processing unit for accessing the first memory and executing computer executable instructions, the set of computer executable instructions comprising;

an encapsulation module that performs the accesses by application software programs of the multiple application workstations by being configured to;

receive the work units;

provide access to the first database for accepted work units,

decompose each of the accepted work units into a sequence of one or more messages associated with a given work unit, each of the messages in a given sequence of one or more messages comprising;

 

a work unit identifier that identifies the given work unit;

 

a sequence number that identifies a processing order for the message in the given sequence of the one or more messages;

 

data associated with at least one database field; and

 

a database type that identifies a database update action for each of the at least one database field;

 

enter the given sequence of one or more messages in the first database; and

 

send the given sequence of one or more messages to the second database;

wherein the encapsulation module program is further configured to carry out all accesses by application software programs and other programs which change the first database instead of these programs, wherein these programs direct their change commands which are intended for the first database to the encapsulation module program, which instead carries out the actual accesses to the first database, and the encapsulation module program is further configured to enter the given sequence of one or more messages in the first database, and send the given sequence of one or more messages to the second database in real time.