Selecting data from multiple databases

, ,
1

Hello,

I have an interface where i get a basic notification from Database A (SQl 2005) , based on which i select data from Database A and Database B (As400).

I also have to update a procees indicator in the transaction table of Database A.

What Transaction Type should i use for the connections to database A and database B?

If I use Local_Transaction for both database A and database B, it would mean i would have to use explicit trasactions.

Is there a way to avoid this?

Thanks in advance,
kiranj

2

To make your integration more guaranteed/error handling and to handle commit/rollback facilities, it is advisable to use explicit transactions under WmART package.

In your case, different database comes into picture… Do you have any reason to avoid explicit transaction features ?

3

better idea is to have a stored procedure in DB A, which will do the query accross A & B (give the proper privilege to this Sproc, leave the mess to the DB).
Then you just still connect only to DB A from IS with or without transaction.

4

what is the data volume from DB-B? If its some master data (of less volume) and no updations are there on Db-B, then it may be a better idea to select that data first and keep it in IS till the process is completed and clean it afterwards. This avoids unnecessary calls to DB and use of XA transactions over heterogeneous DBs.

Cheers
Guna