Shulou(Shulou.com)06/01 Report--

The previous article mainly introduced the migration of oracle to mysql, mainly the migration of the original table structure, but the actual operation and maintenance staff will find that the split operation of sub-library and sub-table is needed after mysql. At this time, using ogg to do it is also very powerful and easy to use.

Mainly combined with two parameters of ogg

Parameter 1:filter

Use a FILTER clause to select rows based on a numeric value by using basic operators or one or more Oracle GoldenGate column-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 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 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) 10000); Example 2 The following uses the @STREQ function to extract records where a string is equal to 'JOE'.This example assumes that the USEANSISQLQUOTES parameter is used in the GLOBALS parameter file to apply SQL-92 rules for single and double quote marks. TABLE ACCT.TCUSTORD, FILTER (@STREQ ("Name", 'joe') >

0)

Example 3 The following selects records in which the amount column is greater than 50 and executes the filter on updates and deletes.

TABLE ACT.TCUSTORD, FILTER (ON UPDATE, ON DELETE, AMOUNT > 50)

Example 4 You 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 two ranges (between two Replicat processes) based on the ID column of the source acct table.

Note that object names are case-sensitive in this 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 is in the form of a string.

You can omit the @ COMPUTE phrase when returning the value of an arithmetic expression to another Oracle GoldenGate function, 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) 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 17 digits. If this limit is exceeded, @COMPUTE returns an 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, AMT 100,0)

Example 3

ANNUAL_SALARY = @ COMPUTE (MONTHLY_SALARY 12)

The use of the two parameters is described above, and the following is a sub-database and table.

Divide the database and table according to a business id (sale_prod_id)-- this id is not the primary key

Source side: scott.sale_ date table

Target side: multi-database and 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

The extraction and delivery process refers to the previous article, the main change is the map of the application process

Map scott.sale_date,target dumbbell sale0.saletraindateMagol filter (@ compute (sale_prod_id\ 5) = 0)

Map scott.sale_date,target dumbbell sale1.saletraindateMagol filter (@ compute (sale_prod_id\ 5) = 1)

Map scott.sale_date,target dumbbell sale2.saletraindateMagol filter (@ compute (sale_prod_id\ 5) = 2)

Map scott.sale_date,target dumbbell sale3.salecalendar date reporter filter (@ compute (sale_prod_id\ 5) = 3)

Map scott.sale_date,target dumbbell sale4.salecalendar date match (@ compute (sale_prod_id\ 5) = 4)

No problem was found in initialization, and abnormal dml was found out of sync in real-time synchronization.

The final solution is on the source side: add trandata scott.sale_date COLS (sale_prod_id)-where sale_prod_id is the field of the sub-table.

The general reason is that ogg synchronization is mainly based on a primary key or a unique key, and this case table key is not a primary key. Therefore, it is impossible to use this table key to divide the data during synchronization.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.