Unable to load data that has * value

Uthuras

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 '|'

I found that some of the colum value of the data file is in '*' value.
The
representation of null value for colums on that particular RDBMS is
'*'. Therefore column like integer,date, time and string would be
represented as
'*' if it is null

For an example below is a portion of data file

-------------------------------------------
CIP| 1| *****| ********|Ciprofloxacin
CHL| 1| *****| ********|Chloramphenicol
CEP| 1| *****| ********|Cephalothin

The 3rd colums is an integer data type and the 4th is a date data
type.
Table structure for the above data is as follows

CREATE TABLE "LAB"."ANTIBIOTIC" (
"CODE" VARCHAR(3) NOT NULL ,
"KEY_NUM" INTEGER NOT NULL ,
"SUPER_NUM" INTEGER ,
"SUPER" DATE,
"NAME" VARCHAR(40) ,
"UNIT_COST" DECIMAL(12,2) ,
"UNITS" VARCHAR(15) ,
"DOSAGE" VARCHAR(15) )
IN "LISUSRTBLSPACE_3" ;

-- DDL Statements for primary key on Table "LAB"."ANTIBIOTIC"

ALTER TABLE "LAB "."ANTIBIOTIC"
ADD PRIMARY KEY
("CODE",
"KEY_NUM");

The load commad used is

LOAD FROM F:\reload_reject\labantibiotic OF DEL MODIFIED BY dumpfile =
f:\reload_reject\labantibiotic.rej
pagefreespace=0 totalfreespace=0 coldel| chardel"" decpt.
timeformat=""HH:MM"" dateformat=""DD/MM/YYYY""
MESSAGES db2load.msg INSERT INTO LAB.ANTIBIOTIC COPY NO INDEXING
MODE
AUTOSELECT ;

commit work

The above statement seem to fail as DB2 could not understand data
value '*'
for INTEGER and DATE data type. I tried using USEDEFAULTS option in my
LOAD command but it still fails. How do i load the '*' value into the
table?
Is is possible ?

Please advice.

Thanks in advance.

Uthuras

Nov 12 '05 #1

Anton Versteeg

The easiest would be to edit the file and replace |********| by ||.
Uthuras wrote:

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 '|'

I found that some of the colum value of the data file is in '*' value.
The
representation of null value for colums on that particular RDBMS is
'*'. Therefore column like integer,date, time and string would be
represented as
'*' if it is null

For an example below is a portion of data file

-------------------------------------------
CIP| 1| *****| ********|Ciprofloxacin
CHL| 1| *****| ********|Chloramphenicol
CEP| 1| *****| ********|Cephalothin

The 3rd colums is an integer data type and the 4th is a date data
type.
Table structure for the above data is as follows

CREATE TABLE "LAB"."ANTIBIOTIC" (
"CODE" VARCHAR(3) NOT NULL ,
"KEY_NUM" INTEGER NOT NULL ,
"SUPER_NUM" INTEGER ,
"SUPER" DATE,
"NAME" VARCHAR(40) ,
"UNIT_COST" DECIMAL(12,2) ,
"UNITS" VARCHAR(15) ,
"DOSAGE" VARCHAR(15) )
IN "LISUSRTBLSPACE_3" ;

-- DDL Statements for primary key on Table "LAB"."ANTIBIOTIC"

ALTER TABLE "LAB "."ANTIBIOTIC"
ADD PRIMARY KEY
("CODE",
"KEY_NUM");

The load commad used is

LOAD FROM F:\reload_reject\labantibiotic OF DEL MODIFIED BY dumpfile =
f:\reload_reject\labantibiotic.rej
pagefreespace=0 totalfreespace=0 coldel| chardel"" decpt.
timeformat=""HH:MM"" dateformat=""DD/MM/YYYY""
MESSAGES db2load.msg INSERT INTO LAB.ANTIBIOTIC COPY NO INDEXING
MODE
AUTOSELECT ;

commit work

The above statement seem to fail as DB2 could not understand data
value '*'
for INTEGER and DATE data type. I tried using USEDEFAULTS option in my
LOAD command but it still fails. How do i load the '*' value into the
table?
Is is possible ?

Please advice.

Thanks in advance.

Uthuras

--
Anton Versteeg
IBM Certified DB2 Specialist
IBM Netherlands

Nov 12 '05 #2

Uthuras

Greetings Anton,

It works! Thanks.

Uthuras

Anton Versteeg <an************@nnll.iibbmm.com> wrote in message news:<3F**************@nnll.iibbmm.com>...

The easiest would be to edit the file and replace |********| by ||.
Uthuras wrote:

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 '|'

I found that some of the colum value of the data file is in '*' value.
The
representation of null value for colums on that particular RDBMS is
'*'. Therefore column like integer,date, time and string would be
represented as
'*' if it is null

For an example below is a portion of data file

-------------------------------------------
CIP| 1| *****| ********|Ciprofloxacin
CHL| 1| *****| ********|Chloramphenicol
CEP| 1| *****| ********|Cephalothin

The 3rd colums is an integer data type and the 4th is a date data
type.
Table structure for the above data is as follows

CREATE TABLE "LAB"."ANTIBIOTIC" (
"CODE" VARCHAR(3) NOT NULL ,
"KEY_NUM" INTEGER NOT NULL ,
"SUPER_NUM" INTEGER ,
"SUPER" DATE,
"NAME" VARCHAR(40) ,
"UNIT_COST" DECIMAL(12,2) ,
"UNITS" VARCHAR(15) ,
"DOSAGE" VARCHAR(15) )
IN "LISUSRTBLSPACE_3" ;

-- DDL Statements for primary key on Table "LAB"."ANTIBIOTIC"

ALTER TABLE "LAB "."ANTIBIOTIC"
ADD PRIMARY KEY
("CODE",
"KEY_NUM");

The load commad used is

LOAD FROM F:\reload_reject\labantibiotic OF DEL MODIFIED BY dumpfile =
f:\reload_reject\labantibiotic.rej
pagefreespace=0 totalfreespace=0 coldel| chardel"" decpt.
timeformat=""HH:MM"" dateformat=""DD/MM/YYYY""
MESSAGES db2load.msg INSERT INTO LAB.ANTIBIOTIC COPY NO INDEXING
MODE
AUTOSELECT ;

commit work

The above statement seem to fail as DB2 could not understand data
value '*'
for INTEGER and DATE data type. I tried using USEDEFAULTS option in my
LOAD command but it still fails. How do i load the '*' value into the
table?
Is is possible ?

Please advice.

Thanks in advance.

Uthuras

Nov 12 '05 #3