Load Errors using VARCHAR Col

Ken Liberty

Load Errors using VARCHAR Col
I have a LOAD job, which is failing on a Decimal defined column which is
preceded by a VARCHAR column.... I've seen in the DB2 Developers Guide, 3rd
edition that it is recommended that VARCHAR columns are placed at the end of the
table, but that does not explain why I cannot have a VARCHAR in the middle of
the table???

I checked file which is being used as input including the lth byte before the
VARCHAR Col and all looks fine to me..... COL14 is the discarded with the
following message.... -DBT1 DSNURWBG - RECORD (1) WILL BE DISCARDED DUE TO
'COL14'
CONVERSION ERROR FOR TEST.TABLE1.

Here is my LOAD Statements:....

LOAD DATA LOG NO INDDN SYSREC00 INTO TABLE
TEST.TABLE1
(
COL1 POSITION( 1 ) INTEGER
,
COL2 POSITION( 5 ) INTEGER
,
COL3 POSITION( 9 ) CHAR(
2) ,
COL4 POSITION( 11 ) INTEGER
,
COL5 POSITION( 15 ) INTEGER
,
COL6 POSITION( 19 ) DATE
EXTERNAL( 10) ,
COL7 POSITION( 29 ) CHAR(
2) ,
COL8 POSITION( 31 ) CHAR(
2) ,
COL9 POSITION( 33 ) CHAR(
1) ,
COL10 POSITION( 34 ) SMALLINT
,
COL11 POSITION( 36 ) SMALLINT
,
COL12 POSITION( 38 ) SMALLINT
,
COL13 POSITION( 40 ) VARCHAR
,
COL14 POSITION( 102: 106) DECIMAL
,
COL15 POSITION( 107: 111) DECIMAL
,
COL16 POSITION( 112: 116) DECIMAL

)

If you've experienced this before or have any recommendations, let me know.....

Thanks Ken



Frank D Marchese

Re: Load Errors using VARCHAR Col
(in response to Ken Liberty)
I don't believe that you can't have VARCHAR columns in mid-row but it is
recommended they be at the end of the row. This is a perf issue having to do
with the amt of the row logged during an SQL UPDATE , as I remember it.

-----Original Message-----
From: Ken Liberty [mailto:[login to unmask email]
Sent: Thursday, January 06, 2000 8:00 AM
To: [login to unmask email]
Subject: Load Errors using VARCHAR Col


I have a LOAD job, which is failing on a Decimal defined column which is
preceded by a VARCHAR column.... I've seen in the DB2 Developers Guide, 3rd
edition that it is recommended that VARCHAR columns are placed at the end of
the
table, but that does not explain why I cannot have a VARCHAR in the middle
of
the table???

I checked file which is being used as input including the lth byte before
the
VARCHAR Col and all looks fine to me..... COL14 is the discarded with the
following message.... -DBT1 DSNURWBG - RECORD (1) WILL BE DISCARDED DUE TO
'COL14'
CONVERSION ERROR FOR TEST.TABLE1.

Here is my LOAD Statements:....

LOAD DATA LOG NO INDDN SYSREC00 INTO TABLE
TEST.TABLE1
(
COL1 POSITION( 1 ) INTEGER
,
COL2 POSITION( 5 ) INTEGER
,
COL3 POSITION( 9 ) CHAR(
2) ,
COL4 POSITION( 11 ) INTEGER
,
COL5 POSITION( 15 ) INTEGER
,
COL6 POSITION( 19 ) DATE
EXTERNAL( 10) ,
COL7 POSITION( 29 ) CHAR(
2) ,
COL8 POSITION( 31 ) CHAR(
2) ,
COL9 POSITION( 33 ) CHAR(
1) ,
COL10 POSITION( 34 ) SMALLINT
,
COL11 POSITION( 36 ) SMALLINT
,
COL12 POSITION( 38 )
SMALLINT
,
COL13 POSITION( 40 ) VARCHAR
,
COL14 POSITION( 102: 106) DECIMAL
,
COL15 POSITION( 107: 111) DECIMAL
,
COL16 POSITION( 112: 116) DECIMAL

)

If you've experienced this before or have any recommendations, let me
know.....

Thanks Ken








Patricia Candella

Re: Load Errors using VARCHAR Col
(in response to Frank D Marchese)
Ken,
In order for this to work.. the position (40 - in your example) must be the
beginning of your length field, and the variable length field must be padded on
your load file to the maximum length of your field. The reason it has to be
padded is so that the next field can be found in the position that you have
specified in your load criteria. I have had problems with this type of load
before if the file that was generated from a BMC Unloadplus utility.. for some
reason, the length bytes are not provided.. on the load file (at least.. I
cannot get the output file to generate a dataset with the length bytes).
Those types of files must be loaded with the BMC Loadplus utility.


Hope this is helpful..
Patty





"Marchese, Frank D" <[login to unmask email]> on 01/06/2000 09:11:08 AM

Please respond to "DB2 Data Base Discussion List" <[login to unmask email]>

To: [login to unmask email]
cc: (bcc: Patricia Candella/Bsg/MetLife/US)
Subject: Re: Load Errors using VARCHAR Col



I don't believe that you can't have VARCHAR columns in mid-row but it is
recommended they be at the end of the row. This is a perf issue having to do
with the amt of the row logged during an SQL UPDATE , as I remember it.

-----Original Message-----
From: Ken Liberty [mailto:[login to unmask email]
Sent: Thursday, January 06, 2000 8:00 AM
To: [login to unmask email]
Subject: Load Errors using VARCHAR Col


I have a LOAD job, which is failing on a Decimal defined column which is
preceded by a VARCHAR column.... I've seen in the DB2 Developers Guide, 3rd
edition that it is recommended that VARCHAR columns are placed at the end of
the
table, but that does not explain why I cannot have a VARCHAR in the middle
of
the table???

I checked file which is being used as input including the lth byte before
the
VARCHAR Col and all looks fine to me..... COL14 is the discarded with the
following message.... -DBT1 DSNURWBG - RECORD (1) WILL BE DISCARDED DUE TO
'COL14'
CONVERSION ERROR FOR TEST.TABLE1.

Here is my LOAD Statements:....

LOAD DATA LOG NO INDDN SYSREC00 INTO TABLE
TEST.TABLE1
(
COL1 POSITION( 1 ) INTEGER
,
COL2 POSITION( 5 ) INTEGER
,
COL3 POSITION( 9 ) CHAR(
2) ,
COL4 POSITION( 11 ) INTEGER
,
COL5 POSITION( 15 ) INTEGER
,
COL6 POSITION( 19 ) DATE
EXTERNAL( 10) ,
COL7 POSITION( 29 ) CHAR(
2) ,
COL8 POSITION( 31 ) CHAR(
2) ,
COL9 POSITION( 33 ) CHAR(
1) ,
COL10 POSITION( 34 ) SMALLINT
,
COL11 POSITION( 36 ) SMALLINT
,
COL12 POSITION( 38 )
SMALLINT
,
COL13 POSITION( 40 ) VARCHAR
,
COL14 POSITION( 102: 106) DECIMAL
,
COL15 POSITION( 107: 111) DECIMAL
,
COL16 POSITION( 112: 116) DECIMAL

)

If you've experienced this before or have any recommendations, let me
know.....

Thanks Ken













Linda Claussen

Re: Load Errors using VARCHAR Col
(in response to Patricia Candella)
Ken,

I have loaded data with the VARCHAR in the middle, due to columns added
after the fact.

I would double check the starting position of the decimal columns and the
data type. Is the decimal data you are loading in the appropriate format?
DECIMAL is the same as DECIMAL PACKED so you need to make sure that the
input data is in the appropriate packed decimal format. If it is not, use
DECIMAL ZONED or DECIMAL EXTERNAL (length,scale) to define the format of
the input data so DB2 can convert it properly to the column data type.

Hope this helps,

Linda F. Claussen
Claussen & Associates
DB2 Consulting and Training
[login to unmask email]
http://www.netins.net/showcase/lclaussen/
Phone: (319) 343-3216
Office: (319) 847-1985

----------
From: Ken Liberty <[login to unmask email]>
To: [login to unmask email]
Subject: Load Errors using VARCHAR Col
Date: Thursday, January 06, 2000 7:00 AM

I have a LOAD job, which is failing on a Decimal defined column which is
preceded by a VARCHAR column.... I've seen in the DB2 Developers Guide, 3rd
edition that it is recommended that VARCHAR columns are placed at the end
of the
table, but that does not explain why I cannot have a VARCHAR in the middle
of
the table???

I checked file which is being used as input including the lth byte before
the
VARCHAR Col and all looks fine to me..... COL14 is the discarded with the
following message.... -DBT1 DSNURWBG - RECORD (1) WILL BE DISCARDED DUE TO
'COL14'
CONVERSION ERROR FOR TEST.TABLE1.

Here is my LOAD Statements:....

LOAD DATA LOG NO INDDN SYSREC00 INTO TABLE
TEST.TABLE1
(
COL1 POSITION( 1 ) INTEGER
,
COL2 POSITION( 5 ) INTEGER
,
COL3 POSITION( 9 ) CHAR(
2) ,
COL4 POSITION( 11 ) INTEGER
,
COL5 POSITION( 15 ) INTEGER
,
COL6 POSITION( 19 ) DATE
EXTERNAL( 10) ,
COL7 POSITION( 29 ) CHAR(
2) ,
COL8 POSITION( 31 ) CHAR(
2) ,
COL9 POSITION( 33 ) CHAR(
1) ,
COL10 POSITION( 34 ) SMALLINT
,
COL11 POSITION( 36 ) SMALLINT
,
COL12 POSITION( 38 )
SMALLINT
,
COL13 POSITION( 40 ) VARCHAR
,
COL14 POSITION( 102: 106) DECIMAL
,
COL15 POSITION( 107: 111) DECIMAL
,
COL16 POSITION( 112: 116)
DECIMAL

)

If you've experienced this before or have any recommendations, let me
know.....

Thanks Ken








Venkat (PCA) Pillay

Re: Load Errors using VARCHAR Col
(in response to Linda Claussen)
Ken

VARCHAR in the middle of the table is never a problem unless you
have two varchars and you want to load without mentioning the full LOAD
card. For example -

LOAD DATA LOG NO INDDN SYSREC00 INTO TABLE
TEST.TABLE1

The above control card works when VARCHAR is at the end but loads wrong data
if you have two varchars. One VARCHAR in the middle and another one after
this.

Since you have specified the entire column definition you should not have
problem unless your data definition does not match with your input file. I
suggest following -

- Check the decimal fields once again. There may be something wrong with
them. Packed decimal fields should be specified else you should mention
DECIMAL EXTERNAL.
- Check for null columns. They need extra one byte in the input data file.

VARCHAR at the end is recommended due to performance reasons (very small
gain though). Since the length of the VARCHAR is different, DB2 has to do
extra calculation to retrieve columns after VARCHAR. This little overhead
can be avoided by placing VARCHAR at the end of the table.

Regards
Venkat Pillay
> -----Original Message-----
> From: Ken Liberty [SMTP:[login to unmask email]
> Sent: Thursday, January 06, 2000 8:00 AM
> To: [login to unmask email]
> Subject: Load Errors using VARCHAR Col
>
> I have a LOAD job, which is failing on a Decimal defined column which is
> preceded by a VARCHAR column.... I've seen in the DB2 Developers Guide,
> 3rd
> edition that it is recommended that VARCHAR columns are placed at the end
> of the
> table, but that does not explain why I cannot have a VARCHAR in the middle
> of
> the table???
>
> I checked file which is being used as input including the lth byte before
> the
> VARCHAR Col and all looks fine to me..... COL14 is the discarded with the
> following message.... -DBT1 DSNURWBG - RECORD (1) WILL BE DISCARDED DUE TO
> 'COL14'
> CONVERSION ERROR FOR TEST.TABLE1.
>
> Here is my LOAD Statements:....
>
> LOAD DATA LOG NO INDDN SYSREC00 INTO TABLE
> TEST.TABLE1
> (
> COL1 POSITION( 1 )
> INTEGER
> ,
> COL2 POSITION( 5 )
> INTEGER
> ,
> COL3 POSITION( 9 ) CHAR(
> 2) ,
> COL4 POSITION( 11 )
> INTEGER
> ,
> COL5 POSITION( 15 )
> INTEGER
> ,
> COL6 POSITION( 19 ) DATE
> EXTERNAL( 10) ,
> COL7 POSITION( 29 ) CHAR(
> 2) ,
> COL8 POSITION( 31 ) CHAR(
> 2) ,
> COL9 POSITION( 33 ) CHAR(
> 1) ,
> COL10 POSITION( 34 ) SMALLINT
> ,
> COL11 POSITION( 36 ) SMALLINT
> ,
> COL12 POSITION( 38 )
> SMALLINT
> ,
> COL13 POSITION( 40 ) VARCHAR
> ,
> COL14 POSITION( 102: 106) DECIMAL
> ,
> COL15 POSITION( 107: 111) DECIMAL
> ,
> COL16 POSITION( 112: 116)
> DECIMAL
>
> )
>
> If you've experienced this before or have any recommendations, let me
> know.....
>
> Thanks Ken
>
>
>
>
>