Trouble with dbexport/dbimport from Windows to Uni
User experienced "Load file has different number of columns" errors when exporting from IDS 9.40 on Windows and importing to IDS 11.70 on Unix. The trailing pipe delimiter on each line was questioned but confirmed as normal. Expert suggested the real issue was likely embedded delimiters/newlines in data records, and recommended using dos2unix to convert Windows CR/LF line endings to Unix LF format.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
We currently have a Win2000 box running IDS 9.40.TC1 which has been having
some issues. We are currently trying to unload the data from the databases on
there to a new UNIX box that currently has IDS 11.70.FC4. We ran a dbexport on
the 9.4 database and used FileZilla using Auto, ASCII, and bin transfers but
we are still getting an error "Load file has different number of columns than
table" and it runs very slowly. I did notice the unload files that the
dbexport created have a "|" at the end of each line. Could this be a bug with
9.4 and not an issue with the file transfer? Or does Informix handle a
dbexport differently on Windows than it does on Unix?
Any suggestions would be appreciated.
Keith Schleicher
IT Database Administrator
Cell: 224-210-8358
Blackberry:
2242108358@messaging.sprintpcs.com<mailto:2242108358@messaging.sprintpcs.com>
Page: 2242108358@sprint.skytel.com<mailto:2242108358@sprint.skytel.com>
For more information, use our DBA Wiki page link below:
http://wiki.intra.sears.com/confluence/display/TechStrag/Database+Management#Dat
abaseManagement<http://wiki.intra.sears.com/confluence/display/TechStrag/Databas
e+Management>
This message, including any attachments, is the property of Sears Holdings
Corporation and/or one of its subsidiaries. It is confidential and may contain
proprietary or legally privileged information. If you are not the intended
recipient, please delete it without reading the contents. Thank you.
Keith,
Informix always includes the trailing delimiter on all platforms. This
message is usually caused by an embedded delimiter or newline or carriage
return character on one of the records in the file that failed.
Art
On Mar 1, 2013 10:22 AM, "Schleicher, Keith" <Keith.Schleicher@searshc.com>
wrote:
> We currently have a Win2000 box running IDS 9.40.TC1 which has been having
> some issues. We are currently trying to unload the data from the databases
> on
> there to a new UNIX box that currently has IDS 11.70.FC4. We ran a
> dbexport on
> the 9.4 database and used FileZilla using Auto, ASCII, and bin transfers
> but
> we are still getting an error "Load file has different number of columns
> than
> table" and it runs very slowly. I did notice the unload files that the
> dbexport created have a "|" at the end of each line. Could this be a bug
> with
> 9.4 and not an issue with the file transfer? Or does Informix handle a
> dbexport differently on Windows than it does on Unix?
>
> Any suggestions would be appreciated.
>
> Keith Schleicher
> IT Database Administrator
> Cell: 224-210-8358
> Blackberry:
> 2242108358@messaging.sprintpcs.com<mailto:
> 2242108358@messaging.sprintpcs.com>
> Page: 2242108358@sprint.skytel.com<mailto:2242108358@sprint.skytel.com>
>
> For more information, use our DBA Wiki page link below:
>
>
>
http://wiki.intra.sears.com/confluence/display/TechStrag/Database+Management#Dat
abaseManagement
> <
> http://wiki.intra.sears.com/confluence/display/TechStrag/Database+Management
> >
>
> This message, including any attachments, is the property of Sears Holdings
> Corporation and/or one of its subsidiaries. It is confidential and may
> contain
> proprietary or legally privileged information. If you are not the intended
> recipient, please delete it without reading the contents. Thank you.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--f46d0408395da9e4f004d6deca46
It seems to be on every line of every file. Is there a way to get this cha=
racter removed? I feel like I did this before and didn't have issues, but =
that was when I was with a different company.
Keith Schleicher
IT Database Administrator
Cell: 224-210-8358
Blackberry: 2242108358@messaging.sprintpcs.com
Page: 2242108358@sprint.skytel.com=A0
For more information, use our DBA Wiki page link below:
http://wiki.intra.sears.com/confluence/display/TechStrag/Database+Managemen=
t#DatabaseManagement
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art K=
agel
Sent: Friday, March 01, 2013 10:38 AM
To: ids@iiug.org
Subject: Re: Trouble with dbexport/dbimport from Window.... [29646]
Keith,=20
Informix always includes the trailing delimiter on all platforms. This mess=
age is usually caused by an embedded delimiter or newline or carriage retur=
n character on one of the records in the file that failed.=20
Art
On Mar 1, 2013 10:22 AM, "Schleicher, Keith" <Keith.Schleicher@searshc.com>
wrote:=20
> We currently have a Win2000 box running IDS 9.40.TC1 which has been=20
> having some issues. We are currently trying to unload the data from=20
> the databases on there to a new UNIX box that currently has IDS=20
> 11.70.FC4. We ran a dbexport on the 9.4 database and used FileZilla=20
> using Auto, ASCII, and bin transfers but we are still getting an error=20
> "Load file has different number of columns than table" and it runs=20
> very slowly. I did notice the unload files that the dbexport created=20
> have a "|" at the end of each line. Could this be a bug with
> 9.4 and not an issue with the file transfer? Or does Informix handle a=20
> dbexport differently on Windows than it does on Unix?
>=20
> Any suggestions would be appreciated.=20
>=20
> Keith Schleicher
> IT Database Administrator
> Cell: 224-210-8358
> Blackberry:=20
> 2242108358@messaging.sprintpcs.com<mailto:=20
> 2242108358@messaging.sprintpcs.com>
> Page:=20
> 2242108358@sprint.skytel.com<mailto:2242108358@sprint.skytel.com>
>=20
> For more information, use our DBA Wiki page link below:=20
>=20
>=20
>=20
http://wiki.intra.sears.com/confluence/display/TechStrag/Database+Managemen=
t#DatabaseManagement=20
> <
> http://wiki.intra.sears.com/confluence/display/TechStrag/Database+Mana
> gement
> >=20
>=20
> This message, including any attachments, is the property of Sears=20
> Holdings Corporation and/or one of its subsidiaries. It is=20
> confidential and may contain proprietary or legally privileged=20
> information. If you are not the intended recipient, please delete it=20
> without reading the contents. Thank you.
>=20
>=20
>=20
>=20
***************************************************************************=
****=20
> Forum Note: Use "Reply" to post a response in the discussion forum.=20
>=20
>=20
--f46d0408395da9e4f004d6deca46=20
***************************************************************************=
****
Forum Note: Use "Reply" to post a response in the discussion forum.=20
This message, including any attachments, is the property of Sears Holdings =
Corporation and/or one of its subsidiaries. It is confidential and may cont=
ain proprietary or legally privileged information. If you are not the inten=
ded recipient, please delete it without reading the contents. Thank you.
Maybe run dos2unix on your unload files to translate CR/LF to LF.
On 3/1/2013 10:52 AM, Schleicher, Keith wrote:
> It seems to be on every line of every file. Is there a way to get this cha=
> racter removed? I feel like I did this before and didn't have issues, but =
> that was when I was with a different company.
>
> Keith Schleicher
> IT Database Administrator
> Cell: 224-210-8358
> Blackberry: 2242108358@messaging.sprintpcs.com
> Page: 2242108358@sprint.skytel.com=A0
>
> For more information, use our DBA Wiki page link below:
> http://wiki.intra.sears.com/confluence/display/TechStrag/Database+Managemen=
> t#DatabaseManagement
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art K=
> agel
> Sent: Friday, March 01, 2013 10:38 AM
> To: ids@iiug.org
> Subject: Re: Trouble with dbexport/dbimport from Window.... [29646]
>
> Keith,=20
>
> Informix always includes the trailing delimiter on all platforms. This mess=
> age is usually caused by an embedded delimiter or newline or carriage retur=
> n character on one of the records in the file that failed.=20
>
> Art
> On Mar 1, 2013 10:22 AM, "Schleicher, Keith" <Keith.Schleicher@searshc.com>
> wrote:=20
>
>> We currently have a Win2000 box running IDS 9.40.TC1 which has been=20
>> having some issues. We are currently trying to unload the data from=20
>> the databases on there to a new UNIX box that currently has IDS=20
>> 11.70.FC4. We ran a dbexport on the 9.4 database and used FileZilla=20
>> using Auto, ASCII, and bin transfers but we are still getting an error=20
>> "Load file has different number of columns than table" and it runs=20
>> very slowly. I did notice the unload files that the dbexport created=20
>> have a "|" at the end of each line. Could this be a bug with
>> 9.4 and not an issue with the file transfer? Or does Informix handle a=20
>> dbexport differently on Windows than it does on Unix?
>> =20
>> Any suggestions would be appreciated.=20
>> =20
>> Keith Schleicher
>> IT Database Administrator
>> Cell: 224-210-8358
>> Blackberry:=20
>> 2242108358@messaging.sprintpcs.com<mailto:=20
>> 2242108358@messaging.sprintpcs.com>
>> Page:=20
>> 2242108358@sprint.skytel.com<mailto:2242108358@sprint.skytel.com>
>> =20
>> For more information, use our DBA Wiki page link below:=20
>> =20
>> =20
>> =20
> http://wiki.intra.sears.com/confluence/display/TechStrag/Database+Managemen=
> t#DatabaseManagement=20
>> <
>> http://wiki.intra.sears.com/confluence/display/TechStrag/Database+Mana
>> gement
>>> =20
>> =20
>> This message, including any attachments, is the property of Sears=20
>> Holdings Corporation and/or one of its subsidiaries. It is=20
>> confidential and may contain proprietary or legally privileged=20
>> information. If you are not the intended recipient, please delete it=20
>> without reading the contents. Thank you.
>> =20
>> =20
>> =20
>> =20
> ***************************************************************************=
> ****=20
>> Forum Note: Use "Reply" to post a response in the discussion forum.=20
>> =20
>> =20
> --f46d0408395da9e4f004d6deca46=20
>
> ***************************************************************************=
> ****
> Forum Note: Use "Reply" to post a response in the discussion forum.=20
>
> This message, including any attachments, is the property of Sears Holdings =
> Corporation and/or one of its subsidiaries. It is confidential and may cont=
> ain proprietary or legally privileged information. If you are not the inten=
> ded recipient, please delete it without reading the contents. Thank you.
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
Hi,
the | at the end of the line is normal (assuming, that
you have not set up a different delimiter character,
as | is the default delimiter character for unload files).
What really could be a problem is that on Windows
you may get the <CR><LF> at the end of the line,
whereas on Linux/UNIX you normally should have
only <LF>.
It may be possible, that the FTP program does not
strip away the <CR> when transferring from Windows
to UNIX/Linux, regadless of the mode. I think I've also
experienced this sometimes.
On UNIX/Linux you can make "strange characters"
(like the <CR>) be seen by using "cat" with
options -evt ... usually the <CR> then shows up
as ^M . (Whereas you may or may not see it in "vi",
because often these days, "vi" on Linux tolerates
the <CR> and doesn't show it ... sometimes you can
guess the existance of the <CR> when "vi" says that
the file is of type "dos".)
If you don't find the utility "dos2unix", you may try to
use "tr", e.g. with a command like:
cat <original_file> | tr -d '\\\\015' > output_file
This should get rid of the <CR> character.
Other than that ... you'll have to count columns
in your table definition as well as the corresponding
unload file.
Regards, Martin
--
Martin Fuerderer
IBM Informix Development Munich, Germany
Information Management
Read about the Informix Warehouse Accelerator:
http://tinyurl.com/the-iwa-blog
IBM Deutschland Research & Development GmbH
Chairman of the Supervisory Board: Martina Koederitz
Board of Management: Dirk Wittkopp
Corporate Seat: Boeblingen, Germany
Reg.-Gericht: Amtsgericht Stuttgart, HRB 243294
From: "Schleicher, Keith" <Keith.Schleicher@searshc.com>
To: ids@iiug.org,
Date: 03/01/2013 16:22
Subject: Trouble with dbexport/dbimport from Windows to.... [29644]
Sent by: ids-bounces@iiug.org
We currently have a Win2000 box running IDS 9.40.TC1 which has been having
some issues. We are currently trying to unload the data from the databases
on
there to a new UNIX box that currently has IDS 11.70.FC4. We ran a
dbexport on
the 9.4 database and used FileZilla using Auto, ASCII, and bin transfers
but
we are still getting an error "Load file has different number of columns
than
table" and it runs very slowly. I did notice the unload files that the
dbexport created have a "|" at the end of each line. Could this be a bug
with
9.4 and not an issue with the file transfer? Or does Informix handle a
dbexport differently on Windows than it does on Unix?
Any suggestions would be appreciated.
Keith Schleicher
IT Database Administrator
Cell: 224-210-8358
Blackberry:
2242108358@messaging.sprintpcs.com<
mailto:2242108358@messaging.sprintpcs.com>
Page: 2242108358@sprint.skytel.com<mailto:2242108358@sprint.skytel.com>
For more information, use our DBA Wiki page link below:
http://wiki.intra.sears.com/confluence/display/TechStrag/Database+Management#Dat
abaseManagement
<
http://wiki.intra.sears.com/confluence/display/TechStrag/Database+Management
>
This message, including any attachments, is the property of Sears Holdings
Corporation and/or one of its subsidiaries. It is confidential and may
contain
proprietary or legally privileged information. If you are not the intended
recipient, please delete it without reading the contents. Thank you.
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
I had the same issue as you and was sort out unloading unl files from linux
directly. Our mistake was copy unl files between windows and linux using
ssh.
Regards
El 01/03/2013 16:22, "Schleicher, Keith" <Keith.Schleicher@searshc.com>
escribió:
> We currently have a Win2000 box running IDS 9.40.TC1 which has been having
> some issues. We are currently trying to unload the data from the databases
> on
> there to a new UNIX box that currently has IDS 11.70.FC4. We ran a
> dbexport on
> the 9.4 database and used FileZilla using Auto, ASCII, and bin transfers
> but
> we are still getting an error "Load file has different number of columns
> than
> table" and it runs very slowly. I did notice the unload files that the
> dbexport created have a "|" at the end of each line. Could this be a bug
> with
> 9.4 and not an issue with the file transfer? Or does Informix handle a
> dbexport differently on Windows than it does on Unix?
>
> Any suggestions would be appreciated.
>
> Keith Schleicher
> IT Database Administrator
> Cell: 224-210-8358
> Blackberry:
> 2242108358@messaging.sprintpcs.com<mailto:
> 2242108358@messaging.sprintpcs.com>
> Page: 2242108358@sprint.skytel.com<mailto:2242108358@sprint.skytel.com>
>
> For more information, use our DBA Wiki page link below:
>
>
>
http://wiki.intra.sears.com/confluence/display/TechStrag/Database+Management#Dat
abaseManagement
> <
> http://wiki.intra.sears.com/confluence/display/TechStrag/Database+Management
> >
>
> This message, including any attachments, is the property of Sears Holdings
> Corporation and/or one of its subsidiaries. It is confidential and may
> contain
> proprietary or legally privileged information. If you are not the intended
> recipient, please delete it without reading the contents. Thank you.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--e89a8ff1c99623554604d6e1be84