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 pump parameter file:

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

 

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 pump parameter file:

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

 

 

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.