Thanks for the feedback!
First of all, exactly what do you mean by “do things” in key order?
Here’s some more background; hoping this will be helpful!
In this case, we are issuing SELECT statements that involve joining several tables.
To expand on my earlier statement of the queries otherwise being “just fine”, these queries normally complete very quickly regardless of where it’s being run from: SnapLogic “Generic JDBC Execute” Snap, or a tool like “averSQL”, or Sybase’s own “Sybase Central.”
The query hangs only in SnapLogic, and it does not do it every time.
We have yet to be able to reproduce it on demand; it’s sporadic.
Note that averSQL uses Windows-native ODBC, not JDBC, and Sybase Central uses the Sybase-native connection API. That makes me suspect the problem is driver-specific. A few days ago, I found a comment elsewhere online where someone using the JConnect (jconn4.jar) driver complained about similar behavior, and Sybase recommended using the (newer) “iAnywhere” JDBC-ODBC bridge drivers. But… (a) I haven’t been able to re-find the comment, and (b) we haven’t been able to get the bridge drivers working on our GroundPlex. (For some reason it can’t find the dbjodbc9.dll file.)
We suspect that the hangs happen when the queries involve one particular table, which is a change log. The change log table has NO primary key defined! (I know: bleah!) It does have 1 unique index (on 5 columns), and 3 other non-unique indexes (one on 4 columns, two others on two columns… hm… and the two others seem to be identical: wow, what’s up with that?)
I think that a deadlock is extremely unlikely, as we’re experiencing this in testing where the only two processes accessing the database are our application (1 QA user) and the SnapLogic pipelines themselves. The QA person makes some changes with the application, then kicks off the pipelines, then checks our central database to see if certain changes have taken place in the data there.
To the best of my knowledge, most if not all of the other tables have PKs. If I can reproduce the problem, I’ll post more details regarding the actual query and the specs of the tables involved. Maybe we can find a problem hiding in those details.
Second, I’m not sure how to find the defaults for Sybase.
I’ve worked with MS SQL Server, Oracle, PostgreSQL, MySQL, and MaxDB… but it’s been over 20 years since I’ve touched Sybase, so I’m essentially approaching it anew. How would I check to see if it is defaulting to clustered? Does it still matter since the potentially problematic table has no PK?