Обсуждение: Copy data from DB2 (Linux) to PG
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.
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.