--ogg of Oracle migration to MySQL sub-Library sub-table scheme (goldengate)

Before the article mainly introduced the Oracle migration to MySQL, mainly is the original table original structure migration, but in the actual operation dimension will find, after MySQL needs to divide the library and the table split operation, this time, uses the Ogg to do, is also very powerful to use.

The main combination of OGG 2 parameters

parameter 1:filter
Use a FILTER clause to select rows based in a numeric value by using basic operators or one or more Oracle GoldenGate Colu Mn-conversion functions.
NOTE to filter a column based on a string, use one of the Oracle GoldenGate string
Functions or use a WHERE clause.
Syntax TABLE,
, FILTER (
[, ON INSERT | On update| On DELETE]
[, IGNORE INSERT | IGNORE UPDATE | IGNORE DELETE]
, );
Or ...
Syntax MAP
, TARGET
,
, FILTER (
[, ON INSERT | On update| On DELETE]
[, IGNORE INSERT | IGNORE UPDATE | IGNORE DELETE]
[, RaiseError]
, );
Valid FILTER clause elements is the following:
An Oracle GoldenGate column-conversion function. These functions is built into
Oracle GoldenGate So, can perform tests, manipulate data, retrieve values,
And so forth. For more information on Oracle GoldenGate conversion functions, see
"Testing and transforming data" on page 158.
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 is zero (indicating FALSE) or Non-zero (indicating TRUE).
  Parentheses (for grouping results in the expression)
  conjunction Operators:and, or
  Here are a few examples from the authorities:
  Example 1 The following calls the @COMPUTE function to extract records in which the price multiplied by the amount exceeds 10,000.
  MAP SALES. Tcustord, TARGET SALES. Tord,
  FILTER (@COMPUTE (Product_price*product_amount) > 10000);
  Example 2 The following uses the @STREQ function to extract records where a string was equal to ' JOE '. This example assumes the Useansisqlquotes parameter are used in the GLOBALS parameter file to apply SQL-92 rules for s Ingle and double quote marks.
  TABLE ACCT. Tcustord, FILTER (@STREQ ("Name", ' Joe ') > 0);
  Example 3 The following selects records in which the amount column was greater than and executes the filter on updates a nd deletes.
  TABLE ACT. Tcustord, FILTER (on UPDATE, on DELETE, AMOUNT > 50);
  Example 4 can use the @RANGE function to divide the processing workload among multiple FILTER clauses, using separate TABLE or MAP statements. For example, the following splits the replication workload into II ranges (between, replicat processes) based on the I D column of the source Acct table.
  Note that object names is case-sensitive in the case. (replicat Group 1 parameter file)
  MAP "Sales". " Acct ", TARGET" Sales "." Acct ", FILTER (@RANGE (1, 2, ID));
  (REPLICAT Group 2 parameter file)
  MAP "Sales". " Acct ", TARGET" Sales "." Acct ", FILTER (@RANGE (2, 2, ID));

Parameter 2:compute
Use the @COMPUTE function to return the value of an arithmetic expression to a target column. The value returned from the function was in the form of a string.
Can omit the @COMPUTE phrase when returning the value of a arithmetic expression to another Oracle GoldenGate functio N, as in:
@STRNUM ((AMOUNT1 + AMOUNT2), left)
The preceding returns the same result as:
@STRNUM ((@COMPUTE (AMOUNT1 + AMOUNT2), left)
Arithmetic expressions can be combinations of the following elements.
Numbers
The names of 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 that is derived from comparisons can is zero (indicating FALSE) or Non-zero (indicating TRUE).
  Parentheses (for grouping results in the expression)
  The conjunction operators and, or. Oracle GoldenGate only evaluates the necessary part of a conjunction expression. Once A statement is FALSE, and the rest of the expression is ignored. This can is valuable when evaluating fields, May is missing or null. For example, if the value of COL1 was and the value of COL2 is ten, then the following is possible:
  @COMPUTE (COL1 > 0 and COL2 < 3) returns 0.
  @COMPUTE (COL1 < 0 and COL2 < 3) returns 0. COL2 < 3 is never evaluated.
  @COMPUTE ((COL1 + COL2)/5) returns 7.
  Syntax
  @COMPUTE (expression)
  Expression
  A valid arithmetic expression. The numeric value plus the precision cannot be greater than. If this limit is exceeded, @COMPUTE returns a error similar to the following.
  2013-08-01 01:54:22 Error OGG-01334 error mapping data from column to column in function COMPUTE.
  Examples
  Example 1
  Amount_total = @COMPUTE (AMT + AMT2)
  Example 2
  Amount_total = @IF (Amt >= 0, Amt100, 0)
  Example 3
  Annual_salary = @COMPUTE (monthly_salary12)

The use of 2 parameters is described above, the following sub-database sub-table

Sub-list by a business ID (sale_prod_id)-This ID is not a primary key
SOURCE Side: Scott.sale_date table
Target side: Multi-Library multi-table:
D_sale0.sale_date
D_sale1.sale_date
D_sale2.sale_date
D_sale3.sale_date
D_sale4.sale_date
D_sale5.sale_date

Extract the delivery process reference the previous article, the main change is the application process map
Map Scott.sale_date,target D_sale0.sale_date,filter (@compute (sale_prod_id \ 5) =0);
Map Scott.sale_date,target D_sale1.sale_date,filter (@compute (sale_prod_id \ 5) =1);
Map Scott.sale_date,target D_sale2.sale_date,filter (@compute (sale_prod_id \ 5) =2);
Map Scott.sale_date,target D_sale3.sale_date,filter (@compute (sale_prod_id \ 5) =3);
Map Scott.sale_date,target D_sale4.sale_date,filter (@compute (sale_prod_id \ 5) =4);

Initialization did not find any problems, real-time synchronization found exception DML is out of sync.
The final solution is at the source: add Trandata scott.sale_date COLS (sale_prod_id)----where sale_prod_id is the field to filter
This is due to the fact that the Ogg synchronization is based primarily on the primary key or unique key, which is not a primary key. So it is not possible to use this table key for data sorting when synchronizing.

--ogg of Oracle migration to MySQL sub-Library sub-table scheme (goldengate)