Method for real time data replication

[0001] The present invention relates generally to data processing systems for manufacturing/fabrication and more specifically to a method of real time access to data stored in data processing systems for manufacturing/fabrication.

[0002] Some non-relational proprietary database systems use Index Sequential Access Method (ISAM) files as the database access method. One such database system is the proprietary PROMIS (PROcess Manufacturing Integration Systems). PROMIS is a factory automation and management system available from the PROMIS System Corporation. The PROMIS system plans, monitors and controls activity in complex process manufacturing environments.

[0003] However it is very difficult to access data in ISAM database systems through SQL query tools such as Oracle™ SQL/Plus, Microsoft Visual Basic/Access™, etc. Although an SQL gateway to the proprietary system is a method to solve the problem, poor performance is a big issue in a large database. Further, engineering data is large and complex, and it is difficult to translate by an SQL gateway.

[0004] Another method is to extract the data using the data extraction functions provided by the proprietary system to an external file, such as a text file and then periodically load the text file to a relational database batch by batch for example every hour. However the user is not able to get data in real time.

[0005] U.S. Pat. No. 5,778,386 to Lin et al. describes a computer operated method comprising a sequence of steps for management of data of a manufacturing operation with workstations in several different functional locations.

[0006] U.S. Pat. No. 5,603,024 to Goldring describes a lossless distribution of time series data in a relational data base network.

[0007] U.S. Pat. No. 5,862,054 to Li describes a method to monitor process parameters from multiple process machines to provide real time statistical process control (SPC).

[0008] U.S. Pat. No. 5,806,075 to Jain et al. describes a method and apparatus for peer-to-peer data replication. The method provides the ability to replicate modifications made at a local site to multiple remote sites in a peer-to-peer environment.

[0009] Accordingly, it is an object of the present invention to provide an improved method of real time access to data stored in data processing systems for manufacturing/fabrication.

[0010] Another object of the present invention is to provide an improved method of real time access to data stored in non-relational data base systems through relational data base programs.

[0011] A further object of the present invention is to provide a data replication process from data stored in data base systems to permit real time access to that data.

[0012] Other objects will appear hereinafter.

[0013] It has now been discovered that the above and other objects of the present invention may be accomplished in the following manner. Specifically, a computer-based method of data replication of data in a programmable computer system having an ISAM database and a transaction log file, with the ISAM database having fields of tables and the transaction log file maintaining all files transactions of the ISAM database, comprises the following steps. Polling the transaction log file for file transactions of at least one selected ISAM database fields of tables by at least one data replication server. Reading the polled file transactions of the at least one selected ISAM database fields of tables by the at least one data replication server. Sending the polled file transactions of the at least one selected ISAM database fields of tables from the at least one data replication server to at least one relational database whereby the polled file transactions of the at least one selected ISAM database fields of tables sent to the at least one relational database is accessible in real time.

[0020] Unless otherwise specified, all structures, layers, steps, methods, etc. may be formed or accomplished by conventional steps or methods known in the prior art.

[0021] The following is a brief summary of the data replication process of the present invention:

[0022] 1. Get configure file, i.e. define which ISAM table is to be replicated and to which database;

[0023] 2. Initialize configure variable;

• • [0024] Database connect string;
  • [0025] Flags to send database tables;

[0026] 3. Connect to database;

[0027] 4. Obtain last applied transaction log sequence number from the last update file;

[0028] 5. Open the transaction log file;

[0029] 6. Find the last applied record using the last applied transaction log sequence number from the transaction log file;

[0030] 7. Loop 7.1 to 7.5:

• • [0031] 7.1 Get the next transaction log record;
  • [0032] 7.2 IF the transaction log record type is delete, THEN:
    • [0033] 7.2.1 Determine from the configure file if need to delete the record from the database table;
  • [0034] 7.3 IF the transaction log record type is put (insert) THEN:
    • [0035] 7.3.1 Determine from the configure file if need to insert the record from the database table;
  • [0036] 7.4 IF the transaction log record type is update THEN:
    • [0037] 7.4.1 Determine from the configure file if need to update the record from the database table;
  • [0038] 7.5 Write the transaction log sequence number to the last update file.

[0039] It is noted that while the PROMIS factory automation system is generally used as an example ISAM database system in the present application, other such database systems, whether or not proprietary, may be used with the teachings of the present invention as is apparent to one skilled in the art.

Proprietary System Overview 100

[0040] As shown in FIG. 1, in the proprietary system 100, all updates are made through the fileserver 10 to the ISAM database 12. A transaction log file 14 is generally used by the proprietary system 100 that maintains all ISAM (Index Sequential Access Method) files transactions including insert/update/delete/before update record.

Use of Data Replication Server System 102

[0041] A further shown in FIG. 1, the inventors have discovered that the limitations of a proprietary ISAM database system 100 may be overcome by applying selected records from the ISAM system transaction log (TLOG) 14 to one or more relational databases 22, 24 in real time by the use of a data replication server system 102 including one or more data replication servers 16, 18, 20. Such relational databases 22, 24 may include an Engineer Data Analysis (EDA) relational database or a Manufacture Execution System (MES) relational database, for example.

[0042] The relational database connections 26; and 28, 30 between data replication servers 16; and 18, 20, respectively, and relational databases 1; 2, respectively, of FIG. 1. may utilized SQL NET protocol to update, for example, EDA and MES relational databases 1; 2, respectively.

Transaction Log File; FIG. 2

[0043] FIG. 2 illustrates an ISAM database system 100 transaction log 14 file sample where every transaction is logged and where, for example: “Recnum”=transaction log sequence number; “Actnum”=process ID (each one represents a user); “FUTA”=future action file; “ACTL”=active lot file; “EQPS”=equipment status; (where “FUTA,” “ACTL” and “EQPS” are Index Sequential Access Method (ISAM) tables); “Beg_Mul_Upd”=begin multiple update; “End_Mul_Upd=end multiple update; “M”=multiple update; “JM”=journal flag; and “Putrec”=put (insert) record. It is noted that for a PROMIS ISAM database system, for example, a journal operation (for recovery purposes) always occurs before an update operation.

[0044] In accordance with the preferred embodiment of the present invention: fields of tables in the ISAM database 12 can be selected as needed; table in the ISAM database 12 can be selected as needed; multiple data loaders may be utilized (for example using two servers 18, 20 to update a single relational database 24); and multiple destination databases may be utilized (for example multiple servers 16, 18, 20 to update two relational databases 22, 24).

Data Extraction Functions of Proprietary System; FIG. 3

[0045] FIG. 3 illustrates 31 [labeled “1)” through “31)” for ease reference] sample data extraction functions of a proprietary system 100, for example a PROMIS system. The data extraction functions (where, for example, “Extr . . . ”=extraction) may be broken down into five groups: Extraction Functions 200 (1 through 5); Work File Maintenance functions 300 (6 through 16); Join Functions 400 (17 and 18); External Interfaces 500 (19 through 29) that permit transfer from the internal format of the system 100 to formats readable by other databases, statistical software packages, etc.; and Special Reports 600 (30 and 31).

[0046] Preferably, the primary data extraction functions from the PROMIS proprietary system 100 of FIG. 3 of interest in the present invention are: the 1) General File Extraction function; and the 2) Active Lot Extraction function within the Extraction Functions group 200. Analogous such functions would be of interest for other systems 100, i.e. those that would provide greater benefits if able to be accessed in real time.

Steps of the Present Invention

[0047] The data replication process of the present invention may be represented by the following steps:

[0048] 1. Get configure file, i.e. define which ISAM table is to be replicated and to which database 22, 24; (where the ISAM tables in FIG. 2 are ACTL, EQPS and FUTA;

[0049] 2. Initialize configure variable;

• • [0050] Database connect string;
  • [0051] Flags to send database tables;

[0052] 3. Connect to database 22, 24;

[0053] 4. Obtain last applied transaction log 14 sequence number from the last update file; (the last update file is a flat file that stores the last update transaction log sequence number);

[0054] 5. Open the transaction log file 14;

[0055] 6. Find the last applied record using the last applied transaction log sequence number from the transaction log file 14;

[0056] 7. Loop 7.1 to 7.5:

• • [0057] 7.1 Get the next transaction log record;
  • [0058] 7.2 IF the transaction log record type (“Operation Etc.” from FIG. 2) is delete, THEN:
    • [0059] 7.2.1 Determine from the configure file if need to delete the record from the database table;
  • [0060] 7.3 IF the transaction log record type is put (insert) THEN:
    • [0061] 7.3.1 Determine from the configure file if need to insert the record from the database table;
  • [0062] 7.4 IF the transaction log record type is update THEN:
    • [0063] 7.4.1 Determine from the configure file if need to update the record from the database table;
  • [0064] 7.5 Write the transaction log sequence number to the last update file.

[0065] Essentially, the above series of steps 1 through 7, define the real time data extraction from the transaction log (TLOG) 14 of a proprietary system 100 by one or more servers 16, 18, 20 of the data replication server 102 to one or more relational databases 22, 24 to permit real time access to the data stored in a proprietary system 100. Data replication servers 16, 18, 20 poll TLOG 14 every few seconds, for example every 3 seconds, until there is no more relevant data.

List of Available Reports From Relational Databases 22, 24; FIG. 4

[0066] FIG. 4 is a sample list of real time reports (numbered 1 through 27 for ease reference) of available from relational databases 22, 24. For example, the Non-Available Equipment Report (#1) shows equipment which are not available due to the equipment being down or are awaiting an engineer to check the equipment. Users may get the real time status report from the reporting system of the present invention.

[0067] The advantages of the present invention include:

[0068] 1. the non-real time data access limitation of proprietary ISAM database systems can be overcome;

[0069] 2. users may access non-relational proprietary ISAM database systems, such as PROMIS, through SQL queries of a relational database;

[0070] 3. end-user query tools can be used to access proprietary ISAM database systems such as Oracle SQL/Plus™, GUI (graphical user interface) query tools, most Microsoft® products such as Excel®, Access® and Visual Basis®, and Java™ applications via JDBC;

[0071] 4. the proprietary system host load is reduced;

[0072] 5. more than one data replication server can update the same relational database to speed up data replication; and

[0073] 6. can replicate to more than one relational database.

[0074] FIG. 5 illustrates a flowchart of an exemplary date replication method of the invention. The method begins at step 500, where a transaction log file of a non-relational database is polled by a data replication server. The method continues to step 502, where responsive to detecting file transactions of the non-relational database, the method reads the file transactions from the transaction log file of the non-relational database. The method then continues to step 504, where the method determines if the file transactions read from the transaction log file indicate a change in the non-relational database based on a record type of the file transactions, wherein the record type is one of a delete, put, and update record. Thereafter, the method continues to step 506, where if the file transactions read from the transaction log file indicate a change in the non-relational database, the file transactions are sent from the at least one data replication server to at least one relational database, wherein the file transactions of the non-relational database sent to the at least one relational database are accessible in real time. The method may further include sending a real time equipment status from the at least one data replication server to the at least one relational database, and/or the method may further include writing a transaction log sequence number to the last update file, as shown in step 508.

[0075] While particular embodiments of the present invention have been illustrated and described, it is not intended to limit the invention, except as defined by the following claims.