DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
This topic has been marked "Resolved."
Author Message
rohit_mca2003
Participant



Joined: 08 Oct 2008
Posts: 37

Points: 449

Post Posted: Thu Jan 11, 2018 8:15 am Reply with quote    Back to top    

DataStage® Release: 9x
Job Type: Parallel
OS: Unix
Additional info: Datatype length exceeds while NULL handling and String Trim
Hi,

I have generic job where I am handling NULL and performing TRIM for string type of column.
As output column of these, If I do not assign data length for output then by default it increases the length of the column. Example:

CASE1:
---------
Output_Col:string=handle_null(in_col,' ')
Output_Col:string=string_trim(in_col)

In source in_col is defined as VARCHAR(20) but after these conversion it increases the length to maximum limit of VARCHAR and since in target it is defined as VARCHAR(20) so job fails because of increased length.

I could define these conversions as below to take care of this issue, when I define the rule as below, I do not get above error & job works fine.

CASE2:
--------
Output_Col:string[20]=handle_null(in_col,' ')
Output_Col:string[20]=string_trim(in_col)

But it might change the value of the 'in_col' with in 'Output_Col'.
I can say this because when I generate CHECKSUM on 'Output_Col' then I get different value in CASE1 and CASE2.

Queries:
1. Is there any way I handle this and datatype length does not change after these conversions (as in CASE1)?
2. I came to know there is a 'environment' variable which stops MODIFY operator to change the datatype or length, but I am not able to find it. Any Idea on that variable?

Any help in this would be great help. Thanks.

_________________
Rohit
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42751
Location: Denver, CO
Points: 220300

Post Posted: Thu Jan 11, 2018 8:22 am Reply with quote    Back to top    

Perhaps APT_OLD_BOUNDED_LENGTH?

_________________
-craig

Research shows that 6 out of 7 dwarves aren't happy
Rate this response:  
Not yet rated
rohit_mca2003
Participant



Joined: 08 Oct 2008
Posts: 37

Points: 449

Post Posted: Thu Jan 11, 2018 10:08 pm Reply with quote    Back to top    

This option does not restrict the length of output_col. We have used this parameter earlier to restrict the usage of disk and scratch space.

_________________
Rohit
Rate this response:  
Not yet rated
rohit_mca2003
Participant



Joined: 08 Oct 2008
Posts: 37

Points: 449

Post Posted: Thu Jan 11, 2018 10:31 pm Reply with quote    Back to top    

This issue is resolved when I defined the conversion as below:

Output_Col:string[max=20]=string_trim[in_col]

If I assigned the value as string[20], is was treating it as CHAR and adding space so defining it as max=20 did the work and it is not exceeding upto the maximum limit of varchar.

Thanks.

_________________
Rohit
Rate this response:  
Not yet rated
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours