Communicating between two difft. databases on difft. instances 1

[gbag]

Hi,
I need some help in updating current table with the info of another db on another instance.
I need to read some data from one DB [db1] and update [and insert] another database [db2]. Both the databases are on different systems [s1 and s2]. On one of the systems I can see connect to both databases, but not simultaneously.

Basically my requirement is something like this.
insert into t1@db1.s1 values (select * from t1@db2.s2)

Can anybody point me how and where to start.
Thanks in advance
gbag.

 

[Hattusas]

You need to export your data from your source database and import the exported data into your target server.That is wahat we are currently doing between AIX and Windows,if required.

Salih Sipahi
Software Engineer.
City of Istanbul Turkey
s.sipahi@sahinlerholding.com.tr

 

[gregsimpson]

gbag,

are three part names the answer.

The parts in a three part name are are data-source-name, remote-schema-name, and remote-table-name.

something along the lines of

t1.db1.sq

A fully qualified table name is as follows:

Rdbname.schema.table

where, for example,

Rdbname = sample
schema = connxdb2
table = customers_db2


Cheers
Greg

 

[gbag]

It did not work.
Can you pls give me a working example.
Thanks

 

[juliane26]

Hi gbag,

you can create a nickname for that (federated object).
Then you can access remote tables like local tables.
Be aware that DB2 does not support two-phase-commit, so you can only read remote data, it can be updated, but only one data source per commit scope.

To do so you need the following steps:

- catalog the remote database
- on the local database do a 'create wrapper', 'create server' and 'create nickname' (all in the 'federated database objects' folder in control center)
- in the 'create nickname' statement you qualify the column mapping and table name mapping
- then you can select the data using the nickname.

Thats it. In 8.2 that is a little more straight forward, since the control center will have a 'nickname' folder and provide a wizard to do the rest for you.

 

[gbag]

I was thinking that federated servers are for communicating with different types of database servers with DB2.
Thanks

 

[juliane26]

It is a similar procedure for other data sources than DB2, they are all 'bundled' as nicknames under the federated servers.

In that case the wrapper will be an Oracle or Sybase or ODBC or whatever wrapper, and you need a local client installed to access them.