How does a high network latency can impact DataStage's connectivity with Databases

Answer

Network latency is a measure of the time it takes a unit of data to travel two points of a network. When analyzing the impact that Network Latency has with DataStage you should look at the latency for a round trip, that is the time it takes the data to make a full circuit. This is necessary because the communication between DataStage Engine and the source or target databases it is generally a 2-way process.

If you think you have high latency and that this can be decreasing the performance of a job you can use the traceroute command (or tracert in windows) to measure the latency. This command sends IP packets to each gateway between 2 points of the network and shows the times for each step. This can help you to identify where the bottleneck is. You should run this command from the DataStage Engine. This is an example of the output:

$ traceroute 9.72.1.2
trying to get source for 9.72.1.2
source should be 9.32.1.3
traceroute to 9.72.1.2 (9.72.1.2) from 9.32.1.3 (9.32.1.3), 30 hops max
outgoing MTU = 1500
1 hostname1 (9.33.1.1) 1 ms 1 ms 0 ms
2 hostname2 (9.33.5.2) 2 ms 2 ms 2 ms
3 hostname3 (9.33.7.4) 2 ms 1 ms 2 ms
4 hostname4 (9.33.8.1) 1 ms 1 ms 1 ms
5 hostname5 (9.33.1.7) 1 ms 1 ms 1 ms
6 hostname6 (9.64.6.1) 7 ms 5 ms 8 ms
7 hostname7 (9.64.7.1) 89 ms 84 ms 84 ms
8 hostname8 (9.72.1.1) 96 ms 95 ms 95 ms
9 hostname9 (9.72.1.2) 104 ms * 104 ms

This output shows that the there are several gateways between these machines and that average roundtrip between the DataStage Engine and the target is around 104 ms. This is a high value for DataStage. For best results in your jobs you should have a latency as small as possible, ideally close to 0ms.

In general terms, there is nothing that can be done from DataStage to improve the latency. This is a network configuration and you should engage your Network Admin to evaluate ways to reduce latency or consider moving the database to a local network area with a lower latency.

Having said that, if you are updating or inserting data into a database, and the stage you are using to connect to the database has an Array Size or Record Count property then you can try to increase these values and compare results. By changing these values you will reduce the number of data exchanges between DataStage and the target Database and thus you may reduce the impact of the high latency. However, please be aware that since this is not addressing the root problem then the improvements may not be significant and can vary in each case.