I am trying to create a integration service with the following flow:
SELECT count() without limit
Gets the “SELECT count()” value and divide it by 1,000 to get the “RepeatCount” using “divideInts” REPEAT with Count == “%RepeatCount%” startTransaction
try
SELECT 1,000 values WHERE integrated = ‘Nope’
Create a doc with 1,000 documents in a document list
Publishes the doc
Update 1,000 values SET integrated = ‘Processing’ commitTransaction
catch roolbackTransaction REPEAT END
But when I try to run the service I’m getting the following errors:
“Error while closing transactions at service completion Error:[ART.117.4015] Adapter Runtime (Adapter Service): Error(s) occurred while closing adapter connections.”
This error indicates that you’re calling multiple Adapter services which are from multiple JDBC Adapter connections configuring with “LOCAL_TRANSACTION” which are not allowed/recommended.
If your flow contains adapter services that use more than one
LOCAL_TRANSACTION connection, you must use explicit transactions.
For implicit transaction:
"A single transaction context can contain any number of XA_TRANSACTION connections but no more than one LOCAL_TRANSACTION connection.
Solution:
If you want to call multiple adapter services from multiple JDBC Adapter connection, you’d need to do the following modification to address this issue:
Change the JDBC adapter connection to use with “XA_Transaction” and not “LOCAL_TRANSACTION”.
If you are using one DB connection then use “No_transaction”(If you don’t mind to implicit commit after every insert).
startTransaction
Main
try
SELECT count() without limit
Gets the "SELECT count()" value and divide it by 1,000 to get the "RepeatCount" using "divideInts"
REPEAT with Count == "%RepeatCount%"
SELECT 1,000 values WHERE integrated = 'Nope'
Create a doc with 1,000 documents in a document list
Publishes the doc
Update 1,000 values SET integrated = 'Processing'
commitTransaction
catch
rollbackTransaction
please try if this works when using a second connection with NO_TRANSACTION type for the select and only use LOCAL_TRANSACTION for the Update.
You can update the connection in the service directly without having to delete and recreate it by using helper service from WmART package. Check for the setAdapterServiceNodeConnection service.
Reload the package afterwards to get the service updated in server memory
I want to know what you are publishing and how, is it JMS publish ? if so check the transaction type in JMS connection alias. Ensure all your connections have common transaction types. Else use XA transaction time.
can you provide some more details how the update is implemented?
Is it looping over 1,000 entries or is it just a batch update on all 1,000 entries at once?
This might make a difference on how the update is dealing with transaction handling.