Can I "replicate" from a flat file which changes, but is not a part of
a formal database?

Stanley Sinclair wrote:

> Can I "replicate" from a flat file which changes, but is not a part of
> a formal database?

At first glance, I'd say you can do that using a table function that
presents the file as table to DB2. However, you have to be able to provide
the necessary change data for the CD/CCD tables so that replication knows
which changes did occur and need to be propagated to the target systems.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

The flat file not having a logging mechanism will not have a basis on what
changed. I am thinking, what if you define a nickname in DB2 pointing to
that flat file and try to replicate using Data Propagator that View, one
limitation though is changes should occur in the view. If changes occur
outside DB2 or in the flat file iteself, changes will still not be captured.

Another alternative is just to "refresh" the table every so often, and maybe
use a timestamp field as your where clause so that it only extracts data
with certain timestamps (but you will need to write code that changes that
timestamp like where system time is between time1 and time2). This will sort
of be an incremental "extract" extracting data based on timestamp that was
not yet extracted. This might still be resource intensive though because it
will read the whole table everytime it "extracts" data.

RdR

"Knut Stolze" <stolze@de.ibm.com> wrote in message
news:d0jivh$vt7$1@fsuj29.rz.uni-jena.de...
> Stanley Sinclair wrote:
>
> > Can I "replicate" from a flat file which changes, but is not a part of
> > a formal database?
>
> At first glance, I'd say you can do that using a table function that
> presents the file as table to DB2. However, you have to be able to
provide
> the necessary change data for the CD/CCD tables so that replication knows
> which changes did occur and need to be propagated to the target systems.
>
> --
> Knut Stolze
> Information Integration
> IBM Germany / University of Jena

RdR wrote:

> The flat file not having a logging mechanism will not have a basis on what
> changed. I am thinking, what if you define a nickname in DB2 pointing to
> that flat file and try to replicate using Data Propagator that View, one
> limitation though is changes should occur in the view. If changes occur
> outside DB2 or in the flat file iteself, changes will still not be
> captured.

If you use nicknames or table functions in that scenario doesn't really
matter. The change data is what you need to provide for DB2 Replication.
A full refresh would be an option, too.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

Hi Knut,

What if I define a nickname pointing to that flat file then define the
nickname in Data Propagator? I think the CDC tables and Prunning tables will
be created when you define the nickname in DPROP and any changes done in
DB2, the changes will be sent to the CDC tables during the capture/apply
process. Of course if the change was done outside DB2 it will not be
captured but if the changes are done in DB2 on the nickname, then the change
should go to the CDC tables if that nickname is defined in Data Propagator
as a table to be replicated. I will try this again.

Thanks,

RdR
"Knut Stolze" <stolze@de.ibm.com> wrote in message
news:d0kd68$c4m$1@fsuj29.rz.uni-jena.de...
> RdR wrote:
>
>> The flat file not having a logging mechanism will not have a basis on
>> what
>> changed. I am thinking, what if you define a nickname in DB2 pointing to
>> that flat file and try to replicate using Data Propagator that View, one
>> limitation though is changes should occur in the view. If changes occur
>> outside DB2 or in the flat file iteself, changes will still not be
>> captured.
>
> If you use nicknames or table functions in that scenario doesn't really
> matter. The change data is what you need to provide for DB2 Replication.
> A full refresh would be an option, too.
>
> --
> Knut Stolze
> Information Integration
> IBM Germany / University of Jena

RdR wrote:

> Hi Knut,
>
> What if I define a nickname pointing to that flat file then define the
> nickname in Data Propagator? I think the CDC tables and Prunning tables
> will be created when you define the nickname in DPROP and any changes done
> in DB2, the changes will be sent to the CDC tables during the
> capture/apply process.

For replication from Informix/Oracle sources, that's absolutely correct.
But in those cases, DB2 Replication (Data Propagator) will also create
triggers on the Oracle/Informix table to populate the CCD table with the
change data. Of course, this can't be done in the same way for external
files.

Note that I haven't tried if you could actually register a nickname that
points to an external file as replication source.

> Of course if the change was done outside DB2 it
> will not be captured but if the changes are done in DB2 on the nickname,
> then the change should go to the CDC tables if that nickname is defined in
> Data Propagator as a table to be replicated. I will try this again.

I don't think so. An operation on a nickname is simply passed to the
wrapper and that wrapper sends it on to the remote source. The wrapper
will transform the operation as is needed, i.e. the Oracle wrapper will
build a proper Oracle SQL statement and execute that one.

And you cannot define a trigger on a nickname. The manual says that a
trigger must be defined on "a base table or an alias that resolves to a
base table". So you need some external mechanisms anyway.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

Hi Knut,

You are right, cannot use a non-relational data source such as a flat file
with DPROP (tried replicating a view of the flat file as a nickname). I
discovered further that even if it is defined as a nickname, cannot do
inserts, updates, deletes on a nickname that is originally a non-relational
source (such as a flat file). Seems like a full refresh will be the clearest
option. I will however try to do incremental refreshes by putting a where
clause based on a flag (after a refresh is done, I will change a flag say a
'Y' to an 'N' so the next refresh, I will use this as a flag) or be more
creative using a timestamp field. The Select - Where combiniations supports
non-relational sources.

Thanks,

RdR


"Knut Stolze" <stolze@de.ibm.com> wrote in message
news:d0km83$fve$1@fsuj29.rz.uni-jena.de...
> RdR wrote:
>
> > Hi Knut,
> >
> > What if I define a nickname pointing to that flat file then define the
> > nickname in Data Propagator? I think the CDC tables and Prunning tables
> > will be created when you define the nickname in DPROP and any changes
done
> > in DB2, the changes will be sent to the CDC tables during the
> > capture/apply process.
>
> For replication from Informix/Oracle sources, that's absolutely correct.
> But in those cases, DB2 Replication (Data Propagator) will also create
> triggers on the Oracle/Informix table to populate the CCD table with the
> change data. Of course, this can't be done in the same way for external
> files.
>
> Note that I haven't tried if you could actually register a nickname that
> points to an external file as replication source.
>
> > Of course if the change was done outside DB2 it
> > will not be captured but if the changes are done in DB2 on the nickname,
> > then the change should go to the CDC tables if that nickname is defined
in
> > Data Propagator as a table to be replicated. I will try this again.
>
> I don't think so. An operation on a nickname is simply passed to the
> wrapper and that wrapper sends it on to the remote source. The wrapper
> will transform the operation as is needed, i.e. the Oracle wrapper will
> build a proper Oracle SQL statement and execute that one.
>
> And you cannot define a trigger on a nickname. The manual says that a
> trigger must be defined on "a base table or an alias that resolves to a
> base table". So you need some external mechanisms anyway.
>
> --
> Knut Stolze
> Information Integration
> IBM Germany / University of Jena