Sequential file performance issue
Moderators: chulett, rschirm, roy
Sequential file performance issue
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 ?
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 ?
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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.
Start from scratch. Monitor the transfer session at the server. i don't know what else to suggest without further details on that.
Franklin Evans
"Shared pain is lessened, shared joy increased. Thus do we refute entropy." -- Spider Robinson
Using mainframe data FAQ: viewtopic.php?t=143596 Using CFF FAQ: viewtopic.php?t=157872
"Shared pain is lessened, shared joy increased. Thus do we refute entropy." -- Spider Robinson
Using mainframe data FAQ: viewtopic.php?t=143596 Using CFF FAQ: viewtopic.php?t=157872
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
http://www-01.ibm.com/support/docview.w ... wg27046170
It's the table load, not the file transfer. I misunderstood that. Thanks, and good luck.
Franklin Evans
"Shared pain is lessened, shared joy increased. Thus do we refute entropy." -- Spider Robinson
Using mainframe data FAQ: viewtopic.php?t=143596 Using CFF FAQ: viewtopic.php?t=157872
"Shared pain is lessened, shared joy increased. Thus do we refute entropy." -- Spider Robinson
Using mainframe data FAQ: viewtopic.php?t=143596 Using CFF FAQ: viewtopic.php?t=157872
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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?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 ?
What about reading the file as a single VarChar field, and effecting the parsing in a downstream Transformer stage?
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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.ray.wurlod wrote:Chances are that there's something else amiss with how the connection to SQL Server is being managed.
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?
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: