Use the FILTER, COMPUTE, and sqlexec commands in GoldenGate.

Use the FILTER, COMPUTE, and sqlexec commands in GoldenGate.

This article mainly introduces the usage of some filtering or computing functions in OGG and the basic usage of sqlexec.

SQLPREDICATE

When initializing with OGG, you can add this parameter to extract to select a qualified record. The following is the description in the OGG official document:

"When initializing data with OGG, using SQLPredicate is a better option than where or filter. This statement is faster to initialize than other parameters because it acts directly on the SQL statement and tells OGG not to take all the data and then filter it (this is the running mode of other parameters ), instead, you should only take the required part."

As follows:

TABLE ggs_owner.emp_details, SQLPREDICATE "where ename = 'gavin '";

For data filtering on the target end, you can still use the where Condition on replicat for data filtering, that is, only part of the data from extract can be shipped, as shown below:

MAP ggs_owner.emp_details, TARGET ggs_owner.emp_details, WHERE (ename = "Gavin ");

FILTER

The Filter function is far more powerful than where. You can use it to Filter data wherever field conversion is available, and where can only use basic operators. For example, we can use numeric operators in the functions of OGG (@ COMPUTE, @ DATE, @ STRFIND, @ STRNUM, etc:

'+', '-', '/', '*' Or comparison operator '>', '<', '= '.

In the following configuration example, we use the STRFIND function in extract to capture records that only meet the corresponding conditions in the ename field. The configuration is as follows:

TABLE ggs_owner.emp_details, FILTER (@ STRFIND (ename, "Gavin")> 0 );

COMPUTE

The following example shows how to use the @ COMPUTE function. In this example, the values of other fields in the same table are calculated based on the original field value.

In this example, the target table EMP has a different structure from the source table, and the target table has an extra field COMM. The COMM field value is calculated from the source field SAL * 1.1. Because the table structures on both sides are different, we need to use the defgen program to create a definition file.

First, create the defgen parameter file on the target terminal based on the EMP table:

Edit params defgen

DEFSFILE/home/Oracle/goldengate/dirsql/emp. SQL USERID ggs_owner, PASSWORD ggs_owner TABLE ggs_owner.emp;

Then run the following command in the OGG installation directory:

[Oracle @ linux02 goldengate] $./defgen paramfile./dirprm/defgen. prm

The target replicat parameter file is defined as follows, and colmap and compute are used. UseDefaults in colmap tells OGG that the fields in the source and target tables are automatically matched by name, while the comm field in the target table is calculated by the sal field at the source end.

REPLICAT rep1 USERID ggs_owner, PASSWORD ********* SOURCEDEFS/home/oracle/goldengate/dirsql/emp. SQL MAP ggs_owner.emp_details, TARGET ggs_owner.emp_details, COLMAP (usedefaults, Comm = @ compute (sal + sal *. 10 ));

Test data synchronization based on the above configuration. We can see that the comm field value in the target table is 1.1 times the sal field value, as shown below:

SQL> select * from emp;

EMPNO ENAME DEPTNO SAL COMM

------------------------------------------------------------

1001 Gavin 10 1000 1100

1002 Mark 20 2000 2200

1003 John 30 3000 3300

SQLEXEC

SQLEXEC can be used in extract or replicat to execute SQL statements, stored procedures, or SQL functions. For example, to load a large volume of data, we can first Delete the index of the table. After the data is loaded, we can re-create the index to improve the performance of data synchronization. In the following replicat example, you can see a similar configuration example:

REPLICAT rep1 USERID ggs_owner, PASSWORD ggs_owner ASSUMETARGETDEFS Sqlexec "drop index loc_ind "; MAP ggs_owner.emp_details, TARGET ggs_owner.emp_details, WHERE (location = "sysydney "); Sqlexec "create index loc_ind on emp_details (location )";