473,219 Members | 2,007 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

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

unable to load data into long varchar field

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.

DDL structure
---------------
-----------------------------------------------
-- DDL Statements for table LAB .RESULTT
------------------------------------------------

CREATE TABLE LAB.RESULTT2 (
REQUEST INTEGER NOT NULL ,
PANEL VARCHAR(3) NOT NULL ,
TIEBREAKER INTEGER NOT NULL ,
TEXT_REPORT LONG VARCHAR)
IN LISUSRTBLSPACE_2 ;

-- DDL Statements for primary key on Table LAB .RESULTT

ALTER TABLE LAB.RESULTT2
ADD PRIMARY KEY
(REQUEST,
PANEL,
TIEBREAKER);
Data File
---------
50394|HPE| 0|ENCAP\
2\
rec\
rtf\
pnoraini\
6\
3503\
{\\rtf1\\ansi\\ansicpg1252\\uc1
\\deff0\\deflang1033\\deflangfe1033{\\fonttbl{\\f0 \\froman\\fcharset0\\fprq2{\\*\\panose
02020603050405020304}Times New
Roman;}{\\f2\\fmodern\\fcharset0\\fprq1{\\*\\panos e
02070309020205020404}Courier New;}}{\\colortbl;\\red0\\green0\\blue0;
\
\\red0\\green0\\blue255;\\red0\\green255\\blue255; \\red0\\green255\\blue0;\\red255\\green0\\blue255; \\red255\\green0\\blue0;\\red255\\green255\\blue0; \\red255\\green255\\blue255;\\red0\\green0\\blue12 8;\\red0\\green128\\blue128;\\red0\\green128\\blue 0;\\red128\\green0\\blue128;
\
\\red128\\green0\\blue0;\\red128\\green128\\blue0; \\red128\\green128\\blue128;\\red192\\green192\\bl ue192;}{\\stylesheet{\\nowidctlpar\\widctlpar\\adj ustright
\\fs20\\lang2057\\cgrid \\snext0 Normal;}{\\*\\cs10 \\additive Default
Paragraph Font;}{
\
\\s15\\nowidctlpar\\widctlpar\\adjustright \\f2\\fs20\\lang2057\\cgrid
\\sbasedon0 \\snext15 Plain Text;}}{\\info{\\title
HISTOPATHOLOGY}{\\author PATHOLOGY18}{\\operator
PATHOLOGY18}{\\creatim\\yr2002\\mo8\\dy12\\hr8\\mi n41}{\\revtim\\yr2002\\mo8\\dy12\\hr8\\min41}{\\ve rsion2}
\
{\\edmins0}{\\nofpages1}{\\nofwords0}{\\nofchars0} {\\*\\company
PATHOLOGY DEPT}{\\nofcharsws0}{\\vern71}}\\paperw11906\\pape rh16838\\margl1152\\margr1152
\\widowctrl\\ftnbj\\aenddoc\\hyphcaps0\\formshade\ \viewkind4\\viewscale85\\pgbrdrhead\\pgbrdrfoot
\\fet0\\sectd
\
\\linex0\\endnhere\\sectdefaultcl
{\\*\\pnseclvl1\\pnucrm\\pnstart1\\pnindent720\\pn hang{\\pntxta
..}}{\\*\\pnseclvl2\\pnucltr\\pnstart1\\pnindent72 0\\pnhang{\\pntxta
..}}{\\*\\pnseclvl3\\pndec\\pnstart1\\pnindent720\ \pnhang{\\pntxta
..}}{\\*\\pnseclvl4
\
\\pnlcltr\\pnstart1\\pnindent720\\pnhang{\\pntxta
)}}{\\*\\pnseclvl5\\pndec\\pnstart1\\pnindent720\\ pnhang{\\pntxtb
(}{\\pntxta )}}{\\*\\pnseclvl6\\pnlcltr\\pnstart1\\pnindent720 \\pnhang{\\pntxtb
(}{\\pntxta )}}{\\*\\pnseclvl7\\pnlcrm\\pnstart1\\pnindent720\ \pnhang{\\pntxtb
(}
\
{\\pntxta )}}{\\*\\pnseclvl8\\pnlcltr\\pnstart1\\pnindent720 \\pnhang{\\pntxtb
(}{\\pntxta )}}{\\*\\pnseclvl9\\pnlcrm\\pnstart1\\pnindent720\ \pnhang{\\pntxtb
(}{\\pntxta )}}\\pard\\plain
\\s15\\qc\\nowidctlpar\\widctlpar\\adjustright
\\f2\\fs20\\lang2057\\cgrid {\\b\\fs24\\ul
\
HISTOPATHOLOGY
\
\\par }\\pard \\s15\\nowidctlpar\\widctlpar\\adjustright {\\b\\fs24
\
\\par R 6755/2002
\
\\par
\
\\par }{\\fs24 MACROSCOPIC:
\
\\par
\
\\par }\\pard \\s15\\qj\\nowidctlpar\\widctlpar\\adjustright {\\fs24
Received 1 paraffin block, four H&E stained slides and 10 slides
stained for Vimentin, Cytokeratin, CD 30, LCA, T-cell, B-cell, NSE,
Chromogranin and HMB45.
\
\\par }\\pard \\s15\\nowidctlpar\\widctlpar\\adjustright {\\fs24
\
\\par
\
\\par MICROSCOPIC:
\
\\par
\
\\par }\\pard \\s15\\qj\\nowidctlpar\\widctlpar\\adjustright {\\fs24
Lymph node. There is marked effacement
\
of its architecture. The lymph node is largely infiltrated by plump
spindle shaped or epithelioid cells arranged in nests separated by
fine and coarse collagen bundles. The cells contain clear or
amhophilic cytoplasm having rounded or ovoid vesicular nu
\
clei with prominent nucleoli.
\
\\par Immunohistochemistry studies show the tumour cells are strongly
immunoreactive to Vimentin and S100, focally stroma immunoreactive to
HMB45, equivocal with NSE and Desmin but non-reactive to CK and Mic2.
\
\\par }\\pard \\s15\\nowidctlpar\\widctlpar\\adjustright {\\fs24
\
\\par
\
\\par DIAGNOSIS:
\
\\par
\
\\par Lymph node: Metastatic Tumour.
\
\\par Morphological features and immunohistochemistry
\
\\par studies are suggestive of Clear cell Sarcoma of
\
\\par Soft Tissue (Melanoma of Soft Parts).
\
\\par
\
\\par
\
\\par
\
\\par
\
\\par
\
\\par Dr. Arni Talib
\
\\par (Pathologist) 2002-08-09
\
\\par Na.
\
\\par T 08 M 88006
\
\\par Tarikh/Masa laporan ditaip : 10/8/02 11:46}{
\
\\par }}HISTOPATHOLOGY@@\
EEEEEEEEEEEEEE\
\
R 6755/2002@@\
AAAAAAAAAAA\
\
MACROSCOPIC:\
\
Received 1 paraffin block, four H&E stained slides and 10 slides
stained \
for Vimentin, Cytokeratin, CD 30, LCA, T-cell, B-cell, NSE,
Chromogranin \
and HMB45.\
\
\
MICROSCOPIC:\
\
Lymph node. There is marked effacement of its architecture. The
lymph \
node is largely infiltrated by plump spindle shaped or epithelioid
cells \
arranged in nests separated by fine and coarse collagen bundles. The
\
cells contain clear or amhophilic cytoplasm having rounded or ovoid \
vesicular nuclei with prominent nucleoli. \
Immunohistochemistry studies show the tumour cells are strongly \
immunoreactive to Vimentin and S100, focally stroma immunoreactive to
\
HMB45, equivocal with NSE and Desmin but non-reactive to CK and Mic2.\
\
\
DIAGNOSIS:\
\
Lymph node: Metastatic Tumour.\
Morphological features and immunohistochemistry \
studies are suggestive of Clear cell Sarcoma of \
Soft Tissue (Melanoma of Soft Parts).\
\
\
\
\
\
Dr. Arni Talib\
(Pathologist) 2002-08-09\
Na.\
T 08 M 88006\
Tarikh/Masa laporan ditaip : 10/8/02 11:46\
Command used to load data
---------------------------
CONNECT TO LISDB;

LOAD FROM F:\DATA\data3\resultt OF DEL MODIFIED BY norowwarnings
delprioritychar
pagefreespace=0 totalfreespace=0 coldel| chardel"" decpt.
timeformat=""HH:MM"" dateformat=""DD/MM/YYYY""
MESSAGES db2load.msg INSERT INTO LAB.RESULTT2 COPY NO INDEXING MODE
AUTOSELECT ;

commit work;

As you can see the 3rd column of the data file is a text data
generated by
an application and was stored in another RDBMS database(Unify). We
dump out
the data from the Unify RDBMS database and load the data into DB2
database with
above mentioned DDL structure.

The LOAD command only insert the first line as shown below

50394|HPE| 0|ENCAP\
Rest of the data never get loaded. I'm not sure of other alternative
of resolving this problem. Has anyone encounter such problem before?

Prompt feedback would be very much appreciated

Thanks in advance

Uthuras
Nov 12 '05 #1
1 5845
Your LOAD statement specifies using a double quote (") as the character
string delimiter. I didn't see it in the data - either at the start or
the end of the string. Try adding it at both ends as part of your
extract process. Without special handling; the delimiter character
cannot appear within the string.

Phil Sherman

Uthuras wrote:
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.
Data File
---------
50394|HPE| 0|ENCAP\
2\
rec\
rtf\ ........... \
\
\
Dr. Arni Talib\
(Pathologist) 2002-08-09\
Na.\
T 08 M 88006\
Tarikh/Masa laporan ditaip : 10/8/02 11:46\
Command used to load data
---------------------------
CONNECT TO LISDB;

LOAD FROM F:\DATA\data3\resultt OF DEL MODIFIED BY norowwarnings
delprioritychar
pagefreespace=0 totalfreespace=0 coldel| chardel"" decpt.
timeformat=""HH:MM"" dateformat=""DD/MM/YYYY""
MESSAGES db2load.msg INSERT INTO LAB.RESULTT2 COPY NO INDEXING MODE
AUTOSELECT ;

commit work;

As you can see the 3rd column of the data file is a text data
generated by
an application and was stored in another RDBMS database(Unify). We
dump out
the data from the Unify RDBMS database and load the data into DB2
database with
above mentioned DDL structure.

The LOAD command only insert the first line as shown below

50394|HPE| 0|ENCAP\
Rest of the data never get loaded. I'm not sure of other alternative
of resolving this problem. Has anyone encounter such problem before?

Prompt feedback would be very much appreciated

Thanks in advance

Uthuras


Nov 12 '05 #2

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

Similar topics

0
by: Preston Landers | last post by:
Hello all. I am trying to write a query that "just" switches some data around so it is shown in a slightly different format. I am already able to do what I want in Oracle 8i, but I am having...
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...
7
by: James o'konnor | last post by:
hello. i have the next for create one table into db2 CREATE TABLE "MYSQUEMA"."TABLADEMO" ( "ID" INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY ( START WITH +0 INCREMENT BY +1 MINVALUE +0...
6
by: Jon Davis | last post by:
I like the drag-and-drop accessibility of dragging a table to a Web Forms designer and seeing a SqlDataAdapter automatically created for me.. being able to create a DataSet from that is fun and...
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...
3
by: den 2005 | last post by:
Hi everyone, Here is code working on..Trying to insert record with a column with Image or VarBinary datatype in sql database from a existing jpeg image file, then retrieve this image from...
11
by: c676228 | last post by:
Hi everyone, I am just wodering in asp program, if there is anybody writing store procedure for inserting data into database since there are so many parameters need to be passed into store...
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: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

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.