473,903 Members | 4,863 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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|199 2-07-19 00:00:00|userab cd|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|userab cd|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 7172
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***********@g mail.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|199 2-07-19 00:00:00|userab cd|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***********@g mail.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|199 2-07-19 00:00:00|userab cd|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***********@g mail.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"|"th is 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***********@g mail.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"|"th is 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***********@g mail.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
3928
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 data elements collected may change year over year, which model better takes of this column dynamicness
2
2986
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 data in the form of delimited ASCII format. The separator for each colum is '|'
6
4334
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 column has 10,000 nulls in table A. Select count(*) from table A where col1 is null=10,000 Using the load utlity, I load the data file as follows into table B which has not null constraint on col1: LOAD FROM LOAD.TEST OF ASC MODIFIED BY...
1
5908
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 data type. The load command with 'delprioritychar' option does not seem to help much on the loading process.
5
3416
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 text field. I want to know which Data type I need to use so that it doesnt waste memory. thanks in advance, rAinDeEr
2
9925
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 "data.txt" OF DEL MODIFIED BY COLDEL, METHOD P (1, 2) MESSAGES "/tmp/db2-load-error.log" INSERT INTO test (i,v)";
1
3789
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 tab_name ALTER COLUMN col_name SET DATA TYPE VARCHAR(100) without any problems. However, a simple Java application reports via JDBC that the column type is still VARCHAR FOR BIT DATA.
10
17962
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
514
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 row "F0-1" and column "46" is missing, but the target column is not nullable. TIA
0
10006
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
10882
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
9692
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
7215
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5899
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
6100
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4734
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4316
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3329
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.