Get data from multiple sources - consolidate data

[Former Member]

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

[regdomaratzki]

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.

[regdomaratzki]

Well, I can't figure out how to share right now. Here's the schema of the remote database (the rem.sql I quoted). If you want the full sample, please email me at firstname.lastname@sap.com, where you obviously replace firstname and lastname with my real first and last name.

set option public.Global_database_id = 1;

create table Admin (
  admin_id bigint default global autoincrement(1000000) primary key,
  data     varchar(64)
);

insert into admin(data) values(uuidtostr(newid()));
insert into admin(data) values(uuidtostr(newid()));
insert into admin(data) values(uuidtostr(newid()));
insert into admin(data) values(uuidtostr(newid()));
insert into admin(data) values(uuidtostr(newid()));
commit;

create procedure admin_ui()
result ( admin_id bigint, data varchar(64) )
begin
  select admin_id,data from Admin;
end;

create publication p1 with scripted upload ( 
   table Admin using (
     PROCEDURE admin_ui FOR UPLOAD INSERT
   ),
);

create synchronization user "rem1";
create synchronization subscription to p1 for "rem1" 
  type 'http' address 'host=localhost;port=8001' script version 'v17';
create synchronization profile sp1 'mp=sql;u=rem1;n=p1;verbosity=high;ap=scripted';

create procedure sp_hook_dbmlsync_schema_upgrade()
begin
    message 'In sp_hook_dbmlsync_schema_upgrade';
    drop synchronization profile sp1;
    drop synchronization subscription to p1 for "rem1";
    drop publication p1;
    create publication p1 ( table Admin );
    create synchronization subscription to p1 for "rem1" 
      type 'http' address 'host=localhost;port=8001' script version 'v17';
    create synchronization profile sp1 'mp=sql;u=rem1;n=p1;verbosity=high;ap=normal';
    UPDATE #hook_dict SET value = 'on success' WHERE name = 'drop hook';
end;

[Former Member]

Updating distributed databases using ASA9 is not possible because of the impact it would bring and the high cost. At least it's not an accepted hypothesis at this point. The use of WebServices was considered, however, the large amount of data to be processed daily and the need for manual control of inserts, updates, deletions (and their respective references), led us to think of a "ready to use" solution.

-- A atualização das bases de dados distribuídas que utilizam ASA9 não é possível pelo impacto que traria e elevado custo. Pelo menos não é uma hipóteste aceita neste momento. A utilização de WebServices foi cogitada, porém, o grande volume de dados a ser processado diariamente e necessidade de controle manual de inserts, updates, deletes (e suas respectivas referencias), nos levou a pensar em uma solução "pronta para uso".

[VolkerBarth]

So have you considered using SQL Remote? Of course each type of distributed database (via MobiLink or SQL Remote or by "collecting log contents") will require some kind of globally unique primary keys - if that is not already contained in the existing systems, it might be difficult to add that lateron.

---

I guess it would be helpful if you could provide more information about your requirements, possibilities and limitations, e.g. what transport could be used between the clients and the central instance - HTTPS, FTP, ...?