Qlik replicate doesn't copy the default values for source Oracle to target MySQL

[desmondchew]

I am running Oracle 11.2.0.4 as source and MySQL 8.0.30 as target database.

The Qlik replicate copies all the data over to the target successfully but leaving behind the default values.

Does Qlik replicate able to set the default values over to the target? Please share the steps if so.

Thank you.

Desmond

[shashi_holla]

Hi @desmondchew 

Could you please check the table DDL in the MySQL database and verify if it has the default clause carried over from the source Oracle. If not then the "Default" DDL is probably not supported and we have to add it manually in the target (MySQL) tables. 

Thank you,

[john_wang · Accepted solution]

Hello @desmondchew ,copy @shashi_holla ,

The "Default" DDL is not supported in current Replicate version yet.

The workaround is that, you may add the Default value in target table.

Hope this helps.

Regards,

John.

[desmondchew]

Hi,

Thank you for your replies. We have over 400+ tables and there are many tables having "default values" assigned to its DDL. The challenge is first to identify the list of tables having default values followed by manually adding default values in the target table.

May I know if there are any other better workaround or methods to expedite this, please?

Thank you.
Desmond

[Michael_Litz]

Hi Desmond,

This may not be applicable for your use case - but in case you are not able to set the defaults in the target database you can still get the values from the source record by using a transformation to look it up from the source.

Please reference this article: 

Transformation: Source Lookup - Oracle ROWID 
https://community.qlik.com/t5/Knowledge/Transformation-Source-Lookup-Oracle-ROWID/ta-p/1805170

Thanks,
Michael

[shashi_holla · Accepted solution]

Hi @desmondchew 

I still think copying Default commands from source DDL is easiest way since we already have it ready in source database. Any of the following options requires more manual effort:

1) For each table we have to do the table transformation for the columns by adding default value in the expression.

2) Add Global rules to "Replace column value" and options are below:

We can choose the best option that might work but we will still end up creating many rules based on how many different column names we have.

Thank you,

[Heinvandenheuvel]

Default values are unlikely to be useful in a pure replication scenario as it would mean the column is NOT being replicated as otherwise it will always have value - actuals, or default as provided by the source DB

Anyway, the answer is NO. A simple no. Replicate is NOT a database clone technology but it can help with that. As documented Replicate's only mission is to transfer row data and it the primary key it needs for that. NO foreign keys, NO constraints, NO defaults, NO nothing, NO storage attributes. NO users, NO synonyms, NO nothing -  Just the hard facts mam!

You are free to add all of that to the target but be sure to choose the 'do nothing' on reload or be ready to redo that otherwise a reload will blow all your work away. 

If you have non-Replicate applications adding rows to the target (making the target no longer a replication of the source(s) then you might desire to set default values or other constraints. It shouldn't be all that hard to create a (SQL) script on the source to generate a SQL script for the target to automate making the desired changes.

Regards,

Hein

[desmondchew]

Thank you for the suggestion on global transformation. As shared by one of the reply, at the end of the day, I will still need to prepare scripts for the affected tables that having default values, altering them set default. 

It would be great if Qlik has the option to allow user to bring over the default values DDL.

Thank you.
Desmond

[Michael_Litz]

The next step would be for you to create a feature request.

To submit Feature Requests going forward please use our Qlik Community "Product Insight and Ideas" forum as these requests will no longer be handled as technical support cases. As a Qlik Community member, you can actively engage with our Product Management team, vote on a product idea coming for other Qlik customers, submit your own ideas and get feedback from other members.

To get started please see our article: "Getting Started with Ideas": https://community.qlik.com/t5/Qlik-Product-Insight-Blog/Getting-Started-with-Ideas/ba-p/1684011

You will be required to have a Qlik ID to logon to the Community which is not the same as your support portal logon. If you have previously registered for a Qlik ID such as one you  use to access the downloads site, you can use the same to logon for the Community. First time accessing Community with a Qlik idea will prompt for a username alias to be used when posting to the Community.  This alias is not a logon but for display purposes when posting. If you do not have a Qlik ID for logon, you can register at the logon screen. The Ideas blog post will provide information on how to use the Ideas board and how to access it.

If you have any issues registering or logging on to the Community, please submit a new case with the issue you are having so we can direct the case to our Customer Service team for assistance.  Please let us know if there is anything else we can do for you at this point as this is no longer the place to submit feature requests.

Thank you,
Michael Litz