Introduction to Heterogeneous Data Replication

1. Introduction to Heterogeneous Data Replication Spring COMMON 1999 Richard Sinn IBM Santa Teresa Lab

2. Agenda • What is and Why Replication ? • Introduction to IBM Data Propagator Relational (DPropR, DPR) • DB2 to DB2 family including DB2/400 • Introduction to DataJoiner • DB2 to non-DB2 • non-DB2 to DB2 • non-DB2 to non-DB2 • Putting Them Together

3. What is Replication ? • Moving Data from one place to another • Replication vs Copying • From Source to Target • Maintain “changes” automatically

4. Data is everywhere Use Information Intelligently and improve decision making Stay competitive with both Data access and Data movement Speedup application deployment Reengineer business processes Increase online throughput Improve system availability Support audit requirements Support Data Warehousing Why Replication ?

5. IBM Data Propagator Solution • Multiple platforms (AS/400, NT, AIX, etc) • Supported by IBM • Simple Administration • Proven Technology • Speed Implementation with IBM services organization • Built on standard SQL

6. DPropR Components Capture Admin Tools Source Server Windows NT, Windows 95, OS/2 Platform Web Browser Source Server Target Server Control Server (Flexible location) Apply

7. Replication Admin Tools • Define replication sources • Also called “Registration” • This is what Capture works with logically • Defines subscriptions • This is what Apply works with logically • Create replication control tables • AS/400 DPR install program will create all the necessary replication control tables

8. Admin Tool: DJRA

9. Define Registration • “Register” a source table as a replication source • Process data using vertical fragmentation • Replicate only selected columns • Select before and after image of a column

10. Registration Registered Source Tables Capture At the Source Server

11. Registration Using DRJA

12. Define Subscription • “Subscribe” to registered table • Defines source and target relationship • For example, many target tables can subscribe to one source table • Horizontal fragmentation • Columns can be further sub-selected

13. Subscription Target Tables Registered Source Tables Apply Subscription Definitions

14. Subscription Using DJRA

15. Replication Flow • Source data changes are “captured” by Capture component according to “registration” • “Change Data” are then put into Staging Tables • According to “subscription”, Apply component “apply” changes to target(s) using the data in Staging Tables

16. The Capture Component Staging Tables Register Table Unit of Work Table Registered Source Tables Journals,Logs Capture At the Source Server

17. Journal/Log based Capture • Capture reads the DB2/400 Journal • Non-AS/400 DB2 will be reading Log • Inserts rows into the change data table • One of the staging tables • Time stamps or relative byte address is used as a log sequence number • Prunes the change data table • Communicates with Apply via ASN/IBMSNAP_PRUNCNTL table

18. The Apply Component Target Tables Register Table Apply Staging Tables Unit of Work Table Subscription Definitions

19. Capture and Apply

20. Subscription Set Time based Replication Event based Replication Multiple Target Types Blocking Factor Update Anywhere Programming Capability SQL Statement / Call Procedure ASNDONE ASNLOAD Support Transaction Consistency DPropR Functions

21. Different Target Types • User copy tables • Represent source data at a particular point in time • Point-in-time tables • Represent source data at a particular point in time plus some overhead columns • Replica tables • Updates to a Replica table are automatically applied to the original source table of the Replica, Auto conflicts detection

22. Consistent Change Data • Consistent Change Data (CCD) • Maintain complete histories of data changes (Audit) • Condense “hot spot” updates before transmitting data • “Loading” dock • Used by NotesPump or other applications

23. Agenda • What is and Why Replication ? • Introduction to IBM Data Propagator Relational (DPropR, DPR) • DB2 to DB2 family including DB2/400

24. What if I have non-DB2 data? What Happens to my: • Oracle Database • Microsoft SQL Server • Sybase SQL Server • Informix Database • And all the applications that built with them ?

25. Introduction to DataJoiner • Transparent Access to Heterogeneous Data Sources • Transparent DDL Support • Heterogeneous Database Joins • Global Optimization • Two-Phase Commit Support • Integrated Data Replication • Spatial Data Management/Access

26. Setup NT Server (on IPCS) Install DJ Install other DB’s protocols SQL*Net for Oracle Informix-net for Informix ODBC drivers or Sybase open client for MS SQL Server or Sybase SQL Server Basic Data Access On NT with DJ: Create database Connect to DB Create Server Mapping (Create “Nickname”) Setup client to go to NT with DJ All access will “map” to “real” target DB DataJoiner Overview

27. DataJoiner Overview DB Protocol Client System DataJoiner NT Server IPCS Non-DB2 (e.g. Oracle)

28. Total Access with DataJoiner

29. DB2/Non-DB2 Replication DataJoiner on NT IPCS Apply Running AS/400 with DPR/400 (Capture/400 Running; Could be any DB2 family) Source Server Sybase, Informix, Oracle Microsoft SQLServer, DB2 Family Admin (DJRA) Target Server

30. Non-DB2/Non-DB2 Replication • Capture Triggers • Using CCD • Sybase, Microsoft • Oracle, Informix • SQL Anywhere DataJoiner on NT IPCS Apply Running Source Server Sybase, Informix, Oracle Microsoft SQLServer, DB2 Family Target Server Admin (DJRA)

31. Trigger Based Capture I • Trigger: Wake up and do actions based on event (such as insert, delete, update) • Insert, Update, and Delete triggers created on the source table • Committed changes inserted into the CCD table • Capture both before and after image of the column

32. Trigger Based Capture II • Prunes CCD tables via trigger on IBMSNAP_PRUNCNTL table • Clean up CCD • Update Synchpoint via Trigger on IBMSNAP_REG_SYNCH Table • Communication with Capture

33. Trigger Based Capture III • Log sequence numbers generated dependent on OEM’s • Pre-existing Triggers • Run-time error • Check by DJRA and inform users • Depends on OEM DB

34. Heterogeneous Replication

35. Heterogeneous Replication • Target table created in data source • Nickname for target table created in DJ Database • DJRA tolerates the pre-existing target table • DataJoiner does the data type mapping • Read only copies (one direction with User Copy, PIT) • No Support for Replica yet

36. Replication DPR on AS/400 and other platforms Integrated support with DPNR, Data Refresher, NotesPump Expert Info and Support from IBM Proven Technology Heterogeneous Data Access DataJoiner on IPCS Server consolidation advantages with IPCS Putting Them Together

37. Putting Them Together • Data from Any to Any • Replicate “Changes” • Time & Event based replication • Update Anywhere • Data Transformation for Datawarehouse • Build Powerful Applications

38. Take Full Control Of Your Data With Data Propagator and DataJoiner

39. Thanks Much For Coming !!!

40. Reference Information • http://www.software.ibm.com/data/dpropr • http://www.software.ibm.com/data/datajoiner • http://www.as400.ibm.com • http://as400bks.rochester.ibm.com/

41. Trademark Information • AIX, AS/400, DataHub, DataJoiner, DataPropagator, DataRefresher, DB2, DProp, DRDA, IBM, IMS, MVS, OS/2, OS/400, RS/6000, VM/ESA and VSE/ESA are trademarks or registered trademarks of the IBM Corpopration in the United States or other countries or both. • Microsoft, Windows, and the Windows 95 and the Windows NT logos are trademarks or registered trademarks of Microsoft Corporation. • UNIX is a registered trademark in the United States and other countries licensed exclusively through X/Open Company Limited. • Lotus Notes is a trademark of the Lotus Development Corporation. • Other company, product, and service names may be trademarks or service marks of others.