topic Re: Migrate IBM DB2 Db to Mongo DB using Talend. Is it possible? Need advise and pointers in Talend Studio

Migrate IBM DB2 Db to Mongo DB using Talend. Is it possible? Need advise and pointers

Anonymous — Sat, 16 Nov 2024 08:42:44 GMT

I am trying to migrate IBM DB2 tables from a schema, can it dynamically pick tables from Soruce DB and create a collection and build a schema in Mongo DB.

I tried to transfer data from a specific table to mongo db that is working fine if I predefine my schema from DB2, if not no data is transferred

Requirement is like i need to fetch schema and table name dynamically and create a collection name in Mongo db.

Need advise and inputs on its feasibility.

Re: Migrate IBM DB2 Db to Mongo DB using Talend. Is it possible? Need advise and pointers

Anonymous — Tue, 13 Feb 2018 00:50:43 GMT

Wow, DB2. That takes me back. . .waaaaay back

Is there a way in DB2 to retrieve a schema programmatically (i.e. using DB2s query language)? If so, you could use tDB2Row to do it, then tFlowToIterate to iterate over the table names (most likely with tMap or other ETL components in between). I've done this with SQL Server, where I've selected all databases from sys.database, then iterated over them to run the same query against each one.

Hope this helps.

David

Re: Migrate IBM DB2 Db to Mongo DB using Talend. Is it possible? Need advise and pointers

vapukov — Tue, 13 Feb 2018 01:00:12 GMT

generally - not, Talend not a migration tool

You can test some tools like - http://mongify.com if want automate transfer

but generally it also not actual, because - nobody do not migrate data AS/IS

always need prepare some nested MongoDB structure for avoid migrating a lot of tables

Example from some project (without link above):
- MySQL have 100 tables (few huge and many small)
- We are prepare by SQL output JSON with all target structure

- Export result JSONs to txt file
- Import with Mongo tools

Finally we have 4 collection with all data from MySQL

Migrations - it always one time unique projects, and if it not make Your everyday income - no reason to create universal Job, "do and forget" or You spend more time for develop something what never will use in feature

Re: Migrate IBM DB2 Db to Mongo DB using Talend. Is it possible? Need advise and pointers

vapukov — Tue, 13 Feb 2018 01:02:42 GMT

@DVSCHWAB wrote:

Wow, DB2. That takes me back. . .waaaaay back

Hi David!

first of all - DB2 still much more more live then Microsoft want to see

second - it not resolve the problem with dynamic structure

Re: Migrate IBM DB2 Db to Mongo DB using Talend. Is it possible? Need advise and pointers

Anonymous — Tue, 13 Feb 2018 09:24:34 GMT

Well the main idea behind the migration is to create a golden copy of data from IBM DB2 to Mongo DB, so thought Talend would be an option to use it, But for bulk migration and dynamic fetch of tables, I m not sure how to handle it.

Or can we Create a Java Program to connect 2 Databases and run a loop to fetch from One db and insert into another. Will that be efficient in terms of speed?

It would be helpful if i get some pointers to deal with these constraints.

Re: Migrate IBM DB2 Db to Mongo DB using Talend. Is it possible? Need advise and pointers

Anonymous — Tue, 13 Feb 2018 13:47:15 GMT

You could write a Java program, although Talend is basically a Java code generator, so that might be overkill.

As to speed, is this a one-time migration, or something that will happen on a regular basis (i.e. more a copy than a migration)? If it's just one time, your program doesn't need to be efficient just to get the job done.

Can you provide a concrete example of the difficulty you're having right now? I'm not sure what you mean by "dynamic fetch of tables". An estimate of how big the DB2 database is would also be helpful.

David

Re: Migrate IBM DB2 Db to Mongo DB using Talend. Is it possible? Need advise and pointers

vapukov — Tue, 13 Feb 2018 13:53:31 GMT

did You check the link which I provide above?

what size of source database? (number of tables and in Gb)

Re: Migrate IBM DB2 Db to Mongo DB using Talend. Is it possible? Need advise and pointers

Anonymous — Wed, 14 Feb 2018 05:23:57 GMT

An example would be like I have a Database with 20 to 30 schemas and in each schema we have atleast 10 to 20 tables. If using Talend, i would like to create a job to automatically to pick schema and tables from DB2 and create a corresponding database in Mongo DB(Documents and Collections) and migrate data into it.

Hope my requirement is clear.

Re: Migrate IBM DB2 Db to Mongo DB using Talend. Is it possible? Need advise and pointers

Anonymous — Wed, 14 Feb 2018 05:26:08 GMT

Yes I did go through mongify, Need a clarification like will that work for IBM DB2 also. and i want to pick the schema and columns automatically rather than defining it.

Will that be possibile?

Re: Migrate IBM DB2 Db to Mongo DB using Talend. Is it possible? Need advise and pointers

vapukov — Wed, 14 Feb 2018 06:53:28 GMT

what I personally do on similar projects (have few more or less similar)

by SQL or GUI tools - export data to JSON
https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_72/sqlp/rbafyjsonpublishing.htm

or with tool https://dbeaver.jkiss.org - select all tables and export to JSON

2. Using mongoimport, or again tools - import all to MongoDB, for example with Studio3T select all JSON files, and import at once

The main benefits - I never spend for this "problem" more than 1 day (if not required transformations)

An example would be like I have a Database with 20 to 30 schemas and in each schema we have atleast 10 to 20 tables.

40-60 mouse clicks

with moderate size of data - it not a problem at all,

if You have terabytes, need think more, but in any case not depend from way - network is would be bottleneck

on real projects, You have a target model, so You need:

- concatenate few column

- multiple other

- add math, lookup

- etc, etc,etc

but in any of this etc, etc,etc, You must know:

- which columns concatenate

- what columns multiple

- what columns use in Math formulas

- etc, etc, etc

So dynamic schema not very actual, and when need - I know how to resolve

Re: Migrate IBM DB2 Db to Mongo DB using Talend. Is it possible? Need advise and pointers

Anonymous — Wed, 14 Feb 2018 06:59:09 GMT

Thanks for the reply, I will try these steps and let u know how it pans out.

Re: Migrate IBM DB2 Db to Mongo DB using Talend. Is it possible? Need advise and pointers

Anonymous — Thu, 13 Sep 2018 09:28:44 GMT

Hi ,

I am trying to migrate a simple db2 table in mongoDB using talend. Getting an error message in tdb2input component.

Exception in component tDB2Input_1
com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-551, SQLSTATE=42501, SQLERRMC=DB2ADMIN;SELECT;PROFESSIONAL.CUSTOMER, DRIVER=4.13.127
at com.ibm.db2.jcc.am.id.a(id.java:677)
at com.ibm.db2.jcc.am.id.a(id.java:60)
at com.ibm.db2.jcc.am.id.a(id.java:127)
at com.ibm.db2.jcc.am.no.c(no.java:2653)
at com.ibm.db2.jcc.am.no.d(no.java:2641)
at com.ibm.db2.jcc.am.no.a(no.java:2090)
at com.ibm.db2.jcc.t4.cb.h(cb.java:141)
at com.ibm.db2.jcc.t4.cb.b(cb.java:41)
at com.ibm.db2.jcc.t4.q.a(q.java:32)
at com.ibm.db2.jcc.t4.sb.i(sb.java:135)
at com.ibm.db2.jcc.am.no.ib(no.java:2059)
at com.ibm.db2.jcc.am.no.a(no.java:3130)
at com.ibm.db2.jcc.am.no.a(no.java:688)
at com.ibm.db2.jcc.am.no.executeQuery(no.java:672)
at local_project.db2tomongodb_0_1.db2toMongoDB.tDB2Input_1Process(db2toMongoDB.java:964)
at local_project.db2tomongodb_0_1.db2toMongoDB.tMongoDBConnection_1Process(db2toMongoDB.java:402)
at local_project.db2tomongodb_0_1.db2toMongoDB.runJobInTOS(db2toMongoDB.java:1443)
at local_project.db2tomongodb_0_1.db2toMongoDB.main(db2toMongoDB.java:1300)
[statistics] disconnected

Please help me !

0.PNG

Re: Migrate IBM DB2 Db to Mongo DB using Talend. Is it possible? Need advise and pointers

vapukov — Thu, 13 Sep 2018 09:51:36 GMT

You choose wrong forum for ask about DB2 problem 🙂

https://stackoverflow.com/questions/1588687/db2-connection-problem-with-java

http://www-01.ibm.com/support/docview.wss?uid=swg21681677

https://developer.ibm.com/answers/questions/189550/getting-sqlcode-551-sqlstate42501-in-the-log-file/

http://www-01.ibm.com/support/docview.wss?uid=swg22005935