copy data from db2 to postgresql

Highlighted
Adventurer

copy data from db2 to postgresql

I am trying to copy data from Db2 to postgresql table. These are identical table and command used is as follows...

 

db2 "export to ${db2pipe} of del modified by nochardel coldel~ timestampformat=\"YYYY-MM-DD HH:MM:SS.UUUUUU\" select * from ${schema}.${tabname} with ur" &

 

psql -d ptcps01d -c \"copy ${schema}.${tabname} from '/tmp/p_pg1' DELIMITERS '~' CSV QUOTE E'\' ' "

 

I tried different combinations but getting different type of error messages ... one of them is as follows... 

 

ERROR: extra data after last expected column
CONTEXT: COPY auth_trans_dtl, line 6496497: "370266014466931 ~2010-05-11 23:39:10.900000~+00000000000000000.~0~+0000000000000.00000~+000000000..."

 

can somebody help me if anyone has moved data from db2 to postgresql. 

Archived Discussions


Effective March 31st, we will no longer engage on PostgresRocks.


How to engage with us further?


  • Thought Leadership: EDB Blogs

  • Tips and Tricks: Postgres Tutorials

  • Customer Support: Create a Case Please note: Only customers with an active EDB support subscription and support portal authorization can create support ticket

  • Engage on Stackoverflow While engaging on Stackoverflow tag the question with EDB or EnterpriseDB.

6 REPLIES 6
Highlighted
Moderator

Re: copy data from db2 to postgresql

The error usually comes when the data does not match the table structure in postgres. Would it be possible to share the table structure and sample data from the exported file.

Highlighted
Level 3 Traveller

Re: copy data from db2 to postgresql

Hi,

 

To my humble opinion, and according to the error, I would check if I have a comma in my data which might be understood by the import as a new column (as it says that it found "extra data after the last expected column"). Try the following:

 

  1. Inspect line #6496497 of your CSV file
  2. Try to export a CSV with the values wrapped in double quotes (or any distinct character) and then import it accordingly (e.g. QUOTE like you did in the COPY command)

In addition, which PostgreSQL version are you using ?

 

Hope this helps a bit,

Doron.

Highlighted
Moderator

Re: copy data from db2 to postgresql

Since there is could be multiple issues with the CSV file, you can try an narrow it down by moving data in small batches. Once you hit the same error and find the cause, you can try and modify the whole CSV file for that issue.

As far as I understand, the problem is with the data and hence it should not matter which version of postgres.

Highlighted
Adventurer

Re: copy data from db2 to postgresql

I think postgresql isn't flexible enough to manipulate copy unlike other DBMS such as db2 or Netezza where bad record can be written to either exception table or badfile. 

The issue here is clearly the null values etc.. Keep in mind the table structure is copied from UDB to postgresql. I am using postgresql 9.5. 

Unfortunately i can not share data to you on this forum. 

Highlighted
Adventurer

Re: copy data from db2 to postgresql

I think the issue here is how to handle NULL values. In CSV file we are using nochardel modifier in db2 which means there won't be any delimiter for character data in csv file. The column delimiter is ~. So if the last record is NULL, how do i tell postgresql that the null value is the last column and there is no more record in that row? 

Highlighted
Moderator

Re: copy data from db2 to postgresql

Not sure but this could be a possible workaround. Would it be possible to specify the coloums that you want to copy from the csv file? Something like::

 

\copy test (test1, test2, test3) FROM '/Users/desktop/test.csv' (FORMAT csv, HEADER, DELIMITER '~');

 

© 2019 EnterpriseDB Corporation. All rights reserved.   |   Privacy Policy   |  Terms of Use   |   Trademarks