ogg中filter用法

最新推荐文章于 2025-11-16 11:48:13 发布

原创最新推荐文章于 2025-11-16 11:48:13 发布 · 5.8k 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#oracle goldengate

oracle ogg 专栏收录该内容

7 篇文章

订阅专栏

博客讨论了在Oracle GoldenGate中如何使用filter进行字符串类型的更新过滤。在ogg for MySQL中，filter可以成功应用在更新字符串上，但ogg for Oracle则需要特殊配置，如使用@before和@after函数结合动态分辨率来实现相同效果。

官方资料对filter说明：

Valid FILTER clause elements are the following:
  ● An Oracle GoldenGate column-conversion function. These functions are built into
  Oracle GoldenGate so that you can perform tests, manipulate data, retrieve values,
  and so forth. For more information about Oracle GoldenGate conversion functions, see “Testing and transforming data” on page 253.
  ● Numbers
  ● Columns that contain numbers
  ● Functions that return numbers
  ● Arithmetic operators:
    + (plus)
    - (minus)
    * (multiply)
    / (divide)
    \ (remainder)
  ● Comparison operators:
    > (greater than)
    >= (greater than or equal)
    < (less than)
    <= (less than or equal)
    = (equal)
    <> (not equal)
  Results derived from comparisons can be zero (indicating FALSE) or non-zero (indicating
  TRUE).
  ● Parentheses (for grouping results in the expression)
  ● Conjunction operators: AND, OR
Use the following FILTER options to specify which SQL operations a filter clause affects. Any
of these options can be combined.
ON INSERT | ON UPDATE | ON DELETE
IGNORE INSERT | IGNORE UPDATE | IGNORE DELETE

filter支数据类型为number,支持返回为number函数,支持含number的列，支持算术运算，比较运算。

ogg for mysql ,filter on updates string 是生效的：

如下案列：（实现before.value <> after.value 就抽取记录）

Extract  t2
SourceDB TMP, UserID ogg, Password ogg
-- FORMATASCII,TS,DELIMITER '|',  OP,NOQUOTE
TranLogOptions AltLogDest /data/mysql/logs/bin_log/mysql_bin.index
FORMATASCII,CHARSET utf8, DELIMITER '|',IND, TS,NOQUOTE
exttrail  ./dirdat2/t2

-- Table TMP.test,keycols(birth,id,name,),cols(birth,id,name) ;
-- SQLEXEC (SPNAME <sp name>,
-- [ID <logical name,]
-- {PARAMS <param spec> | NOPARAMS})
GETUPDATEBEFORES
NOCOMPRESSUPDATES

-- table TMP.Bill_Del,filter(on updates,@STRCMP(before.Transport_Level,Transport_Level) <> 0 or @STRCMP(before.Transport_State,Transport_State) <> 0 or @
STRCMP(before.Updated_Time,Updated_Time) <> 0 or @STRCMP(before.Transport_staff,Transport_staff) <> 0 or @STRCMP(before.Driver_staff,Driver_staff) <> 0 or @STRCMP(before.V
ehicle_Id,Vehicle_Id) <> 0 or @STRCMP(before.Carrier_Id,Carrier_Id) <> 0 or @STRCMP(before.Carrier_Tdg_Mode,Carrier_Tdg_Mode) <> 0 or @STRCMP(before.Departure_Time,Departu
re_Time) <> 0 ),where (Transport_State='T20' or Transport_State='T30' or Transport_State='T50' or Transport_State='T40') ;

TABLE TMP.Bill_Del,filter(ON UPDATE,@STRCMP(before.Transport_Level,Transport_Level) <> 0 ) ;
-- TABLE TMP.Bill_DeliveryLoading_Hdr_copy,FILTER(ON UPDATES, before.Transport_Level <> Transport_Level) ;

其中使用

TABLE TMP.Bill_Del, FETCHCOLS(Transport_Level),FILTER(ON UPDATES, before.Transport_Level <> Transport_Level)

部分字段是varchar类型，违反filter规则。
故将其改为返回为number函数

TABLE TMP.Bill_Del,filter(ON UPDATE,@STRCMP(before.Transport_Level,Transport_Level) <> 0 ) ;

这样就起到filter的效果.

但是在ogg for oracle ,filter on update string时，同样的配置总是不生效。经过测试改为如下生效了：
extract t1
Dynamicresolution
userid ggs,password ggs
–setenv(ORACLE_SID=OGGSOURCE)

BR BROFF
exttrail d:\ogg\dirdat\t1

– GETUPDATEBEFORES
– GETUPDATEAFTERS
NOCOMPRESSUPDATES

– IGNOREdeletes
– IGNOREinserts

table lmis.test,
– FETCHBEFOREFILTER, FETCHCOLS(c),
FILTER ( ON UPDATE,@STRCMP( @BEFORE(c),@AFTER(c)) <> 0 ),
TOKENS ( bstock= @STRCMP( before.c,c) );
– filter(on update,@strcmp(a,before.a)<> 0 )
– filter( before.a <> a ).

– filter(on update, @STREQ( @before(a),a) <> 0 );

– table lmis.fd_stock,
– FETCHBEFOREFILTER, FETCHCOLS(STOCK_STATUS),
– keycols(lc_code ,owner_no,goods_id,lotno_id,location_id,stock_status, stock_qty,instorehouse_preplus_qty,outstorehouse_preminus_qty),
– filter(on update, @STREQ( @before(STOCK_STATUS),STOCK_STATUS ) = 1 ),
– TOKENS ( bstock= before.STOCK_STATUS ,astock = STOCK_STATUS ) ,
– where( owner_no = ‘FC5’ );

采用了@before与@after函数