SIGINT while accessing SQL Server DB via ODBC Enterprise

Bill_G · Post by **Bill_G** » Wed Feb 28, 2007 4:04 pm

we experienced a production issue this morning where some of our jobs that are extracting data from SQL Server via the ODBC Enterprise Stage seem to hang - others connecting to the same DB run to completion. For the jobs that are hanging, I am unable to view data via the ODBC stage (Designer becomes unresponsive). I am also unable to import metadata via manager- again it becomes unresponsive.

I have checked permissions on the tables and I don't see any issues. The application DBA has assured me that nothing has changed in the tables or on the DB configuration.

I am not getting any errors or warnings. We have bounced the ETL server to no avail. Any suggestions are greatly appreciated.

Thanks

Bill

ray.wurlod · Post by **ray.wurlod** » Wed Feb 28, 2007 5:48 pm

Try disabling the job monitor (APT_NO_JOBMON) to see whether it's the source of the signals.

Bill_G · Post by **Bill_G** » Wed Feb 28, 2007 9:00 pm

Sorry Ray, I copied the title from a email section and didn't check before I posted. The SIGINT occurs only when we kill the job after it hangs while trying to extract data from SQL Server.

ray.wurlod · Post by **ray.wurlod** » Thu Mar 01, 2007 12:24 am

Is the behaviour consistent? That is, is it always the same jobs/queries that hang? Have you tried executing the same queries using a client other than DataStage, but from the DataStage server machine? Have you employed the test/diagnostic tools that are in the branded_odbc directory?

Bill_G · Post by **Bill_G** » Thu Mar 01, 2007 7:00 am

The behavior is consistent for the same tables - works for some others it does not. I looked at the diagnostic tools in the branded_odbc directory, but I have not been successful in getting it to work. I did find that the server itself was physically moved from one frame to another the night before our problems arose.

Bill_G · Post by **Bill_G** » Thu Mar 01, 2007 9:24 am

Ray,

Have you had success employing the diagnostic tools? it looks as if it requires me to run a script to create a table on my db. Is that how you have used the tool?

Thanks for the replies.

Bill

Bill_G · Post by **Bill_G** » Thu Mar 01, 2007 9:26 am

Also I turned tracing on, the last few lines look like this:

ppid=4083:pid=4 ff8:b75f52a0 EXIT SQLFetch with return code 0 (SQL_SUCCESS)
HSTMT 0x0863dda8

ppid=4083:pid=4 ff8:b75f52a0 ENTER SQLFetch
HSTMT 0x0863dda8

ppid=4083:pid=4 ff8:b75f52a0 EXIT SQLFetch with return code 0 (SQL_SUCCESS)
HSTMT 0x0863dda8

ppid=4083:pid=4 ff8:b75f52a0 ENTER SQLFetch
HSTMT 0x0863dda8

It never receives the reply from the server to exit the fetch.