Transcription
International Journal of Reviews in ComputingIJRIC 2009 IJRIC. All rights reserved.www.ijric.orgE-ISSN: 2076-3336DESIGN OF HETEROGENEOUS DATABASES REPLICATIONUSING XML1,2SEIFEDINE KADRY, 1,2HUSSAM KASSEM, 2MOHAMAD SMAILI1Lebanese University - CNAM, Lebanon2Lebanese University - Faculty of Science, LebanonE-mail: skadry@gmail.com, hussamkassem@yahoo.com, mosmaili@ul.edu.lbABSTRACTNowadays, the application software and the variety of the (DBMS) are in increasing expansion, and thereplications between different database systems due to different infrastructures are more than needed.Since, the replication of data between different DBMS is not always possible. We propose in this paper, anew design to solve the replication problem between heterogeneous DBMS systems using XMLtechnology.Keywords: Database Management Systems (DBMS), Replication, eXtensible Markup Language (XML),Structured Query Language (SQL) Server, Oracle.The replication is a synonym of distributed data.Furthermore, it replicates data to multiple databasecopies, and the consistency of the database ismaintained by the process of synchronization. Thequestion is how to distribute data using replication,when, where, and how data is propagated.1. INTRODUCTIONPresently, most companies have differentsystems, i.e. payroll system, financial system, auditsystem, core system, etc. These systems usedifferent database management systems (e.g. oracle,Sybase, SQL Server, DB2, ), they shouldcommunicate and some times may need to replicateto generate a unified report from one system. Thereplication between these databases is very difficultdue to the internally heterogeneous structure.In the following subsections, we define thereplication software, the replication types and thereplication metaphors.2.1 REPLICATION SOFTWAREIn this paper, a proposed design to solve thisproblem is given. This design uses XML asMiddleware and .NET as interface.Each DBMS has its own module (i.e. replicationbetween SQL Server and SQL Server, Oracle andOracle ), but we don't have replication softwarebetween different DBMSs (i.e. between DB2 andOracle, SQL Server and Sybase ).The paper is organized as follows. Section 2describes the replication, the replication software,the replication types, and the replication metaphors.In Section 3, we present the XML language, and therelation with DBMS. The proposed design is givenin section 4. Finally we draw the conclusions.2.2 REPLICATION TYPESIn general, we have two types of replication:Eager and Lazy replications [1].2. REPLICATION2.2.1 EAGER REPLICATIONReplication is a "set of technologies" that canmove and copy data and database objects from oneDBMS to another of the same type or different,across different platforms and geographic locales.This allows users to work with a local copy of thedatabase, and any changes made are transferred toone or more remote servers or mobile users acrossthe network.Eager replication is also known as synchronousreplication. In this method, an application canupdate a local replica of a table, and within thesame transaction, it can also update other replicasof the same table. No concurrency anomalies occur,since synchronous replication gives serializableexecution. Any anomaly in concurrency is detectedby the locking method. If any of the nodes are90
International Journal of Reviews in ComputingIJRIC 2009 IJRIC. All rights reserved.www.ijric.orgE-ISSN: 2076-3336sites, as in the case of handheld sets or mobiledevices. This is shown in Figure 2.disconnected, eager replication prevents the updatefrom taking place. Furthermore, the atomicity of thetransactions is guaranteed by the employment of the2PC method. However, there is a compromise inperformance as a result of all the updates beingcarried in a single transaction.Eager replication consists of the following steps:execute, transmit, notify, and either commit orrollback. An executed transaction is transmitted todifferent nodes, and in the event of failure in onenode, the transaction is rolled back and all the othernodes are notified of the failure. The transaction isthen aborted in all nodes. If replication is successfulin all the nodes, a commit is broadcast and a copyof the committed transaction is then sent to all thenodes. This type of replication is illustrated inFigure 1.Figure 2: Lazy ReplicationWith this type of replication, it is possible fortwo different sites to update the same data on thesame destination site. This will lead to a conflict inthe updating of the data. Such update conflicts needto be resolved in lazy replication, and this is doneby associating timestamps with each of thetransaction objects. Each object carries thetimestamp associated with the previous update ofthat data. So when a transaction is sent to thedestination site, it first checks to see whether thetimestamp associated with the local copy of thereplicated data matches the incoming transaction’sold timestamp for that data. If they match thechanges, including the transaction’s newtimestamp, the updated transaction will be applied.If the timestamps do not match at the initial stage,the updated transaction is rejected. SQL Server, forexample, has a conflict resolution viewer that dealswith updates, inserts, and deletes.Figure 1: Eager Replication2.2 LAZY REPLICATIONLazy replication is also known as asynchronousreplication. In this case, if the transactions arecommitted, they are sent to the different sites forthe updates to occur. However, if they are rolledback, the changes will not be transmitted to thedifferent sites. Thus, the very nature ofasynchronous replication allows the updates ofcommitted transactions to be sent to disconnected2.3 REPLICATION METAPHORS [1]91
International Journal of Reviews in Computing 2009 IJRIC. All rights reserved.www.ijric.orgIJRICE-ISSN: 2076-3336subscriber. There are different types of agentssupporting different types of replicationsPublisherThe publication server (or publisher) contains thedatabase or databases that are going to bepublished. This is the source of the data (replicated)to other servers.3. XML3.1 XML ORIGIN AND GOALS [2], [3]XML stands for eXtensible Markup Languagedefined by World Wide Web Consortium (W3Cwww.w3c.org ). Markup languages describe theform of the document; this form is the way ofdescribing how the content of the document shouldbe interpreted.DistributorThe distribution server (or distributor) can eitherbe on the same server as the publication server oron a different server (in which case it is a remotedistribution server). This server contains thedistribution database. This database, also called thestore-and-forward database, holds all the datachanges that are to be forwarded from the publisheddatabase to any subscription servers that subscribeto the data. A single distribution server can supportseveral publication servers. The distribution serveris truly the workhorse of data replication.Therefore, the word Markup here is misleading;XML ought to refer eXtensible Meta Language,because it is a standardized, flexible, followingprecise, exacting rules for structure, syntax, andsemantics of data to be interpreted. XML is not aprogramming language, it is a grammar used todefine and describe data structures. XML is popularfor many reasons, among:SubscriberThe subscription server (or subscriber) contains acopy of the database or portions of the database thatare being published. This is known as store-andforward.Easy Data ExchangeIn earlier days, programs could exchange dataeasily because data was stored as text. But, today,we need conversion programs or modules to letapplications transfer data between themselves. Infact, proprietary data formats have become socomplex that frequently one version of anapplication can't even read data from an earlierversion of the same application.ArticleAn article is any grouping of data to bereplicated; it is a component of a publication. Itmay contain a set of tables or a subset of tables.Articles can also contain a set of columns (verticalfiltering), a set of rows (horizontal filtering), storedprocedures, views, indexed views, or User DefinedFunctions (UDFs).Customizing Markup LanguagesYou can create customized markup languagesusing XML, and that represents its extraordinarypower.PublicationThe Publisher server contains a collection ofarticles in the publication database. This databasetells the Publisher server which data needs to besent to other servers or to the subscribing servers.In other words, the publication database acts as thedata source for replication.Self-Describing DataThe data in XML documents is self-describing.Example: ?xml version "1.0"? greeting style "informal" from Chris Bates /from to Mr. M. Mouse /to message Hi, how are you doing? /message signature / /greeting Any database used as a source of replicationneeds to be enabled as a Publisher server. Thepublished database contains one or morepublications. A publication is a unit which containsone or more articles that are sent to the subscribingservers.Based solely on the names given to each XMLelement here, we can figure out what's going on.Each element describes itself, this means that XMLdocuments are, to a large extent, tdefinetheirsubscriptions for a particular set of publications inorder to receive the snapshot from the publisherserver.Structured and Integrated DataXML can specify the data, the structure, and howvarious elements are integrated into other elements.This is important when we are dealing withcomplex and important data. In XML, we canAgentsThey are the processes responsible for copyingand distributing data between publisher and92
International Journal of Reviews in Computing 2009 IJRIC. All rights reserved.www.ijric.orgrepresent a long bank of statement, and build in thesemantic rules that specify the structure of thedocument. So that, the document will be checkedcorrectly.A document's DTD specifies the correct syntaxof the document, DTDs can be stored in a separatefile or in the document itself, using a !DOCTYPE element. Here's an example inwhich a !DOCTYPE element is added to thegreeting XML document developed previously:1. ?xml version "1.0" encoding "UTF‐8"? 2. ?xml‐stylesheettype "text/css"href "first.css"? 3. !DOCTYPE DOCUMENT [4. !ELEMENT DOCUMENT (GREETING,MESSAGE) 5. !ELEMENT GREETING (#PCDATA) 6. !ELEMENT MESSAGE (#PCDATA) 7. ] 8. DOCUMENT 9. GREETING 10.Hello From XML11. /GREETING 12. MESSAGE 13.Welcome to the wild and woolly world ofXML.14. /MESSAGE 15 /DOCUMENT If we check the code, we will notice that thedifferent elements are nested according to theirphysical locations within the building.In fact, this emphasis on the correctness ofdocuments is strong in XML. since, browsers aresupposed to check our document; if there's aproblem, they are not supposed to proceed anyfurther.ANDVALIDE-ISSN: 2076-3336An XML document is valid if there is aDocument Type Definition (DTD) associated, andif the document complies with that DTD.Let us read the XML document: apartmentbldg apartment room type "bedroom" furniture type "armoire" belongingtype "t‐shirt"color "navy"size "xl" / belonging type "sock" color "white" / belonging type "watch" / /furniture /room /apartment /apartmentbldg 3.2 WELL-FORMEDDOCUMENTSIJRICXMLLet us examine the above document:- Line 3 declares an inline DTD calleddocument and that anything inside thesquare brackets [] comprises the inlineDTD. In this case, Document is the rootelement.- Line 4 indicates that the root elementcontains Greeting element followed byMessage element. Therefore, an XMLinstance with a Message element beforeGreeting element would not be validaccording to this DTD.- Line 5 and 6 declare that Greeting andMessage element contains only text data,we could validate this in DTD byspecifying the type PCDATA (ParsedCharacter DATA).To be well-formed, an XML document mustfollow the syntax rules set up for XML by W3C inthe XML 1.0 specification (which we can find atwww.w3.org/TR/REC-xml).Informally, well-formedness often means that thedocument must contain one or more elements. Theroot element, must contain all the other elements.Each element also must nest inside any enclosingelements properly. For example, this document isnot well-formed because the /GREETING closing tag comes after the opening MESSAGE tag for the next element: ?xml version "1.0" encoding "UTF‐8"? DOCUMENT GREETING Hello From XML MESSAGE /GREETING Welcome to the wild and woolly world of XML. /MESSAGE /DOCUMENT Most XML browsers check XML documents forwell-formedness, but, few of them check forvalidity. Here's a XML validator on the Web:W3C XML Validator, validator.w3.org/. This isthe official W3C HTML validator. Although it'sofficially for HTML, it also includes some XML93
International Journal of Reviews in Computing 2009 IJRIC. All rights reserved.www.ijric.orgIJRICE-ISSN: 2076-3336support. The XML document must be online to bechecked with this validator.The resultant XML:P.S: A valid document is always a well-formeddocument, but the reverse is not always true. rowxmlns:xsi "http://www.w3.org/2001/XMLSchema‐instance" Employee SocialSecurityNumber "123456789" FirstName Jhon /FirstName LastName Smith /LastName /Employee /row rowxmlns:xsi "http://www.w3.org/2001/XMLSchema‐instance" Employee SocialSecurityNumber "333445555" FirstName Frank /FirstName LastName Wong /LastName /Employee /row .Inserting data using OPENXML3.3 XML AND RDBMS [4], [5]This section focus on the relation between XMLand RDBMS. To simplify and clarify this relation,two cases studies using SQL Server 2005 andOracle 10g databases are illustrated.Retrieving or selecting data using XML appearsas XML documents after executing a SELECTquery. Also updating, inserting, deleting can bedone using XML.3.3.1 SQL SERVER AND XMLIn the following, two examples are given. Thefirst one is select data from an SQL server table("Employee") into an XML file. The second isinsert data into the table form an XML file.Select statement using FOR XML clauseOPENXML primarily used to insert data from anXML document into tables in the database. Beforeusing the OpenXML function to insert data, theXML document needs to be parsed and mapped toan in-memory tree structure that represents thenodes in the document. We use thesp xml preparedocument stored procedure, whichreads the document and verifies that it’s a validXML document. The stored procedure then returnsa handle to a node tree that can be used to retrievedata from the elements and attributes in thedocument.To output the result of the select statement asXML format, we use the FOR XML clause PATHmode:After the node tree has been created, you can usethe OpenXML function to return a rowsetcontaining data in the XML document. The primaryuse of this functionality is to get XML data into arelational format so that it can be inserted into atable. This process is known as shredding thedocument.SELECT Ssn AS"Employee/@SocialSecurityNumber",Fname AS "Employee/FirstName",Lname AS "Employee/LastName"FROM dbo.EMPLOYEEFOR XML PATH,ELEMENTS XSINIL;Once a document has been fully processed, youshould use the sp xml removedocument storedprocedure to reclaim the memory used by the nodetree.FOR XML PATH is designed for explicitlydefining your XML result structure. This is a saferoption than the RAW or AUTO modes inproduction code because, we always know theresult XML structure in advance, even if the tablestructure changes.Example: following, the steps to insert into thetable "Department" a new department with 2employees using openxml.The @ element in the first row is to indicate thatis considered as an attribute. The XSINIL modifiertells FOR XML to represent SQL NULLs with anxsinil "true" attribute in the resultant XML.The XML document used: ?xml version "1.0"? 94
International Journal of Reviews in ComputingIJRIC 2009 IJRIC. All rights reserved.www.ijric.orgE-ISSN: 2076-3336 FirstName MiddleName LastName BirthDate Address Sex Salary SuperSocialSecurityNumberThe following procedure inserts the xml data intothe "Department" table: Department DepartmentNumber "20"DepartmentName "Information Technology"ManagerSocialSecurityNumber "999887777"ManagerStartDate "10/20/1905" Employees EmployeeSocialSecurityNumber "765765765" FirstName Hussam /FirstName MiddleName N /MiddleName LastName Kassem /LastName BirthDate 04/20/1971 /BirthDate Address Bekaa,Lebanon,Tamnin Fawkastr /Address Sex M /Sex Salary 30000.00 /Salary CREATE PROCEDURE InsertDepartmentEmployees@xmlDepEmp VARCHAR(2000)ASDECLARE @iTree INTEGEREXEC sp xml preparedocument @iTree OUTPUT,@xmlDepEmpINSERT dbo.DEPARTMENT(Dnumber,Dname,Mgr ssn,Mgr start iTree, 'Department', 1)WITH (DepartmentNumber INTEGER,DepartmentName agerStartDate DATETIME)INSERT dbo.EMPLOYEEselect * ,1)WITH (SocialSecurityNumber CHAR(9),FirstNameVARCHAR(15) 'FirstName',MiddleName CHAR(1)'MiddleName', LastName VARCHAR(15)'LastName', BirthDate DATETIME'BirthDate',Address VARCHAR(30) 'Address', SexCHAR(1) 'Sex',Salary DECIMAL 'Salary',SuperSocialSecurityNumber r INTEGER'././@DepartmentNumber')EXEC sp xml removedocument @iTree; SuperSocialSecurityNumber 999887777 /SuperSocialSecurityNumber /Employee EmployeeSocialSecurityNumber "222111333" FirstName Seifedine /FirstName MiddleName N /MiddleName LastName Kadry /LastName BirthDate 07/15/1977 /BirthDate Address Bekaa,Lebanon,Rafidstr /Address Sex M /Sex Salary 40000.00 /Salary SuperSocialSecurityNumber 999887777 /SuperSocialSecurityNumber /Employee /Employees /Department The tree structure of the above XML document is:/(root) agerSocialSecurityNumber,@ ManagerStartDate) EmployeesoEmployee(@SocialSecurityNumber) FirstName MiddleName LastName BirthDate Address Sex Salary Number)3.3.2 ORACLE AND XML [6], [7]As we discussed in the previous section therelation between SQL Server and XML, we discussin this section the relation between Oracle andXML. Oracle offers more support for XML thanSQL Server 2005.The Oracle XML Database (XML DB) refers tothe collection of XML technologies built into the95
International Journal of Reviews in ComputingIJRIC 2009 IJRIC. All rights reserved.www.ijric.orgE-ISSN: 2076-3336Oracle 10g database, providing high-performanceand native storage, retrieval, and processing ofXML. ADDRESS 980 dallas,houston,tx /ADDRESS /Employee Select data from Oracle table into XML fileInserting data to department table from XML fileInserting into "cv2.department" table: DATEtype inserted asDAY/ MONTH /YEAR.unstructured inserting method using CLOB.The below query selects data from the tableemployee and outputs the result in XML format:DECLAREinsCtx DBMS XMLSTORE.ctxType;rows NUMBER;xmlDoc CLOB : ' ROWSET ROW num "1" DNAME IT /DNAME DNUMBER 20 /DNUMBER MGR SSN 123456789 /MGR SSN SELECT XMLELEMENT("Employee",XMLATTRIBUTES ( e.fname ' ' e.lname AS"name" ),XMLFOREST( e.Bdate as "BirthDate", e.Address))AS "result"FROM cv2.employee e;Where:- xmlelement() function creates the rootelement. "Employee".- xmlattibutes() may be used only as anargument of xmlelement().- xmlforest() produces a forest of XMLelements from the given list of arguments. MGR START DATE 23/12/2000 /MGR START DATE /ROW ROW DNAME communication /DNAME DNUMBER 30 /DNUMBER MGR SSN 123456789 /MGR SSN Following the XML output of the previous query: Employee name "Jhon Smith" BirthDate 09/01/65 /BirthDate ADDRESS 731 fondron,houston,tx /ADDRESS /Employee Employee name "Frank Wong" BirthDate 08/12/55 /BirthDate ADDRESS 638 voss,houston,tx /ADDRESS /Employee Employee name "James Borg" BirthDate 10/11/37 /BirthDate ADDRESS 450 stone,houston,tx /ADDRESS /Employee Employee name "Alicia Zelaya" BirthDate 19/01/68 /BirthDate ADDRESS 3321,castle,spring,tx /ADDRESS /Employee Employee name "Jenifer Walace" BirthDate 20/06/41 /BirthDate ADDRESS 291 berry,bellaire,tx /ADDRESS /Employee Employee name "Ramesh Narayan" BirthDate 15/09/62 /BirthDate ADDRESS 975 fireoak,humble,tx /ADDRESS /Employee Employee name "Joyce English" BirthDate 31/07/72 /BirthDate ADDRESS 5631 rice,houston,tx /ADDRESS /Employee Employee name "Ahmad Jabbar" BirthDate 29/03/69 /BirthDate MGR START DATE 20/11/1995 /MGR START DATE /ROW /ROWSET ';BEGINinsCtx : DBMS XMLSTORE.newContext('cv2.Department');‐‐ Get saved contextDBMS XMLSTORE.clearUpdateColumnList(insCtx);‐‐ Clear the update settings‐‐ Set the columns to be updated as a list of valuesDBMS XMLSTORE.setUpdateColumn(insCtx,'DNAME');DBMS XMLSTORE.setUpdateColumn(insCtx,'DNUMBER');DBMS XMLSTORE.setUpdateColumn(insCtx,'MGR SSN');DBMS XMLSTORE.setUpdateColumn(insCtx,'MGR START DATE');‐‐ Insert the doc.rows : DBMS XMLSTORE.insertXML(insCtx,xmlDoc);DBMS OUTPUT.put line(rows ' rows inserted.');‐‐ Close the contextDBMS XMLSTORE.closeContext(insCtx);END;96
International Journal of Reviews in ComputingIJRIC 2009 IJRIC. All rights reserved.www.ijric.orgE-ISSN: 2076-3336Each database server has access to its offlineXML files from a specific shared replicationfolders, in other words, if we replicate data amongthree databases, each database has two sharedfolders, one folder holding XML files sent from adatabase server, another folder holding XML filessent from the other database server (cf. figure 3).4. THE PROPOSED DESIGNIn the section, the restate and the illustration ofthe problem are given. In addition, the completedproposed design is drawn.4.1 PROBLEMATICToday, most of the business companies havemore than one software to manage the wholebusiness process. For example, we find in the Bankone software for the core business (accounts,transactions, balance, ), one software for theHuman Resources department (employees records,salaries, bonus, ), and another software for thePrivate Banking department (Bonds, treasury, ) etc.We always need to generate a unified reportbetween these different systems which use differentdatabase management systems (DB2, Oracle,Sybase, SQL Server ). The replication is one ofthe major solutions to generate this report and avoidthe import/export error in exchange data betweendifferent systems. We do often face problems in thereplication scenario due to the heterogeneous (i.e.,internally different) database systems structures.4.2 SUGGESTED DESIGNOur proposed design to solve the replicationproblem is based on the XML technologies. As wementioned previously, the XML is the mostappropriate language for data exchange.Furthermore, most of the databases today supportfor XML, which means we can create an XML fileor read from XML file easily. Therefore, our newidea is to use the XML as middleware betweendifferent databases, and .NET as interface.Figure 3: Offline ReplicationIn figure 3, the folders of SQL Server offlineXML files, hold the files sent by Oracle(OracleToSQL), and DB2 (DB2ToSQL).Each replication folder contains:Offline Row replication files (XML files) forinserting, deleting, updating, are saved in the sharedreplication folder according to the following namesconvention:The XML solution for replicating data between,for example, SQL Server and Oracle, is achieved ifit provides:The replication type (offline), the database serversending the file, the kind of operation (insert,delete, update) followed by the table name, and aserial number of nine digits (generatedsystematically when the data is serialized as XML)1- Solution for Offline Backup Replication.2- Solution for Online Backup Replication.3- Data Consistency, Concurrency, RecoverySolution.Example for inserts:Off Ora Ins employee220320090.xml,Off Ora Ins employee220320091.xml, 4.2.1 SOLUTION FOR OFFLINE BACKUPREPLICATIONOff: means offline replication, Ora: sent fromOracle, Ins: insert file number 220320090 inemployee table.A row or an entire table, saved as XML file, itwill be replicated asynchronously as a backupoperation to a database server. The offline rowreplication will assist to check the data consistency.The number 220320090 is read as follows: thefirst eight digits 22032009 represents a date casted97
International Journal of Reviews in ComputingIJRIC 2009 IJRIC. All rights reserved.www.ijric.orgto a long integer number, the last digit represents afile serial number.For each new row added, deleted, or updated, therow is triggered as an XML document from'Inserted' in sender instance server, saved in ashared repository folder, and uploaded tocorrespondent table in receiver instance server.Example for updates:Off Ora Upd employee220320090.xml,Off Ora Upd employee220320091.xml, .Our design concerns data replication betweendifferent databases systems, where the databasemanagement is managed in each database systemseparately from the other ones (which is known bythe autonomous database management).These files are saved daily for backups.Offline replication file(replicated once per day)perdatabaseE-ISSN: 2076-3336table:Note that the online row replication takespriority over offline.There are some requirements:Ex:Off Ora employee22032009.xml,Off Ora departemnt22032009.xml, . 4.2.2 SOLUTION FOR ONLINE BACKUPREPLICATION In online backup replication, a row is triggeredfor each DML (insert, update, delete) query, asXML document in a repository folder, and aprocess (JOB) from other server side check thechanges to upload the data synchronously. For any DML task (insert, delete, update) in anydatabase, a trigger is fired to allow an XMLdocument containing the DML task to be saved inthe shared repository folder. A process running oneach database servers, tracks the changes, and theXML data is uploaded to the databases (cf. figure4). The local operations (queries process,transactionmanagement,systemadministration ) of the DBMSs must be notaffectedbytheirparticipationinmultidatabase replication process.The system consistency or operation shouldnot be compromised when DBMS involvedin data replication task.The DBMSs are free to use the data modelsand transaction management techniqueswithout interfering with each other (designautonomy).Each of the DBMSs is free to make its owndecision as to what type of information itwants to provide to the other DBMSs(communication autonomy).The level of communication among DBMSsystems in our design is the process of datareplication as XML documents.For a replication task between SQL Server andOracle, the data consistency is maintainedprogrammatically as follows:a- From SQL Server to OracleFrom SQL Server database, an XML documentcontaining data that will be replicated to Oracledatabase, is saved in a shared repository folder(MSSQLServer ToOracle) accessed by Oracledatabase. (SQL Server: Write, Oracle: Read andreplicate), (cf. figure 5). This folder contains XMLdocuments, and in each table in the database, thereare:Three XML documents for online replication,named by current date convention. As an example,the 'cv2.employee' table in 'cv2.schema' in Oraclehas: Figure 4: Online Replication98Document for online insert replication withits corresponding lock file.
International Journal of Reviews in Computing 2009 IJRIC. All rights reserved.www.ijric.orgIJRICE-ISSN: 2076-3336contains XML documents, and in each table in thedatabase, there are:(ex: file name: On SQL Ins employee-22-052009.xml)&(SQLOraLockIns.txt). Document for online update replication withits corresponding lock file.(ex: file name: On SQL Upd employee-22-052009.xml)&(SQLOraLockUpd.txt). Document for online delete replication withits corresponding lock file.(ex: file name: On SQL Del employee-22-052009.xml)&(SQLOraLockDel.txt).Three XML documents for online replication,and one for offline, named by current dateconvention. As an example, the 'employee' table in'employeedb' in SQL Server database has: Document for online insert replication withits corresponding lock file.(ex: file name: On Ora Ins employee-22-052009.xml)&(OraSQLLockIns.txt). Document for online update replication withits corresponding lock file.(ex: file name: On Ora Upd employee-22-052009.xml)&(OraSQLLockUpd.txt). Document for online delete replication withits corresponding lock file.(ex: file name: On Ora Del employee-22-052009.xml)&(OraSQLLockDel.txt).On: Online, SQL: sent from SQL Server, Ins:insert, Upd: update, Del: delete.The Online (insert, update, delete) replications,which is synchronous, in Oracle are done for eachdata row manipulated in the original SQL Serverdatabase tables.For each online operation, the new online dataare inserted into the empty XML file (old data inthe XML file are deleted by Oracle job sequencetasks).Online (insert, update, delete) replications, whichis synchronous, in SQL Server are done for eachdata row manipulation in the original Oracledatabase tables.The XML documents names in the shared folderare changed daily according to the current dateconvention.For each online operation, the new online dataare inserted into the empty XML file (old data inthe XML file are deleted by SQL Server jobsequence tasks).The XML documents names in the shared folderare changed daily, and named according to thecurrent date convention.Figure 5: Shared XMLs Folder (SQL Server toOracle)b- From Oracle to MS SQL ServerFrom Oracle database, an XML documentcontaining data will be replicated to SQL Serverdatabase, is saved in a shared repository folder(OracleToMSSQL Server) accessed by the SQLServer database. (Oracle: Write, SQL Server:Read and replicate), (cf. figure 6). This folderFigure 6: Shared XMLs Folder ( Oracle to SQLServer)99
International Journal of Reviews in Computing 2009 IJRIC. All rights reserved.www.ijric.org4.2.3 LOGICAL FLOWREPLICATION TASKOFONLINEa- SQL Server - XML Logic FlowThe Common Language Run
Since, the replication of data between different DBMS is not always possible. We propose in this paper, a new design to solve the replication problem between heterogeneous DBMS systems using XML technology. Keywords: Database Management Systems (DBMS), Replication, eXtensible Markup Language (XML), Structured Query Language (SQL) Server, Oracle. 1.
