ODBC SQL Server Driver Timeout expired error

[samdsx]

Hi,

I am using datastage version 7.5.2. There are several jobs in the project that connect to SQL Server database using ODBC stage. Randomly the jobs are failing with the below error. Some of the times the jobs are running absolutely fine but other times they fail with time out error.

"APT_CombinedOperatorController(1),0: Fatal Error: Could not connect to datasource[DataDirect][ODBC SQL Server Driver]Timeout expired"

Our DBAs are not being of much help. Any suggestions on how to avoid this error would be helpful. Appreciate any information.

Thanks!

[SURA]

It is one of the error we used to get and can't find the route cause of this issue. At the time of failure, check the SQL Server logs, Unix logs etc to find some clue.


Rerun is the workaround we have.

DS User

[ray.wurlod]

Sounds like you need one or more of the following:
(a) less load on the machine
(b) less load on the connection(s) to SQL Server
(c) a more powerful server (or two)
(d) faster network
(e) more or dedicated network channels

[samdsx]

Thanks for the suggestions Ray. The volume of data is less in our case which is less than 100000 records. Are there any options to set the odbc DSN for this connection on datastage server to never timeout? Please let me know.

[samdsx]

So far we also resorted to restarting the jobs.