IC5Notice: We have upgraded developerWorks Community to the latest version of IBM Connections. For more information, read our upgrade FAQ.
Topic
  • 2 replies
  • Latest Post - ‏2013-03-09T22:49:49Z by SystemAdmin
SystemAdmin
SystemAdmin
708 Posts

Pinned topic SQL Server to DB2 newline characters

‏2013-03-07T06:32:27Z |
I am trying to migrate data from a SQL Server database to DB2 on AIX via pipes using the IBM Data Movement Tool. I am hitting a problem with newlines being defined on Windows as CR+LF but only LF on AIX. Where string data on the source SQL Server side is at its maximum defined length for the column the data is being truncated when inserted into the DB2 target database and the warning below is received.

SQL3125W The character data in row "F0-1" and column "192" was truncated
because the data is longer than the target database column.

When unloading the data to a delimited file I can see that there are newline characters present within the data. I believe the problem is that these appear as \r\n on the AIX platform and consequently push the length of the string over its maximum defined length.

One option I have is to abandon the use of pipes, export the SQL Server data to delimited files and then perform a conversion on the delimited files such as awk '{ sub("\r$", ""); print }' before loading it into DB2.

However, this will require a significant amount of disk space. Does anyone know how the "\r\n" to "\n" conversion can be performed whilst still using pipes for the data extract/load?

Thanks,

Mike
Updated on 2013-03-09T22:49:49Z at 2013-03-09T22:49:49Z by SystemAdmin
  • SystemAdmin
    SystemAdmin
    708 Posts

    Re: SQL Server to DB2 newline characters

    ‏2013-03-09T22:49:39Z  
    first thing i would do is to check the length of the source and target column
    if the sql server column is varchar(100) for example, \r\n counts as 2 characters in SQL server as well, so i don't see why this would be a problem
  • SystemAdmin
    SystemAdmin
    708 Posts

    Re: SQL Server to DB2 newline characters

    ‏2013-03-09T22:49:49Z  
    first thing i would do is to check the length of the source and target column
    if the sql server column is varchar(100) for example, \r\n counts as 2 characters in SQL server as well, so i don't see why this would be a problem