472,789 Members | 1,004 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,789 software developers and data experts.

load data into varchar column

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
7 7055
forgot to mention the environment.

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

Please advise.
Jignesh.

Mar 16 '06 #2
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
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
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
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

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
by: mittal.pradeep | last post by:
What is the better table design for a data collection application. 1. Vertical model (pk, attributeName, AttributeValue) 2. Custom columns (pk, custom1, custom2, custom3...custom50) Since the...
2
by: Uthuras | last post by:
Greetings all, Machine : PENTIUM Os :Windows 2000 Product : DB2 UDB EE Release : 7.2 I've extracted data from a RDBMS database and try to load into DB2 database. I extracted the...
6
by: JS | last post by:
EE instance DB2 v7.2.0 fixpack 3 on WIN2K. I select some data from a table A and write it to a file using the COALESCE function and whitespace as the null character: eg coalesce(col1, ' '). This...
1
by: Uthuras | last post by:
Greetings, Machine : Pentium IV Os Windows 2000 server Product : DB2 UDB Release : 7.2 We are fail to load the following data file format into db2 database table that has long varchar...
5
by: rAinDeEr | last post by:
Hi, I have a web application with a table to store terms and conditions of a Company. This may some times run into many pages and some times it may be just a few sentences. It is a character...
2
by: imam888 | last post by:
How do I load "varchar for bit" data in hexadecimal format into a DB2 table ? Table definition =========== create table test ( i integer, v varchar for bit data (16)) db2 "IMPORT FROM...
1
by: RGow | last post by:
Hi, I'd like to know whether there is a way to alter the data type of a VARCHAR FOR BIT DATA column to be simply a VARCHAR column? I executed an ALTER TABLE statement, similar to, ALTER TABLE...
10
by: pbd22 | last post by:
Hi. Like the title says - how do i do this? I was given the following example: INSERT INTO TABLE2 SELECT * FROM TABLE1 WHERE COL1 = 'A' The above statement threw the following error:
2
by: Steve Rainbird | last post by:
Is there way of telling the load utility that if it finds spaces in the input file, where it expects an integer, to insert a zero? Currently I am getting an error. SQL3116W The field value in...
0
by: erikbower65 | last post by:
Using CodiumAI's pr-agent is simple and powerful. Follow these steps: 1. Install CodiumAI CLI: Ensure Node.js is installed, then run 'npm install -g codiumai' in the terminal. 2. Connect to...
0
linyimin
by: linyimin | last post by:
Spring Startup Analyzer generates an interactive Spring application startup report that lets you understand what contributes to the application startup time and helps to optimize it. Support for...
0
by: erikbower65 | last post by:
Here's a concise step-by-step guide for manually installing IntelliJ IDEA: 1. Download: Visit the official JetBrains website and download the IntelliJ IDEA Community or Ultimate edition based on...
0
by: kcodez | last post by:
As a H5 game development enthusiast, I recently wrote a very interesting little game - Toy Claw ((http://claw.kjeek.com/))。Here I will summarize and share the development experience here, and hope it...
0
by: Taofi | last post by:
I try to insert a new record but the error message says the number of query names and destination fields are not the same This are my field names ID, Budgeted, Actual, Status and Differences ...
14
DJRhino1175
by: DJRhino1175 | last post by:
When I run this code I get an error, its Run-time error# 424 Object required...This is my first attempt at doing something like this. I test the entire code and it worked until I added this - If...
0
by: Rina0 | last post by:
I am looking for a Python code to find the longest common subsequence of two strings. I found this blog post that describes the length of longest common subsequence problem and provides a solution in...
0
by: lllomh | last post by:
How does React native implement an English player?
0
by: Mushico | last post by:
How to calculate date of retirement from date of birth

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.