SQL 数据复制过程中控制表运行状况分析
2010-02-04 00:00:00 来源:WEB开发网SQL 数据复制概述
在 DB2 UDB 中,典型的 DB2 到 DB2 的 SQL 数据复制通过两个程序来实现,既 Capture 程序和 Apply 程序 ( 如图 1)。
图 1. DB2 到 DB2 的 SQL 数据复制原理图
查看原图(大图)
Capture 程序运行在源系统中,它通过读取 DB2 的 log 文件来发现数据库中发生变化的数据,并把这些变化的数据保存到中间表(即:CD 表 change-data table)中。Apply 程序一般运行在目标系统中,它从中间表中获取数据并把数据保存到目标表中。
控制表的作用
Capture 程序和 Apply 程序都会使用一套控制表,控制表中包含源表和目标表的对应关系,复制过程中用到的参数等重要信息。Capture 和 Apply 之间的通信和协调也通过控制表来完成。控制表还用来跟踪数据复制过程中各任务的运行状况,并且保存各种警告和错误信息。因此,理解和掌握各控制表的功能、结构和运行时的状态,对理解 SQL 数据复制过程及解决 SQL 复制中的错误是至关重要的。
本文将介绍 DB2 SQL 复制引擎所使用的控制表,着重描述它们之间的相互关系、运行原理及触发机制。在阅读本文之前,读者须掌握如何通过 DB2 SQL 复制图形界面工具(复制中心)来配置 SQL 复制和执行基本的 SQL 复制操作。如果你以前没有这方面的经验,请参阅参考文献 1 中的第 2 章。
控制表列表及功能简介
我们把源系统中的控制表称为 Capture 控制表 (Capture control tables),下面所列的是各 Capture 控制表的表名及功能简介。默认的表模式 (schema) 是 ASN,我们也可以指定其它的表模式。
表 1. Capture 控制表功能简介
表名 | 功能简介 |
ASN.IBMSNAP_CAPSCHEMAS | 包含所有 Capture Schema 的名称,这个表的 schema 是固定的 ASN,不能另外指定 |
schema.IBMSNAP_CAPENQ | 对于每个 Capture Schema, 使用此表来确保每个数据库只有一个 Capture 程序正在运行 |
schema.CD table | 它起到中间表的作用,在 create registration 时才创建,用来保存源表中变化的数据 , 此表的默认表模式 (schema) 是登录的 DB2 用户名 |
schema.IBMSNAP_CAPPARMS | 包含 Capture 程序运行时所需要的参数信息 |
schema.IBMSNAP_CAPTRACE | 包含 Capture 程序运行时所产生的 trace 信息 |
schema.IBMSNAP_PARTITIONINFO | 对于多分区的环境,此表包含 Capture 程序重起时所需要的 log 文件中的序号标识 ( 在单分区环境中 , 此表不会被创建 ) |
schema.IBMSNAP_PRUNE_LOCK | 在冷启动期间或保留限制修剪期间用来序列化 Capture 程序对 CD 表的存取 |
schema.IBMSNAP_PRUNE_SET | 协调对 CD 表的修剪 |
schema.IBMSNAP_PRUNCNTL | 用来协调 Capture 程序和 Apply 程序的同步点更新 |
schema.IBMSNAP_REGISTER | 用于存放源表和相对应的 CD 表信息 |
schema.IBMSNAP_REG_SYNCH | 此表在源是非 DB2 数据库时使用。在 Apply 程序从 IBMSNAP_REGISTER 表中读取信息之前,此表上的更新触发器通过为 IBMSNAP_REGISTER 表中所有行初始化 SYNCHPOINT 值来模拟 Capture 程序 ( 如果源库是 DB2, 此表不会被创建 ) |
schema.IBMSNAP_RESTART | 此表包含 Capture 程序重起时,读取 log 文件的起始点位置信息 |
schema.IBMSNAP_SIGNAL | 包含 Capture 程序运行时所需要的所有信号 |
schema.IBMSNAP_UOW | 对于操作源表的每一个已完成的事务,提供其附加信息 |
在目标系统中的控制表称为 Apply 控制表 (Apply control tables),下面所列的是各 Apply 控制表的表名及功能简介。对于典型的 DB2 到 DB2 的复制,默认的表模式 (schema) 是 ASN,我们也可以指定其它的表模式。
表 2. Apply 控制表功能简介
表名 | 功能简介 |
schema.IBMSNAP_APPENQ | 用来确保每个 Apply 限定符只有一个 Apply 程序正在运行 |
schema.IBMSNAP_APPLYTRACE | 用来保存 Apply 程序的 trace 信息 |
schema.IBMSNAP_APPLYTRAIL | 用来保存 Apply 程序的认证跟踪信息 |
schema.IBMSNAP_APPPARMS | 用来保存 Apply 程序的参数,可以通过更改这些参数来操作 Apply 程序 |
schema.IBMSNAP_SUBS_COLS | 用来保存原表和目标表中对应列的信息 |
schema.IBMSNAP_SUBS_EVENT | 用来保存 Apply 程序处理 subscription 时发生的事件 |
schema.IBMSNAP_SUBS_MEMBR | 用来保存源表和目标表的对应关系,并指定处理信息 |
schema.IBMSNAP_SUBS_SET | 针对每一组 subscription, 保存 Apply 程序的处理信息 |
schema.IBMSNAP_SUBS_STMTS | 用来保存针对 subscription 的存储过程或 SQL 调用 |
本文中的用例
本文将结合一个简单 SQL 复制用例来阐述控制表的运行状况和触发机制。这个例子将从准备源数据开始,直到做完一次完整的数据同步为止,以此来说明各控制表的用途及触发机制。例子中的源库是 srcdb,目标库是同一个 DB2 实例上的数据库 tgtdb,源表是 T_1,schema 为默认的登录 DB2 用户名。目标表是 tgtdb 中的 T_TGT_1,schema 为默认的登录 DB2 用户名。源表中的原始数据是两条记录。然后在源表中各进行一次 Insert,Update 和 Delete 操作,以此来观察控制表的运行状况。
准备源数据 , 创建控制表
准备源数据
创建 srcdb 作为源库 ,tgtdb 作为目标库,创建表 T_1 并插入下面的数据作为源数据。
db2 => select * from T_1
ID NAME
------ --------------------
1 A
2 B
控制表的创建
我们可以使用 Replication Center 或 ASNCLP 程序来创建控制表。如果你足够熟悉控制表的细节,你也可以手工运行 DB2 SQL 程序创建这些表(本质上它们只是一些 DB2 的表)。我们这里通过 ASNCLP 来创建控制表(Capture 控制表和 Apply 控制表)。下面是相关的 ASNCLP 语句。
创建 Capture 控制表:
set server capture to db srcdb;
create control tables for capture server;
创建 Apply 控制表:
set server control to db tgtdb;
create control tables for apply control server;
ASNCLP 的语法规则请参阅参考文献 4。
下面让我们来看看源库和目标库中的控制表。表 1 和表 2 列出的所有控制表都被创建了。
Capture 控制表:
db2 => list tables for schema ASN
Table/View Schema Type Creation time
------------------ -------- ----- ---------------
IBMSNAP_CAPENQ ASN T 2006-11-13-20.57.13.842439
IBMSNAP_CAPPARMS ASN T 2006-11-13-20.57.13.694200
IBMSNAP_CAPSCHEMAS ASN T 2006-11-13-20.57.12.965814
IBMSNAP_CAPTRACE ASN T 2006-11-13-20.57.13.601691
IBMSNAP_PRUNCNTL ASN T 2006-11-13-20.57.13.289228
IBMSNAP_PRUNE_LOCK ASN T 2006-11-13-20.57.14.037758
IBMSNAP_PRUNE_SET ASN T 2006-11-13-20.57.13.510111
IBMSNAP_REGISTER ASN T 2006-11-13-20.57.13.082576
IBMSNAP_RESTART ASN T
2006-11-13-20.57.12.895058
IBMSNAP_SIGNAL ASN T 2006-11-13-20.57.13.883280
IBMSNAP_UOW ASN T 2006-11-13-20.57.13.749844
Apply 控制表:
db2 => list tables for schema ASN
Table/View Schema Type Creation time
----------------- ---------- ----- ---------------
IBMSNAP_APPENQ ASN T 2006-11-13-20.57.15.024764
IBMSNAP_APPLYTRACE ASN T 2006-11-13-20.57.15.536613
IBMSNAP_APPLYTRAIL ASN T 2006-11-13-20.57.15.957494
IBMSNAP_APPPARMS ASN T 2006-11-13-20.57.16.096731
IBMSNAP_SUBS_COLS ASN T 2006-11-13-20.57.15.629765
IBMSNAP_SUBS_EVENT ASN T 2006-11-13-20.57.15.864379
IBMSNAP_SUBS_MEMBR ASN T 2006-11-13-20.57.15.426980
IBMSNAP_SUBS_SET ASN T 2006-11-13-20.57.15.309487
IBMSNAP_SUBS_STMTS ASN T 2006-11-13-20.57.15.755960
在控制表中插入相关数据
控制表创建完成后,我们需要在控制表中插入相关的数据。
创建 registration
首先我们需要注册源表,可以通过 asnclp 程序来完成,下面是 asnclp 语句。
create registration (USER.T_1) differential refresh;
控制表 IBMSNAP_REGISTER 中将会被插入一条记录,它包括相对应的 CD 表名,我们没有指定特殊的 CD 表名,因此默认的表名是 CDT_1, STATE 列值被设置为 I,即 inactive 状态。相对应的 CD 表结构会被同时创建。下面就是 IBMSNAP_REGISTER 表中的部分字段内容及 CD 表的结构。
IBMSNAP_REGISTER:
SOURCE_TABLE CD_TABLE STATE
------------ -------- -----
T_1 CDT_1 I
describe table CDT_1
Column Type Type
name schema name Length Scale Nulls
-------------- ------ --------- ------ --- ----
IBMSNAP_COMMITSEQ SYSIBM CHARACTER 10 0 No
IBMSNAP_INTENTSEQ SYSIBM CHARACTER 10 0 No
IBMSNAP_OPERATION SYSIBM CHARACTER 1 0 No
ID SYSIBM SMALLINT 2 0 No
NAME SYSIBM CHARACTER 20 0 Yes
创建 subscription set
我们也是用 asnclp 程序来创建 subscription。
create subscription set setname SET_TBL applyqual AQ
activate yes timing interval 10
start date "2001-02-02" time "09:00:00.000000";
这条语句指定了 interval 10, 它的意思是每隔 10 分钟,Apply 程序处理一次 subscription set,这条语句运行后,一条记录将会插入控制表 IBMSNAP_SUBS_SET 中,它的 sleep_minutes 值被设置为 10 (10 分钟 ),下面就是此表中的部分字段内容。
APPLY_QUAL SET_NAME SOURCE_SERVER TARGET_SERVER SLEEP_MINUTES
---------- ----------- ----------- --------- ---------
AQ SET_TBL SRCDB TGTDB 10
创建 subscription member
我们用 asnclp 程序来创建 subscription member。
create member in setname SET_TBL applyqual AQ activate yes source USER.T_1
target name USER.T_TGT_1;
这条语句运行时将会执行下面的一些操作:
如果是 subscription set 中第一个 member,一条记录将被插入到控制表 IBMSNAP_PRUNE_SET 中,这条信息将用来剪除 CD 表中已经被复制成功的数据。下面就是此表中的部分字段内容。
TARGET_SERVER APPLY_QUAL SET_NAME SYNCHTIME SYNCHPOINT
-------- ---------- -------- ------------- ------------
TGTDB AQ SET_TBL - x'00000000000000000000'
一条记录将被插入到 IBMSNAP_PRUNCNTL 中,并且会被程序自动分配一个 MAP_ID,下面就是此表中的部分字段内容。
SOURCE_TABLE TARGET_TABLE PHYS_CHANGE_TABLE MAP_ID
------------ ------------ ----------------- ----------
T_1 T_TGT_1 CDT_1 0
一条记录将被插入到 IBMSNAP_SUBS_MEMBR 中,它记录了源表和目标表的对应关系,目标表的表名为 T_TGT_1 。它的 MEMBER_STATE 列将被设置为 N(New),下面就是此表中的部分字段内容。。
SOURCE_TABLE TARGET_TABLE MEMBER_STATE
------------ ------------ ------------
T_1 T_TGT_1 N
针对每个需要复制的数据列,将有一条记录插入到 IBMSNAP_SUBS_COLS 中,它描述了源表中的列和目标表中的列的对应关系。下面就是此表中的部分字段内容。
APPLY_QUAL SET_NAME TARGET_TABLE IS_KEY TARGET_NAME EXPRESSION
--------- ----------- -------- ------ --------- --------
AQ SET_TBL T_TGT_1 Y ID IDAQ SET_TBL T_TGT_1 N NAME NAME
如果目标表不存在,它将在这一步中被同时创建。下面就是目标表的结构描述。
describe table T_TGT_1
Column Type Type name schema name Length Scale Nulls
---------------- -------- ---------- -------- ----- ------
ID SYSIBM SMALLINT 2 0 NoNAME SYSIBM CHARACTER 20 0 Yes
第一次启动 Capture 程序和 Apply 程序
启动 Capture 程序
当第一次启动 Capture 程序时,一条记录将被插入到 IBMSNAP_REGISTER 中,它被设置为 global_record,它的 GLOBAL_RECORD 列被设置成 Y,并且 SYNCHPOINT/SYNCHTIME 列被设置成当前 DB2 log 的点。Capture 程序会读取 IBMSNAP_REGISTER 来查找注册过的源表。一个注册过的源表不会被设置成 active 状态直到 Apply 程序做完 full refresh 之后。下面就是此表中的部分字段内容。
SOURCE_TABLE CD_TABLE STATE SYNCHPOINT SYNCHTIME GLOBAL_RECORD
---------- -------- ----- --------- ----------- ----------
T_1 CDT_1 I - - N - I x'455951AF33A0E7580000'
2006-11-13-21.22.27.158315 Y
在本文的例子当中,我们指定一个特殊的参数 autoprune=n,它的意思是不自动剪除 CD,UOW 和 IBMSNAP_SIGNAL 表中的数据,这样我们就可以更清楚的跟踪数据复制的过程。如果是在实际应用中,请根据具体情况设置此参数。
启动 Apply 程序
启动 Apply 程序时,它会读取 IBMSNAP_SUBS_SET 中的记录,当一个新的 subscription set 的 SYNCHPOINT,SYNCHTIME 和 LASTSUCCESS 为 NULL 时,Apply 程序将进行 full refresh 的操作。
Apply 程序读取 IBMSNAP_SUBS_MEMBR 中的记录,并且更新 IBMSNAP_PRUNCNTL, 设置 SYNCHPOINT=x ’ 00000000000000000000 ’ ,设置 SYNCHTIME = CURRENT TIMESTAMP。
然后,Apply 程序会针对每一个 member 插入一条记录到 IBMSNAP_SIGNAL 中,SIGNAL_TYPE 的值是 CMD, SIGNAL_SUBTYPE 的值是 CAPSTART ,SIGNAL_INPUT_IN 是从表 IBMSNAP_PRUNCNTL 中得到的 MAP_ID,下面就是 IBMSNAP_SIGNAL 中部分字段的内容。
SIGNAL_TIME SIGNAL_TYPE SIGNAL_SUBTYPE SIGNAL_INPUT_IN SIGNAL_LSN
----------- ----------- ----------- ----------- -------------
2006-11-13-21.42.05.251477 CMD CAPSTART 0 x'4559572D0000000F0000'
Apply 程序在默认情况下将使用 export/import 方法来进行 full refresh 操作。它将从源表中 export 数据,然后 import 到目标表中。
当所有的数据复制完成后,Apply 程序会更新 IBMSNAP_SUBS_SET,设置 LASTSUCCESS 和 SYNCHTIME 为当前时间,并且更新 IBMSNAP_SUBS_MEMBR,设置 MEMBER_STATE 为 L(Loaded),意思是目标表中数据已经被 Load 成功。下面就是 IBMSNAP_SUBS_MEMBR 中部分字段的内容。
SOURCE_TABLE TARGET_TABLE MEMBER_STATE
------------ ------------ ------------
T_1 T_TGT_1 L
同步变化的数据
Capture 程序读取变化的数据
当 Capture 程序监测到 IBMSNAP_SIGNAL 中的信号时,它会进行下面的一些操作:
匹配 SIGNAL_INPUT_IN 和 IBMSNAP_PRUNCNTL 表中的 MAP_ID。
更新 IBMSNAP_PRUNCNTL,用 IBMSNAP_SIGNAL 的 LSN (log record sequence number) 来更新 IBMSNAP_PRUNCNTL 中的 SYNCHPOINT 值。用 IBMSNAP_SIGNAL 中的 timestamp 来更新 IBMSNAP_PRUNCNTL 的 SYNCHTIME。
用 SIGNAL 的 LSN 值更新 IBMSNAP_REGISTER 中的 CD_OLD_SYNCHPOINT, 并且设置该记录的 STATE 为 A (active) 状态。
Capture 程序使用 SIGNAL 的 LSN 来作为监控 log 变化的起点。注意这个 LSN 是 Apply 在做 full refresh 之前发出来的, 这就确保了没有数据会被遗漏。
当 Capture 程序监测到一个源表中的数据发生变化时,它会把变化的记录存到内存中。当 Capture 监测到一个数据库的 commit 时,它会把变化的数据插入到 CD 表中。同时在 UOW 表中插入一条记录。当达到了 COMMIT_INTERVAL 时间段后,Capture 会发出它的 commit 信号,这时会更新下面的数据。
针对每一个变化的源表,IBMSNAP_REGISTER 中的 CD_NEW_SYNCHPOINT 会被更新成 UOW 表中最新被记录的 LSN。
IBMSNAP_REGISTER 表中 global_record 的 SYNCHPOINT 也会被更新成 UOW 表中最新被记录的 LSN。
在我们的例子中,我们将对源表进行下面的三个操作,让我们来看一下 CD 表和 UOW 表中的数据。
对源表的操作:
INSERT INTO T_1 VALUES (3, 'C');
UPDATE T_1 SET NAME='modified' WHERE ID=1;
DELETE FROM T_1 WHERE ID=2;
CD 表中的数据:
db2 => select * from CDT_1
IBMSNAP_COMMITSEQ IBMSNAP_INTENTSEQ IBMSNAP_OPERATION ID NAME
------------ ------------ ------------ --- ------------
x'45596719000000010000' x'0000000000000272861D' I 3 C
x'4559671E000000010000' x'00000000000002728831' U 1 modified
x'45596724000000010000' x'00000000000002728A3A' D 2 B
UOW 表中的数据:
select IBMSNAP_UOWID,IBMSNAP_COMMITSEQ,IBMSNAP_LOGMARKER from ASN.IBMSNAP_UOW
IBMSNAP_UOWID IBMSNAP_COMMITSEQ IBMSNAP_LOGMARKER
-------------- --------------- -----------------
x'000000000000000001C7' x'45596719000000010000' 2006-11-13-22.50.01.000000
x'000000000000000001C8' x'4559671E000000010000' 2006-11-13-22.50.06.000000
x'000000000000000001C9' x'45596724000000010000' 2006-11-13-22.50.12.000000
下面是 IBMSNAP_REGISTER 中的数据,我们可以看到它的 SYNCHPOINT 值与 UOW 表中的最新的 IBMSNAP_COMMITSEQ 值是相同的。
SOURCE_TABLE CD_TABLE STATE SYNCHPOINT SYNCHTIME GLOBAL_RECORD
----------- ----------- ------ ----- ---------- ------------
T_1 CDT_1 A - - N
- I x'45596724000000010000' 2006-11-13-22.53.31.545264 Y
需要强调的是,Capture 程序的 COMMIT_INTERVAL 的值是在启动 Capture 程序时指定的,默认值是 30 秒,Capture 程序的 commit 操作不受 Apply 程序的影响。
Apply 程序处理变化的数据
Apply 程序检查 IBMSNAP_SUBS_SET 中 ACTIVATE =1 的 subscription set, 并且计算这个 subscription set 是否符合处理条件。符合处理条件是指当前时间大于 sleep_minutes 加 LASTRUN 的时间。在本文的例子中,sleep_minutes 在创建 subscription set 时被设置成 10 分钟,这主要是让我们有时间能更清楚的跟踪各控制表的状态。如果是在实际应用中,请根据具体情况来设置 sleep_minutes。
当 subscription set 符合处理条件时,Apply 程序会做下面的操作。
读取 IBMSNAP_SUBS_STMTS 表,执行值为 BEFORE 的 SQL 语句。
读取 IBMSNAP_SUBS_MEMBR 和 IBMSNAP_SUBS_COLS。
设置下限为 IBMSNAP_SUBS_SET 表中的 SYNCHPOINT。下面是此表中的部分字段的内容,因为是第一次做同步,所以 SYNCHPOINT 值为空。
APPLY_QUAL SET_NAME SLEEP_MINUTES SYNCHPOINT
----------- ------- ------- ---------------
AQ SET_TBL 10 -
读取 IBMSNAP_REGISTER 中 global record 的 SYNCHPOINT 值,将它作为上限,如果上限和下限相等,就说明没有变化的数据需要处理。下面是此表中部分字段的内容。
SOURCE_TABLE CD_TABLE STATE SYNCHPOINT SYNCHTIME GLOBAL_RECORD
------------ --------- ------ ----- ----------- -------------
T_1 CDT_1 A - - N
- I x'45596724000000010000' 2006-11-13-22.53.31.545264 Y
从 CD 表中选取上限和下限之间的数据。将结果保存到 spill 文件中。
读取 spill 文件并将信息放到目标表中。
运行 IBMSNAP_SUBS_STMTS 中被标为 AFTER 的 SQL 语句。
更新 IBMSNAP_SUBS_SET 表中 SYNCHPOINT 和 SYNCHTIME 列,将其设置为上限的 LSN 和时间戳。下面即是此表中的部分字段的内容,我们可以看到,SYNCHPOINT 已经更新了,与 IBMSNAP_REGISTER 中的值相同。
APPLY_QUAL SET_NAME SLEEP_MINUTES SYNCHPOINT
-------- ----------- ------- ------------
AQ SET_TBL 10 x'45596724000000010000'
将 IBMSNAP_MEMBR 中的 MEMBER_STATE 设置为 S (Synchronized),意思是完成一次同步。
更新 IBMSNAP_PRUNE_SET 表中的 SYNCHPOINT, 将其设置为上限的 LSN。
在 IBMSNAP_APPLYTRAIL 表中插入一条 audit 记录。
Capture 程序剪除控制表中无用的数据
Capture 程序的剪除线程是通过 PRUNE_INTERVAL 和 AUTOPRUNE 参数来控制的。如果 AUTOPRUNE 设置成 NO,则剪除不会自动发生,只能通过 Replication Center 或 asnccmd 命令来发出剪除指令。如果 AUTOPRUNE 设置成 YES,剪除操作将每隔 X 秒进行一次,X 是通过 PRUNE_INTERVAL 来指定。
在上面的小节中已经提到 Apply 程序会更新 IBMSNAP_PRUNE_SET 表中的 SYNCHPOINT。剪除线程就是根据这个值来删除 CD,UOW 和 IBMSNAP_SIGNAL 表中的数据。剪除完成后 IBMSNAP_REGISTER 表中的 CD_OLD_SYNCHPOINT 将被更新为 IBMSNAP_PRUNE_SET 表中的 SYNCHPOINT。
至此,我们阐述了在一次循环中 Capture 程序和 Apply 程序所做的各种操作。需要说明的是这种循环是不断进行的,而且 Capture 程序和 Apply 程序是互不干扰的。Capture 程序可能是每隔 30 秒做一次 commit,Apply 程序可能是每隔 10 分钟处理一次 subscription set。彼此之间不存在先后、包含或等待关系。这一点一定要注意。
结束语
上面我们已对典型的 DB2 到 DB2 的 SQL 数据复制过程中各控制表的工作原理进行了阐述,在 SQL 复制中还有很多其它的类型,例如针对其它数据源 (Oracle,Sybase) 等,其控制表的工作原理可能略有不同,请参考其它相关文档资料。
- ››SQL Server 2008 R2 下如何清理数据库日志文件
- ››数据中心合理布线的10个技巧
- ››sqlite 存取中文的解决方法
- ››SQL2005、2008、2000 清空删除日志
- ››SQL Server 2005和SQL Server 2000数据的相互导入...
- ››sql server 2008 在安装了活动目录以后无法启动服...
- ››sqlserver 每30分自动生成一次
- ››sqlite 数据库 对 BOOL型 数据的插入处理正确用法...
- ››sql server自动生成批量执行SQL脚本的批处理
- ››sql server 2008亿万数据性能优化
- ››SQL Server 2008清空数据库日志方法
- ››sqlserver安装和简单的使用
赞助商链接