IBM Software Group An Introduction to Web Sphere
® IBM Software Group An Introduction to Web. Sphere Information Integrator Q Replication Tridex June 16, 2005 Beth Hamel DB 2 Replication Product Architect hameleb@us. ibm. com © 2004 IBM Corporation
IBM Software Group Agenda Topics § The Basics of MQ Based Replication § Publishing DB 2 data to MQ in an XML format § Application Examples of Replication and Publishing
IBM Software Group SQL Replication Architecture Admin DB 2 IM S DB Log based 2 Staging CD tables CD 1 Sybas e Oracl e Control SQL Serv er Inform ix Capture Control CD 1 CD Apply Federation Engine SQL Serv er Inform ix Teradat a Trigger based Any source External application Sybas e Oracl e Nicknames § Flexible scheduling, transformation, distribution § Typically used for business intelligence, distribution and consolidation, application integration
IBM Software Group Why Create Another Replication Architecture? § Performance: Combine high throughput with low latency § Capability: Significantly improve multi-directional replication support § New function: Event publishing, table difference utility § Manageability: Reduce the number of replication objects to be defined and managed, ease the definition process with new Replication Center wizards
IBM Software Group Q Replication Architecture Admin Utilities Control Apply Capture Sourc e Federation Engine Log based Web. Sphere MQ § § § Each message represents a transaction Highly parallel apply process Differentiated conflict detection and resolution Integrated infrastructure for replication and publishing Staged availability of heterogeneous support Target
IBM Software Group Q Replication – Q Subscription Process SOURCE METADATA SOURCE 2 Q Apply Browse r SOURCE 1 METADATA DB 2 Log Q Captur e Apply Agent ADMINISTRATION TGT 3 TGT 1 TGT 2 Replication Center Replication Monitor TARGET
IBM Software Group Q Replication High Volume with Low Latency Performance
IBM Software Group Subscription Types § Unidirectional 4 Changes are replicated in one direction between two servers (i. e. from source to target) 4 Changes can be filtered and transformed § Bidirectional 4 Changes are replicated in two directions between two servers 4 Utilizes VALUE based conflict detection § Peer to peer 4 Changes are replicated between 2 or more servers 4 Utilizes VERSION based conflict detection
IBM Software Group Q Replication Multidirectional Configurations § Peer-to-peer – No master copy – Guaranteed Convergence – Version based conflict resolution – Requires extra columns and triggers to provide versioning of rows – N nodes: N * (N-1) subscriptions § Bi-directional – One node prevails in case of conflicts – Value based conflict resolution Primary Secondary/backup – Uses old and new value data comparisons – 2 nodes only
IBM Software Group Q Replication – Defining Subsets or Filters § Subset data 4 Subset of rows through Q Capture predicate on subscription/publication 4 Subset of columns through subscription/publication definition 4 Option included for ignoring deletes 4 Signal defined to allow user selected transactions to be ignored § Predicate examples 4 Based on values in the row data itself • WHERE : LOCATION ='EAST' AND : SALES > 100000 4 Based on values in other data • WHERE : LOCATION ='EAST' AND : SALES > (SELECT SUM(expense) FROM STORES WHERE stores. deptno = : DEPTNO)
IBM Software Group Q Replication - Transformations § Transformations achieved through: 4 Triggers on the target table 4 Publish event to User Application 4 Stored Procedures called by Apply at the row level Apply calls Stored Procedure , mapping columns to input parms COL 1 COL 2 COL 3 COL 4 In. Parm 1 In. Parm 2 In. Parm 3 In. Parm 4 Update Target table X where trg 1 = “a”; Stored Procedure performs logic and makes insert/update/delete TRG 1 TRG 2 TRG 3 TRG 4
IBM Software Group Apply Load Options § A subscription is defined as either: automatic load, manual load, no load required § Automatic load: 4 Load is performed by Apply, with automatic coordination of the simultaneous capture of changes, loading of the new table, and apply of changes to other tables. § Manual load: 4 Load is performed by user, coordination is required, and will be handled by user (with some help from our administration). § No load: 4 No loading required, no coordination required, can immediately capture and apply changes 4 Example: target system is built through backup/restore, with replication started from an inactive source
IBM Software Group Replication Administration § Replication Center GUI 4 Launchpads, Wizards, Online Help 4 Definitions, Operations, Monitoring § Command Line Interface 4 Scripts or interactive mode 4 Example: C: asnclp REPL > CREATE QSUB USING REPLQMAP. . . REPL > CREATE SUBSCRIPTION SETNAME. . . REPL > CREATE MEMBER IN SETNAME. . . § Java API’s 4 Typically used when replication is embedded
IBM Software Group Q Create Subscription Wizard Create large numbers of subscriptions at a time!!
IBM Software Group Table Reconciliation Utilities § ASNTDIFF 4 Utility that compares a subscription’s source table (S) with its target table (T) • Generates a table of differences between the two o Rows in S but not in T o Rows in T but not in S o Rows in T and S, but with different values • Checksum used to compare contents of entire row • Very similar concept to file compares such as UNIX diff command • Differences can be used to change source, target, or both § ASNTREP 4 Utility that uses the table built by the tdiff utility and issues SQL to make table (T) match table (S) S o n l y T Intersection of S and T o n l y
IBM Software Group New in 2005 – MQ Client Support SOURCE MQ SERVER METADATA SOURCE 2 SOURCE 1 METADATA DB 2 Log RECV QUEUE Q Captur e MQ CLIENT Q Apply Browse r MQ CLIENT Apply Agent SEND QUEUE METADATA New – MQ Server not required on source or target Apply Agent TGT 3 § Distributed platforms only § Allows separation of Database servers and MQ servers § Allows replication support on platforms which currently lack MQ Server support TGT 1 TGT 2 TARGET
IBM Software Group New in 2005 – Federated Targets WEBSPHERE INFORMATION INTEGRATOR SOURCE 2 METADATA SOURCE 1 METADATA DB 2 Log RECV QUEUE Q Apply Browse r METADATA Apply Agent Q Captur e Apply Agent SEND QUEUE Apply Agent § Uses Web. Sphere II Federation § Provides high speed parallel apply of data § New targets: 4 Oracle, Sybase (fp 9) TGT 3 METADATA TGT 1 TGT 2 4 MS SQL Server, and Informix(fp 10) FEDERATED TARGET
IBM Software Group Why Publish Data? § Application to Application Messaging 4 Drive downstream applications or APIs based on the transactional changed data of database events 4 Reduce application development and maintenance, performance impact to source applications, and availability impact to source applications § Meet Auditing Requirements 4 Capture and store information regarding what changes were made to critical business data and by whom § Event Notification 4 Stream changed data information to Web interfaces 4 Stream only particular events of interest (filter data) § Warehouse / Business Intelligence 4 Integrate captured changed data with an ETL tool 4 Perform very complex transformations 4 Use a specific transaction format to update target
IBM Software Group Q Replication – Event Publication Process SOURCE 2 SOURCE 1 User Application METADATA DB 2 Log Q Captur e WBI Event Broker User Application ADMINISTRATION DB 2 MQ Listener Replication Center Replication Monitor TARGET User Stored Procedure
IBM Software Group Event Publishing - Publication Options § Format 4 Only data from committed transactions is published 4 Data is UTF-8, self describing with XML tags 4 Row based = one row per message 4 Transaction based = one transaction per message § Row Content 4 Subset by column 4 Subset by predicate 4 Changed column values only or all column values 4 New data values only or include old values 4 Row sent on any change or only on published column changes 4 Suppress deletes
IBM Software Group Information Integrator Event Publishing for Classic Sources § Capture data changes for classic sources using log data where available § Correlate by transactions WS Information Integrator Event Publishers for z/OS within a single database § Publish onto message queue in XML format DB 2 UDB for z/OS VSAM IMS IDMS Extending the value proposition of the MQ based replication and publishing architecture
IBM Software Group Replication and Event Publishing Products: z/OS DB 2 Data. Propagator for z/OS 4 DB 2 UDB sources and targets (DB 2 for z/OS V 7 and V 8) 4 SQL Replication only Web. Sphere Information Integrator Replication for z/OS 4 DB 2 UDB sources and targets (DB 2 for z/OS V 7 and V 8) DB 2 Data. Propagator 4 Includes SQL Replication, Q Replication, and DB 2 Event Publisher Web. Sphere Information Integrator Event Publisher for z/OS 4 Event publishing to message queues 4 Available for DB 2, IMS, or VSAM New!!!! WS II V 8. 2
IBM Software Group Replication and Event Publishing Products: Distributed Platforms DB 2 LUW DB 2 Data. Propagator Web. Sphere Information Integrator Replication Edition DB 2 Data. Propagator Web. Sphere Information Integrator Event Publisher Edition 4 DB 2 LUW and Informix IDS sources and targets 4 SQL Replication only 4 Includes SQL Replication, Q Replication, and DB 2 Event Publisher 4 DB 2 LUW sources and targets ( Q Replication) – note that Websphere MQ is bundled with this product 4 Multi-vendor sources and targets (SQL Replication) 4 DB 2 LUW sources – note that Websphere MQ is bundled with this product New!!!! WS II V 8. 2
IBM Software Group Combining SQL and Q Replication with Event Publishing Staging Tables Log Reader 1 CD 2 Capture Schema „CAP 1“ DB 2 Log CD 1 TARGET 1 SQL Apply Q Sub Log Reader 2 SOURCE 3 SOURCE 2 SOURCE 1 TARGET 2 Q Apply Q Capture Schema „CAP 2“ Event Pub User Application SQL Replication and Q Replication can co-exist Managed at source by using multiple capture schemas One Q Capture can handle both Publications and Subscriptions
IBM Software Group Continuous Availability using Q Replication Read Only Applications Q Apply Q Capture Q Apply DSNA Database Primary DSNB Secondary Database Primary Connection Available for Failover Read/Write Applications § Q Replication provides a solution for continuous availability where the active secondary system is also available for other applications
IBM Software Group Why Use Q Replication for Continuous Availability? § Advantages 4 Allows the fastest switchover with transactionally consistent data 4 Excellent solution for scheduled outage • • Allows flexibility of OS level, DB level, application level, data format Can be easily tested and monitored 4 Allows for database read or write activity on secondary • • secondary site may be used for other applications is the only solution for geographically dispersed updateable databases 4 Can supplement other HA solutions 4 Allows for lower cost hardware or platform 4 Low impact on source applications § Disadvantages 4 Asynchronous • Some data is left behind in a failure scenario 4 Application awareness is required (triggers, generated always columns)
IBM Software Group High Availability Disaster Recovery for DB 2 LUW Log data is copied synchronously or asynchronously §db §HADR §Agent §database § Agent §Bufferpool(s) §Database Storage §Log Buffer §Recovery Log §db §HADR §Agent §Copied data is continuously §applied using forward §recovery §HADR Buffer §Bufferpool(s) §Recovery Log §Production Database §Database Storage §Standby Database Offers a complete solution for high availability –easy to implement, replicates the complete database Will not initially support reads at secondary, partitioned tables
IBM Software Group High Availability - Q Replication compared with HADR § DB 2 II Q based Replication 4 Sync, async, near-sync 4 Near real time async 4 whole DB 2 database 4 selected tables/columns 4 DDL, DML 4 DML only ** 4 very simple to set up and manage 4 more complex to set up and manage 4 similar configurations only 4 sites can be very different 4 no support for unlogged LOBs 4 can support unlogged LOBs 41 read/write site only ** 4 multiple read and/or update sites 4 No DPF 4 DPF ok ** Current restriction only
IBM Software Group Peer to Peer Q Replication Read/Write Applications Q Apply Q Capture Primary Database Q Capture Q Apply Secondary Database § Replication processes and subscriptions are defined in both directions and data changes flow in both directions § Recursion is stopped by Capture, which reads special logged events created by Apply § Conflict detection is typically necessary, unless the application is carefully designed to completely avoid conflicts
IBM Software Group Peer to Peer Q Replication – Best Practices § Workload balancing 4 Provides best results with high ratio of reads to writes § Conflicts 4 Plan carefully – avoidance is the best policy 4 May occur with failovers and switchbacks 4 Consider the application impact: for database convergence, single row updates are backed out, not whole transactions § Exceptions table 4 Understand the exceptions table – all conflicts are logged there 4 Consider a global view or replicated consolidation of exceptions tables 4 Consider a trigger on the exceptions table for additional actions that need to be performed § Application considerations 4 Make a plan to handle serialized objects such as sequences and identity columns 4 Consider impacts to triggers and triggered actions
IBM Software Group Online Trading – A case for very high speed replication Trade History Applications Trading Applications Trade Processing Data Q Capture Trade History Data Q Apply § In many online environments OLTP data is kept separately from query/history data for better performance of both update and query applications § This user has just made an online trade – he will keep hitting enter until he sees that the trade is complete, in this case meaning it has been replicated to the trade history database
IBM Software Group Order Processing – Exploiting II Event Publishing Create Billing Request Q Capture Create New Order Entry Data WBI Event Broker Create Shipping Reques § As new orders are entered into the order entry system, the pertinent data is captured and published into a queue § The Websphere Business Integrator Event Broker processes the queued data § A billing transaction is created and queued in one system and a shipping transaction is created and queued in another system
IBM Software Group Customer Profile Management – Exploiting II Event Publishing Change Customer Info Customer Data II IMS Event Publisher Create Customer Change Request WBI Event Broker § When a change to customer profile information occurs in one system, the pertinent data is captured and published into a queue § The Websphere Business Integrator Event Broker processes the queued data § Transactions are created to update the customer profile information in all other database systems, as applicable Create Customer Change Request
IBM Software Group Other Important Sources of Information/Education § Web. Sphere Information Integrator sites on the web: 4 http: //www-306. ibm. com/software/data/integration/ 4 http: //www-306. ibm. com/software/data/db 2 imstools/ 4 http: //db 2 ii 2. dfw. ibm. com/wps/portal/!ut/p/!ut/p/. scr/Login 4 http: //www-1. ibm. com/support/docview. wss? uid=swg 27005663 § Developer Works: 4 http: //www-106. ibm. com/developerworks/db 2/zones/db 2 ii/ 4 Tutorials, whitepapers, samples available now § IBM Education for Q Replication: 4 DW 240: 3 day course without MQ basics 4 DW 241: 4 day course with MQ basics included § Redbook recently published for Q Replication, EP later this year § Consider IBM Services as part of your implementation plan
- Slides: 34