I have workflow which has multiple sessions. In one of the sessions we are hitting sql server database and generating the data flatfile. Now what is happening is Informatica/Integration service opens the connection and issues the query to database(as per the logs), session log last message is "query issued to the database", session keeps on running(I have left it for 2-3 days even more) and no rows are returned from source(monitoring stats show 0 records). Session doesn't fail or terminate.
When checked with dba, they said connection is opened by the user and is awaiting command from the application. Informatica admin guys say logs are not showing any error or issue, so there is no problem from informatica side.
Same query when directly executed on database gives the result in 2-3 minutes(since its a big table with over 300 million records). We have tried with small set of data also(45k) but still it gets nothing from database and session keeps on running.
Attaching workflow and session logs for reference.
Can anyone suggest or help what can be done in this regards?
The problem is that we are not getting any problem, session keeps on running with zero rows returned.
Same workflow runs perfectly fine(with same amount of records in source) in dev and sit environment, we are facing this issue in production only.
Please help or suggest what can be done to find the issue?