Get data from multiple sources - consolidate data

[#1]

Recently I was given the need to make queries on cross-bound and consolidated data from multiple tables and on data from multiple clients. The information is only in one sense "Client -> Base Centralizer", do not need all information, are just some tables chosen. All clients run Sybase Adaptive Sql Anywhere 9.0.2.3951

The "centralizing" structure is brand new and can be assembled as we wish, could use current versions! What would be the suggestion to feed this centralizing base? Mobilink?

Many thanks!

DRauber

[Breck_Carter]

For your purposes the Remote Data Access "proxy table" feature might be a bit easier to work with than MobiLink.

Proxy tables created on (for example) SQL Anywhere 16 can be used represent local copies of SQL Anywhere 9 tables or views. An INSERT ... SELECT FROM a proxy table will "pull" the data across from SQL Anywhere 9 to SQL Anywhere 16 and store it in a local real table.

The following example shows some code run on a SQL Anywhere 16 database to "pull" data across from a SQL Anywhere 9 database. The ODBC "DRIVER=SQL Anywhere 16" is used because it works fine with SQL Anywhere 9 databases, AND because "DRIVER=Adaptive Server Anywhere 9.0" is a PITA on modern computers 🙂

-- Simple SQL Anywhere 16 and 9 setup

BEGIN
   DROP TABLE local_table;
   EXCEPTION WHEN OTHERS THEN
END;

CREATE TABLE local_table (
   local_column SMALLINT NOT NULL );

BEGIN
   DROP TABLE proxy_RowGenerator;
   EXCEPTION WHEN OTHERS THEN
END;

BEGIN
   DROP SERVER ddd9;
   EXCEPTION WHEN OTHERS THEN
END;

CREATE SERVER ddd9 CLASS 'ASAODBC' USING 'DRIVER=SQL Anywhere 16; ENG=ddd9; DBN=ddd9; UID=DBA; PWD=SQL;';

CREATE EXISTING TABLE proxy_RowGenerator AT 'ddd9..dbo.RowGenerator';

FORWARD TO ddd9 { SELECT @@VERSION AS remote_version };

SELECT @@VERSION AS local_version;

INSERT local_table ( local_column )
SELECT row_num 
  FROM proxy_RowGenerator
 WHERE row_num <= 10;

COMMIT;

SELECT local_column
  FROM local_table
 ORDER BY local_column;

remote_version
'9.0.2.3951'

local_version
'16.0.0.2512'

local_column
1
2
3
4
5
6
7
8
9
10

[regdomaratzki]

Breck's idea is excellent (and simpler) if you can access all the remote databases you want to query from a single location.

My suggestion assumed that you did not have access to all the databases.

[Former Member]

Unfortunately, I do not have access to all the servers running the database! Few of the distributed databases have external access, and some cases that have access are precarious. Thank you!

-- Infelizmente, não tenho acesso a todos os servidores rodando o banco! Poucas das bases de dados distribuídas tem acesso externo, e alguns casos que tem acesso é precário. Muito obrigado!