Use Filter,compute and SqlExec commands in Goldengate

This paper mainly introduces the usage of some filtering or computational functions in Ogg, and the basic usage of sqlexec.

sqlpredicate

When using Ogg initialization, you can add this parameter to extract to select records that match the criteria, as described in the Ogg official documentation:

"When initializing data with Ogg, using sqlpredicate is a better option than where or filter. Use this statement to initialize faster than other parameters because it directly acts on the SQL statement, telling Ogg not to take all the data and then filter it (this is how other parameters run), but should only take the necessary parts. ”

As follows

TABLE ggs_owner.emp_details, sqlpredicate "where ename= ' Gavin '";

For the target end of the data filtering, you can still use the Where condition on the REPLICAT data filtering, that is, only take extract out of some of the data for delivery, as follows:

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 for data filtering wherever there is a field conversion, where only the basic operator is used. For example, we can use numeric operators in these 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 in the Ename field that meet only the appropriate criteria, configured as follows:

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

COMPUTE

The next example explains how to use the @compute function, which calculates the values of other fields in the same table based on some original field values.

In this example, the target table EMP is different from the source table structure, and there is more than one field comm on the target table. The value of the Comm field is calculated from the source field sal*1.1. Because the table structure is different on both sides, we need to first create a definition file with the Defgen program.

First, we create the Defgen parameter file on the target side 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 execute it under the OGG installation directory:

[Email protected] goldengate]$ /defgen paramfile./DIRPRM/DEFGEN.PRM

The Replicat parameter file of the target side is defined as follows, which uses the COLMAP and compute. The usedefaults in Colmap tells Ogg that the fields of the source and target tables are automatically matched by name, whereas the Comm field of the target table is derived from the Sal field on the source side.

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));

Based on the above configuration for data synchronization testing, you can see that the Comm field value in the target table is 1.1 times times the value of the Sal field, as follows:

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, for large quantities of data loading, we can first delete the index of the table, until the data is loaded, and then rebuild the index, thereby improving 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= "Sydney"); SqlExec "CREATE index loc_ind on Emp_details";

Use Filter,compute and SqlExec commands in Goldengate