From Sybase to Netezza: decimal mark error

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
arg_etl
Participant
Posts: 3
Joined: Mon Aug 14, 2017 2:03 pm

From Sybase to Netezza: decimal mark error

Post by arg_etl »

We've found a problem when we tried to upload to Netezza the data that we got from Sybase, this issue arose specifically with the floating points data types. Then, we found out that the issue is not from any of the databases, but from datastage itself.

The test that proves it is the Row Generator when we try to write into a Sequential File.

The issue is that we need a period as a decimal mark, in all the numeric data type (double, float real, decimal, numeric). But the floating point data types (Double, Float, Real), which are written in Scientific Notation, have a comma instead. The fixed point data types(Decimal, Numeric) doesn't have any problem because when we change the decimal delimiter, it changes correctly.

Image


We've tried changing the Default Decimal Delimiter, the NLS, and some other Locales in datastage and regional settings in windows server, but the problem persists.

We want to keep the Sybase datatype to the destination database.

We truly appreciate any help!
Regards
qt_ky
Premium Member
Premium Member
Posts: 2895
Joined: Wed Aug 03, 2011 6:16 am
Location: USA

Post by qt_ky »

You might also want to test Row Generator stage to Peek stage, to remove the database stages and the Sequential File stage as variables.

The "decimal separator" as it is called in the Sequential File stage properties can be set or overridden there.

It can also be set or overridden in the job level in the job properties on the Defaults tab.

It is also set at the project level per project in Administrator on the Parallel tab.

The locales documentation also calls it the "radix character."
Choose a job you love, and you will never have to work a day in your life. - Confucius
arg_etl
Participant
Posts: 3
Joined: Mon Aug 14, 2017 2:03 pm

Post by arg_etl »

Thank you for your answer! Unfortunately, those are all set to period, and it still doesnt change..
qt_ky wrote:You might also want to test Row Generator stage to Peek stage, to remove the database stages and the Sequential File stage as variables.
Yeah,but for some reason the Row Gen doesn't generate decimals numbers, so the peek doesnt show the comma. Anyway, we need to pass the info through a Sequential file or Dataset stage at some point, so, even if that fix it, the problem is still there.
qt_ky wrote:The "decimal separator" as it is called in the Sequential File stage properties can be set or overridden there.

It can also be set or overridden in the job level in the job properties on the Defaults tab.

It is also set at the project level per project in Administrator on the Parallel tab.
Yeah, we set it all to period, even the locale, that should make a difference, is set to 'en_US', so it should use period..
Also, the Windows is in spanish, but we tried changing it in a virtual machine, and it didn't work.

Sequential File properties:
Image
Job NLS and Locales:
Image
Job Defaults
Image
arg_etl
Participant
Posts: 3
Joined: Mon Aug 14, 2017 2:03 pm

Solution

Post by arg_etl »

This issue is solved with the patch APAR JR58661.
Regards.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Thanks for coming back and posting your resolution!
-craig

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