Reading using FTP Stage - Need to improve performance

vnspn · Post by **vnspn** » Tue Mar 13, 2007 1:02 pm

Hi,

We are using DS Server Edition, 7.1 version. Here is the process that we are doing in the Job.

We have just 3 stages in the Job - a FTP, a Transformer and a Sequential File stage. Using the FTP stage we are reading from a file from a remote Mainframe server. In the Transformer we use DS transforms to convert the EBCDIC to ASCII. Then, write it to a Sequential file.

The number of records to be extracted from the Mainframe file is so hugh (around 40 million). I test ran the Job with 0.5 million records and it took 1 hour for the Job to complete. With this speed we cannot process the 40 million records. We think the major cause for this slow run, is because of the FTP stage. The FTP stage executes only at 120 rows/sec.

Please let me know if there are any possibilities in improving the performance of this scenario, like trying to change the settings, etc. Or also let us know if any better alternate method would be preferrable for performing this task above said.

Thanks.

DSguru2B · Post by **DSguru2B** » Tue Mar 13, 2007 1:18 pm

I doubt it. I suspect the Ebcidic to Ascii conversion is the bottleneck. For testing, ftp the file, without any transformations, just a straight load to a flat file.

chulett · Post by **chulett** » Tue Mar 13, 2007 3:00 pm

Ugh. Or just use a tool designed to move Mainframe/UNIX files at high speed and do the EBCDIC to ASCII conversion on the fly during the transfer.

narasimha · Post by **narasimha** » Tue Mar 13, 2007 4:36 pm

Write a ftp script to move the file into your datastage server first.
Now that you have the file locally, do your transformation from EBCIDIC to ASCII.

The other factor affecting the performance could be network delays.
Try to identify the bottleneck whether it is - FTP Stage, Transformation, Network Traffic....

kumar_s · Post by **kumar_s** » Tue Mar 13, 2007 4:42 pm

Dont you have CFF stage pluging? If so, as noted, FTP the file using script and use CFF to Extract as well as covert into Ascii.

ray.wurlod · Post by **ray.wurlod** » Tue Mar 13, 2007 5:40 pm

PUSH the file (in binary mode) from the mainframe, rather than pulling it from the DataStage server. Use the CFF stage to read it.

vnspn · Post by **vnspn** » Wed Mar 14, 2007 2:59 pm

DSguru2B,

As you said I split this process into 2 Jobs - First one to only FTP the file and the Second one to covert the data from EBCDIC to ASCII. I could find that the bottleneck is in FTP ing using the FTP stage.

The first Job using the FTP stage to just transfer the data runs for the same 1 hour (as mentioned in the original post). But the second Job transforming EBCDIC to ASCII for the same number of records ran for only 6 minutes.

So, its the FTP stage that slowing the processing. Anyone have any idea in improving the performance of the FTP stage.

The reason I didn't want to move the file completely was, the full file is going to be of size about 300 GB. Thats why I wanted to process it directly by reading it from the remote server instead of moving such a big(comsumption of hugh disk space).

Please let me know any sample FTP batch script to fetch a file from Mainframes to Windows.

Thanks.

DSguru2B · Post by **DSguru2B** » Wed Mar 14, 2007 4:13 pm

Search for ftp scripts on google or as Craig mentioned, any tool that the mainframe guys use to ftp files, I think its NDM, not sure. Get them to push the file whereever you want. Connect Direct is another tool used for fast and secure ftps.
Also while doing the ftp, monitor the network to find out how heavily loaded it is. Clearly the bottleneck is the ftp and not the transformations itself.

chulett · Post by **chulett** » Wed Mar 14, 2007 5:39 pm

And as an added bonus, those file transfer utilities can typically do the EBCDIC to ASCII transfer on the fly so you don't have to.