Sybase JDBC 603 and Explicit Transactions

I’m using the JDBC driver 6.0.3 to connect to Sybase (12.5.0). I have the connection set up as LOCAL_TRANSACTION. I have an adapter service created to call a stored procedure. From a flow service, I will call upon the stored procedure.

If I use the WmART package to define an explicit transaction (using pub.art.transaction.startTransaction - with a hard coded transaction name, and pub.art.transaction.commitTransaction - with the same transaction name) wrapped around the call to the adapter service, everything appears to operate normally as I do not get any exception errors thrown. When I go to the Sybase database, I find out that the new record was not inserted.

If I remove the calls to make it an implicit transaction, the new record is inserted into the database (and verified in the database outside of webMethods). If I put the explicit transaction calls back in and change the parameters in the adapter service (remove a field), I will get an error message back from Sybase about the missing field.

Apparently, I don’t have problems in communication to Sybase within an explicit transaction, but if I want the data to actually be processed, it only seems to work when done as an implicit transaction.

Eventually, this transaction will be done in conjunction with a stored procedure call to an Oracle database (using an XA_TRANSACTION connection). I’ve also done an explicit transaction that calls the Oracle stored procedure first and then the Sybase stored procedure before committing the transaction. When it runs, no exceptions are thrown. The result is that the record is inserted into Oracle, but nothing is inserted into Sybase.

I’ve even placed the Sybase stored procedure call into it’s own isolated flow service and called the flow from the outer explicit transaction after the call to the Oracle stored procedure. Again, only the Oracle database showed a new record. No exception from Sybase was thrown, so no rollback occurred.

The DBA that manages the Sybase server is looking to get a license so I can try to change the Sybase connection from LOCAL_TRANSACTION to XA_TRANSACTION, but I don’t have any guarantee that I’ll have this opportunity.

Has anyone been able to perform explicit transactions to Sybase successfully?

This work is a result of migrating a process that is currently working in Enterprise 5.0 using proprietary clients to both Oracle and Sybase. We’ve been able to use the JDBC drivers to work with Oracle successfully in other integrations, but this is our first try to use JDBC drivers to work with Sybase.

Any and all help is greatly appreciated!

Paul

Paul which jdbc driver are u using? try datadirect in case you haven’t.

I am using the driver that came from jConnect 5.5. The driver is packaged in jconn2.jar which has been added to the classpath. For the connection configuration, here’s more info:

The Transaction Type is set to LOCAL_TRANSACTION. The DataSource Class is set to com.sybase.jdbc2.jdbc.SybDataSource. The Other Properties field remains empty.

For the JDBC Driver Alias under the settings for the JDBC Pools, the driver class used is com.wm.dd.jdbc.sybase.SybaseDriver which is the DataDirect Connect JDBC OEM driver that comes with JDBC 6.0.3.

Here’s an update in my further testing and debugging…

I have been able to successfully run an exclusive transaction that calls one Oracle stored procedure (XA connection) and one Sybase stored procedure (local connection). Each stored procedure is set up as an adapter service. So it follows this flow design:

startTransaction (from WmART package)
try block
adapter service call to Oracle stored procedure
adapter service call to Sybase stored procedure
commitTransaction (from WmART package)
catch block
rollbackTransaction (from WmART package)

So it appears that a simple transaction works. I’ve now move on to debug more complex transactions that involve calling multiple stored procedures to both Oracle and Sybase (still only one Oracle database and one Sybase database).

Our Sybase DBA is trying to get a demo license for their DTM component to allow XA transactions. In the meantime, I can only define the connection to the Sybase database as a LOCAL_TRANSACTION.

Could each adapter service be attempting it’s own connection to Sybase? If so then that would mean multiple LOCAL_TRANSACTION connections within a single explicit transaction which isn’t allowed.

Paul in local transaction mode each resource manager involved is separately coordinating its own changes, and only its changes, rather than having a sync point coordinator, such as RRS or wmART, coordinate them. In other words this transaction will involve one service within a single server program, and at max will access only one database and I think this is why you are seeing this behaviour I suggest waiting till you SA comes up with XA license for sybase. In terms of adapters yes they do create their own connections to the database but since new transaction is in context of XA its co-ordinated by ART.

So what you’re stating is that for the local Sybase connection, each adapter service configured to that local connection will attempt to manage their own transaction and will not cooperate with any outside sync point like what’s found in WmART. Wouldn’t that then imply that even though I’ve defined the beginning of an explicit transaction, the calls to the local connection will actually behave as several implicit transactions but won’t commit due to the outer explicit call?

The examples in the JDBC User’s Guide only show a transaction that makes only one call to a local connection and not multiple calls to the same local connection.

So I guess I have two choices: 1) get the DBA to buy the license so I can change the connection to XA, or 2) build massively nested transactions for each Sybase call.

You got it, In explicit transactions you can only use one local transaction connection at most, for more than one you would have to use XA. Due to this limit using nested transactions within one might work for you, give it a shot and see what happens but if you have more than one local transactions within a explicit transaction with XA then it won’t and I suggest getting a license.