Trying to work out whether this feature might form part of some new
design we're thinking of doing., and I can find very little discussion
of this feature so I'm wondering whether anyone much is using it and
what experience of it there is.

My main concern is performance.

Reading various sources of information (but being a bit confused by page
646 of the book):

(1) ON EXTERNAL DATA SOURCE ... WITH COMMON TRANSACTION

Scenario:

(a) Running a stored procedure in database A
(b) EXECUTE STATEMENT ... ON EXTERNAL DATA SOURCE ... WITH COMMON
TRANSACTION ... on database B
(c) repeat (b) several times
(d) Complete the stored procedure which is running in database A and
commit the transaction in database A.

Suppose the actual statement executed in (b) is updating one field in
one record in a properly indexed table, ie is about the simplest UPDATE
statement possible.

My reading of the material I've found is that

(i) The first time (b) is run a new connection will be opened to
database B and a new transaction will be started
(ii) On subsequent executions of (b) then (provided the connection
string etc matches) both the connection and the transaction will be re-used
(iii) I can't find any documentation of when the connection on database
B get closed, and when and under what circumstances the transaction on
database B gets committed and when and under what circumstances it gets
rolled back

(2) ON EXTERNAL DATA SOURCE ... WITH AUTONOMOUS TRANSACTION

Scenario as above, except AUTONOMOUS instead of COMMON at (b).

My reading of the material I've found is that

(i) Every time (b) is run a new connection will be opened to database B
and a new transaction will be started
(ii) Each time the statement run in (b) completes the transaction in
database B will be committed, unless there was an error in which case it
will be rolled back
(ii) Each time the statement run in (b) completes the connection to
database B will be closed

Have I got that right so far? - in which case please

(A) In case (1), what's the answer to point (iii)?
(B) In case (2), isn't it rather expensive to reopen the connection to
database B every time? Or is the cost of reconnection so little compared
to the cost of starting a new transaction that it doesn't matter? (! -
opening a connection must be several packets across the network and
starting a new server process, so it can't be *that* cheap?)

--
Tim Ward