topic Re: How does CDC work with IBM DB2 for iSeries as a source? in Qlik Replicate

How does CDC work with IBM DB2 for iSeries as a source?

iti-attunity-sup — Fri, 12 Jul 2024 01:58:19 GMT

I have two questions:

1. How dose QR check for data changes that occur in DB2? For example, in Oracle, I believe you check for SCN increments.

2. After detecting data changes, I assume QR read the journal. How dose QR load the journal at this time?
For instance, QR read the entire portion since the last read in time, or split it into multiple reads, etc.?

Re: How does CDC work with IBM DB2 for iSeries as a source?

john_wang — Fri, 12 Jul 2024 02:09:55 GMT

Hello @iti-attunity-sup ,

Thanks for reaching out to Qlik Community!

>> 1. How dose QR check for data changes that occur in DB2? For example, in Oracle, I believe you check for SCN increments.

Not really. Qlik Replicate parses the Oracle redo log files to capture the changes rather than rely on the SCN increments.

For DB2i, Qlik Replicate call the function QSYS2.Display_Journal() to capture the changes.

>> 2. After detecting data changes, I assume QR read the journal. How dose QR load the journal at this time?

Qlik Replicate call the API QSYS2.Display_Journal(), and the API will read the journal which will get changes from series of journal receivers in the backend.

Hope this helps.

John.

Re: How does CDC work with IBM DB2 for iSeries as a source?

iti-attunity-sup — Fri, 12 Jul 2024 02:19:20 GMT

hello John

Thanks for reply

1.What I want to know is not the actual method of capturing the changes, but how QR detects that changes have occurred.

2.After reading the entire journal once, under what conditions does QR narrow down the journal entries when reading it again? Does QR read all the entries that occurred after the last read at once?

best regards.

Re: How does CDC work with IBM DB2 for iSeries as a source?

john_wang — Fri, 12 Jul 2024 02:29:37 GMT

Hello @iti-attunity-sup ,

Thanks for the clarification.

1. Got it. Yes, QR will use the stream position (which SCN of Oracle, LSN of DB2 is part of the stream position) to determine if new changes coming.

2. QR calling the API once, and will process all the available changes in the journal, until to the end of the journal. Then QR will pause, and startup the processing again : after the retry interval trigger the capture repeatedly (see the setting in endpoint GUI, as below).

Hope this clear the doubts.

Regards,

John.

Re: How does CDC work with IBM DB2 for iSeries as a source?

iti-attunity-sup — Fri, 12 Jul 2024 03:05:42 GMT

hello John

Thanks for reply

Is it correct to say that QR detects data updates by monitoring LSN increments even in the case of IBM DB2 for iSeries?

I am not sure if LSN is a feature in IBM DB2 for iSeries as well.

best regards.

Re: How does CDC work with IBM DB2 for iSeries as a source?

john_wang — Fri, 12 Jul 2024 03:13:54 GMT

Hello @iti-attunity-sup ,

Qlik Replicate use the parameter STARTING_SEQUENCE in QSYS2.Display_Journal() API to locate the position. You may set SOURCE_CAPTURE to Verbose and check the task log file to understand the detailed parameter usage.

Regards,

John.

Re: How does CDC work with IBM DB2 for iSeries as a source?

iti-attunity-sup — Tue, 16 Jul 2024 00:59:07 GMT

Hello John

Thanks for reply.

I understand that for a single read, the position following the previously read journal position is specified in STARTING_SEQUENCE, and it reads until the end.

When is the QSYS2.Display_Journal() API called?

Does it check the sequence every 5 seconds("Check for changes every (sec)") and call the API to read the journal if the sequence has incremented? Or does it call it every 5 seconds regardless?

best regards.

Re: How does CDC work with IBM DB2 for iSeries as a source?

DesmondWOO — Tue, 16 Jul 2024 01:29:18 GMT

Hi @iti-attunity-sup ,

According to my understanding, if there are any further changes recorded in the journal, Replicate will continue reading them. Once all changes have been read, Replicate will wait for 5 seconds before running the API to check the source databases for any new changes.

Regards,
Desmond

Re: How does CDC work with IBM DB2 for iSeries as a source?

iti-attunity-sup — Wed, 17 Jul 2024 00:14:28 GMT

Hello Desmond

Thanks for reply.

Is it correct to say that the QSYS2.Display_Journal() API is called every 5 seconds, except when changes are continuously recorded in the journal?

best regards.

Re: How does CDC work with IBM DB2 for iSeries as a source?

DesmondWOO — Wed, 17 Jul 2024 01:56:34 GMT

Hi @iti-attunity-sup ,

Yes.

- If Replicate finds changes in the journal, it retrieves records using the QSYS2.Display_Journal().

- If Replicate reaches the end of the journal, i.e. no more changes, Replicate will check again after 5 seconds.

Regards,
Desmond

Re: How does CDC work with IBM DB2 for iSeries as a source?

iti-attunity-sup — Wed, 17 Jul 2024 08:29:27 GMT

Hello Desmond

Thanks for reply.

You mentioned "Replicate will check again after 5 seconds." Is QSYS2.Display_Journal() the method used for this check?

What I want to know is, if the method for reading the journal is different from the method for checking whether there are changes in the journal, could you explain how the check is performed?

best regards.

Re: How does CDC work with IBM DB2 for iSeries as a source?

john_wang — Wed, 17 Jul 2024 09:08:24 GMT

Hello @iti-attunity-sup ,

Qlik Replicate calls the API once, and will process all the available changes in the journal, until to the end of the journal. Then Qlik Replicate will sleep, and startup the processing again after the Check for changes every (sec) expires.

thanks,

John.

Re: How does CDC work with IBM DB2 for iSeries as a source?

iti-attunity-sup — Thu, 18 Jul 2024 00:14:02 GMT

Hello John

Thanks for reply.

In other words, am I correct in understanding that Qlik Replicate does not check if there have been any updates to the journal?

best regards.

Re: How does CDC work with IBM DB2 for iSeries as a source?

john_wang — Thu, 18 Jul 2024 00:34:12 GMT

Hello @iti-attunity-sup ,

Seems I did not get your doubt accurately. Could you please elaborate the scenario?

Regards,

John.

Re: How does CDC work with IBM DB2 for iSeries as a source?

iti-attunity-sup — Thu, 18 Jul 2024 02:27:28 GMT

Hello John

Based on our previous exchanges, I understand the following:

Qlik Replicate calls the QSYS2.Display_Journal() API to read the journal.
The portion of the journal to be read is controlled by the STARTING_SEQUENCE parameter.
While Qlik Replicate is reading the journal, if changes continue to be recorded in the journal, it will continue to read until it reaches the end.
After reading to the end of the journal, the next time Qlik Replicate calls the QSYS2.Display_Journal() API, it sets the value of the STARTING_SEQUENCE parameter to the next position from the last read point.

Is my understanding the same as yours so far?

What I want to know is whether Qlik Replicate, after sleeping for the period specified by "Check for changes every (sec)", checks if there have been updates in the journal and calls the QSYS2.Display_Journal() API only if there are updates, or if it calls the API without performing a check.

best regards.

Re: How does CDC work with IBM DB2 for iSeries as a source?

john_wang — Thu, 18 Jul 2024 02:59:31 GMT

Hi @iti-attunity-sup ,

There is not special 'pre-check' step to detect if new changes coming, only the API is called: return changes, or nothing.

Regards,

John.