cancel
Showing results for 
Search instead for 
Did you mean: 

copy data from db2 to postgresql

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. 

6 REPLIES 6
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.

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.

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.

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. 

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? 

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 '~');