Hi Joe,

Sorry it took so long to reply. Please crosspost CIS questions to
sybase.public.omni as well, please.

I'm glad you found a workaround, though lowering "cis cursor rows" will
probably negatively affect performance.

Please post your DDL and the problem statement so we can have a look at it.

Thanks,
Bill Menton
CIS QA

jbuhl wrote:

Hi Joe,

One thing I notice when I run a very simple join is that ASE/CIS sends a
TDS_LANGUAGE to ECDA, so the cursor is internal to CIS. On the ODBC Side,
ECDA does a SQLPrepare/SQLExecute. With a sniffer I can see that the ODBC
driver sends this sp_execute, which I assume is related to the statement
that was prepared. I also see a commit issued after all of this so this
implies some sort of locking, though I can never monitor locks when I do it
(my query is very simple so maybe the locks are set quickly so that I cannot
see them at all).

I am guessing that this sproc starts a transaction on MS SQL Server. If I
force a transaction on the ASE/CIS side, I do see that there are some locks
on the ms sql server side, one is "IX", others "IU", "U". I do not know the
details of your join statement, but I suspect the ODBC driver itself is
setting up the locks, based on the type of queries delivered by ASE/CIS.

I also notice that ECDA sets AutoCommit to OFF, which means implicit
transactions are turned on (When I turn OFF the isolation level) - so I am
not sure if this is appropriate or not. It seems that no metter what I set
the Access Service to do, like trying to turn off the implicit transactions,
it does not seem to matter.

This might warrant further research, leading to a possible connection
property on the ECDA to NOT turn on implicit transactions - this might
help - and instead of prepare / execute, maybe the ecda could be forced to
do SQLExecDirect, assuming no parameters are involved in the query (if
parameters are used for some reason, this goes to the dreaded
Prepare/Execute combo - which forces the driver to use sp_execute - which I
find is some sort of undocumented feature of the MS ODBC Driver).

PV


"Bill Menton" <wmenton (AT) sybase (DOT) com> wrote