How to use Qlik replicate to migrate database to database

[Pradeep099]

We are planning to migrate database from DB2 Z/OS to AWS DB2 LUW. Just want to know is Qlik will support AWS DB2 LUW as target .

[john_wang · Accepted solution]

Hello @Pradeep099 ,

Happy New Year!

Yes, DB2 LUW are supported via general ODBC target endpoint . 

You define an ODBC DSN and then use the DSN in the target endpoint in Replicate.  A sample of in my env:

Hope this helps.

Regards,

John.

[Heinvandenheuvel]

>> We are planning to migrate database

Rig of the bat, Replicate is not well suited for full database migration. Originally is was support, for Oracle only, but it just was not realistic. There are just too many non-data objects in play: Users, Storage, Indexes, Permissions, Roles, 

IF one were to consider a full DB migration it would have to in multiple stages where first all meta data is created; then _perhaps_ have Replicate create all tables, probably empty at first to get partitioning and storage in place; then activate fullload+cdc; then adjust all tables to define and enable constraints, indexes and so on; then hope CDC can keep up with those constraints and such enabled; then cut over.

>>  Just want to know is Qlik will support AWS DB2 LUW as target .

Despite what @john_wang indicates, imho the answer is NO, both pedantically, professionally  and practically.

Pedantically:   AWS DB2 LUW is not listed as a supported target in the support Matrix.

Yes, you can use an ODBC target to load data and changes into a AWS DB2 LUW database, but will you get all datatypes/indexes/constrains/partitions as you need them? no.

Professionally: For the ODBC target, both Support Matrix and UserGuide spell out  "However, unless enabled through Professional Services and approved by your Account Manager, the ODBC target endpoint should not be used."

Using Replicate for such (one time I guess) mission is just not practical. 

I strongly recommend taking this challenge  up with your DB2 consultants.

hth,

Hein.

[john_wang]

Hello @Heinvandenheuvel ,

Totally agree with you and thank you so much for your great insight. Good to know you are around the question too. 

Happy New Year,

John.

[Pradeep099]

Hi @john_wang @Heinvandenheuvel 

Thanks for your response.

We are planning to Migrate entire mainframe database to AWS DB2 LUW and DB size is around 80TB.  Can we use Qlick replication for this and also want to know what are the challenges we might face during this migration.

Thanks,

Pradeep

[Heinvandenheuvel · Accepted solution]

>> Thanks for your response.

You are welcome

>> what are the challenges we might face during this migration.

As indicated before - all the non-data artifacts. Stored procedures, Triggers, Views, Synonym,...

On the data side there may be the 'usual' challenges which are fine to research and resolve once for ongoing replications but excessive effort for a one time migration - bad dates, truncated lobs, odd characters, nulls vs empty, 

And there may be a speed issue as Replicate does not really optimize writing to ODBC targets, so we are looking at one call per row where a DB native/optimized tool can do perhaps 100 times better.

It behooves you to performance test the suggest idea with two or three larger, wider, tables one with LOB's to see if you can even get in the required ballpark.

If you want this to work you need DB2 native tools/scripts/backups/restores to move all the 'structure', and then TRY with replicate to move the data.

I don't think Qlik Replicate is likely to be the best way to approach this, but will be happy to hear I'm wrong.

Good luck.

Hein

[Pradeep099]

Hi @Heinvandenheuvel 

Thanks for your valuable explanation.

Is there any other suitable tool to perform this migration with less manual work ?

Thanks,

Pradeep

[CeliaMM · Accepted solution]

Hi @Pradeep099 ,

As already noted, migrating a database implies migrating an entire ecosystem. You need to migrate the data, translate the table DDLs and other DDLs, internal and external code, permissions, re-route the third-party tools to connect to the new database, etc.

Some time ago, I wrote something to guide how to plan and architect a database migration. It includes the different aspects you should consider and an example of a plan at the bottom. I hope it helps.

For me, Qlik Replicate can help in two aspects during a migration: moving data and translating all or part of the table DDLs. Of course, you should consider the best strategy to move the data in your scenario (waterfall or deltas), and set up Qlik Replicate and the rest of your plan accordingly.

For me, translating table DDLs is the most cumbersome task in a migration. If you find a tool that translates 80% of them, you will spend most of the time manually translating the remaining 20%.

In my opinion, you should evaluate the different aspects of your migration, your business requirements, technical needs and caveats. Then you can define an architecture and plan for your migration. Even though Qlik Replicate may have some caveats for your scenario, it is worth discussing how you can leverage it with your Qlik account team.

My two cents,

Celia

[Pradeep099]

Hi Team,

We were trying to setup Qlik,  seeing below service failed . Please check the attachment for more details. Please help me how to resolve this?

Service Name – areplicate

[/apps/attunity/replicate/bin]# systemctl status areplicate.service
● areplicate.service - SYSV: Attunity Replicate service management
Loaded: loaded (/etc/rc.d/init.d/areplicate; generated)
Active: failed (Result: exit-code) since Wed 2022-02-23 01:28:25 EST; 45min ago
Docs: man:systemd-sysv-generator(8)
Process: 228579 ExecStart=/etc/rc.d/init.d/areplicate start (code=exited, status=126)

Feb 23 01:28:25 ip-10-45-123-190.us.aegon.com systemd[1]: Starting SYSV: Attunity Replicate service management...
Feb 23 01:28:25 ip-10-45-123-190.us.aegon.com areplicate[228579]: /etc/rc.d/init.d/areplicate: line 207: /apps/attunity/replicate/bin/arep_login.sh:>
Feb 23 01:28:25 ip-10-45-123-190.us.aegon.com areplicate[228579]: Failed to start service
Feb 23 01:28:25 ip-10-45-123-190.us.aegon.com systemd[1]: areplicate.service: Control process exited, code=exited status=126
Feb 23 01:28:25 ip-10-45-123-190.us.aegon.com systemd[1]: areplicate.service: Failed with result 'exit-code'.
Feb 23 01:28:25 ip-10-45-123-190.us.aegon.com systemd[1]: Failed to start SYSV: Attunity Replicate service management.

Thanks,

Pradeep

[Heinvandenheuvel]

@Pradeep099 This appears to be an independent question from the base topic with its answer.  It is quit possibly in the context of the same project but still, please consider opening a fresh topic with a fresh subject line representative of the new question perhaps: "Failure to start Replicate service on yxz platform"

Clearly indicate the server type used, the OS and its version, the Replicate version, and the most detailed error message you can find. The attached picture means little or nothing - just showing it once was there, and a partial version with failure to connect.

Since it appears to have worked before, what changed? File/(data) directory locations?File protections? Username locked? The details will be on the Replicate server, perhaps in <replicatedata>/logs/repsrv.log

hth,

Hein