Q REP培训

Click to edit Master title style,Click to edit Master text styles,Second level,Third level,Fourth level,Fifth level,IBM Software Group | DB2 Information Management Software,Q,复制原理介绍,Q Replication Architecture,Control,Control,Federation,Engine,Log based,Source,Admin,WebSphere MQ,Capture,Target,Apply,Capture,程序将数据捕获并放入,MQ,消息队列：,每一个消息,(Message),都代表一个交易,在一组源,/,目标数据库之间，可以应用一个或多个传输队列,高并行性的,Apply,处理：,数据以数据源的交易单元为单位提交到目标表中,为保证数据完整性，将按照数据在源端提交的顺序作用在目标端,可并行提交交易数据到多个目标表中,健壮的差异冲突检测：,包括处理删除操作和主键更改操作,统一的数据复制和发布架构：,数据可以以,XML,的形式发布出来，以供外部应用程序的调用,数据发布与数据复制在,Capture,端的实现完全一致,Q Replication Overview,Capture,和,Apply,异步方式工作且不直接连接,只有在初始化数据加载时，,QApply,才需要连接到,Source,复制的数据单元：,source transaction,数据完整性通过以下方式得以保护,:,WebsphereMQ,的持久性消息,Q Apply,侦查被遗漏的消息,对任意交易保证“一次且仅一次”的提交,(“once and only once” apply),The BIG Picture,ADMINISTRATION,Replication,Monitor,Replication,Center,TGT3,TARGET,TGT1,Q Apply,Browser,Apply Agent,Apply Agent,Apply Agent,TGT2,METADATA,SOURCE,SOURCE2,SOURCE1,METADATA,DB2 Log,Q Capture,Transactional Q Capture,TX1: INSERT S1,TX2: INSERT S2,TX3: ROLLBACK,TX1: COMMIT,TX1: UPDATE S1,TX3: DELETE S1,DB2 Log,SOURCE2,SOURCE1,注意：,必须启用数据库归档日志,(RECOVERY,logging),数据复制不支持,Data Links , Spatial,字段,Q Capture,程序：,在日志中读取,I/U/D,记录,当复制预订,/,数据发布处于激活状态时，从内存中将每个独立的行操作以事务为单元进行组织,正在执行的事务将保留在内存中，直至被中断或者提交日志记录,内存中被中断的事务被丢弃，而已提交的事务被写入,MQ,发送队列（,sendq,）,一个,Q Capture,程序应用一个,WebSphere,MQ,队列管理器 （,queue manager,）,Transactional Q Capture,TX1: INSERT S1,TX2: INSERT S2,TX3: ROLLBACK,TX1: COMMIT,TX1: UPDATE S1,TX3: DELETE S1,DB2 Log,SUBS,CAPPARMS,SOURCE2,SOURCE1,TX1: INSERT S1,TX1: COMMIT,TX1: UPDATE S1,CAPTURE,In-Memory-Transactions,Transaction is still “,i,n,-,flight“,Nothing inserted yet.,“,Zapped“ at Abort,Never makes it to send queue,TX3: DELETE S1,TX3: ROLLBACK,TX2: INSERT S2,Restart,Queue,MQ Put when Commit,record is found,Send,Queue,Admin Queue,Queue Manager,Multi-Threaded Q Capture,Initial thread,：,初始化环境，启动其他各种线程，响应,/,处理命令行发出的请求。,lock thread,：,在控制表,.IBMQREP_CAPENQ,上建立排他锁,(Exclusive Lock),，该控制表用于管理全局信号。,admin/monitor/misc thread,：,将,capture,的状态向外发布到控制表,.,IBMQREP_CAPMON,中，发布频率为参数,monitor_interval,所指定；,发布日志消息到数据库日志,db2.diaglog,以及控制表,.,IBMQREP_CAPTRACE,；,接收来自,MQ,管理队列,(ADMINQ queue),中的管理控制消息。,pruning thread,：,如果参数,autoprune,为,”Y”,，该线程平时处于休眠状态，在执行,prune,操作时或者在每个剪除周期被唤醒，剪除周期由参数,prune_interval,指定,剪除信号表,signal table,跟踪表,trace table,以及监控表,monitor table,。,worker thread,：,读数据库日志，在内存中按每个事务组织相应数据，发送提交的事务到,MQ,的发送队列,sendq,.,Multi-Threaded Q Capture,HOLD,Thread,ADMIN,Thread,WORKER,Thread,PRUNING,Thread,SOURCE2,SOURCE1,CAPMON,I,I,I,DB2 Log,Admin Q,Commands,Memory stats,Latency stats,Transaction states,Prunes Monitor ,Trace,Signal Tables,Initial,Thread,asnqccmd,IPC,Commands,SIGNAL,asnqcap,Restart Q,Send Q,Send Q,Q Replication Q Subscription Process,ADMINISTRATION,Replication,Monitor,Replication,Center,TGT3,TARGET,TGT1,Q Apply,Browser,Apply Agent,Apply Agent,Apply Agent,TGT2,METADATA,SOURCE,SOURCE2,SOURCE1,METADATA,DB2 Log,Q Capture,Q Apply Architecture,一个,Q Apply,实例只连接一个队列管理器,(,queue manager ),该队列管理器可包含多个接收队列,Receive Queue.,Q Apply,实例为每一个接收队列,(,receive queue),启动一个浏览线程,(,browser thread),一个接收队列对应且只对应一个,Q Capture,实例,浏览线程,(,browser thread),是彼此隔离的,:,每个浏览线程处理各自的复制预定集,(subscription),并 拥有自己的代理线程池,(pool of agent threads),有各自相对应管理队列,(admin queue),从而发现,Q Capture,是否出现故障或是否处于数据加载阶段,WebSphere MQ,Queue Manager,Browser thread,agent,agent,agent,agent,agent,Browser thread,agent,agent,agent,agent,agent,Receive Queue 1,Receive Queue 2,stored procedures,Target Database,tables,Hold lock thread,House keeping thread,Q Apply Architecture,stored procedures,WebSphere MQ,Queue Manager,Target Database,tables,Browser thread,agent,agent,agent,agent,agent,Browser thread,agent,agent,agent,agent,agent,Receive Queue 1,Receive Queue 2,整个,Q Apply,实例有一个管家线程,(,housekeeping thread) ,它可以,:,剪除在接收队列中已经被复制消息,(DONEMSG table,is used to avoid 2 phase commit between MQ and DB2 to still guarantee that messages are processed once and only once and are deleted once processed),将监控统计数据记录到,APPLYMON,表,记录日志消息到,APPLYTRACE,表以及日志文件,剪除,APPLYMON, APPLYTRACE,和,DELTOMB (P2P only),表中的过期数据，,at the specified interval,DONEMSG,APPLYTRACE,APPLYMON,DELTOMB (P2P),PRUNES,POPULATES,LOG file,House keeping thread,Hold lock thread,Guarantees 1 apply instance,for a set of control tables,(schema/owner),Q Apply Transaction life-cycle: stage 1,第一阶段,:,重建事务,(rebuild the transaction),并执行相关性分析,读取所有消息，在内存中重新构建事务,非破坏性的,MQGET (,采用,browse,方式,),针对正在处理的事务，分析可能存在的相关性（如对同一个表中同一条记录的修改）,如果没有发现相关性,-,将交易放入工作队列,work queue,如果发现相关性,-,记录该交易并延缓处理,Browser thread,agent,agent,agent,agent,agent,agent,agent,Work Queue,Done Queue,Transaction Dependency Graph,I,U,U,D,I,Msg,1,Msg,2,Transaction:,?,并行处理并保证事务的连续性,FIFO list in Memory,Q Apply Transaction life-cycle: stage 2,Stage 2: apply the transaction,An agent gets and removes the transaction from the work queue,Rebuild and execute SQL to recreate the transaction changes,Inserts the message id (,msgid,) of all the messages constituting this transaction in the DONEMSG table,Commits the database,Inserts the transaction into the done queue,Browser thread,Agent,agent,agent,agent,agent,agent,agent,Work Queue,Done Queue,I,U,U,D,I,Msg,1,Msg,2,Transaction:,INSERT INTO T1,UPDATE T2,UPDATE T2,DELETE FROM T1,INSERT INTO T4,INSERT INTO,DONEMSG (,msg1, msg2),COMMIT,Q Apply Transaction life-cycle: stage 3,Stage 3: discard the transaction and clear dependencies,The browser thread gets the transaction back from the done queue,Checks for dependent transactions not having dependencies on other transactions,If any, schedule those transactions for execution by inserting them into the work queue,Free memory used by this transaction,Browser thread,agent,agent,agent,agent,agent,agent,agent,Work Queue,Done Queue,Transaction Dependency Graph,I,U,U,D,I,Msg,1,Msg,2,Transaction:,?,Apply agent threads pool,(,NUM_APPLY_AGENTS,),Browser thread,Transaction being,applied,Transaction applied,Transaction ready,to be applied,agent,agent,agent,agent,agent,agent,agent,Q Apply : Transaction Processing Summary,Transaction Dependency Graph,Subscriptions,child,child,child,parent,parent,parent,Note: Work Queue and Done Queue are internal data structures not,WebsphereMQ,queues,(,MEMORY_LIMIT,),Work Queue,Done Queue,3 stages,processing of,Transactions:,1,2,3,Q Apply Performance,A Q Apply program will start one Apply browser for each receive queue defined,Each Apply browser will read from its receive queue and apply transactions in parallel,Using as many agents as the user has defined,Performing dependency analysis and serializing transactions as necessary to preserve data integrity,Also possible to observe strict transactional order without parallelism,It is the users choice regarding how many queues to define,A high degree of parallelism can be reached using one queue,All related tables should be subscribed to on the same queue (using the same replication queue map),In some ways, a receive queue is similar to the SQL Replication subscription set,Sample Q-Replication Scenarios,REPLICA,REPLICA,REPLICA,Peer to Peer,REPLICA,Bidirectional,CD1,PRIMARY,CD1,SECONDARY,Subsets,Transformations,Conflict Detection/Resolution,Updateable Predicates,Updateable Primary Keys,High-Availability Applications,CD1,SOURCE,COPY,COPY,COPY,Data Distribution (1:m),CD1,SOURCE,CD1,SOURCE,CD1,SOURCE,COPY,Data Consolidation (n:1),Q,复制系统架构,主要消息队列,Q Replication queues- local setupQCapture,QApply share the Queue Manager,Source,Q Capture,Target,Q Apply,Send_Recvq,Adminq,3.Local queue,1.Local queue,Restartq,2.Local queue,Spillq,4. Local queue,Requires at least 4 local queues:,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,3. Send_Recvq for QApply to receive the transaction and control messages from Q Capture,4. Spillq, dynamic queue for QApply to hold the transaction messages as the target table is being loaded,Q Replication queues- Remote setup -QCapture /QApply do not share Queue Manager,Source,Q Capture,Target,Q Apply,Recvq,Adminq,1.Remote queue,Sendq,Adminq,2.Remote queue,3.Local queue,1.Local queue,Restartq,2.Local queue,Spillq,4.Local queue,Requires at least,4 local queues,2 remote queues,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,3. Recvq for QApply to receive the transaction and control messages from Q Capture,4. Spillq, dynamic queue for QApply to hold the transaction messages as the target table is being loaded,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,定义,Q,复制步骤,创建,Q Capture,控制表,创建,Q Apply,控制表,创建,Q,预订集,启动,Q Capture,启动,Q Apply,Q Apply,模式,队列管理器,发送队列,接收队列,管理队列,配置,MQ,队列,Q Capture,模式,队列管理器,管理队列,重新启动队列,创建,Q,复制控制表,Capture,Apply,创建,replication Queue Map,Q Map A-B,System A,System B,System C,Q Map A-C,创建,Q subscription,支持对数据目标列的选择,支持对数据目标行的过滤,目前不支持计算列,支持,CCD,的模式,支持忽略删除操作,每个,Subscription,使用一个,Replication Queue Map,S,ubscription,间完全独立,即使它们复制同一数据源,注意,:,这个概念与,SQL Replication,略有不同,在,SQL Replication,中,同一数据源的所有,Subscription,可 共享一张,CD,表。,Q,复制,:,目标,Oracle, Sybase,Administration asnclp command line processor,C:asnclp,REPL 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,Interactive Mode,Script Mode,Example,Command line processor to define Replication Scenario,s,Calls same,Java,APIs as the Replication Center,Interactive and Script Mode supported,asnclp,Q Apply: Initial Load Processing (Full Refresh),SOURCE,SOURCE2,SOURCE1,DB2 Log,Q Capture,TGT3,TARGET,TGT1,Q Apply,Browser,Apply Agent,Apply Agent,Load Apply Agent,TGT2,MQ Channel,Loading,Apply Agent,Handles RI automatically,i.e. drops and saves RI, loads, puts RI back on,Can handle Peer to Peer loading of n peers,Includes starting a new peer into an existing active configuration,Agent pool,Spill Queue,CAPSTART,Msg,1,Schema,Msg,2,Trans,Messages,3,Q Apply Load Options,A subscription can be defined with:,automatic load, manual load, no load required,Automatic load:,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:,Load is performed by user, coordination is required, and will be handled by user (with some help from our administration).,No load:,No loading required, no coordination required, can immediately capture and apply changes,Example: target system is built through backup/restore, with replication started from an inactive source,Throughput with different values of row-size and transaction-size - AIX,The more rows the transaction has,the higher throughput,QCap,can achieve in term of Rows/s.,Throughput and row-size have an inverse relationship.,Row Size,(Bytes),Q Replication scalability,Multiple Capture Schemas,Multiple Applies or Browsers,Putting the Pieces Together,Q Apply - appschema,Q Capture - capschema,capschema.IBMQREP_SUBS,sourcetable1 SENDQ1,sourcetable2 SENDQ1,Q Apply Browser Thread 1,appschema.IBMQREP_TARGETS,targettable1,targettable2,RECVQ1,sourcetable3 SENDQ2,sourcetable4 SENDQ2,Q Apply Browser Thread 2,appschema.IBMQREP_TARGETS,targettable3,targettable4,RECVQ2,Putting the Pieces Together,Q Apply appschema1,Q Capture - capschema,capschema.IBMQREP_SUBS,sourcetable1 SENDQ1,sourcetable2 SENDQ1,Q Apply Browser Thread 1,appschema1.IBMQREP_TARGETS,targettable1,targettable2,RECVQ1,sourcetable1 SENDQ2,sourcetable4 SENDQ2,Q Apply Browser Thread 1,appschema2.IBMQREP_TARGETS,targettable3,targettable4,RECVQ2,Q Apply appschema2,Tuning database from the Q-Apply perspective,Tables that have high written activities :,Workload tables that have change data applied.,asn.ibmqrep_donemsg,asn.ibmqrep_signal,( in Bi-directional ),Those tables that have high written activities should:,Belongs to DMS,tablespace,(LUW),Tablespace,is spanned across multiple disks for parallel I/O,In a high volume replication environment, the,Extentsize,of,tablespace,should be larger than the default,extentsize,of 32 (recommended value of 64) (LUW),Indexes should be on its own,tablespace,with dedicated,bufferpool,Logging :,Data and Logs should be stored on different set of disks,Filesystem,/data sets that hold log files should be spanned across multiple disks.,In DB2 for z/OS : Use VSAM striped log datasets for better read/write throughput,数据复制差异检查和修复工具,asntdif,Generates a list of differences between 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,asntrep,Repairs target based on differences found by asntdiff,ASNTDIFF,asntdiff,Utility that compares a subscriptions source table (S) with its target table (T),Generates a list of differences between the two,Rows in S but not in T,Rows in T but not in S,Rows in S and T with different non-key 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,S only,T only,Intersection of S and T,Assume DB2 sample database on UNIX or Windows,Source is sample Employee table with key over EMPNO,Target is TARGET_EMPLOYEE and is a copy of source as created while creating a Q subscription or SQL Subscription member,asntdiff db=SAMPLE schema=SAMPLASN Where=“SUBNAME = SUBEMP” /*Q Sample */,asntdiff db=SAMPLE schema=CNTRLASN Where=Target_table = TARGET_EMPLOYEE AND APPLY_QUAL=AQQUAL” /* SQL Sample */,What gets put in ASN.TDIFF,D + key values for each row in the target that is not in the source,These rows will not be put in ASN.TDIFF if you have chosen to suppress the capture of deletes at the source.,U + key values and non-key values for each row in the target that does not match the same row in the source,I + key values and non-key values for each row in the source that does not exist at the target,Repairing a Target Table,Asntrep,If not bound, binds itself isolation UR,You can rebind to CS if needed for your circumstances,Utility that effects repair of a target table,Uses the differences identified by tdiff,Copies the diff table to the target,Deletes rows that exist in the target but not the source,i.e all rows in target that have keys in diff table marked as D is deleted.,Inserts source rows into the target that were missing from target,i.e Fetches source rows that have keys in diff table marked as I,Updates target rows with non key data.,i.e target rows that have non key data different from source marked as U,are updated.,Can be used with manual load procedures instead of the DB2 load utility,How does asntrep work?,Uses the SUBNAME in the WHERE parameter to find the column mappings in the Q Capture and Q Apply control tables.,Selects from ASN.TDIFF to get the differences,Inserts missing rows to the target,Deletes extra rows from the target,Updates mismatched rows in the target,Writes messages,