468,121 Members | 1,509 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,121 developers. It's quick & easy.

LOAD question

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
Nov 12 '05 #1
3 3851
"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
Nov 12 '05 #2
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" <ma@switchboard.net> 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

Nov 12 '05 #3
> > > 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.
Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by Shabam | last post: by
1 post views Thread by paul-esposito | last post: by
2 posts views Thread by Lee Alexander | last post: by
7 posts views Thread by Dan Nash | last post: by
1 post views Thread by Jerry LeVan | last post: by
1 post views Thread by huyuhui | last post: by
1 post views Thread by charlie imac | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.