JDBC Rollbacks & commits when inserting same data to multiple databases

, ,
1

Client has multiple databases which I have to insert the same data to, I am handling it via looping over the DB list and overriding $connectionName.
However, in case of error in any of the inserts (let’s say 4th of 5) I am supposed to rollback the inserted data from all databases.

I generate unique ID via java UUID to assign each insert a transactionName, then when it fails I loop over each transactionNames and try to rollback. I am using pub.art.transaction for that. However, while running the service it returns given errors:

com.wm.pkg.art.error.DetailedServiceException: [ART.117.4018] Adapter Runtime (Adapter Service): Error while closing transactions at service completion Error:[ART.117.4015] Adapter Runtime (Adapter Service): Error(s) occurred while closing adapter connections.
[ART.117.4015] Adapter Runtime (Adapter Service): Error(s) occurred while closing adapter connections…
[ART.117.4015] Adapter Runtime (Adapter Service): Error(s) occurred while closing adapter connections.
[ART.117.4015] Adapter Runtime (Adapter Service): Error(s) occurred while closing adapter connections.

Any better way to approach this? This is adapters info:

Transaction Type LOCAL_TRANSACTION
DataSource Class com.microsoft.sqlserver.jdbc.SQLServerDataSource

2

Use XA transaction instead of local TX. For more details refer jdbc guide.

If you are interacting with two data sources you can use adapter notification to sync data.

3

Thanks for quick reply!

With that You mean that I could just insert to one database, subscribe to that notification, and based on that insert to second, third, etc? Doesn’t that require DB link to get the inserted data?

4

It’s just a option but I feel it is not a best fit for your requirement and it does require messaging system either um or broker which is additional overhead.

Use XA transaction jdbc connection and handle the transaction by start commit rollback.

Let us know if you have any problems with this.

5

I’ve just read JDBC documentation about XA it and it seems perfect for what I want. If I understood correctly I can just start a single transaction, do multiple inserts to multiple databases and after it commit or rollback that one transaction depending on state?

6

Yes! That’s how it should be two phase commit. Try and let us know how it goes.

7

It seems that I cannot create any new adapter using my newly created XA connection. I can’t create new adapter using the existing LOCAL_TRANSACTION connection either, so I guess issue lies somewhere else. When I pass new XA $connectionName to an existing adapter, select operation works, but insert does not…

Here’s the error:

[ART.114.243] Adapter Runtime (Metadata): Failed to run resourceDomainLookupValues service. Details provided in error log.
[ART.118.5053] Adapter Runtime (Connection): Unable to get a connection to resource db.connection.XAConnectionDB2.
[SCC.126.109] Failed to enlist transaction resources from connection db.connection.XAConnectionDB2(19). Transaction will be aborted.
on where.parameter [Ljava.lang.String;@d733a7b

java.lang.Exception: Adapter values are not available.

at com.softwareag.is.core.adapter.metadata.AdapterMetadataException.<init>(AdapterMetadataException.java:57)

at com.softwareag.is.core.adapter.metadata.AdapterMetadataParameterValue.assertNoErrorString(AdapterMetadataParameterValue.java:3755)

at com.softwareag.is.core.adapter.metadata.AdapterMetadataParameterValue.getValidValuesInternal(AdapterMetadataParameterValue.java:3639)
8

Here’s the error from insert when I inject $connectionName to an existing adapter:

[ART.117.4012] Adapter Runtime (Adapter Service): Unable to run adapter service. Error occurred when connecting to resource db.connection.XAConnectionDB2.
[ART.118.5053] Adapter Runtime (Connection): Unable to get a connection to resource db.connection.XAConnectionDB2.
[SCC.126.109] Failed to enlist transaction resources from connection db.connection.XAConnectionDB2(8). Transaction will be aborted.

Keep in mind, that SELECT adapter works using the same injection…

9

Hi,

I would prefer having a connection and a dedicated adapter service for each database.

Instead of setting $connectionName each time just invoke the corresponding adapter service.

Regards,
Holger

10

I’d prefer that as well, but client wants to have DB list configurable via properties/GV and he wants to add/remove connections without the need to change the code