Trim in 8.1 and 8.5

karthi_gana · Post by **karthi_gana** » Fri Jul 13, 2012 12:48 am

All,

I am working on Datastage 8.1 to Datastage 8.5 migration project. I just wanted to know
a) will the extra leading & trailing spaces will be trimmed by default in datastage?
b) Do we need to explicitly use TRIM function to trim the leading & trailing space in Datastage 8.5 ?

We have noticed some weird scenario while inserting the records into a table.

yes...

i have the below file...

ABC Manpower
XYZ Pvt ltd
MVC Pipes
kk Hardwares
NJK Gardens

Datastage 8.1 inserts the record without trailing space.

say for example

ABC Manpower- In 8.1 -- it was inserted without any space after the last letter. i.e without trailing space. Other records also inserted as mentioned above.

Datastage 8.5 inserts the record with trailing space.

ABC Manpower<one empty space> - 8.5 All the records were inserted with one epmty space after the last character/letter.

how? i heard like...datastage 8.1 will TRIM the trailing spaces by default.
is it ? Datstage 8.5 won't have this facility ? is it?

ArndW · Post by **ArndW** » Fri Jul 13, 2012 2:01 am

DataStage will generally not automatically trim strings. Is your database Oracle? Did you use the same configuration file (DSPARAMS) in both installation? If your database is ORACLE, your setting for "APT_ORACLE_PRESERVE_BLANKS" might be different between the 2 installations and that might be causing the different behaviour.

karthi_gana · Post by **karthi_gana** » Fri Jul 13, 2012 2:26 am

I have checked both the config files. Both are same only.

APT_ORACLE_PRESERVE_BLANKS is set to False in both the version.

ArndW · Post by **ArndW** » Fri Jul 13, 2012 3:30 am

Are both versions writing to the same database? Perhaps one Oracle installation is using other options if they are different. Also, in the 8.5 version, put in a test transform stage just before writing to the database which has a 2nd output link which has the condition "In.{StringColumn} <> Trim(In.{StringColumn})" to see if the appended space is present in DataStage before writing to the database.

chandra.shekhar@tcs.com · Fri Jul 13, 2012 8:47 am

Whats the datatype at your end ?
If It is CHAR then it could be possible that extra space might come due to lesser length of the string.

karthi_gana · Post by **karthi_gana** » Fri Jul 13, 2012 10:56 pm

it is VARCHAR only.

I just saw somethind different in the transformer...

yes...the source datatype is nvarchar...
the target datatype is varchar...
in target... "Unicode" is selected under "Extended property"...

i couldn't connect these three dots...

VARCHAR, NVARCHAR , UNICODE.

JRodriguez · Post by **JRodriguez** » Sat Jul 14, 2012 8:23 am

Karti_gana,

Are you using new Oracle connector in 8.5? If yes, there is a property to preserve the space or trim the values in there...

Hope this help