packed decimal value, how to supress zeros

[Gaurav.Dave]

Hello,

I have 4 set of datastage job, which is running in sequence to achieve the desire target valus....
(1) DB2 -->xfm--> Datasets,
(2) Datasets ---->join--->Datasets,
(3) Datasets ---> join-->xfm---> Sequential file
and then
(4) db2 loader to load to target db2 database.

When I check the Sequential file stage, the last one before running db2 loader, I have some decimal values (8,0) which is representing like "00000000."). This creating problem while loading to target database, I am getting error message "cannot be converted to
a PACKED DECIMAL value, but the target column is not nullable" and records are being dropped.

I am trying to convert "00000000." to "0" means supressing zeros so that I can load it to the target database.

Can anyone guide me how to achieve it?

Thanks,

Gaurav

[DSguru2B]

Probably whats happening is that you are trying to load the Null values in a Sequential file. This will not be handled, hence DS is converting the nulls to a default.
I would recommend a peek stage to be added to all the jobs and observe the data there and post your observation.
Thanks.

[ray.wurlod]

Zero is not valid to be represented as a packed decimal. Search the forum for "PACKED" "zero" (all terms) to find an environment variable that allows this behaviour to be overridden.

[Gaurav.Dave]

Thanks Ray

Still I am not able find the solution. Wondering if I need edit properties for the particular column (decimal one)..

Regards,
Gaurav

[Abhijeet]

Thanks Ray

Still I am not able find the solution. Wondering if I need edit properties for the perticular column (decimal one)..

Regards,
Gaurav

you need to do two things .

1> remove the "." from the string when writing it to the file.
2> by default "all zeroes" is not considered as a packed decimal value {atleast in datastage}. So you need to change some property in ur db so that it will accept it.

[kommven]

USE Abs OR DIVIDE THE VALUE BY 1 IN TRANSFORMER