unable to load data into long varchar field

[Uthuras]

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
'delprioritycha r' 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\\uc 1
\\deff0\\deflan g1033\\deflangf e1033{\\fonttbl {\\f0\\froman\\ fcharset0\\fprq 2{\\*\\panose
020206030504050 20304}Times New
Roman;}{\\f2\\f modern\\fcharse t0\\fprq1{\\*\\ panose
020703090202050 20404}Courier New;}}{\\colort bl;\\red0\\gree n0\\blue0;
\
\\red0\\green0\ \blue255;\\red0 \\green255\\blu e255;\\red0\\gr een255\\blue0;\ \red255\\green0 \\blue255;\\red 255\\green0\\bl ue0;\\red255\\g reen255\\blue0; \\red255\\green 255\\blue255;\\ red0\\green0\\b lue128;\\red0\\ green128\\blue1 28;\\red0\\gree n128\\blue0;\\r ed128\\green0\\ blue128;
\
\\red128\\green 0\\blue0;\\red1 28\\green128\\b lue0;\\red128\\ green128\\blue1 28;\\red192\\gr een192\\blue192 ;}{\\stylesheet {\\nowidctlpar\ \widctlpar\\adj ustright
\\fs20\\lang205 7\\cgrid \\snext0 Normal;}{\\*\\c s10 \\additive Default
Paragraph Font;}{
\
\\s15\\nowidctl par\\widctlpar\ \adjustright \\f2\\fs20\\lan g2057\\cgrid
\\sbasedon0 \\snext15 Plain Text;}}{\\info{ \\title
HISTOPATHOLOGY} {\\author PATHOLOGY18}{\\ operator
PATHOLOGY18}{\\ creatim\\yr2002 \\mo8\\dy12\\hr 8\\min41}{\\rev tim\\yr2002\\mo 8\\dy12\\hr8\\m in41}{\\version 2}
\
{\\edmins0}{\\n ofpages1}{\\nof words0}{\\nofch ars0}{\\*\\comp any
PATHOLOGY DEPT}{\\nofchar sws0}{\\vern71} }\\paperw11906\ \paperh16838\\m argl1152\\margr 1152
\\widowctrl\\ft nbj\\aenddoc\\h yphcaps0\\forms hade\\viewkind4 \\viewscale85\\ pgbrdrhead\\pgb rdrfoot
\\fet0\\sectd
\
\\linex0\\endnh ere\\sectdefaul tcl
{\\*\\pnseclvl1 \\pnucrm\\pnsta rt1\\pnindent72 0\\pnhang{\\pnt xta
..}}{\\*\\pnsec lvl2\\pnucltr\\ pnstart1\\pnind ent720\\pnhang{ \\pntxta
..}}{\\*\\pnsec lvl3\\pndec\\pn start1\\pninden t720\\pnhang{\\ pntxta
..}}{\\*\\pnsec lvl4
\
\\pnlcltr\\pnst art1\\pnindent7 20\\pnhang{\\pn txta
)}}{\\*\\pnsecl vl5\\pndec\\pns tart1\\pnindent 720\\pnhang{\\p ntxtb
(}{\\pntxta )}}{\\*\\pnsecl vl6\\pnlcltr\\p nstart1\\pninde nt720\\pnhang{\ \pntxtb
(}{\\pntxta )}}{\\*\\pnsecl vl7\\pnlcrm\\pn start1\\pninden t720\\pnhang{\\ pntxtb
(}
\
{\\pntxta )}}{\\*\\pnsecl vl8\\pnlcltr\\p nstart1\\pninde nt720\\pnhang{\ \pntxtb
(}{\\pntxta )}}{\\*\\pnsecl vl9\\pnlcrm\\pn start1\\pninden t720\\pnhang{\\ pntxtb
(}{\\pntxta )}}\\pard\\plai n
\\s15\\qc\\nowi dctlpar\\widctl par\\adjustrigh t
\\f2\\fs20\\lan g2057\\cgrid {\\b\\fs24\\ul
\
HISTOPATHOLOGY
\
\\par }\\pard \\s15\\nowidctl par\\widctlpar\ \adjustright {\\b\\fs24
\
\\par R 6755/2002
\
\\par
\
\\par }{\\fs24 MACROSCOPIC:
\
\\par
\
\\par }\\pard \\s15\\qj\\nowi dctlpar\\widctl par\\adjustrigh t {\\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\\nowidctl par\\widctlpar\ \adjustright {\\fs24
\
\\par
\
\\par MICROSCOPIC:
\
\\par
\
\\par }\\pard \\s15\\qj\\nowi dctlpar\\widctl par\\adjustrigh t {\\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 Immunohistochem istry 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\\nowidctl par\\widctlpar\ \adjustright {\\fs24
\
\\par
\
\\par DIAGNOSIS:
\
\\par
\
\\par Lymph node: Metastatic Tumour.
\
\\par Morphological features and immunohistochem istry
\
\\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 }}HISTOPATHOLOG Y@@\
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. \
Immunohistochem istry 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 immunohistochem istry \
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\r esultt 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

[Philip Sherman]

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
'delprioritycha r' 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\r esultt 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