Migrating data from DB2 zOS to PostgreSQL

Started by Swapnil Vazeover 9 years ago6 messagesgeneral

Jump to latest

Swapnil Vaze

swapvaze28@gmail.com

over 9 years ago

Hello,

We need some help on how we can migrate data from DB2 zOS database to
postgres database.

Are there any utilities present? Any thoughts how we should approach?

--
Thanks & Regards,
Swapnil Vaze

Julien Rouhaud

rjuju123@gmail.com

over 9 years ago

In reply to: Swapnil Vaze (#1)

Re: Migrating data from DB2 zOS to PostgreSQL

On Tue, Dec 06, 2016 at 11:06:12AM +0530, Swapnil Vaze wrote:

Hello,

Hello

We need some help on how we can migrate data from DB2 zOS database to
postgres database.

Are there any utilities present? Any thoughts how we should approach?

You can use this utility: https://github.com/dalibo/db2topg

The README should provide all needed informations.

--
Julien Rouhaud
http://dalibo.com - http://dalibo.org

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Swapnil Vaze

swapvaze28@gmail.com

over 9 years ago

In reply to: Julien Rouhaud (#2)

Re: Migrating data from DB2 zOS to PostgreSQL

Hello Julien,

We created DDLs from DB2 zOS system and tried to run script in below format:

./db2topg.pl -f sql1.txt -o testdir

It throws below error:

I don't understand <CREATE TABLE "A90DVDT"."DLR_FAM_MRKTSHR_FY_END"

at ./db2topg.pl line 880, <IN> line 24.

For testing we used file with below content:

cat sql1.txt
-- This CLP file was created using DB2LOOK Version "10.5"
-- Timestamp: Tue Dec 6 04:14:28 CST 2016
-- Database Name: DB239
-- Database Manager Version: DB2 Version 11.1.0
-- Database Codepage: 1208

------------------------------------------------
-- DDL Statements for Table "A90DVDT"."DLR_FAM_MRKTSHR_FY_END"
------------------------------------------------

CREATE TABLE "A90DVDT"."DLR_FAM_MRKTSHR_FY_END"
(
"DEALER_ID" CHAR(6) NOT NULL ,
"MKTSHR_MONTH" DATE NOT NULL ,
"L12_DP_DLR_IN_AOR" DECIMAL(15,6) ,
"L12_DP_DLR_OUT_AOR" DECIMAL(15,6) ,
"L12_DP_DLR_SHARE" DECIMAL(8,5) ,
"L12_SA_DLR_SHARE" DECIMAL(8,5) ,
"L12_CA_DLR_SHARE" DECIMAL(8,5) ,
"L12_U90_DLR_IN_AOR" DECIMAL(15,6) ,
"L12_U90_DLR_OUT_AOR" DECIMAL(15,6) ,
"L12_U90_DLR_SHARE" DECIMAL(8,5)
);

Thanks,
Swapnil

On Tue, Dec 6, 2016 at 12:23 PM, Julien Rouhaud <julien.rouhaud@dalibo.com>
wrote:

On Tue, Dec 06, 2016 at 11:06:12AM +0530, Swapnil Vaze wrote:

Hello,

Hello

We need some help on how we can migrate data from DB2 zOS database to
postgres database.

Are there any utilities present? Any thoughts how we should approach?

You can use this utility: https://github.com/dalibo/db2topg

The README should provide all needed informations.

--
Julien Rouhaud
http://dalibo.com - http://dalibo.org

--
Thanks & Regards,
Swapnil Vaze

Sameer Kumar

sameer.kumar@ashnik.com

over 9 years ago

In reply to: Swapnil Vaze (#3)

Re: Migrating data from DB2 zOS to PostgreSQL

On Tue, 6 Dec 2016, 9:27 p.m. Swapnil Vaze, <swapvaze28@gmail.com> wrote:

Hello Julien,

We created DDLs from DB2 zOS system and tried to run script in below
format:

./db2topg.pl -f sql1.txt -o testdir

It throws below error:

I don't understand <CREATE TABLE "A90DVDT"."DLR_FAM_MRKTSHR_FY_END"

at ./db2topg.pl line 880, <IN> line 24.

For testing we used file with below content:

cat sql1.txt
-- This CLP file was created using DB2LOOK Version "10.5"
-- Timestamp: Tue Dec 6 04:14:28 CST 2016
-- Database Name: DB239
-- Database Manager Version: DB2 Version 11.1.0
-- Database Codepage: 1208

------------------------------------------------
-- DDL Statements for Table "A90DVDT"."DLR_FAM_MRKTSHR_FY_END"
------------------------------------------------

CREATE TABLE "A90DVDT"."DLR_FAM_MRKTSHR_FY_END"
(
"DEALER_ID" CHAR(6) NOT NULL ,
"MKTSHR_MONTH" DATE NOT NULL ,
"L12_DP_DLR_IN_AOR" DECIMAL(15,6) ,
"L12_DP_DLR_OUT_AOR" DECIMAL(15,6) ,
"L12_DP_DLR_SHARE" DECIMAL(8,5) ,
"L12_SA_DLR_SHARE" DECIMAL(8,5) ,
"L12_CA_DLR_SHARE" DECIMAL(8,5) ,
"L12_U90_DLR_IN_AOR" DECIMAL(15,6) ,
"L12_U90_DLR_OUT_AOR" DECIMAL(15,6) ,
"L12_U90_DLR_SHARE" DECIMAL(8,5)
);

I will strongly recommend removing the quotes around table name and column
names. Else you may have challenges porting application to PG. As PG by
default refers to object names in small case unless you out quotes around
them.

So after migration a statement like

Select * from DLR_FAM_MRKTSHR_FY_END;

would fail.

Also about the error, see what is before this create table statement. Make
sure there is a semi colon to terminate the statement before the CREATE
table.

How are you running it? psql?

Thanks,
Swapnil

On Tue, Dec 6, 2016 at 12:23 PM, Julien Rouhaud <julien.rouhaud@dalibo.com

wrote:

On Tue, Dec 06, 2016 at 11:06:12AM +0530, Swapnil Vaze wrote:

Hello,

Hello

We need some help on how we can migrate data from DB2 zOS database to
postgres database.

Are there any utilities present? Any thoughts how we should approach?

You can use this utility: https://github.com/dalibo/db2topg

The README should provide all needed informations.

--
Julien Rouhaud
http://dalibo.com - http://dalibo.org

--
Thanks & Regards,
Swapnil Vaze

Best Regards,

*Sameer Kumar | DB Solution Architect*

*ASHNIK PTE. LTD.*

101 Cecil Street, #11-11 Tong Eng Building, Singapore 069533

T: +65 6438 3504 | www.ashnik.com

Skype: sameer.ashnik | T: +65 8110 0350

[image: www.ashnik.com] <http://www.ashnik.com/>

Marc Cousin

cousinmarc@gmail.com

over 9 years ago

In reply to: Sameer Kumar (#4)

Re: Migrating data from DB2 zOS to PostgreSQL

Please, file these as issues on github (i'm the author). It will be much
easier to solve your problems.

Regards

On 06/12/2016 11:42, Sameer Kumar wrote:

On Tue, 6 Dec 2016, 9:27 p.m. Swapnil Vaze, <swapvaze28@gmail.com
<mailto:swapvaze28@gmail.com>> wrote:

Hello Julien,

We created DDLs from DB2 zOS system and tried to run script in below
format:

./db2topg.pl <http://db2topg.pl> -f sql1.txt -o testdir

It throws below error:

I don't understand <CREATE TABLE "A90DVDT"."DLR_FAM_MRKTSHR_FY_END"

at ./db2topg.pl <http://db2topg.pl> line 880, <IN> line 24.

For testing we used file with below content:

cat sql1.txt
-- This CLP file was created using DB2LOOK Version "10.5"
-- Timestamp: Tue Dec 6 04:14:28 CST 2016
-- Database Name: DB239
-- Database Manager Version: DB2 Version 11.1.0
-- Database Codepage: 1208

------------------------------------------------
-- DDL Statements for Table "A90DVDT"."DLR_FAM_MRKTSHR_FY_END"
------------------------------------------------

CREATE TABLE "A90DVDT"."DLR_FAM_MRKTSHR_FY_END"
(
"DEALER_ID" CHAR(6) NOT NULL ,
"MKTSHR_MONTH" DATE NOT NULL ,
"L12_DP_DLR_IN_AOR" DECIMAL(15,6) ,
"L12_DP_DLR_OUT_AOR" DECIMAL(15,6) ,
"L12_DP_DLR_SHARE" DECIMAL(8,5) ,
"L12_SA_DLR_SHARE" DECIMAL(8,5) ,
"L12_CA_DLR_SHARE" DECIMAL(8,5) ,
"L12_U90_DLR_IN_AOR" DECIMAL(15,6) ,
"L12_U90_DLR_OUT_AOR" DECIMAL(15,6) ,
"L12_U90_DLR_SHARE" DECIMAL(8,5)
);

I will strongly recommend removing the quotes around table name and
column names. Else you may have challenges porting application to PG. As
PG by default refers to object names in small case unless you out quotes
around them.

So after migration a statement like

Select * from DLR_FAM_MRKTSHR_FY_END;

would fail.

Also about the error, see what is before this create table statement.
Make sure there is a semi colon to terminate the statement before the
CREATE table.

How are you running it? psql?

Thanks,
Swapnil

On Tue, Dec 6, 2016 at 12:23 PM, Julien Rouhaud
<julien.rouhaud@dalibo.com <mailto:julien.rouhaud@dalibo.com>> wrote:

On Tue, Dec 06, 2016 at 11:06:12AM +0530, Swapnil Vaze wrote:

Hello,

Hello

We need some help on how we can migrate data from DB2 zOS database to
postgres database.

Are there any utilities present? Any thoughts how we should approach?

You can use this utility: https://github.com/dalibo/db2topg

The README should provide all needed informations.

--
Julien Rouhaud
http://dalibo.com - http://dalibo.org

--
Thanks & Regards,
Swapnil Vaze

--

--

Best Regards,

*Sameer Kumar | DB Solution Architect*

*ASHNIK PTE. LTD.*

101 Cecil Street, #11-11 Tong Eng Building, Singapore 069533

T: +65 6438 3504 | www.ashnik.com <http://www.ashnik.com/>

Skype: sameer.ashnik | T: +65 8110 0350

**

www.ashnik.com <http://www.ashnik.com/>

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Noname

bend@linux4ms.net

over 9 years ago

In reply to: Marc Cousin (#5)

Re: Migrating data from DB2 zOS to PostgreSQL

<html><body><span style="font-family:Verdana; color:#000000; font-size:10pt;"><div>We have recently done something similar - Converting Informix zLinux to Postgres x86_64.</div><div>Short answer - We used unloads to put out csv files, wrote some python programs to cleanup data, then</div><div>imported them into our Postgres instance ...<br></div><div><br></div><div><span style="font-size: 8pt;"></span><span style="font-size: 8pt;">Ben Duncan - Business Network Solutions, Inc. 336 Elton Road Jackson MS, 39212<br>"Never attribute to malice, that which can be adequately explained by stupidity"<br>- Hanlon's Razor<br></span></div><div><br><br></div>
<blockquote id="replyBlockquote" webmail="1" style="border-left: 2px solid blue; margin-left: 8px; padding-left: 8px; font-size:10pt; color:black; font-family:verdana;">
<div id="wmQuoteWrapper">
-------- Original Message --------<br>
Subject: [GENERAL] Migrating data from DB2 zOS to PostgreSQL<br>
From: Swapnil Vaze <<a href="mailto:swapvaze28@gmail.com">swapvaze28@gmail.com</a>><br>
Date: Mon, December 05, 2016 11:36 pm<br>
To: <a href="mailto:pgsql-general@postgresql.org">pgsql-general@postgresql.org</a><br>
<br>
<div dir="ltr">Hello,<div><br></div><div>We need some help on how we can migrate data from DB2 zOS database to postgres database.<br clear="all"><div><br></div><div>Are there any utilities present? Any thoughts how we should approach?</div><div><br></div>-- <br><div class="gmail_signature" data-smartmail="gmail_signature"><div>Thanks & Regards,</div> <div>Swapnil Vaze</div></div> </div></div>
</div>
</blockquote></span></body></html>

Import Notes

Resolved by subject fallback