load data into varchar column

tojigneshshah

Hi,

I am loading data from ascii delimiter file and some of the rows are
getting rejected while loading.

1.0|11487.0|FQ|105061.0|332735.0|01|X.NNIE HATFIELD|1992-06-25
00:00:00|1992-
2.0|13711.0|FQ|111309.0|247556.0|15||1992-05-16 00:00:00|1992-05-28
00:00:00|1992-
3.0|13332.0|FQ|55568.0|197880.0|01|X.LIA DROWS|1992-03-14
00:00:00|1992-04-10 |1992-05-29 00:00:00|1992-07-22
00:00:00|02|1992-07-19 00:00:00|userabcd|FAB|1992-09-02 00:00:00|SYDNY
|DX.|6/6/02 - hearing s
cheduled for 7/22/02 @ 9 am at ERF. Date may have to be changed, but
sent hearing notice. Informed J. matrell that if 7/22 t
urned out to be a conflict for her to let me know and I'd work with
her.\
\
5/29/02 rec'd clmt's authorization for X. Oven to be her representative
and that X. Oven can help J. matrell as the decision..
.. will schedule hearing\
\
info is rec||||userabcd|1992-10-30 10:16:04|userabcd|1993-05-22
17:02:15|
In above data file, there are 3 rows (which starts with 1.0,2,0 and
3.0). First two loading properly while the 3rd rows load only till
'hearing s' and rest of the data loads null in rest of the column.

The column in which the text data goes is defined as varchar(3000)

I am using load from <filename.del> of del modified by coldel| insert
into <tabname>.

Any help would be appreciated.

TIA
Jignesh.

Mar 16 '06 #1

tojigneshshah

forgot to mention the environment.

My env. is AIX5.2, DB2 UDB V8.2 EEE Single partition.

Please advise.
Jignesh.

Mar 16 '06 #2

Knut Stolze

to***********@gmail.com wrote:

Hi,

I am loading data from ascii delimiter file and some of the rows are
getting rejected while loading.

1.0|11487.0|FQ|105061.0|332735.0|01|X.NNIE HATFIELD|1992-06-25
00:00:00|1992-
2.0|13711.0|FQ|111309.0|247556.0|15||1992-05-16 00:00:00|1992-05-28
00:00:00|1992-
3.0|13332.0|FQ|55568.0|197880.0|01|X.LIA DROWS|1992-03-14
00:00:00|1992-04-10 |1992-05-29 00:00:00|1992-07-22
00:00:00|02|1992-07-19 00:00:00|userabcd|FAB|1992-09-02 00:00:00|SYDNY
|DX.|6/6/02 - hearing s

Is there a linebreak here? Then I think that DB2 reads the line up to this
point and loads only that because the end-of-line identifies the
end-of-record.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

Mar 16 '06 #3

tojigneshshah

It seems there is a line break up but i m not sure.

The exported column has data type text (2GB) and target column
varchar(3000). Also tried with DB2's CLOB (2GB) but getting the same
result, ie: rows getting rejected after the backslash. ie: '\' .

My source database is Informix 7.2 and used foll. syntax for unload.
"unload to <filename.del> select * from <tablename>".

And the target datbase is DB2 UDB 8.2EEE and used foll.syntax for load
"load from <filename.del> of del modified by coldel| messages
<messagefile> insert into <tablename> nonrecoverable data buffer 4096"

Please advise.
Jignesh.

Knut Stolze wrote:

to***********@gmail.com wrote:

Hi,

I am loading data from ascii delimiter file and some of the rows are
getting rejected while loading.

1.0|11487.0|FQ|105061.0|332735.0|01|X.NNIE HATFIELD|1992-06-25
00:00:00|1992-
2.0|13711.0|FQ|111309.0|247556.0|15||1992-05-16 00:00:00|1992-05-28
00:00:00|1992-
3.0|13332.0|FQ|55568.0|197880.0|01|X.LIA DROWS|1992-03-14
00:00:00|1992-04-10 |1992-05-29 00:00:00|1992-07-22
00:00:00|02|1992-07-19 00:00:00|userabcd|FAB|1992-09-02 00:00:00|SYDNY
|DX.|6/6/02 - hearing s

Is there a linebreak here? Then I think that DB2 reads the line up to this
point and loads only that because the end-of-line identifies the
end-of-record.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

Mar 16 '06 #4

Ian

to***********@gmail.com wrote:

It seems there is a line break up but i m not sure.

The exported column has data type text (2GB) and target column
varchar(3000). Also tried with DB2's CLOB (2GB) but getting the same
result, ie: rows getting rejected after the backslash. ie: '\' .

My source database is Informix 7.2 and used foll. syntax for unload.
"unload to <filename.del> select * from <tablename>".

And the target datbase is DB2 UDB 8.2EEE and used foll.syntax for load
"load from <filename.del> of del modified by coldel| messages
<messagefile> insert into <tablename> nonrecoverable data buffer 4096"

2 things: DB2 can support newlines in delimited ASCII files using the
"modified by delprioritychar" option, provided the field that has the
line break has the charater delimiter around it. For example, here's
1 record with 3 columns (1 decimal, 2 character fields):

1.0|"hello"|"this is a line
with a break in the middle"

That will load properly into DB2 with "modified by coldel|
delprioritychar" option.
However: Informix is retarded (from a DB2 perspective) in it's unload
routines (I worked on a huge Informix->DB2 conversion and this was a
major issue). It uses a '\' to signify that the record is continued
(a la UNIX standard). In addition, it does not differentiate between a
NULL character string and 0-length character string.

So, you have two options: Write a tool to extract data from Informix
that writes into a normal format, or write a tool to translate the file
from Informix unload format to something DB2 will recognize.

Mar 16 '06 #5

tojigneshshah

Hi Ian,

Thanx for your thorough explanation.

Since you've been involved in major migration task, could you give some
hints or some tools or could you share any of your script? What tool
and which format should be used to get rid of backlash. Will awk/perl
do the needful?.

Please advise.
Ian wrote:

to***********@gmail.com wrote:

It seems there is a line break up but i m not sure.

The exported column has data type text (2GB) and target column
varchar(3000). Also tried with DB2's CLOB (2GB) but getting the same
result, ie: rows getting rejected after the backslash. ie: '\' .

My source database is Informix 7.2 and used foll. syntax for unload.
"unload to <filename.del> select * from <tablename>".

And the target datbase is DB2 UDB 8.2EEE and used foll.syntax for load
"load from <filename.del> of del modified by coldel| messages
<messagefile> insert into <tablename> nonrecoverable data buffer 4096"

2 things: DB2 can support newlines in delimited ASCII files using the
"modified by delprioritychar" option, provided the field that has the
line break has the charater delimiter around it. For example, here's
1 record with 3 columns (1 decimal, 2 character fields):

1.0|"hello"|"this is a line
with a break in the middle"

That will load properly into DB2 with "modified by coldel|
delprioritychar" option.
However: Informix is retarded (from a DB2 perspective) in it's unload
routines (I worked on a huge Informix->DB2 conversion and this was a
major issue). It uses a '\' to signify that the record is continued
(a la UNIX standard). In addition, it does not differentiate between a
NULL character string and 0-length character string.

So, you have two options: Write a tool to extract data from Informix
that writes into a normal format, or write a tool to translate the file
from Informix unload format to something DB2 will recognize.

Mar 16 '06 #6

tojigneshshah

i got the solution using sed.

thanx all of you for your time
regards
Jignesh.

Mar 16 '06 #7

Ian

to***********@gmail.com wrote:

i got the solution using sed.

thanx all of you for your time
regards
Jignesh.

We used perl.

It's somewhat complex, more than just removing a '\' character. I
can't share the original script we wrote, but you need to do the
following (this is from memory from a while back):

1) Remove the '\', and then add character delimiters (" is default)
around the entire multi-line field. And, you need to escape any
" characters that are present in within the field.

2) Because Informix uses '\' to signify that a record continues on the
next line, it will escape '\' characters within a field with '\\'.
These, of course, need to be found and fixed.

3) Non-printable ASCII characters are escaped with a '\', too.

4) If you unload with pipe ('|') as the column delimiter, any fields
with the pipe character will be escaped as '\|'.
I am pretty sure there were more things to consider, but I can't
remember. As I said, Informix unload format is *retarded*, unless you
are loading back into Informix.

As a joke, we named our script 'informFIX.pl'. :-)

Mar 16 '06 #8