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:
- Do not exclude key columns, and do not use COLSEXCEPT to exclude columns that contain unsupported data types.
- COLS should include the column used in KEYCOL in replicat.
