Introduction to Heterogeneous Data Replication Spring COMMON 1999

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

Agenda What is and Why Replication ? n Introduction to IBM Data Propagator Relational (DProp. R, DPR) n – DB 2 to DB 2 family including DB 2/400 n Introduction to Data. Joiner – – – n DB 2 to non-DB 2 to DB 2 non-DB 2 to non-DB 2 Putting Them Together

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

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

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

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

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

Admin Tool: DJRA

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

Registration Registered Source Tables Capture At the Source Server

Registration Using DRJA

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

Subscription Target Tables Registered Source Tables Apply Subscription Definitions

Subscription Using DJRA

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

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

Journal/Log based Capture n Capture reads the DB 2/400 Journal – Non-AS/400 DB 2 will be reading Log n Inserts rows into the change data table – One of the staging tables n n n 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

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

Capture and Apply

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

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

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

Agenda What is and Why Replication ? n Introduction to IBM Data Propagator Relational (DProp. R, DPR) n – DB 2 to DB 2 family including DB 2/400

What if I have non-DB 2 data? What Happens to my: n Oracle Database n Microsoft SQL Server n Sybase SQL Server n Informix Database n And all the applications that built with them ?

Introduction to Data. Joiner n n n n 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

Data. Joiner Overview Setup n n n 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 n On NT with DJ: – – – n n 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

Data. Joiner Overview DB Protocol Client System Data. Joiner NT Server IPCS Non-DB 2 (e. g. Oracle)

Total Access with Data. Joiner

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

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

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

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

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

Heterogeneous Replication

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

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

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

Reference Information n n http: //www. software. ibm. com/data/dpropr http: //www. software. ibm. com/datajoiner http: //www. as 400. ibm. com http: //as 400 bks. rochester. ibm. com/

Trademark Information n AIX, AS/400, Data. Hub, Data. Joiner, Data. Propagator, Data. Refresher, DB 2, 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. n Microsoft, Windows, and the Windows 95 and the Windows NT logos are trademarks or registered trademarks of Microsoft Corporation. n UNIX is a registered trademark in the United States and other countries licensed exclusively through X/Open Company Limited. n Lotus Notes is a trademark of the Lotus Development Corporation. n Other company, product, and service names may be trademarks or service marks of others.