There are certain queries that are timing out on me.
select count(*) from log
where log has 50-60 million entries
(unfortunately it seems Sybase doesn't shortcircuit this common query to just returning the number of rows in the table)
I've been looking through the documentation for query timeouts, but don't see any.
ODBC has SQL_ATTR_QUERY_TIMEOUT but defaults to infinite unless overridden by the data source.
I was wondering if there was a Sybase parameter which was causing these queries or TCP connections to time out.
Sorry for the delay in getting back to you, but I've been away for a while.
As far as I know there is no "default" timeout for open client. Open client applications may supply a timeout as part of their connection and/or query processing.
Depending on how your client is written, it may be sending asynchronous or synchronous SQL. Synchronous is the simpler to write. Your application sends the SQL and waits for the SQL to return. Asynchronous is when you send the SQL and a callback function is called when the query returns. For the sake of simplicity I'm assuming you're writing a synchronous program.
CS_CONTEXT* p_ctx ;
// Connect to database using context and connection allocation routine
timeout = 5 ; // This is the number of seconds to wait before timing out
ct_config = ( p_ctx, CS_SET, CS_TIMEOUT, (CS_VOID*)&timeout, CS_UNUSED, NULL) ;
Login timeouts can be undertaken using specifying CS_LOGIN_TIMEOUT. Obviously this needs to be setup before connecting to the database.
Quite alright, I rewrote things so the need to fix this is less pressing (with only slight loss in reliability).
I certainly appreciate any response I can get.
Unfortunately, we are only using at the moment Microsoft's ODBC lib to communicate with the database.
That's why I referred to SQL_ATTR_QUERY_TIMEOUT which is set using SQLSetStmtAttr.
Since you say no timeout is provided by default, I assume that means that whether I am connecting using the ODBC layer or this Sybase library you gave, if I don't specify a timeout, it should wait indefinitely.
This definitely puzzles me since looking through the code, I don't see any place where a timeout is set.
As for synch vs asynch, you're correct that all threads that do SQL queries do them synchronously.
I've always assumed that ODBC uses open client at some point, but it is conceivable that connects directly to the ip address and port in the same way as JDBC. I have never seen an ODBC connection to ASE that didn't have Open Client installed on the machine.
Having something installed does not necessarilly mean that it is used by an application. I tend to install Wordpad, but that doesn't mean I use it