character to numeric translation

[przytula]

running the ds server and having an seq.inputfile
data is extracted as char(11) for a specific field
the transformer calls a routine that handles this value and the result is numeric field (all digits) I display the field before leaving the routine, and the value is correctly displayed and should be numeric as it is the output of an arithmatic operation.
Call DSLogInfo("Arg1",Ans)
BTWSIGNA..Transformer_1 (64120701341): Arg1
the target is db2 database
when defining the target column as char(11) the value is correctly loaded
when defining as bigint the value is translated to 2147483647
Any idea / help
Thanks for all info
Best Regards, Guy Przytula

[Sainath.Srinivasan]

From the immediate look it is the (2^n - 1). i.e, the max value that can be stored in an n bit field.

You need to check the length (in bits / byte) for the numeric field and calculate this 2^n-1. Maybe you can advance to a larger storage via a larger datatype.

[przytula]

Thanks for the info, but this is not the problem.
It seems that data is converted to numeric like if you use date from character you also get a numerical value instead..
Hoping for other ideas
Best Regards, Guy Przytula

[Sainath.Srinivasan]

The point is, did you try
1.) writing it to a seq file
2.) use Oracle To_Number in the 11 char field to see the result
3.) Use datatypes of larger size in Oracle

Sometimes, Datastage returns the value as something like -1, which is translated to the value you have mentioned.

[przytula]

Sorry, we are talking about DB2 for the moment.
The size of the field is large enough : 11 digits and bigint can keep 19 digits..
putting in a char field is no problem : length11 and correct
Best Regards, Guy Przytula

[ray.wurlod]

It appears that DataStage is not handling BigInt, and treating the value as Integer (signed 32-bit integer).

Check whether this is the case by asking your support provider; the real question is "is BigInt" a supported data type. It wasn't in early versions of DataStage, I haven't checked for some time.

The workaround is probably user-defined SQL that is identical to the generated SQL, crazy as that sounds, but with the SQL type in the Columns grid set to CHAR(11) even though it's a BIGINT in the database.
You may get one "metadata mismatch" warning, but it will probably work.

[przytula]

Thanks for this update. Yes, I changed the metadata to char for this bigint column and this corrected the problem.
I will continue with ascential to see if this is correct.
Best Regards, Guy Przytula