Q REP培训PPT课件.ppt

Q复制原理介绍 QReplicationArchitecture Control Control FederationEngine Logbased Source Admin WebSphereMQ Capture Target Apply Capture程序将数据捕获并放入MQ消息队列 每一个消息 Message 都代表一个交易在一组源 目标数据库之间 可以应用一个或多个传输队列高并行性的Apply处理 数据以数据源的交易单元为单位提交到目标表中为保证数据完整性 将按照数据在源端提交的顺序作用在目标端可并行提交交易数据到多个目标表中健壮的差异冲突检测 包括处理删除操作和主键更改操作统一的数据复制和发布架构 数据可以以XML的形式发布出来 以供外部应用程序的调用数据发布与数据复制在Capture端的实现完全一致 QReplication Overview Capture和Apply异步方式工作且不直接连接只有在初始化数据加载时 QApply才需要连接到Source复制的数据单元 sourcetransaction数据完整性通过以下方式得以保护 WebsphereMQ的持久性消息QApply侦查被遗漏的消息对任意交易保证 一次且仅一次 的提交 onceandonlyonce apply TheBIGPicture TransactionalQCapture DB2Log 注意 必须启用数据库归档日志 RECOVERYlogging 数据复制不支持DataLinks Spatial字段 QCapture程序 在日志中读取I U D记录当复制预订 数据发布处于激活状态时 从内存中将每个独立的行操作以事务为单元进行组织正在执行的事务将保留在内存中 直至被中断或者提交日志记录内存中被中断的事务被丢弃 而已提交的事务被写入MQ发送队列 sendq 一个QCapture程序应用一个WebSphereMQ队列管理器 queuemanager TransactionalQCapture DB2Log TX1 COMMIT TX1 UPDATES1 CAPTUREIn Memory Transactions TX3 ROLLBACK RestartQueue SendQueue AdminQueue QueueManager Multi ThreadedQCapture Initialthread 初始化环境 启动其他各种线程 响应 处理命令行发出的请求 lockthread 在控制表 IBMQREP CAPENQ上建立排他锁 ExclusiveLock 该控制表用于管理全局信号 admin monitor miscthread 将capture的状态向外发布到控制表 IBMQREP CAPMON中 发布频率为参数monitor interval所指定 发布日志消息到数据库日志db2 diaglog以及控制表 IBMQREP CAPTRACE 接收来自MQ管理队列 ADMINQqueue 中的管理控制消息 pruningthread 如果参数autoprune为 Y 该线程平时处于休眠状态 在执行prune操作时或者在每个剪除周期被唤醒 剪除周期由参数prune interval指定剪除信号表signaltable 跟踪表tracetable 以及监控表monitortable workerthread 读数据库日志 在内存中按每个事务组织相应数据 发送提交的事务到MQ的发送队列sendq Multi ThreadedQCapture I I I AdminQCommands PrunesMonitor Trace SignalTables asnqccmd IPCCommands asnqcap RestartQ SendQ SendQ QReplication QSubscriptionProcess TARGET QApplyBrowser ApplyAgent ApplyAgent ApplyAgent SOURCE QCapture QApply Architecture 一个QApply实例只连接一个队列管理器 queuemanager 该队列管理器可包含多个接收队列ReceiveQueue QApply实例为每一个接收队列 receivequeue 启动一个浏览线程 browserthread 一个接收队列对应且只对应一个QCapture实例浏览线程 browserthread 是彼此隔离的 每个浏览线程处理各自的复制预定集 subscription 并拥有自己的代理线程池 poolofagentthreads 有各自相对应管理队列 adminqueue 从而发现QCapture是否出现故障或是否处于数据加载阶段 WebSphereMQQueueManager ReceiveQueue1 ReceiveQueue2 storedprocedures TargetDatabase tables Holdlockthread Housekeepingthread QApply Architecture storedprocedures WebSphereMQQueueManager TargetDatabase tables ReceiveQueue1 ReceiveQueue2 整个QApply实例有一个管家线程 housekeepingthread 它可以 剪除在接收队列中已经被复制消息 DONEMSGtableisusedtoavoid2phasecommitbetweenMQandDB2tostillguaranteethatmessagesareprocessedonceandonlyonceandaredeletedonceprocessed 将监控统计数据记录到APPLYMON表记录日志消息到APPLYTRACE表以及日志文件剪除APPLYMON APPLYTRACE和DELTOMB P2Ponly 表中的过期数据 atthespecifiedinterval DONEMSG APPLYTRACE APPLYMON DELTOMB P2P PRUNES POPULATES LOGfile Housekeepingthread Holdlockthread Guarantees1applyinstanceforasetofcontroltables schema owner QApply Transactionlife cycle stage1 第一阶段 重建事务 rebuildthetransaction 并执行相关性分析读取所有消息 在内存中重新构建事务非破坏性的MQGET 采用browse方式 针对正在处理的事务 分析可能存在的相关性 如对同一个表中同一条记录的修改 如果没有发现相关性 将交易放入工作队列workqueue如果发现相关性 记录该交易并延缓处理 Browserthread WorkQueue DoneQueue TransactionDependencyGraph I U U D I Msg1 Msg2 Transaction 并行处理并保证事务的连续性 FIFOlistinMemory QApply Transactionlife cycle stage2 Stage2 applythetransactionAnagentgetsandremovesthetransactionfromtheworkqueueRebuildandexecuteSQLtorecreatethetransactionchangesInsertsthemessageid msgid ofallthemessagesconstitutingthistransactionintheDONEMSGtableCommitsthedatabaseInsertsthetransactionintothedonequeue Browserthread Agent agent agent agent agent agent agent WorkQueue DoneQueue I U U D I Msg1 Msg2 Transaction INSERTINTOT1UPDATET2UPDATET2DELETEFROMT1INSERTINTOT4INSERTINTODONEMSG msg1 msg2 COMMIT QApply Transactionlife cycle stage3 Stage3 discardthetransactionandcleardependenciesThebrowserthreadgetsthetransactionbackfromthedonequeueChecksfordependenttransactionsnothavingdependenciesonothertransactionsIfany schedulethosetransactionsforexecutionbyinsertingthemintotheworkqueueFreememoryusedbythistransaction Browserthread WorkQueue DoneQueue TransactionDependencyGraph I U U D I Msg1 Msg2 Transaction Applyagentthreadspool NUM APPLY AGENTS Browserthread Transactionbeingapplied Transactionapplied Transactionreadytobeapplied QApply TransactionProcessingSummary Note WorkQueueandDoneQueueareinternaldatastructuresnotWebsphereMQqueues MEMORY LIMIT WorkQueue DoneQueue 3stagesprocessingofTransactions 1 2 3 QApplyPerformance AQApplyprogramwillstartoneApplybrowserforeachreceivequeuedefinedEachApplybrowserwillreadfromitsreceivequeueandapplytransactionsinparallelUsingasmanyagentsastheuserhasdefinedPerformingdependencyanalysisandserializingtransactionsasnecessarytopreservedataintegrityAlsopossibletoobservestricttransactionalorderwithoutparallelismItistheuser schoiceregardinghowmanyqueuestodefineAhighdegreeofparallelismcanbereachedusingonequeueAllrelatedtablesshouldbesubscribedtoonthesamequeue usingthesamereplicationqueuemap Insomeways areceivequeueissimilartotheSQLReplicationsubscriptionset SampleQ ReplicationScenarios SubsetsTransformationsConflictDetection ResolutionUpdateablePredicatesUpdateablePrimaryKeysHigh AvailabilityApplications Q复制系统架构 主要消息队列 QReplicationqueues localsetupQCapture QApplysharetheQueueManager Source QCapture Target QApply 3 Localqueue 1 Localqueue 2 Localqueue 4 Localqueue Requiresatleast4localqueues 1 AdminqforQCapturetoreceivecontrolmessagesfromQApplyorsubscribingapp 2 RestartqholdstheQCapturepositionintheDB2log 3 Send RecvqforQApplytoreceivethetransactionandcontrolmessagesfromQCapture 4 Spillq dynamicqueueforQApplytoholdthetransactionmessagesasthetargettableisbeingloaded QReplicationqueues Remotesetup QCapture QApplydonotshareQueueManager Source QCapture Target QApply 1 Remotequeue Sendq 2 Remotequeue 3 Localqueue 1 Localqueue 2 Localqueue 4 Localqueue Requiresatleast4localqueues2remotequeues 1 AdminqforQCapturetoreceivecontrolmessagesfromQApplyorsubscribingapp 2 RestartqholdstheQCapturepositionintheDB2log 3 RecvqforQApplytoreceivethetransactionandcontrolmessagesfromQCapture 4 Spillq dynamicqueueforQApplytoholdthetransactionmessagesasthetargettableisbeingloaded 1 SendqforQCapturetoputmessages RemotedefforthelocalrecvqontheQApplyside 2 AdminqforQApplytoputmessages RemotedefforthelocaladminqontheQCaptureside 定义Q复制步骤 创建QCapture控制表 创建QApply控制表 创建Q预订集 启动QCapture 启动QApply QApply模式队列管理器 发送队列接收队列管理队列 配置MQ队列 QCapture模式队列管理器管理队列重新启动队列 创建Q复制控制表 Capture Apply 创建replicationQueueMap QMapA B QMapA C 创建Qsubscription 支持对数据目标列的选择支持对数据目标行的过滤目前不支持计算列支持CCD的模式支持忽略删除操作每个Subscription使用一个ReplicationQueueMapSubscription间完全独立 即使它们复制同一数据源 注意 这个概念与SQLReplication略有不同 在SQLReplication中 同一数据源的所有Subscription可共享一张CD表 Q复制 目标Oracle Sybase Administration asnclpcommandlineprocessor C asnclpREPL CREATEQSUBUSINGREPLQMAP QReplicationcommands REPL CREATESUBSCRIPTIONSETSETNAME SQLReplicationcommands REPL CREATEMEMBERINSETNAME C asnclp freplscript asn InteractiveModeScriptMode Example QApply InitialLoadProcessing FullRefresh SOURCE QCapture TARGET QApplyBrowser ApplyAgent ApplyAgent LoadApplyAgent MQChannel Loading ApplyAgent HandlesRIautomaticallyi e dropsandsavesRI loads putsRIbackonCanhandlePeertoPeerloadingofnpeersIncludesstartinganewpeerintoanexistingactiveconfiguration Agentpool SpillQueue 1 QApplyLoadOptions Asubscriptioncanbedefinedwith automaticload manualload noloadrequiredAutomaticload LoadisperformedbyApply withautomaticcoordinationofthesimultaneouscaptureofchanges loadingofthenewtable andapplyofchangestoothertables Manualload Loadisperformedbyuser coordinationisrequired andwillbehandledbyuser withsomehelpfromouradministration Noload Noloadingrequired nocoordinationrequired canimmediatelycaptureandapplychangesExample targetsystemisbuiltthroughbackup restore withreplicationstartedfromaninactivesource Throughputwithdifferentvaluesofrow sizeandtransaction size AIX Themorerowsthetransactionhas thehigherthroughputQCapcanachieveintermofRows s Throughputandrow sizehaveaninverserelationship RowSize Bytes QReplicationscalability MultipleCaptureSchemas MultipleAppliesorBrowsers PuttingthePiecesTogether QApply appschema QCapture capschema capschema IBMQREP SUBSsourcetable1SENDQ1sourcetable2SENDQ1 QApplyBrowserThread1 appschema IBMQREP TARGETStargettable1targettable2 RECVQ1 sourcetable3SENDQ2sourcetable4SENDQ2 QApplyBrowserThread2 appschema IBMQREP TARGETStargettable3targettable4 RECVQ2 PuttingthePiecesTogether QApply appschema1 QCapture capschema capschema IBMQREP SUBSsourcetable1SENDQ1sourcetable2SENDQ1 QApplyBrowserThread1 appschema1 IBMQREP TARGETStargettable1targettable2 RECVQ1 sourcetable1SENDQ2sourcetable4SENDQ2 QApplyBrowserThread1 appschema2 IBMQREP TARGETStargettable3targettable4 RECVQ2 QApply appschema2 TuningdatabasefromtheQ Applyperspective Tablesthathavehighwrittenactivities Workloadtablesthathavechangedataapplied asn ibmqrep donemsgasn ibmqrep signal inBi directional Thosetablesthathavehighwrittenactivitiesshould BelongstoDMStablespace LUW TablespaceisspannedacrossmultipledisksforparallelI OInahighvolumereplicationenvironment theExtentsizeoftablespaceshouldbelargerthanthedefaultextentsizeof32 recommendedvalueof64 LUW IndexesshouldbeonitsowntablespacewithdedicatedbufferpoolLogging DataandLogsshouldbestoredondifferentsetofdisksFilesystem datasetsthatholdlogfilesshouldbespannedacrossmultipledisks InDB2forz OS UseVSAMstripedlogdatasetsforbetterread writethroughput 数据复制差异检查和修复工具 asntdifGeneratesalistofdifferencesbetweensourceandtargettablesDifferencescanbeusedtoeffectchangestosourceortargetCanalsobeusedjusttoverifytargetonaperiodicbasisasntrepRepairstargetbasedondifferencesfoundbyasntdiff ASNTDIFF asntdiffUtilitythatcomparesasubscription ssourcetable S withitstargettable T GeneratesalistofdifferencesbetweenthetwoRowsinSbutnotinTRowsinTbutnotinSRowsinSandTwithdifferentnon keyvaluesChecksumusedtocomparecontentsofentirerowVerysimilarconcepttofilecomparessuchasUNIXdiffcommandDifferencescanbeusedtochangesource target orboth AssumeDB2sampledatabaseonUNIXorWindowsSourceissampleEmployeetablewithkeyoverEMPNOTargetisTARGET EMPLOYEEandisacopyofsourceascreatedwhilecreatingaQsubscriptionorSQLSubscriptionmemberasntdiffdb SAMPLEschema SAMPLASNWhere SUBNAME SUBEMP QSample asntdiffdb SAMPLEschema CNTRLASNWhere Target table TARGET EMPLOYEE ANDAPPLY QUAL AQQUAL SQLSample WhatgetsputinASN TDIFF D keyvaluesforeachro