Error while loading into Netezza table for Numeric column

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
Vrisha
Premium Member
Premium Member
Posts: 60
Joined: Sat Jul 15, 2017 9:32 pm
Location: Texas,USA

Error while loading into Netezza table for Numeric column

Post by Vrisha »

source is a input file (.txt) with column (NQF) with value 0.0377000000000
Target database - Netezza

Target column - NQF- has a datatype of Numeric ( precision -5; scale 4) with Nullability - Yes

When job runs, it is throwing this error
FILE_NM FROM EXTERNAL '/is/isdata/tmp/cbi-etl-prd_34144274_nzw_0_2_20190424043309373050' USING(remotesource 'odbc' delimiter '?' ignorezero false ctrlchars true escapechar '\' logDir '/is/isdata/tmp/' boolStyle 'T_F' encoding 'internal' nullValue 'N' maxErrors 1 ). Reason: [SQLCODE=HY008][Native=51] Operation canceled; [SQLCODE=HY000][Native=46] ERROR: External Table : count of bad input rows reached maxerrors limit

Testing done
1) When null value is given in the input and tested, the job runs perfectly
2) when 0.0377000000000, it is throwing the above error.
3) checked in Textpad if any invisible/junk characters are present, but nothing found.

Asked the source application team to check if any characters found, nothing found. But job is failing.

Any suggestions please.
Suja
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

A precision of negative 5? What would that even mean? I'm assuming that is your issue. :?

A quick search to see if Netezza supported something bizarre like that turned up nothing but still... as a test, what happens if you remove the negative?
-craig

"You can never have too many knives" -- Logan Nine Fingers
Vrisha
Premium Member
Premium Member
Posts: 60
Joined: Sat Jul 15, 2017 9:32 pm
Location: Texas,USA

Post by Vrisha »

Sorry for the confusion. What I meant is precision is 5 ( not negative)
I mean precision hyphen 5.
Suja
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

So you actually mean Precision 5 ?

Precision is the total number of significant digits in the number; scale is the number of significant digits following the decimal placeholder.

However, the error message suggests that the error is occurring in a Boolean type. Can you please verify how this "number" is defined in Netezza and in the metadata used in your job design?
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

This also seems to be some kind of bulk load, perhaps an nzload from an external table? This rather than straight up conventional inserts. Not saying it makes a difference, just want a full picture of the scenario. What stage are you using?
-craig

"You can never have too many knives" -- Logan Nine Fingers
Vrisha
Premium Member
Premium Member
Posts: 60
Joined: Sat Jul 15, 2017 9:32 pm
Location: Texas,USA

Post by Vrisha »

Hi Craig,
the job flow is like
RCP job and multiinstance,
sequential file ---> Transformer---> netezza table
(source) ( target)
Suja
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Sorry but that doesn't answer either of our questions. :(
-craig

"You can never have too many knives" -- Logan Nine Fingers
Post Reply