Sequential file performance issue

synsog · Post by **synsog** » Mon Jul 20, 2015 3:51 pm

we have a sequential file FTP from mainframe contains 67million records.
Target is MS SQL server 2008.

In v8.7 datastage, the job took 1hr 50 minutes.

We recently upgraded to v11.3.1.1 and the job is taking 29hrs.

We checked the UVCONFIG file etc every thing is similar and v11.3 box is having bigger RAM and enough memory.
Running with 4node APT configuration file.

Any suggestions or help ?

ray.wurlod · Post by **ray.wurlod** » Mon Jul 20, 2015 5:48 pm

How long does it take to read into a Copy stage as target? That will advise you of the read speed. Chances are that there's something else amiss with how the connection to SQL Server is being managed.

synsog · Post by **synsog** » Tue Jul 21, 2015 7:36 am

Thanks Ray.

I tested with Source Sequential File , Target Copy Stage. Even also its reading 80 records per second which will eventually says it will take 30+hrs to process 67Million records.

What else I can check ?

FranklinE · Post by **FranklinE** » Tue Jul 21, 2015 8:29 am

Looks like an FTP server potential issue, not a DataStage issue. Much depends on your FTP configurations and any extra security layers involved.

Start from scratch. Monitor the transfer session at the server. i don't know what else to suggest without further details on that.

synsog · Post by **synsog** » Tue Jul 21, 2015 2:41 pm

Frank,

We are not having issues with FTP, the file is on Engine server. When we try to load into MSSQL server table it is taking more than a day for 67million.
Today we re-tried on v8.7 , it processed within 2hrs.

PaulVL · Post by **PaulVL** » Tue Jul 21, 2015 2:55 pm

I attened an IBM Webinar on Performance Tuning. Here's the PDF. It may contain some helpful hints.

http://www-01.ibm.com/support/docview.w ... wg27046170

FranklinE · Post by **FranklinE** » Tue Jul 21, 2015 2:59 pm

It's the table load, not the file transfer. I misunderstood that. Thanks, and good luck.

ray.wurlod · Post by **ray.wurlod** » Tue Jul 21, 2015 3:32 pm

synsog wrote:Thanks Ray.

I tested with Source Sequential File , Target Copy Stage. Even also its reading 80 records per second which will eventually says it will take 30+hrs to process 67Million records.

What else I can check ?

That is very unusual. Even with one reader I can get tens of thousands of rows per second out of a Sequential File stage. Is there anything unusual about the file?

What about reading the file as a single VarChar field, and effecting the parsing in a downstream Transformer stage?

rkashyap · Post by **rkashyap** » Tue Jul 21, 2015 7:56 pm

ray.wurlod wrote:Chances are that there's something else amiss with how the connection to SQL Server is being managed.

Same as noted earlier, I suspect that the issue is in loading to SQL Server. Datastage throttles input to match the consumption rate of the output. If loading to SQL server is slow, then upstream read operator would slow down to match it. This slowing down may be appearing as read rate of 80 rows/second.

See section on Buffering on page 27 of Parallel Job Advanced Developer's Guide.

Questions:
- Are you using same SQL Server drivers in 8.7 and 11.3?
- Any change in topology or server location between 8.7 and 11.3?
- Share your job design? What are the stages used?

ray.wurlod · Post by **ray.wurlod** » Tue Jul 21, 2015 9:54 pm

Except, @rkashyap, the OP (@synsog) claims to seem the same result when writing to a Copy stage.

qt_ky · Post by **qt_ky** » Wed Jul 22, 2015 7:26 am

Can your server or storage administrator check into the file I/O performance? Maybe the disk subsystem is screwed up.