When I use DB connector and if let us say that the SQL query / Procedure runs for hours, is there any timeout handled by Dell Boomi and make the process failed after timeout ?
The Database Connector does not currently have a timeout setting. Other users have requested similar functionality at this Idea link which you can vote/comment on to raise visibility:
Specify a Process max run time or timeout per process or per atom
A possible approach is create a process that uses the AtomSphere API to query the Execution RecordObject for your specific process and filter if the Status is INPROCESS or STARTED. Check if the executionTime is greater than the desired time when you would like for it to timeout and if the execution passes that criteria, you can then use the AtomSphere API API Actions to cancel the execution. I haven't tried this myself, but in theory i think i could work.
Hope this helps,
Retrieving data ...