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

MobiLink is an excellent solution to consolidate data from multiple SQL Anywhere databases to a single centralized database. That centralized database could be another SQL Anywhere database, or any number of RDBMS supported by MobiLink.

The trickiest part of the project is taking all the data in the distributed databases and getting it to the centralized database. My suggestion is as follows :

1) Create the schema of your centralized database with the exact same schema as the remotes, except each table now has another column in the table to identify which database the row came from, and this column is now part of the primary key at the centralized database.

2) Use a feature of MobiLink clients called scripted uploads to initially bulk load the data in the remote databases. Scripted uploads allows you to write a stored procedure whose result set will be the data to be uploaded. You essentially create a stored procedure that executes "select * from table_name" and the whole table is uploaded. Do this for each table you want to synchronize.

3) Once you've confirmed that an upload using scripted upload was successful, stop using scripted upload, and instead use the "regular" method to synchronize, which involves scanning the transaction log and sending all changes made to the remote to the centralized database.

Scripted uploads is not a feature that is available in SQL Anywhere v9, so an upgrade to a more recent version at the clients will be needed, or a more complicated method to load the data from the remotes to the consolidated could be found.

I've attached a sample that uses SQL Anywhere v17 to do what I've described above. You don't need to run the sample, but if you look at the rem.sql file in the ZIP file, you'll see how I've initially populated the remote database with some data, and then defined a publication with scripted upload to use the admin-ui stored procedure to synchronize the initial data. The procedure called sp-hook-dbmlsync-schema-upgrade is a special procedure that runs only when there is a successful synchronization. You can see that the stored procedure drops the scripted upload publication and defines a regular (or log scanning) publication.

If you want to run the sample :

1. Unzip the contents of the ZIP file into an EMPTY directory
2. Open a DOS prompt and CD into the directory when the files exist
3. Make sure SQL Anywhere v17 is in your path and the %SQLANY17% environment variable is set
4. Type mlrep

Hope that helps,

Reg

PS: I'm only working part time right now, and will be offline for a week or so after this. My response will be delayed, but hopefully Volker or Breck can step in if you have questions.

Just to add: In case you cannot access the client databases directly (in contrast to Breck's suggestion), and the data you want to collect is rather simple (say, just a few tables without deeper foreign key relationships): As an alternative to MobiLink (or SQL Remote), you might also consider using a web service to collect those. The consolidated could establish a web service and the ASA 9 clients could then use the web client feature to call the service and supply their data. (AFAIK ASA9 does support web services and web client procedures/functions.)

If that is easier to implement than a MobiLink solution will depend on the complexity of the data structures and your knowledge w.r.t. both technologies...

Yet another possibility is to run UNLOAD SELECT ... FROM ... TO 'filespec.txt' statements on the SQL Anywhere 9 database, manually transport the filespec.txt files to the central computer, and run LOAD TABLE FROM 'filespec.txt' statements on the SQL Anywhere 16 database.