By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,136 Members | 1,054 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,136 IT Pros & Developers. It's quick & easy.

unable to load data into long varchar field

P: n/a
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
Share this Question
Share on Google+
1 Reply


P: n/a
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 discussion thread is closed

Replies have been disabled for this discussion.