We are pulling table from Sql server to Netezza through an interface in odi 11g.
In the first step a dump file is created on netezza server and then this file is loaded to the table but while loading the file, the interface step is getting failed due to the "carriage returns"(CR) and line feeds(LF) present in a string(within a column) on file. This is causing split in the string and the second split of the string is being loaded as a new record and this is failing due to data type mismatch or exceeding character length errors.
I have fixed it using work around by using replace statement and this is executed on the source(sql server) while creating the file and it worked fine, there are no more CR and LFs in between the strings in the dump file.
My code:
REPLACE(REPLACE(REPLACE(column namee,CHAR(13),''),CHAR(10),''), '~','').
But production team complained that this interface is taking more time than usual due to the multiple replace statements applied on multiple columns in mapping.
Can you please suggest any way that we can improve the performance using translate statement, I tried the below code but its not working.
TRANSLATE(column namee, 'CHAR(10)CHAR(13)~' , 'CHAR(32)CHAR(32) ' )
Thanks