Connections in one transaction
Dear friends, Is it possible to make connections to more then one databse on different hosts in one transaction like a link in Oracle? And if it's possible, please, tell me, how can I do it? Thank you in advance. Sent via Deja.com http://www.deja.com/ Before you buy.
helgi73@my-deja.com wrote:
>
> Dear friends,
> Is it possible to make connections to more then one databse on different
> hosts in one transaction like a link in Oracle? And if it's possible,
> please, tell me, how can I do it? Thank you in advance.
Actually it is far easier than a link in Oracle. You just do it, and it
does not matter if the other database is in the same server instance or
another one. Witness:
Suppose you have a sales database that you are currently connected to on the
local server with table 'transactions'. Also on the same server instance is
a sales_contact database with table 'contacts'. Finally on the HR server,
in another building yet ;-), is the compensation database with tables
'salespersons' and 'commissions'. Now the big boss wants to correllate
salespeople who are not earning big commissions last month with the number
of sales transactions and client contacts each made so she can whittle out
the deadwood:
SELECT s.name,
c.bucks,
(SELECT count(*)
FROM transactions t
WHERE t.salespersonid = s.salespersonid) AS n_transacts,
(SELECT count(*)
FROM sales_contact:contacts sc
WHERE sc.salespersonid = s.salespersonid) AS n_contacts
FROM compensation@hr_server:commissions c,
compensation@hr_server:salespersons s
WHERE s.salespersonid = c.salespersonid
AND c.bucks < 2000
AND c.compmonth = (month(TODAY) - 1);
You just use whatever part of the complete ANSI naming/addressing standard
is needed, towit:
'owner'.database@server:'owner'.table.column
So for the 'external' table contacts in the database sales_contact on the
same server I just needed to specify:
sales_contact:contacts
in the FROM clause. However, for the 'external' tables salespersons and
compensation in the remote database compensation on the server 'hr_server' I
needed to include the @server clause as:
compensation@hr_server:commissions
I assume that this is not an ANSI mode database and the owner names are not
required so I left the clutter out. If you use ANSI mode databases you will
need to include the owner name for any database and/or table which the user
does not own. It's simple once you get used to it.
Art S. Kagel
In article <84vnj8$vtv$1@nnrp1.deja.com>, helgi73@my-deja.com wrote: > Dear friends, > Is it possible to make connections to more then one databse on different > hosts in one transaction like a link in Oracle? And if it's possible, > please, tell me, how can I do it? Thank you in advance. > > Sent via Deja.com http://www.deja.com/ > Before you buy. > Hmmm... Never tried this one. Have you tried creating a synonym to the tables you need to reference on the other databases in your primary one? -- # unrm / ksh: unrm: not found # man cpio Sent via Deja.com http://www.deja.com/ Before you buy.
I guess in ESQLC, one can make THREAD SAFE application in which one can create multiple thread for one transaction. Each and every thread can connect to any remote databases. I never tried it but I read it in ESQL manuals. Just a thought... mars1972@my-deja.com wrote: > In article <84vnj8$vtv$1@nnrp1.deja.com>, > helgi73@my-deja.com wrote: > > Dear friends, > > Is it possible to make connections to more then one databse on > different > > hosts in one transaction like a link in Oracle? And if it's possible, > > please, tell me, how can I do it? Thank you in advance. > > > > Sent via Deja.com http://www.deja.com/ > > Before you buy. > > > > Hmmm... Never tried this one. Have you tried creating a synonym to the > tables you need to reference on the other databases in your primary one? > -- > # unrm / > ksh: unrm: not found > # man cpio > > Sent via Deja.com http://www.deja.com/ > Before you buy.