Hi,
There are DB views on SQL server, the views access tables from different users/schemas in SQL Server. This SQL server is in a different location/data center, we have some firewall rules to access this SQL server machine from the data center where ODI server exists. We have configured the SQL server access in topology.
This set up has been working since years, more than 5 yesrs, but last week its broke!
When I run the scenario from ODI the step where it shoots the 'Select' query to the SQL server DB from ODI never ends!
couple of things to mention here..
Theere are few DB views which are accessable , there are many views which are not accessable. both reside on the same SQL server machine connection through topology. The onces which are getthing through were very quick the failing onces are the onces runs longer time.
There were no patches or OS updates happened from both the environments .
This continued for two days, during that how many times we tried the connection was just hanging not able to complete the execution.
We tried to trace at network layer, from ODI server side network shows the connection is still active, with no data transfer, but the SQL server network trace showed there was some abort call initated, SQL server teams says the logs are reporting the error as '2' they believes the abort is from the client side. Nothing recorded in ODI logs..
After two days where nothing we were able to find, on third day its started working automatically. It did not work even after the restart of the entire stack where ODI resides. There was no restart at SQL server end as that server environment is a bit complicated to action.
Its working fine now, but how can we investigate tihs further, how can we know what was going on! is there any way we can trace or track at eveny layer where and what is going wrong?
Appriciate your thougts..
Regards
Hesh