LOAD question

[Murty]

I am getting an error while loading data where numeric columns are suffixed
with sign (eg, 500-, 2167+)
in the input file. (using LOAD uitility).
Any suggestions (without wrapping it with a shell script or C program to
massage the data) ?

Thanks

[Mark A]

"Murty" <ad***@comcast.net> wrote in message
news:LK********************@rwcrnsc51.ops.asp.att. net...

I am getting an error while loading data where numeric columns are suffixed with sign (eg, 500-, 2167+)
in the input file. (using LOAD uitility).
Any suggestions (without wrapping it with a shell script or C program to massage the data) ?

Thanks

Can you tell us:

1. DB2 release and fixpak level
2. OS
3. Exact load syntax used (if done with control center, you can save the
generated script)
4. Format of data in load file (delimited, fixed position, IXF, etc)
5. Exact error message you are getting

[Murty]

Ver : DB2 8.1 FP 2
OS : Linux

It is fixed position columns file and here is the LOAD command........

LOAD FROM "/home/u01aps0/top10.txt" OF ASC MODIFIED BY
DATEFORMAT="YYYYMMDD" TIMESTAMPFORMAT="YYYYMMDD"
METHOD L(1 4, 9 16, 17 19, 20 25, 26 28, 29 31, 32 32, 33 35, 36 75, 76 91,
92 106, 108 108, 109 123, 125 139)
NULL INDICATORS(0,0,0,0,0,0,0,0,0,0,0,0,0,0)
MESSAGES "/home/u01aps0/top10.msg"
REPLACE INTO APS.X (FUND, POST_DATE, STTMT_CURRENCY, ACCOUNT, SUB_ACCOUNT,
LOCAL_CURR, BASIS, BASE_CURR, DESCRIPTIONS, DR_BAL_MONTHEND,
CR_BAL_MONTHEND, MEMO_IND, DR_BAL_EOD, CR_BAL_EOD)
INDEXING MODE REBUILD;

I am actually getting the following warning. It is loading the row with
NULLS into the columns in question
and that is not what I wanted....

SQL3121W The field value in row "1" and column "76" cannot be converted to
a
FLOAT value. A null was loaded.

"Mark A" <[e-mail]> wrote in message
news:sU******************@news.uswest.net...

"Murty" <ad***@comcast.net> wrote in message
news:LK********************@rwcrnsc51.ops.asp.att. net...

I am getting an error while loading data where numeric columns are

suffixed

with sign (eg, 500-, 2167+)
in the input file. (using LOAD uitility).
Any suggestions (without wrapping it with a shell script or C program

to

massage the data) ?

Thanks

Can you tell us:

1. DB2 release and fixpak level
2. OS
3. Exact load syntax used (if done with control center, you can save the
generated script)
4. Format of data in load file (delimited, fixed position, IXF, etc)
5. Exact error message you are getting

[Mark A]

> > > I am getting an error while loading data where numeric columns are

suffixed

with sign (eg, 500-, 2167+)
in the input file. (using LOAD uitility).
Any suggestions (without wrapping it with a shell script or C

program to

massage the data) ?

Thanks

"Murty" <ad***@comcast.net> wrote in message
news:Banlb.838252$YN5.946392@sccrnsc01... Ver : DB2 8.1 FP 2
OS : Linux

It is fixed position columns file and here is the LOAD command........

LOAD FROM "/home/u01aps0/top10.txt" OF ASC MODIFIED BY
DATEFORMAT="YYYYMMDD" TIMESTAMPFORMAT="YYYYMMDD"
METHOD L(1 4, 9 16, 17 19, 20 25, 26 28, 29 31, 32 32, 33 35, 36 75, 76 91, 92 106, 108 108, 109 123, 125 139)
NULL INDICATORS(0,0,0,0,0,0,0,0,0,0,0,0,0,0)
MESSAGES "/home/u01aps0/top10.msg"
REPLACE INTO APS.X (FUND, POST_DATE, STTMT_CURRENCY, ACCOUNT, SUB_ACCOUNT,
LOCAL_CURR, BASIS, BASE_CURR, DESCRIPTIONS, DR_BAL_MONTHEND,
CR_BAL_MONTHEND, MEMO_IND, DR_BAL_EOD, CR_BAL_EOD)
INDEXING MODE REBUILD;

I am actually getting the following warning. It is loading the row with
NULLS into the columns in question
and that is not what I wanted....

SQL3121W The field value in row "1" and column "76" cannot be converted to a
FLOAT value. A null was loaded.

Looking at the manual, it appear that it doesn't like the leading blanks
and/or the trailing sign (not sure which). I didn't see any parms that can
be set to allow it, but I am not an expert in this.

You might try a test file of a few records to find out which values it will
accept and if the problem is the leading blanks or trailing sign (or both).
If there is a problem with the "+" then you can replace it with a decimal.