Q REP培训ppt课件.ppt

Q复制原理介绍,QReplicationArchitecture,Control,Control,FederationEngine,Logbased,Source,Admin,WebSphereMQ,Capture,Target,Apply,Capture程序将数据捕获并放入MQ消息队列：每一个消息(Message)都代表一个交易在一组源/目标数据库之间，可以应用一个或多个传输队列高并行性的Apply处理：数据以数据源的交易单元为单位提交到目标表中为保证数据完整性，将按照数据在源端提交的顺序作用在目标端可并行提交交易数据到多个目标表中健壮的差异冲突检测：包括处理删除操作和主键更改操作统一的数据复制和发布架构：数据可以以XML的形式发布出来，以供外部应用程序的调用数据发布与数据复制在Capture端的实现完全一致,QReplicationOverview,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,QReplicationQSubscriptionProcess,TARGET,QApplyBrowser,ApplyAgent,ApplyAgent,ApplyAgent,SOURCE,QCapture,QApplyArchitecture,一个QApply实例只连接一个队列管理器(queuemanager),该队列管理器可包含多个接收队列ReceiveQueue.QApply实例为每一个接收队列(receivequeue)启动一个浏览线程(browserthread)一个接收队列对应且只对应一个QCapture实例浏览线程(browserthread)是彼此隔离的:每个浏览线程处理各自的复制预定集(subscription)并拥有自己的代理线程池(poolofagentthreads)有各自相对应管理队列(adminqueue)从而发现QCapture是否出现故障或是否处于数据加载阶段,WebSphereMQQueueManager,ReceiveQueue1,ReceiveQueue2,storedprocedures,TargetDatabase,tables,Holdlockthread,Housekeepingthread,QApplyArchitecture,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),QApplyTransactionlife-cycle:stage1,第一阶段:重建事务(rebuildthetransaction)并执行相关性分析读取所有消息，在内存中重新构建事务非破坏性的MQGET(采用browse方式)针对正在处理的事务，分析可能存在的相关性（如对同一个表中同一条记录的修改）如果没有发现相关性-将交易放入工作队列workqueue如果发现相关性-记录该交易并延缓处理,Browserthread,WorkQueue,DoneQueue,TransactionDependencyGraph,I,U,U,D,I,Msg1,Msg2,Transaction:,?,并行处理并保证事务的连续性,FIFOlistinMemory,QApplyTransactionlife-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,QApplyTransactionlife-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,AQApplyprogramwillstartoneApplybrowserforeachreceivequeuedefinedEachApplybrowserwillreadfromitsreceivequeueandapplytransactionsinparallelUsingasmanyagentsastheuserhasdefinedPerformingdependencyanalysisandserializingtransactionsasnecessarytopreservedataintegrityAlsopossibletoobservestricttransactionalorderwithoutparallelismItistheuserschoiceregardinghowmanyqueuestodefineAhighdegreeofparallelismcanbereachedusingonequeueAllrelatedtablesshouldbesubscribedtoonthesamequeue(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,Administrationasnclpcommandlineprocessor,C:asnclpREPLCREATEQSUBUSINGREPLQMAP.(QReplicationcommands)REPLCREATESUBSCRIPTIONSETSETNAME.(SQLReplicationcommands)REPLCREATEMEMBERINSETNAME.,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,QApplyappschema1,QCapture-capschema,capschema.IBMQREP_SUBSsourcetable1SENDQ1sourcetable2SENDQ1,QApplyBrowserThread1,appschema1.IBMQREP_TARGETStargettable1targettable2,RECVQ1,sourcetable1SENDQ2sourcetable4SENDQ2,QApplyBrowserThread1,appschema2.IBMQREP_TARGETStargettable3targettable4,RECVQ2,QApplyappschema2,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,asntdiffUtilitythatcomparesasubscriptionssourcetable(S)withitstargettable(T)GeneratesalistofdifferencesbetweenthetwoRowsinSbutnotinTRowsinTbutnotinSRowsinSandTwithdifferentnon-keyvaluesChecksumusedtocomparecontentsofentirerowVerysimilarconcepttofilecomparessuchasUNIXdiffcommandDifferencescanbeusedtochangesource,target,orboth,AssumeDB2sampledatabaseonUNIXorWindowsSourceissampleEmployeetablewithkeyoverEMPNOTargetisTARGET_EMPLOYEEandisacopyofsourceascreatedwhilecreatingaQsubscriptionorSQLSubscriptionmemberasntdiffdb=SAMPLEschema=SAMPLASNWhere=“SUBNAME=SUBEMP”/*QSample*/asntdiffdb=SAMPLEschema=CNTRLASNWhere=Target_table=TARGET_EMPLOYEEANDAPPLY_QUAL=AQQUAL”/*SQLSample*/,WhatgetsputinASN.TDIFF,D+keyvaluesfore