Datastage job got stuck when extracting data from SQL SERVER

[ray.wurlod]

What's different between the two environments? Hint: "nothing" is not the correct answer.

[Rakesh311]

Its not NOTHING ..Both SQL SERVER environments have differences.

Below are those differences
DEV:

OS:-WINDOWS 2003
Number of CPU -2


Stage :

OS- Microsoft Windows Server 2008 Enterprise
Number of CPU-8

[boxtoby]

Hi,

I think we need to know a bit more about what is going on here:

I assume the job is hanging after extracting data from the database using one of the database connect stages. If so, does the sql statement perform well when run from the SQL Server IDE?

What sort of volumes are being returned between dev and stage?

What is the job doing? Has it got many other stages? For example, it might be waiting for a look up link to complete somewhere else in the job.

Is the stage you are referring to a source feed or look up feed?

If the volumes are high, as you say, are look ups done with join stages?

Cheers,
Bob.

[FranklinE]

There's not much to go on, but check my topic at viewtopic.php?t=151911 in case it might help.

[crystal_pup]

Try reducing the Array Size value to 1

[kduke]

Are you sure it is getting locked on update? Are you using (NOLOCK) on your extracts? Could be locking on extract. If not then are you updating more than one row?

[Rakesh311]

We were getting ASYNCHRONOUS_NETWORK_IO lock SQL server.

After Working with IBM,Microsoft, Network Team we resolved the issue.
This is due to some "Advanced" network setting of Microsoft Windows Server 2008.

This is what we done

1. In the Property list, click TCP/IP Offload, click Disable in the Value list, and then click OK.
2. In the Property list, click Large Send Offload, click Disable in the Value list, and then click OK.
3. Disable other Offload properties for IPV4

Now things are working fine


Thankyou everyone !!