When working with Qlik Replicate, there is a limitation applies to LOB columns:
• When replicating a table that has no Primary Key or Unique Index, LOB columns will not be replicated
This is because Replicate uses SOURCE_LOOKUP function to retrieve the LOB columns thru PK/UI position to the row(s) in source DB table. Hence the PK/UI is mandatory.
Another limitation about ROWID data type:
• As the ROWID data type is not supported, materialized views based on a ROWID column are also not sup...
This article describes how to overcome the above limitations and setup Replicate task to replicate LOB columns if the source table has no Primary Key or Unique Index. The workaround works for Oracle source only as the Oracle materialized view is introduced, and the Oracle internal hidden ROWID Pseudocolumn will be used as PK/UI of the materialized view.
Basically the idea is
(1) Expose hidden column ROWID as a regular column in materialized view (in step 3.)
(2) Change the data type ROWID to CHAR (explicitly, or implicitly) (in step 3.)
(3) Define the ROWID column as the PK/UI of the materialized view (in step 4.)
Then the materialized view can be replicated just as same as a regular Oracle table.
Environment
- Qlik Replicate All supported versions
- Oracle database All supported versions
Resolution
1. Assume there is a table in Oracle source database which has NO Primary Key nor Unique Index. Column "NOTES" is a CLOB column.
CREATE TABLE kitclobnopk (
id integer,
name varchar(20),
notes clob
);
2. Create materialized view log for the above table
create materialized view log on kitclobnopk WITH ROWID including new values;
3. Create materialized view which exposes ROWID hidden column as regular column (alias KITROWID) to Replicate.
CREATE MATERIALIZED VIEW KITCLOBNOPK_MV
REFRESH FAST ON COMMIT
WITH ROWID
AS
select ROWIDTOCHAR(t.rowid) kitrowid, t.id, t.name,t.notes from kitclobnopk t;
The materialized view data refreshes whenever the base master table changes (I/U/D) being committed. The MV can be custom to meet other requirements and add other rich logics etc.
4. Define the column ROWID as the Primary Key of the materialized view
ALTER MATERIALIZED VIEW KITCLOBNOPK_MV ADD CONSTRAINT KITCLOBNOPK_MV_PK PRIMARY KEY (kitrowid);
5. Add the materialized view kitclobnopk_mv to task as same as it's a regular table, the MV meets both Full Load and CDC demand. (NO need to include the original source master table "kitclobnopk" in Qlik Replicate task).
6. Limitations and considerations of the WA
(1) The single UPDATE operation in master table maybe translates to DELETE+INSERT pair operations in materialized view, depends on how the materialized view data refreshes. We may see that in Replicate GUI monitor perspective.
(2) If the ROWID changed in the materialized view (eg ALTER TABLE <tableName> SHRINK SPACE special operations ), then table or task reload requires. Just like the RRN Column in DB400 .
(3) This is only a sample which works in internal labs (sanity test based on Oracle 12c source + Replicate 2022.5). No huge data stress test done, or being verified in a PROD system yet. For implementation and further questions, Professional Service engaged.
Qlik Replicate
