Copy data from DB2 (Linux) to PG

On Thu, Nov 1, 2018 at 10:28 AM Ravi Krishna <srkrishna1@aol.com> wrote:

I have a project to develop a script/tool to copy data from DB2 to PG. The approach I am thinking is

1. Export data from db2 in a text file, with, say pipe as delimiter.
2. Load the data from the text file to PG using COPY command.

I've never used it, but there is this in case it's helpful:
https://github.com/dalibo/db2topg/

In order to make it faster I can parallelize export and load with upto X number of tables concurrently.

Is there a tool in which I can avoid first exporting and then loading. I think one way I can do it is by
using pipes by which I can export from db2 to a pipe and simultaneously load it to PG using COPY.

Any other tool for this job?

Is the DB2 side static or being actively updated?

I've never used it, but there is this in case it's helpful:
https://github.com/dalibo/db2topg/

I looked into it.  I thought it is a schema convertor plus data load.  In other words,
it is one of those one time migration script.  What I need is a constant refresh.
We plan to use it daily to replicate data from db2 to pg. 

On Thu, Nov 1, 2018 at 10:50 AM Ravi Krishna <srkrishna1@aol.com> wrote:

[...] What I need is a constant refresh.
We plan to use it daily to replicate data from db2 to pg.

Perhaps you've already considered and discarded the idea, but your use
case made me think back to when I was looking at AWS SCT as a way to
migrate ourselves off a legacy DB into MySQL. It looks as though it
has support for converting from DB2 to Postgres as well, and if I
recall correctly it had facilities for ongoing replication.

https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Source.DB2LUW.html

On 1/11/18 7:27 μ.μ., Ravi Krishna wrote:

I have a project to develop a script/tool to copy data from DB2 to PG. The approach I am thinking is

1. Export data from db2 in a text file, with, say pipe as delimiter.
2. Load the data from the text file to PG using COPY command.

In order to make it faster I can parallelize export and load with upto X number of tables concurrently.

Is there a tool in which I can avoid first exporting and then loading. I think one way I can do it is by
using pipes by which I can export from db2 to a pipe and simultaneously load it to PG using COPY.

https://pgloader.io/

Any other tool for this job?

thanks.

--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

On 01.11.2018 18:27, Ravi Krishna wrote:

I have a project to develop a script/tool to copy data from DB2 to PG. The approach I am thinking is

1. Export data from db2 in a text file, with, say pipe as delimiter.
2. Load the data from the text file to PG using COPY command.

In order to make it faster I can parallelize export and load with upto X number of tables concurrently.

Is there a tool in which I can avoid first exporting and then loading. I think one way I can do it is by
using pipes by which I can export from db2 to a pipe and simultaneously load it to PG using COPY.

Any other tool for this job?

thanks.

Haven't tried it myself, but you may be able to connect the DB2 database
to your PostgreSQL cluster using this FDW module:
https://github.com/wolfgangbrandl/db2_fdw

Then you could just use INSERT INTO ... SELECT  statements to do the ETL
process with the necessary type conversions and whatnot.

Looks like db2_fdw is DB2 LUW only though, so you might be out of luck
if your DB2 is on IBM i (or z ;-)

Kind regards
Florian

Haven't tried it myself, but you may be able to connect the DB2 database
to your PostgreSQL cluster using this FDW module:
https://github.com/wolfgangbrandl/db2_fdw
Looks like db2_fdw is DB2 LUW only though, so you might be out of luck
if your DB2 is on IBM i (or z ;-)

As the thread indicates, I am using DB2 Linux.
I used Carto one with ODBC and it is just not production ready. Found two bugs within days of
testing. I have created bugs in the github.

Then you could just use INSERT INTO ... SELECT  statements to do the ETL
process with the necessary type conversions and whatnot.

I am currently using Unix pipes to export data from DB2 and concurrently load it to PG via COPY.
It saves nearly half the time of exporting first to file and then loading it.