I struggled with the same problem, and there does not seem to be a way to
add additional generated virtual columns, but you can inject tokens into
the message. They are not placed inside the column list, there is a
separate section for tokens that you need to read out in with your code.
To inject tokens in goldengate:
TABLE XX.*, TOKENS(COMMIT_TIMESTAMP = @COMPUTE( @NUMSTR(@DATE('CDATE',
'YYYY-MM-DD HH:MI:SS.FFFFFF', @GETENV
('GGHEADER','COMMITTIMESTAMP')))*1000 ));
On Tue, Jan 19, 2021 at 12:39 PM Sourav Biswas <biswas.sourav@xxxxxxxxxxx>
wrote:
Hello All,
How to replicate additional column using Goldengate Big Data Adapter?
Source Database Version: 12.1.0.2
Source Goldengate Version: 12.2.0.2.2
Target Goldengate Big Data Adapter: 19.1.0.0.5
Target Destination: Solace
*Source Table Definition: *
SQL> desc XYZ.PCM
Name Null? Type
----------------------------------------- --------
----------------------------
PAYMENT_CODE NUMBER(10)
PAYMENT_DESCRIPTION VARCHAR2(240)
TYPE_PG_NAME VARCHAR2(240)
GL_CODE NUMBER
CATEGORY VARCHAR2(240)
STATUS VARCHAR2(240)
SETTLEMENT VARCHAR2(240)
COMMISSION NUMBER
SETTLEMENT_DAYS NUMBER
*Replicat Param File: *
REPLICAT REPFXMT1
TARGETDB LIBFILE libggjava.so SET property=dirprm/REPFXMT1.props
REPORTCOUNT EVERY 1 MINUTES, RATE
REPLACEBADCHAR SPACE
--MAP XYZ.PCM, TARGET XYZ.PCM;
--MAP XYZ.PCM, TARGET XYZ.PCM , COLMAP(USEDEFAULTS, DBNAME = @GETENV
('GGFILEHEADER', 'DBNAME'));
MAP XYZ.PCM, TARGET XYZ.PCM, COLMAP(USEDEFAULTS, STATUS = @GETENV
('GGFILEHEADER', 'DBNAME'));
When we try to replicate 9 columns from source to target using “MAP
XYZ.PCM, TARGET XYZ.PCM;” clause, it works fine.
However, when we try to replicate addition column using “MAP XYZ.PCM,
TARGET XYZ.PCM , COLMAP(USEDEFAULTS, DBNAME1 = @GETENV ('GGFILEHEADER',
'DBNAME'));” clause, it fails with below error;
2021-01-18 15:04:38 INFO OGG-15056 The definition for target table
XYZ.PCM is derived from the source table XYZ.PCM.
...(USEDEFAULTS, DBNAME1 =...
^
Error in COLMAP clause. Unrecognized clause or element.
Source Context :
SourceModule : [er.mapping]
SourceID : [er/mapping.cpp]
SourceMethod : [get_map_entry]
SourceLine : [3089]
ThreadBacktrace : [17] elements
:
[/goldengatecu/goldengate/ggsolace/libgglog.so(CMessageContext::AddThreadContext())]
:
[/goldengatecu/goldengate/ggsolace/libgglog.so(CMessageFactory::CreateMessage(CSourceContext*,
unsigned int, ...))]
:
[/goldengatecu/goldengate/ggsolace/libgglog.so(_MSG_String(CSourceContext*,
int, char const*, CMessageFactory::MessageDisposition))]
:
[/goldengatecu/goldengate/ggsolace/replicat(get_map_entry(ggs::gglib::ggunicode::UString
const&, int, wc_def*, ObjectMetadataRequest const&, unsigned int, unsigned
int, ggs::gglib::ggmetadata::MetadataContext&,
ggs::gglib::ggmetadata::TableManager&, unsigned int,
ggs::gglib::ggmetadata::CTblMetadata*,
ggs::gglib::ggmetadata::CMetadataReader*, bool, bool, bool))]
:
[/goldengatecu/goldengate/ggsolace/replicat(wc_def::resolve_wc_entry(ObjectMetadataRequest
const&, int, ggs::gglib::ggapp::CQualDBObjName<(DBObjType)1>*,
ggs::gglib::ggmetadata::MetadataContext&,
ggs::gglib::ggmetadata::TableManager&,
ggs::gglib::ggmetadata::CTblMetadata*,
ggs::gglib::ggmetadata::CMetadataReader*))]
:
[/goldengatecu/goldengate/ggsolace/replicat(WILDCARD_check_table(ObjectMetadataRequest
const&, bool, bool, unsigned int,
ggs::gglib::ggapp::CQualDBObjName<(DBObjType)1>*,
ggs::gglib::ggmetadata::TableManager&,
ggs::gglib::ggmetadata::CTblMetadata*,
ggs::gglib::ggmetadata::CMetadataReader*, bool,
ggs::gglib::gglcr::CommonLCR*))]
:
[/goldengatecu/goldengate/ggsolace/replicat(ggs::er::ERContext::findSourceMetadata(ggs::gglib::ggapp::CQualDBObjName<(DBObjType)1>
const&, int, ggs::gglib::ggmetadata::CTblMetadata*,
ggs::gglib::ggmetadata::CMetadataReader*, bool))]
:
[/goldengatecu/goldengate/ggsolace/replicat(ggs::gglib::ggapp::ReplicationContext::sourceMetadataLookup(ggs::gglib::gglcr::CommonLCR
const*))]
:
[/goldengatecu/goldengate/ggsolace/replicat(ggs::er::ReplicatContext::processReplicatLoop())]
:
[/goldengatecu/goldengate/ggsolace/replicat(ggs::er::ReplicatContext::run())]
: [/goldengatecu/goldengate/ggsolace/replicat()]
:
[/goldengatecu/goldengate/ggsolace/replicat(ggs::gglib::MultiThreading::MainThread::ExecMain())]
:
[/goldengatecu/goldengate/ggsolace/replicat(ggs::gglib::MultiThreading::Thread::RunThread(ggs::gglib::MultiThreading::Thread::ThreadArgs*))]
:
[/goldengatecu/goldengate/ggsolace/replicat(ggs::gglib::MultiThreading::MainThread::Run(int,
char**))]
:
[/goldengatecu/goldengate/ggsolace/replicat(main)]
: [/lib64/libc.so.6(__libc_start_main)]
: [/goldengatecu/goldengate/ggsolace/replicat()]
2021-01-18 15:04:38 ERROR OGG-00919 Error in COLMAP clause.
As a test, we tried to replicate using “MAP XYZ.PCM, TARGET XYZ.PCM,
COLMAP(USEDEFAULTS, STATUS = @GETENV ('GGFILEHEADER', 'DBNAME'));” clause
and it works fine. “Status” is an existing column in source table with
VARCHAR2(240) datatype. When this clause is used, the target “Status”
column is overwritten with ‘DBNAME’ value. This is not a fix, we just
wanted to see whether Goldengate Adapter can replicat token values.
The expectation is to replicate 9 columns from source and apply them to
target along with additional column with token value. Please suggest how to
do this, like is there a way to define this new column datatype to GG
Adapter, or something else.
Best Regards,
Sourav Biswas
+91-9650017306
