COLS & COLSEXCEPT filter parameters are used to exclude or include(select) few columns of a table from goldengate extraction.
i.e if a table on source has 5 column, and requirement is to replicat only 3 columns, then COLS and COLSEXCEPT filter can be used
These parameters are valid for extract and extract pump only. If we use COLS & COLSEXCEPT in extract, then keyword PASSTHRU need to be added in the pump parameter file. But if we use these in the pump, then no need of using PASSTRHU.

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.