Hi Bobby,
I modified the Extract & Replicat params file(shown below). However, the
replicat Abends with same error for fresh data load. It looks like whenever, I
introduce new Column Name(SOURCE_DB_NAME) it fails, but when I use an existing
column from Source table, it works fine and overwrites the data with Token
Value.
Please suggest work around.
Current Extract Params:-
TABLE XYZ.PCM,tokens(DBNAME1=@GETENV('GGFILEHEADER', 'DBNAME'));
Current Replicat Params:-
MAP XYZ.PCM, TARGET XYZ.PCM, COLMAP(USEDEFAULTS,
SOURCE_DB_NAME=@TOKENS('DBNAME1'));
Replicat Abends with same error message:-
2021-01-19 19:19:33 INFO OGG-15056 The definition for target table XYZ.PCM
is derived from the source table XYZ.PCM.
...(USEDEFAULTS, SOURCE_DB_NAME=@...
^
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*, ObjectMetadataReques
t const&, unsigned int, unsigned int, ggs::gglib::ggmetadata::MetadataContext&,
ggs::gglib::ggmetadata::TableManager&, unsigned int, ggs::gglib::ggmetadata::CT
blMetadata*, ggs::gglib::ggmetadata::CMetadataReader*, bool, bool, bool))]
:
[/goldengatecu/goldengate/ggsolace/replicat(wc_def::resolve_wc_entry(ObjectMetadataRequest
const&, int, ggs::gglib::ggapp::CQualDBO
bjName<(DBObjType)1>*, ggs::gglib::ggmetadata::MetadataContext&,
ggs::gglib::ggmetadata::TableManager&, ggs::gglib::ggmetadata::CTblMetadata*,
ggs::gglib::ggme
tadata::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::CommonLC
R 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::Threa
dArgs*))]
:
[/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-19 19:19:33 ERROR OGG-00919 Error in COLMAP clause.
Best Regards,
Sourav Biswas
+91-9650017306
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of Sourav Biswas <biswas.sourav@xxxxxxxxxxx>
Sent: Tuesday, January 19, 2021 7:17 PM
To: Bobby Curtis <curtisbl@xxxxxxxxx>; Oracle Mailinglist
<oracle-l@xxxxxxxxxxxxx>
Subject: Re: How to replicate additional column using Goldengate Big Data
Adapter?
Hi Bobby,
Thank you for your prompt response. Let me try this out, I will get back with
you shortly.
Best Regards,
Sourav Biswas
+91-9650017306
________________________________
From: Bobby Curtis <curtisbl@xxxxxxxxx>
Sent: Tuesday, January 19, 2021 6:34 PM
To: biswas.sourav@xxxxxxxxxxx <biswas.sourav@xxxxxxxxxxx>; Oracle Mailinglist
<oracle-l@xxxxxxxxxxxxx>
Subject: Re: How to replicate additional column using Goldengate Big Data
Adapter?
Morning/Evening,
In short, TOKENS are going to be your friend with this approach.
Define a Token:
TABLE <table>, TOKENS(<token name> = <token data>);
Looks something like this (in extract):
TABLE XYZ.PCM, TOKENS (DBNAME1=@GETENV(‘GGFILEHEADER’, ‘DBNAME’));
Then in the replicat file:
MAP XYZ.PCM, TARGET XYZ.PCM, COLMAP(USEDEFAULTS, STATUS=@TOKENS(‘DBNAME1’));
This should get what you want out of the adapter.
-Bobby Curtis
bobby.curtis@xxxxxxxxxxxx<mailto:bobby.curtis@xxxxxxxxxxxx>
From: "oracle-l-bounce@xxxxxxxxxxxxx" <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of Sourav Biswas <biswas.sourav@xxxxxxxxxxx>
Reply-To: "biswas.sourav@xxxxxxxxxxx" <biswas.sourav@xxxxxxxxxxx>
Date: Tuesday, January 19, 2021 at 6:39 AM
To: Oracle Mailinglist <oracle-l@xxxxxxxxxxxxx>
Subject: How to replicate additional column using Goldengate Big Data Adapter?
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