Table in source schema created in target schema

[Ole_Dufour]

After adding a new transformation to a CDC-only task, I noticed that sometimes a new table is created in the target schema of our Oracle database. This table has the same name and structure as the table defined in the transformation of the CDC task. It is actually a table present in our source database.

What could explain this strange behavior? I would like to emphasize that the behavior is rather random and does not occur every time when changing a Qlik task.

[john_wang]

Hello @Ole_Dufour ,

Thanks for reaching out to Qlik Community!

We need additional information to understand the behavior:

1- what's the source DB type

2- "a new transformation" - is it a global transformation? and what's the transformation type?

3- "sometimes a new table is created in the target schema" - do you include the table in the task by using "Table Selection Patterns"?

4- how the task start up again after the transformation definition? is it RESUME , or else?

thanks,

John.

[SushilKumar]

Hello @Ole_Dufour 

Thanks for reaching out to Qlik Community!

"I noticed that sometimes a new table is created in the target schema of our Oracle database." This table has the same name and structure as the table defined in the transformation of the CDC task. It is actually a table present in our source database. "

Request you check the transformation condition. however, QR Supposed to create table from Source to target to apply changes and whatever transformation you are using based upon the Control tables and on source table columns so it obvious it should inherit Source table columns.

Regards,

Sushil Kumar

[DesmondWOO]

Hi @Ole_Dufour ,

Transformation can involve data manipulation or changes to table structure. Could you tell how you have defined transformation in your task?

If your table was previously created in the target, please check your full load settings. Ensure that the option for "if target table already exists" is set to either "DROP and CREATE table" or "ARCHIVE and CREATE table".

Regards,
Desmond

[Heinvandenheuvel]

Seems to me this is standard behaviour. Replicate is supposed to, and will, create a target table if it does not exist independent of whether the tasks is full-load, CDC or both, typically on startup - or when the table is created on the source.

There are some exceptions/delays. For example for DB2 sources per documentation: "In Batch Optimized Apply mode, when a table that matches the task's table capture pattern is created in the source database (CREATE TABLE), the table will not be created in the target database until a transaction occurs in any of the captured tables."

"sometimes a new table is created". You know that is BS. Replicate doesn't create table when it feels like it. A table is always created given certain conditions. Now it may be you do not yet understand those conditions. Your mission, should you accept it, is to determine what was different in the general circumstance when a table is or isn't created.

Transformations would not influence this, unless perhaps when a) those transformations are of the filter type making a table not needed yet? or b) when the transformation results in a 'dynamic' target table name such as a name = name + yyyymmdd 

Hein.

[Ole_Dufour]

Hi John,
please find below the answers to your questions

1) DB type = Oracle for source and target.
2) I said that the problem occurred after adding a new transformation, but I'm not sure if its the cause of the issue. Any way by adding a transformation I mean adding a target table value based on the source value :

3) Yes, I nclude the table in the task by using "Table Selection Patterns"?

4) No, I always start the task by selecting "reload target", except if it is a new task, then I select "Start processing"

[Ole_Dufour]

Hi Hein,

Thank you for your answer.

It is entirely possible that, as you mentioned, adding transformations is not the origin of the problem. When creating a new Qlik task, sometimes the source table is created in the target schema, and I am unable to reproduce the problem. I understand that nothing happens without a reason and that I might not be providing enough information.

Qlik is supposed to replicate data from a predefined source table into a predefined target table. If you say that is the standard behavior, fair enough, but I do not quite understand the point of that. Each time, we need to delete that table manually…

Regards,

Ole

[Ole_Dufour]

Hi Desmond,

thank you for your answer. Our Full load settings have always been as per below

[john_wang]

Thanks for you update @Ole_Dufour .

I never saw the "Selected Tables" name like <SOURCE SCHEMA>.<SOURCE TABLE> in your screen copy. How you made it? We even cannot reproduce it in our labs 🙂

Regarding the "Table Selection Patterns", the steps are as below:

1. input schema name

2. input the table name prefix, in my sample it's "TEST2", so far all tables which name prefixed by "TEST2" will replicated to target side. After the task startup, if a new such table is created, it will be replicated automatically.

3. press "Include" button

4. The pattern "SCOTT.TEST2%" will be added

5. In above pattern will be appear here - in your sample, you did not use pattern.

6. This is an individual explicitly selected table, SCOTT.KIT, it does not matter to the pattern selection - in your sample, it appears as  <SOURCE SCHEMA>.<SOURCE TABLE>

My question is: how you made it?

thanks,

John.

[Ole_Dufour]

My company doesn't want to disclose the source schema and table name so I manipulated the html dom via F12 just to hide both. I'm sure Hein will be able to explain to you what I did. 😉