I have 2 JDBC adapter services, one AS400 SelectSQL and the other SQL Server InsertSQL, both run successfully individually, but when putting together to run in one flow service, they always fails. It’s log file shows: “…commit failed: more than 1 local trans enlisted…”. According to log file, the records were inserted OK, but were rolled back due to error.
The interesting thing is: if I just STEP THROUGH the whole flow service, no problem; OR if I select the last step and select “TRACE TO HERE”, basically let it run from start-to-end, no problem also!
This problem drives me crazy! Pls pls help!
Jin
FYI: please see the log data under “RUN” (with problem), and under “STEP/TRACE” (without problem):
UNDER “RUN”:
2004-10-11 17:36:50 CDT [ISP.0090.0004C] NdsUserGroup.utils:updAs400Accts: – 426 records inserted into database.
2004-10-11 17:36:50 CDT [SCC.0121.0034E] commit failed: more than 1 local trans enlisted. xid = [FormatId=45744, GlobalId=usrfd-wmqat/1097013631146, BranchQual=1] rxid = {2}
2004-10-11 17:36:50 CDT [SCC.0121.0050I] rollback-only flag is set. rolling back transaction, xid = usrfd-wmqat/1097013631146
2004-10-11 17:36:50 CDT [ART.0114.1007E] Adapter Runtime: Error Logged. See Error log for details. Error: [ART.117.4036] Adapter Runtime (Adapter Service): Unable to commit transaction. Transaction state:Transaction is rolled back .
2004-10-11 17:36:50 CDT [ISS.0015.9998E] Exception –> com.wm.pkg.art.error.DetailedServiceException: [ART.117.4036] Adapter Runtime (Adapter Service): Unable to commit transaction. Transaction state:Transaction is rolled back .
UNDER “STEP/TRACE”:
2004-10-11 17:39:52 CDT [ADA.0001.0101D] Connected to database on “usrfd12.usrfd.perbio.net” with “LOCAL_TRANSACTION”.
2004-10-11 17:39:53 CDT [ADA.0001.0101D] Connected to database on “s10a2411.usrfd.perbio.net” with “LOCAL_TRANSACTION”.
2004-10-11 17:40:04 CDT [ISP.0090.0004C] NdsUserGroup.utils:updAs400Accts: – 426 records inserted into database.
This one drove me crazy too. You need to do a pub.art.transaction:startTransaction in each adapter and then a
pub.art.transaction:commitTransaction before calling adapter services in another adapter.
The reason that you don’t get this when stepping is that each call of the debug step starts in its own thread, and the IS does not manage the transaction over another thread. But when you run the service, it runs in the same thread.
Roger, I think I figured out why. The 2 JDBC Adapter services use 2 different JDBC Connections (one to AS400, one to SQL Server), and both JDBC connections have “LOCAL_TRANSACTION” as TRANSACTION TYPE. The error message of “more than 1 local trans enlisted” makes me think only 1 “LOCAL_TRANSACTION” is allowed somehow. So I made one of the JDBC connection “NO_TRANSACTION” for my SelectSQL adapter service, and keep everything else the same. It works like a charm!
This solution, though works, may not be the best solution. What if I do need both JDBC connection to be "“LOCAL_TRANSACTION”? Maybe I will have to use pub.db services instead of JDBC Adapters.
BTW, I am using wM 6.1, and I did not find pub.art.transaction. They maybe available in earlier versions of wM. But I do have pub.db services which is equivalent.
If I understand correctly, you suggest I use “pub.db:” flow services instead of JDBC adapters. I am sure that will work also, but JDBC adapter is another alternative I would like to use.
pub.art.transaction is in the WmArt package in 6.1 (Art = Adapter Run Time). This will work with JDBC. Everything you are doing will work, you just have to commit the changes on one adapter before doing updates/inserts/deletes on another. And when you debug, you should disable the pub.art.transaction steps.
The pub.Art transaction services availble from IS6.x onwards for Supporting JDBC Adapter Local_Transaction(start,commit,rollback)functionality.
Since you are using IS6.1,You will definetely have the Art services located in the WmART package,check it again.
you can make use of WmDB package like pub.db services also for select,insert,update etc…using execSQL service.For this you need to configure database Alias from the ISAdminConsole/Adapters/WmDB page.
When testing your flow just run the service instead of step thru the service especially when using ART services or else just disable them when debugging the mapping part.
We have this same (or similar) situation but are still getting the message “unable to rollback, transaction is still active” even after applying WM IS6.1 Fix 57.
we have WM 6.1 and JDBC Adapter 6.0.3
Situation is: JDBC adapter for a selectSQL from AS400 (call it connection-A), invoke an existing BackStage 6.0.3 service (which inserts/updates an Oracle database) call it connection-B, and then do a JDBC adapter for an updateSQL from AS400 (connection_A and same table as the selectSQL).
the AS400 Connection (A) is a NO_Transaction and connection-B is a LOCAL_Transaction.
The Question I have is:
Does EACH of these need to have a start transaction/commit/rollback or just the LOCAL_Transaction connection?
Maybe someone can kindly provide a summary level example… such as which is what I currently have:
JDBC Adapter selectSQL (implicit trans)
invoke BackStage service… (which has explicit transaction processing within the service)
JDBC Adapter updateSQL (implicit trans)
The next question I’d have is if I did have them both as Local_Transactions, how would it look?
Please HELP… it’s been a thorn in our side for a while now!
And I assume I am correct in assuming that the no_transaction does not require any commit. And I also assume it gets commited on the adapter service update vs the end of the flow service??
I’m so confused however, because we’re still getting the “unable to commit” error with all that being done
In your case you have two jdbc adapter services which are using two different connection context but with same transaction type(Local Transaction).
This is not directly handled by IS transaction Manager. You cannot keep more than one adapter services with Local transaction using different Connection context in transaction boundary within a flow service unless explicit transaction(pub.art.startTransaction/commitTransaction/rollbackTransaction.)
The best practice is in such cases always use explicit transaction. And use NO_TRANSACTION For all Select Adapter Service.
For more understanding kindly look at mixed transaction example in JDBC ADAPTER User Guide Appendix C.