710 likes | 1.17k Views
DB2 Information Integrator 8.2 Q Replication and Event Publishing - the elevator tale …. Presentation given by: Pav Kumar-Chatterjee, IBM Product Introduction Center, Hursley (IDUG – 2 December 2004). Welcome!. DB2 Information Integrator 8.2: Q Replication - Overview. CD. CD. CD. CD1.
E N D
DB2 Information Integrator 8.2 Q Replication and Event Publishing- the elevator tale … Presentation given by: Pav Kumar-Chatterjee, IBM Product Introduction Center, Hursley (IDUG – 2 December 2004) Welcome!
CD CD CD CD1 CD1 CD1 IMS DB2 DB2 Sybase Sybase Oracle Oracle SQL Server SQL Server Informix Informix Control Control Teradata Capture Admin Apply Nicknames Current Architecture for SQL Replication Log based Staging tables Federation Engine Trigger based • DB2 Data Propagator • DB2 II Replication Edition • IMS Data Propagator
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 .
Source Target Control Control Substantially modified New New and modified Utilities Admin Apply Q Replication Components Capture Federation Engine Log based WebSphere MQ
DB2 Information Integrator Event Publishers for z/OS Capture, Externalize (XML) and Deliver to MQ DB2 UDB for z/OS VSAM IMS What is DB2 II Event Publishing? • Capture changed-data from DB2, IMS and CICS/VSAM • Correlate by transactions within single database • Extract to consistent and documented XML format • “Publish” to WebSphere MQ queue • Received & Processed by any MQ “listener” • Pick up messages • Take action • Two Event Publisher infrastructures: • DB2 • IMS and CICS/VSAM
Source Control Substantially modified New New and modified Utilities Admin Event Publishing Components XML Capture Federation Engine Log based WebSphere MQ
Event Publishing - Publication Options • Format • Only data from committed transactions is published • Data is self describing with XML tags • Row based = one row per message • Transaction based = one transaction per message • Row Content • Subset by column • Subset by predicate • Changed column values only or all column values • New data values only or include old values
DB2 Information Integrator 8.2: Replication and Event Publishing Products
DB2 DataPropagator for z/OS Replication and Event Publishing Products: z/OS • DB2 UDB sources and targets (DB2 for z/OS V7 and V8) • SQL architecture DB2 Information Integrator Replication for z/OS • DB2 UDB sources and targets (DB2 for z/OS V7 and V8) • SQL and Queue architectures (includes DB2 Event Publisher) DB2 DataPropagator DB2 Information Integrator Event Publisher for z/OS • Event publishing to message queues • Available for DB2, IMS, or VSAM IMS DataPropagator for z/OS • IMS sources and DB2 UDB for z/OS target
MQ listener WebSphere MQ Client Federation Server z/OS OS/390 WebSphere MQ for z/OS Metadata Catalog Changed-data Capture Agents IMSlogdata CICSlogdata DataMapper IMS & VSAM Implementation on z/OS • Leverages DB2 II Classic Federation mappings and Server infrastructure • Install changed data capture agents for IMS and/or VSAM. Identify data to be monitored in the metadata. • Install/Configure WebSphere MQ Deliver data to MQ listener or WMB !
MQ listener WebSphere MQ Client Q Capture WebSphere MQ Metadata DB2log z/OS & Distributed Replication Center DB2 Implementation on z/OS • Each message represents a DB2 transaction • XML format consistent with IMS & VSAM • Replication Center for metadata management • “Front-end” of Q Replication solution • Install/Configure WebSphere MQ • No Q Capture on i-Series Deliver data to MQ listener or WMB !
Replication and Event Publishing Products: Distributed Platforms and iSeries • DB2 LUW and Informix IDS sources and targets. • SQL architecture. DB2 LUW DB2 DataPropagator DB2 Information Integrator Replication Edition • Multi-vendor sources and targets (SQL architecture) • DB2 LUW sources and targets (Queue architecture) – note that Websphere MQ is bundled with this product. DB2 DataPropagator DB2 Information Integrator Event Publisher Edition • DB2 LUW sources (Queue architecture) – note that Websphere MQ is bundled with this product. DB2 DataPropagator for iSeries • DB2 iSeries sources and targets.
SOURCE2 SOURCE1 METADATA DB2 Log ADMINISTRATION Replication Monitor Replication Center DB2 Implementation z/OS and LUW: Process Flow SOURCE User Application Q Capture XML User Application WMB DB2 MQ Listener User Stored Procedure TARGET
SOURCE3 SOURCE2 SOURCE1 Staging Tables TARGET1 TARGET2 CD2 CD1 DB2 Log Co-existing DB2 Event Publishing with DB2 Replications (Q and SQL) Log Reader 1 Capture Schema „CAP1“ SQL Apply Q Sub Q Apply Q Capture Schema „CAP2“ Log Reader 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
Terminology • SQL Replication – Replication via “staging tables”. • Q Replication – Replication via WebSphere MQ. • Q Capture, Q Apply – Q Replication engine components. • Q Subscription – The process of defining Q objects for data replication. • Q Publication – The process of defining Q objects for XML publication. • Event publishing – Publishing changed rows or data-events as XML messages via WebSphere MQ. • Do I need a Websphere Application Server to run Q Replication? • Do I need an DB2 II license to run Q Replication? • What happens to DataPropagator and will it go away?
SECONDARY SOURCE SOURCE SOURCE SOURCE PRIMARY CD1 CD1 CD1 CD1 CD1 CD1 COPY COPY COPY REPLICA REPLICA REPLICA REPLICA COPY Sample Q Replication Scenarios Subsets Transformations Conflict Detection/Resolution Updateable Predicates Updateable Primary Keys High-Availability Applications Data Distribution (1:m) Peer to Peer Data Consolidation (n:1) Bidirectional
Restartq Adminq Adminq Recvq Spillq DB2 Source DB2 Target Sendq Q Apply Q Capture Q Replication queues • Requires at least 4 queues (minimum): Local queue Remote queue Remote queue Local queue Local queue Local queue 1. Recvq for QApply to receive the transaction and informational messages from Q Capture 2. Spillq, dynamic queue for QApply to hold the transaction messages as the target table is being loaded • 1. Adminq for Q Capture to receive control messages from QApply or subscribing app • 2. Restartq holds the Q Capture position in the DB2 log
What exactly is a Replication Queue Map? (1) A Queue Manager Queue Manager Q Capture Q Apply Send Queue (remote) Receive Queue (local) Admin Queue (local) Admin Queue (remote)
What exactly is a Replication Queue Map? (2) Queue Manager Queue Manager Q Capture Q Apply Send Queue (remote) Receive Queue (local) Admin Queue (remote) Admin Queue (local) Q Apply Receive Queue (local) Admin Queue (remote) Send Queue (remote) Queue Manager
Subscriptions and Publications • Subscriptions – used for replication • Point to point, a 1-3 fan-out requires 3 subscriptions. • Unidirectional, bidirectional, or peer to peer. • Unidirectional subs offer more options than bidirectional, bidirectional offers more options than peer to peer (as Apply complexity grows, less options are offered). • Publications – used for event publishing • More options available here, because characteristics of downstream application are unknown. • There is no overlap or interaction between publications and subscriptions. • They use separate queues and are independent objects.
Conflict Detection and Resolution “one size does not fit all” – choices provided, care must be taken in selecting the options most appropriate for a given application! • VERSION based: • Based upon time zone adjusted timestamps, most recent timestamp “wins” • Users must add two extra columns to support it (timestamp, tie-breaker) – GUI driven • Extra columns maintained by triggers (insert/update) • VALUE based: • Conflict level options offered: • Check all columns on update - requires transmission of all old/new values • Check only changed columns on update - allows for column merge • Check only key columns • Resolution choices offered: Force or Ignore • Force Action - requires transmission of all new values • force convergence on conflicts • log the conflict • Ignore Action • log the conflict • Force/Ignore used together in a pair provides "very good" convergence • - Example: HA environment (Force on the copy, and Ignore on the Server)
SOURCE2 SOURCE1 METADATA METADATA DB2 Log TGT3 TGT1 TGT2 Replication Center Replication Monitor Q Subscription Process Flow SOURCE Q Apply Browser Apply Agent Q Capture Apply Agent Apply Agent ADMINISTRATION TARGET
Replication Utilities (Q and SQL) • asntdiff • Builds a table of differences between the source and target tables • Differences can be used to effect changes to source or target • Can also be used just to verify target on a periodic basis • asntrepair • Repairs target based on differences found by tdiff • The Alert Monitor • Provides automated monitoring of your replication environment • Alerts you to error and other conditions • The Replication Analyzer • Generates report about the state of your replication environment • Technical support tool, but used by many customers • All utilities work with both Q and SQL replication
Target Target Target DProp & QApply DProp & QCapture Replication Monitor Utilities: Alert Monitor Source server Target server Log • Status: • System health • Event: • Errors/Warnings • Thresholds exceeded: • Throughput • Latency • Queue depth • Poll capture and apply at user defined interval • Operates on user defined alerts and thresholds Notification via email or pager
DB2 Information Integrator 8.2: Q Replication - Administration
Replication Administration • Replication Center GUI • Launchpads, Wizards, Online Help • Definitions, Operations, Monitoring • Command Line Interface • Scripts or interactive mode • Example: • Java API’s • Typically used when replication is embedded • C:\asnclp • REPL > CREATE QSUB USING REPLQMAP ... • REPL > CREATE SUBSCRIPTION SET SETNAME ... • REPL > CREATE MEMBER IN SETNAME ...
What is is ? • Command line processor to define Replication Scenarios • Calls same Java API‘s as the Replication Center • Interactive and Script Mode supported ASNCLP for Command Line Operation and Scripting Example • Interactive Mode • Script Mode C:\asnclpREPL > CREATE QSUB USING REPLQMAP ... (Q Replication commands) REPL > CREATE SUBSCRIPTION SET SETNAME ... (SQL Replication commands) REPL > CREATE MEMBER IN SETNAME ... C:\asnclp -f replscript.asn
Supported Platforms • DB2 II Replication Edition for Multiplatform 8.2 • AIX • AIX 5.1 or later • DB2 ESE for AIX Version 8.2 • WebSphere MQ for Linux for Intel and Linux for zSeries Version 5.3 • Linux • Linux for intel distribution that supports DB2 ESE 8.2 and WebSphere MQ 5.3 • DB2 ESE for Linux Version 8.2 • WebSphere MQ for Linux for Intel and Linux for zSeries Version 5.3 • Windows • DB2 II Replication for z/OS 8.2 • z/OS • z/OS Version 1.2 or later • XML Tool kit for z/OS and OS/390 Version 1.4 • DB2 UDB V7 & V8 for z/OS and OS/390 • WebSphere MQ for z/OS Version 5.3 • DB2 II Q Replication does not support iSeries
Topics • Quick overview of Websphere MQ objects. • Websphere MQ queues required for Q Replication.
Quick overview of MQ objects • Queue Manager • Manages queues of messages for application programs • Provides application programming interface • Coordinates updates to databases and queues using *two-phase commit • Sends one message to more than one destination using distribution lists, etc • Queue Manger objects • Queues • store **messages sent by programs • different types of queues, each with a specific purpose • Message channels • connects two queue managers • these channels are unidirectional • *Note: Q Replication does not require two-phase commit • ** Note: Q Replication uses persistent messages
Restartq Adminq Adminq Recvq Spillq Source Target Q Apply Q Capture Q Replication queues - Remote setup Q Capture /Q Apply do not share Queue Manager 1.Local queue 2.Remote queue Sendq 1.Remote queue 3.Local queue 4.Local queue 2.Local queue 3. Recvq for QApply to receive the transaction and control messages from Q Capture 1. Adminq for Q Capture to receive control messages from QApply or subscribing app 4. Spillq, dynamic queue for QApply to hold the transaction messages as the target table is being loaded 2. Restartq holds the Q Capture position in the DB2 log 1. Sendq for Q Capture to put messages..Remote def for the local recvq on the QApply side 2. Adminq for Q Apply to put messages..Remote def for the local adminq on the QCapture side
Examples of MQ commands on LUW • Create queue manager crtmqm –u <dead.letter.queue.name> CSQ • Start queue manager strmqm CSQ • Start the MQ interactive command line runmqsc CSQ • define qlocal (‘IBMQREP.ASN.ADMINQ’) • define qlocal (‘IBMQREP.ASN.RESTARTQ’) • define qlocal (‘IBMQREP.ASN.SEND_RECVQ’) MAXDEPTH(640000) MAXMSGL(20971520) • define qmodel (IBMQREP.SPILL.MODELQ) DEFSOPT(shared) MAXDEPTH(500000) MSGDLVSQ(fifo) DEFTYPE(permdyn) • For z/OS, the queues would have to be defined with SHARE REPLACE • Use the RC or asnclp to create control tables and define Replication Queue Map. • Next page for the remote setup.
MQ / Q Replication interaction • Q Replication will not work with the distribution lists. • Q Replication can work in the MQ cluster environment. • Cluster configuration is not a Q Replication requirement • A receive cluster queue must be defined exactly once in a cluster, so not a good fit where the cluster setup is for load balancing • Make sure the DISCINT is large enough if no channel initiator is in place, and with channel initiator the user can tune the timeout attribute. • DISCINT – is the channel attribute, to control how long the channel should remain running, when there are no messages to send • Q Replication can detect missing messages. • MQSeries highly recommend to have Dead-letter-queue defined. • We do not provide DLQ handler, however QApply knows when the message is missing from the queue, as Q Replication messages are “dense”. • http://www.ibm.com/software/integration/wmq/ • http://www.ibm.com/software/integration/support/supportpacs • MQ Series Primer – a good easy reading….highly recommended.
DB2 Information Integrator: Q Replication The Good the bad and the ugly(configuration wise!)
The Good (1) In order to pick one over the other, will depend on the workload, need for throughput, latency requirement, etc.
The Good (2) Represents single Q Capture instance sending data over to multiple target databases. There is one Q Apply instance per target.
The Good (3) Represents the functionality of Q Capture, where it can publish DB2 transactions in the XML format. The messages in the XML and Compact format cannot be sent over the same data queue.
The Good (4) One could require multiple instances of Q Capture, depending on the latency requirements for various applications or potentially to provide higher Q Capture throughput.
The Good (5) Three Q Capture instances running on the single source and sending data over to a single target, where three Q Apply instances are running. This configuration could be used to parallelize traffic in the sysplex environment.
The Good (6) This figure represents a consolidation scenario, of data from multiple sources going to the same target database. Recommendation is to use this configuration , where each browser is dealing with a set of partitioned data. For example, all state data getting consolidated in the federal database.
The Bad (1) Cannot map 2 sendq’s to same recvq. Checked during setup, but not if aliases… Implications: dense numbering, which adminq to send the message?