goldengate的defgen

 當source/target表結構完全一致時，replicat宣告assumetargetdefs即可，如果不一致，則需指定sourcedefs/targetdefs檔案，其由 defgen生成；

 

什麼時候使用defgen

1  跨資料庫環境，例如從oracle到mysql

2  source/target表定義不一致

Defgen可生成sourcedef和targetdef，前者用於target後者用於source

 

檔案頭包含metadata，檔案體包含表資訊

The file begins with a file header that shows the version of DEFGEN, information about character sets, the database type, the locale, and internal

metadata that indicates other data properties. Following the header are the tabledefinition sections.

Each table-definition section contains a table name, record length,number of columns, and one or more column definitions

詳情可參照DataTypes From Defgen [ID 966190.1]

 

其生命週期如下

配置

Ggsci輸入edit params defgen

--格式如下

CHARSET <character set> --DEFGEN讀取檔案時採用的字符集，預設為local OS字符集

DEFSFILE <full_pathname> [APPEND | PURGE] [CHARSET <character set>]  --指定data-def檔名，charset標註產生的def檔案字符集，預設為local os字符集

[{SOURCEDB | TARGETDB} <dsn>] USERID <user>[, PASSWORD <password> [<encryption options>]]

TABLE <owner>.<table> [, {DEF | TARGETDEF} <template name>]; --指定候選表，而def/targetdef據此表產生def模板

 

執行

defgen paramfile dirprm/defgen.prm [reportfile dirrpt/defgen.rpt] [NOEXTATTR] [UPDATECS UTF-8]

預設DEFGEN使用本地locale寫def檔案，可使用defgen修改def檔案字符集

defgen paramfile ./dirdef/source.def UPDATECS UTF-8

 

傳輸

預設以ASCII模式FTP到remote，如果def檔案以 remote的字符集建立，則使用binary模式，避免出現不必要的換行；

 

例1

建立多個source-def檔案

DEFSFILE C:\ggs\dirdef\sales.def

USERID ogg, PASSWORD AACAAAAAAAAAAAJAUEUGODSCVGJEEIUGKJDJTFNDKEJFFFTC, &

AES128 KEYNAME mykey1

TABLE ord.*;

 

DEFSFILE C:\ggs\dirdef\admin.def

USERID ogg, PASSWORD AACAAAAAAAAAAAJAUEUGODSCVGJEEIUGKJDJTFNDKEJFFFTC, &

AES128 KEYNAME mykey1

TABLE hr.*;

 

REPLICAT acctrep

USERID ogg, PASSWORD AACAAAAAAAAAAAJAUEUGODSCVGJEEIUGKJDJTFNDKEJFFFTC, &

AES128 KEYNAME mykey1

SOURCEDEFS c:\ggs\dirdef\sales.def

MAP ord.*, TARGET ord.*;

SOURCEDEFS c:\ggs\dirdef\admin.def

MAP hr.*, TARGET hr.*;

 

 

Def模板

使用def模板

可被新表使用避免建立額外的def檔案，新表必須有相同的表結構；

否則每次加入新表，必須為之建立def 檔案，然後將內容複製到master def檔案，最後重啟程式

對於新增的表，無須重啟程式或建立新的def檔案

Def指定source-def模板

Targetdef指定target-def模板

 

When you create a definitions file, you can specify a definitions template that reduces the need to create new definitions files when tables are added to the Oracle GoldenGate

configuration after the initial startup.

To use a template, all of the new tables must have identical structures, such as in a customer database where there are separate but identical

tables for each customer (see “Rules for tables to be considered identical”).

If you do not use a template and new tables are added after startup, you must generate a definitions file for each new table that is added to the Oracle GoldenGate configuration,

then copy their contents to the existing master definitions file, and then restart the process.

 

例2

建立source def檔案

DEFSFILE C:\ggs\dirdef\record.def --指定輸出的def檔案

USERID ogg, PASSWORD AACAAAAAAAAAAAJAUEUGODSCVGJEEIUGKJDJTFNDKEJFFFTC, &  --指定資料庫連線資訊

AES128 KEYNAME mykey1

TABLE acct.cust100, DEF custdef;  --依據acct.cust100建立template，供acct下同結構的表使用

TABLE ord.*;

TABLE hr.*;

 

 

replicat引數引用該檔案

REPLICAT acctrep

USERID ogg, PASSWORD AACAAAAAAAAAAAJAUEUGODSCVGJEEIUGKJDJTFNDKEJFFFTC, &

AES128 KEYNAME mykey1

SOURCEDEFS c:\ggs\dirdef\record.def

MAP acct.cust*, TARGET acct.cust*, DEF custdef;

MAP ord.prod, TARGET ord.prod;

MAP ord.parts, TARGET ord.parts;

MAP hr.emp, TARGET hr.emp;

MAP hr.salary, TARGET hr.salary;

 

同時使用assumetargetdefs/sourcedefs

REPLICAT acctrep

USERID ogg, PASSWORD AACAAAAAAAAAAAJAUEUGODSCVGJEEIUGKJDJTFNDKEJFFFTC, &

AES128 KEYNAME mykey1

SOURCEDEFS c:\ggs\dirdef\record.def

MAP acct.cust*, TARGET acct.cust*, DEF custdef;

MAP ord.prod, TARGET ord.prod;

MAP ord.parts, TARGET ord.parts;

MAP hr.emp, TARGET hr.emp;

MAP hr.salary, TARGET hr.salary;

ASSUMETARGETDEFS

MAP rpt.stock, TARGET rpt.stock;