data load question

I'm on 11.50.fc3, aix 6 ... I'm trying to load some data into a table from a file created by DB2 for MVS ... The problem is that some of the data contains a CR/LF in DB@, which is ending up as a "new line" in the file on the Unix box ... Does anyone have any suggestions as to how to load this data ... I've tried removing the "new line", but of course that gets rid of all of them, and then the dbload won't work .. I've tried both delimited and fixed length files .. neither work ... Thanks for any suggestions .... Peter Peter Logan Senior Database Administrator Phone: 616/878-8309

Peter, here on our environment, all the troubles of cr/lf where solved, and were troubles from windows to linux, and vice-versa. We solve them using two linux commands: unix2dos, and dos2unix, depending "to" and "from" the conversions are needed. Try there and maybe it removes your unrecognized end-of-lines, ok? Regards! Alexandre Marini Analista de Tecnologia da Informação - DBA SEFAZ-MS / UIMP / Sistemas: Fronteiras e SIG-DW Peter_Logan@spartanstores.com escreveu: > I'm on 11.50.fc3, aix 6 ... I'm trying to load some data into a table > from a file created by DB2 for MVS ... The problem is that some of the > data contains a CR/LF in DB@, which is ending up as a "new line" in the > file on the Unix box ... Does anyone have any suggestions as to how to > load this data ... I've tried removing the "new line", but of course that > gets rid of all of them, and then the dbload won't work .. I've tried both > delimited and fixed length files .. neither work ... > > Thanks for any suggestions .... > > Peter > > Peter Logan > Senior Database Administrator > Phone: 616/878-8309 > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > >

On Fri, Jun 19, 2009 at 07:22, Peter_Logan@spartanstores.com < Peter_Logan@spartanstores.com> wrote: > I'm on 11.50.fc3, aix 6 ... I'm trying to load some data into a table > from a file created by DB2 for MVS ... The problem is that some of the > data contains a CR/LF in DB@, which is ending up as a "new line" in the > file on the Unix box ... Does anyone have any suggestions as to how to > load this data ... I've tried removing the "new line", but of course that > gets rid of all of them, and then the dbload won't work .. I've tried both > delimited and fixed length files .. neither work ... > So, this is a data where there's a CRLF in the middle of a field? What's the type of the field? If it is text, it is probable that you don't want the CR in there at all. Even if you do, the way to continue a field over a newline (LF) is to use backslash-LF. So, the source data will need to be hacked. Or you need a non-standard load tool that recognizes field delimiters and allows unescaped newlines in the middle of a field. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2008.0513 -- http://dbi.perl.org/ "Blessed are we who can laugh at ourselves, for we shall never cease to be amused." NB: Please do not use this email for correspondence. I don't necessarily read it every week, even. George Carlin<http://www.brainyquote.com/quotes/authors/g/george_carlin.html>; - "Electricity is really just organized lightning." --001517491f4e6972a9046cb7a8fb

That's what I figured ... it's a char in Db2 ... Time for someone to manually fix the data ... only 76m rows ... that will be fun .... Thanks ... Peter Logan Senior Database Administrator Phone: 616/878-8309 From: "Jonathan Leffler" <jleffler.iiug@gmail.com> To: ids@iiug.org Date: 06/19/2009 02:26 PM Subject: Re: data load question [16108] Sent by: ids-bounces@iiug.org On Fri, Jun 19, 2009 at 07:22, Peter_Logan@spartanstores.com < Peter_Logan@spartanstores.com> wrote: > I'm on 11.50.fc3, aix 6 ... I'm trying to load some data into a table > from a file created by DB2 for MVS ... The problem is that some of the > data contains a CR/LF in DB@, which is ending up as a "new line" in the > file on the Unix box ... Does anyone have any suggestions as to how to > load this data ... I've tried removing the "new line", but of course that > gets rid of all of them, and then the dbload won't work .. I've tried both > delimited and fixed length files .. neither work ... > So, this is a data where there's a CRLF in the middle of a field? What's the type of the field? If it is text, it is probable that you don't want the CR in there at all. Even if you do, the way to continue a field over a newline (LF) is to use backslash-LF. So, the source data will need to be hacked. Or you need a non-standard load tool that recognizes field delimiters and allows unescaped newlines in the middle of a field. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2008.0513 -- http://dbi.perl.org/ "Blessed are we who can laugh at ourselves, for we shall never cease to be amused." NB: Please do not use this email for correspondence. I don't necessarily read it every week, even. George Carlin< http://www.brainyquote.com/quotes/authors/g/george_carlin.html>; - "Electricity is really just organized lightning." --001517491f4e6972a9046cb7a8fb ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

On Fri, Jun 19, 2009 at 11:38, Peter_Logan@spartanstores.com < Peter_Logan@spartanstores.com> wrote: > That's what I figured ... it's a char in Db2 ... Time for someone to > manually fix the data ... only 76m rows ... that will be fun .... > Famous last words - from a moderately qualified Perl hacker: It shouldn't be dreadfully hard to do in Perl - especially if you can say with certainty how many columns there are in each record (and assuming the data is in a delimited format, since that's all the standard Informix loaders work with). Roughly, the logic is: $target = ...number of fields per record... $data = ""; $n_fields = 0; while ($line = <>) { $l_fields = count number of fields in $line; if ($n_fields + $l_fields == $target) concatenate/munge/print/zero else if ($n_fields + $l_field > $target) throw a hissy fit - there's a major problem else add $line to $data and get the next hunk on the next cycle } check that you've not got anything left over The other option is to investigate HPL - the High-Performance Loader. It can do all sorts of things - but I can't drive it (too lazy) so I'm not certain that it will help. > From: "Jonathan Leffler" <jleffler.iiug@gmail.com> > On Fri, Jun 19, 2009 at 07:22, Peter_Logan@spartanstores.com < > Peter_Logan@spartanstores.com> wrote: > > > I'm on 11.50.fc3, aix 6 ... I'm trying to load some data into a table > > from a file created by DB2 for MVS ... The problem is that some of the > > data contains a CR/LF in DB@, which is ending up as a "new line" in the > > file on the Unix box ... Does anyone have any suggestions as to how to > > load this data ... I've tried removing the "new line", but of course that > > gets rid of all of them, and then the dbload won't work .. I've > tried both > > delimited and fixed length files .. neither work ... > > So, this is a data where there's a CRLF in the middle of a field? > What's the type of the field? If it is text, it is probable that you don't > > want the CR in there at all. Even if you do, the way to continue a field > over a newline (LF) is to use backslash-LF. So, the source data will need > to be hacked. Or you need a non-standard load tool that recognizes field > delimiters and allows unescaped newlines in the middle of a field. > -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2008.0513 -- http://dbi.perl.org/ "Blessed are we who can laugh at ourselves, for we shall never cease to be amused." NB: Please do not use this email for correspondence. I don't necessarily read it every week, even. George Carlin<http://www.brainyquote.com/quotes/authors/g/george_carlin.html>; - "Electricity is really just organized lightning." --0015174c3388926b76046cbbe575