Topic
3 replies Latest Post - ‏2013-01-18T07:22:36Z by SystemAdmin
SystemAdmin
SystemAdmin
1143 Posts
ACCEPTED ANSWER

Pinned topic Recommendation for generating files in Windows to be loaded in Informx/Unix

‏2013-01-16T18:50:33Z |
Hi all,

Interesting situation ... we generate files using SSIS (on a Windows server) every night and FTP them to a Unix server to be loaded into an Informix database. The recommendation (from the application developers) was to FTP these files in ASCII mode presumably to modify the x'0D0A' (CR/LF) bytes that are appended to the end of each record during file creation on the Windows server to x'0A' (LF) when they are transferred to the Unix server.

This worked fine until the files started getting too big (over 1G) then the Windows server started failing because it brings the entire file into memory during conversion before actually transmitting. So our Unix admin recommended that we FTP them in binary mode then run the dos2unix command to modify the CR/LF to an LF. So we put this solution in place and then started failing again after awhile when the files got to be over 2G because dos2unix can't handle a file that big. I know there is a version dos2unix that will handle 4G files but I'd like to eliminate dos2unix all together, if possible.

What I'd like to do is to generate the files using SSIS and have SSIS create each record with just an LF (which I think can be done) and then FTP in binary mode. It seems this would eliminate the FTP ASCII conversion overhead and eliminate the need for a dos2unix conversion.

Is there anything else that I'm missing? Is this all that dos2unix does? or the FTP ASCII conversion does?

Thanks
khar
Updated on 2013-01-18T07:22:36Z at 2013-01-18T07:22:36Z by SystemAdmin
  • SystemAdmin
    SystemAdmin
    1143 Posts
    ACCEPTED ANSWER

    Re: Recommendation for generating files in Windows to be loaded in Informx/Unix

    ‏2013-01-16T19:16:01Z  in response to SystemAdmin
    Hi,
    instead of dos2unix, you can use tr command. For example:
    tr -d '\015' < oldfile > newfile
    This will delete all ^M characters.
    HTH
    Hrvoje
    • SystemAdmin
      SystemAdmin
      1143 Posts
      ACCEPTED ANSWER

      Re: Recommendation for generating files in Windows to be loaded in Informx/Unix

      ‏2013-01-16T20:44:34Z  in response to SystemAdmin
      Are there any file size restrictions using "tr"?

      But basically I really want to just generate the file on Windows, ftp in binary mode over to Unix, and load into Informix. No other processing, if possible.

      I'd just like to confirm that the only thing dos2unix has been doing for us is to change x'0D0A' to x'0A'.

      Thanks
      • SystemAdmin
        SystemAdmin
        1143 Posts
        ACCEPTED ANSWER

        Re: Recommendation for generating files in Windows to be loaded in Informx/Unix

        ‏2013-01-18T07:22:36Z  in response to SystemAdmin
        AFAIK dos2unix changes only windows (dos) newline to unix (as you wrote 0x0d 0x0a to 0x0a).
        Since tr is working as filter (reading from input file and writing to output file), I'm
        expecting there's no limit on file size - drawback is that you have two copies of this
        (big) file.

        Of course, you can try to produce file on windows with unix like newline characters - I
        do not know what you are using on windows side to produce this file.
        HTH
        Hrvoje