Introduction:-  We are going to have look at COLS & COLSEXCEPT FILTER in goldengate.  First, we have mentioned the technical definition of COLS & COLSEXCEPT FILTER. after completing, we are going to learn about the steps use ofCOLS & COLSEXCEPT FILTER in goldengate . These parameters are used to Replicat only specific columns from the Source to Target. Suppose, let us consider a scenario, we have 10 columns in the source and we need only 9 columns to be replicated to the target table. Yes, this is possible using the above parameters.

COLS and COLSEXCEPT control the columns for which data is captured. It means, only data of specific columns are captured and not all the columns. This parameter is valid for Extract Process and should. Upon using this parameter, only the specified columns values are captured by the Extract Process and not all column’s values. COLS This clause specifies the columns whose data needs to be captured. The columns which are used in this COLS clause are captured and the remaining columns will be ignored.

COLS – This is used to include/select set of columns of a table from replication.

COLSEXCEPT – This is used to exclude a set of columns of a table from replication.

EXAMPLE:

Here we will extract only EMP_ID, EMP_NAME, DEPTNO and exclude SALCATGOERY, SERVICE_TYPE.

COLS and COLEXCPET parameter can be defined either in EXTRACT or EXTRACT PUMP PARAMETER.

COLSEXCEPT:

If we are defining COLSEXCEPT parameter in extract parameter, then extract prm file will look as below.

extract parameter file:

EXTRACT EXT1
userid ggate_user, password ggate_user
FETCHOPTIONS FETCHPKUPDATECOLS, USESNAPSHOT, USELATESTVERSION
DBOPTIONS ALLOWUNUSEDCOLUMN
DISCARDFILE /u01/ggate/datacapture/EXT4.dsc, APPEND, MEGABYTES 100
EXTTRAIL /u01/ggate/datacapture/T0
WARNLONGTRANS 6H, CHECKINTERVAL 1H
TABLE APPLIANCE.EMPTAG,COLSEXCEPT (SALCATGOERY,SERVICE_TYPE);

Extract pump parameter file:

As we used COLSEXCEPT filter in the extract, We need to use PASSTHRU parameter in pump parameter file.

EXTRACT EXT1P
userid ggatebss, password ggatebss#123
PASSTHRU
RMTHOST 172.18.83.472, PORT 7893
RMTTRAIL /ggatebss/oradata/TI2/y0
TABLE APPLIANCE.EMPTAG;

But, If we are defining colsexcept parameter in pump parameter, then pump prm file will look as below.i.e no changes to extract file.

Extract parameter file:

EXTRACT EXT1
userid ggate_user, password ggate_user
FETCHOPTIONS FETCHPKUPDATECOLS, USESNAPSHOT, USELATESTVERSION
DBOPTIONS ALLOWUNUSEDCOLUMN
DISCARDFILE /u01/ggate/datacapture/EXT4.dsc, APPEND, MEGABYTES 100
EXTTRAIL /u01/ggate/datacapture/T0
WARNLONGTRANS 6H, CHECKINTERVAL 1H

Extract pump parameter file:

As we used COLSEXCEPT filter in the pump itself, No need to add PASSTHRU parameter.

EXTRACT EXT1P
userid ggatebss, password ggatebss#123
RMTHOST 172.18.83.472, PORT 7893
RMTTRAIL /ggatebss/oradata/TI2/y0
TABLE APPLIANCE.EMPTAG,COLSEXCEPT (SALCATGOERY,SERVICE_TYPE);

COLS:

Instead of COLSEXCEPT, if you are using COLS , then the only change is

COLSEXCEPT (SALCATGOERY,SERVICE_TYPE); – >> COLS(EMP_ID,EMP_NAME,DEPTNO);

RESTRICTIONS:

  1. Do not exclude key columns, and do not use COLSEXCEPT to exclude columns that contain unsupported data types.
  2. COLS should include the column used in KEYCOL in replicat.

 

About The Author

Leave a Reply