on 2013 Sep 20 7:06 PM
I'm trying to implement a routine synchronization and am having some difficulties. One of the tables has ~ 100000 records and every time I run the sync that table is "swept" by entire process and is getting very time consuming. Must be seen as only the difference between the tables? Another detail: only the record that was entered and updated being synchronized with the other db, deleted the records are not being deleted in another db (how to solve this too?) I'm using Adaptive Server Anywhere Initialization Utility Version 9.0.2.3951
The command lines I'm using are these:
CREATE table TBidentifier ( emp char(3) not null, id INTEGER identity not null, description char(20), PRIMARY KEY (emp, id)); BEGIN DECLARE I INTEGER; DECLARE TOTAL INTEGER; SET I = 1; SET TOTAL = 100000; WHILE I <= TOTAL LOOP INSERT INTO TBidentifier (emp, description) VALUES ('001','TEST SEQ '||CAST(I AS CHAR) ); SET I = I + 1; END LOOP; END; create publication Lab_all ( table tbidentifier ) create synchronization user "simple" CREATE SYNCHRONIZATION SUBSCRIPTION TO "DBA"."Lab_all" FOR "simple" TYPE 'TCPIP' ADDRESS 'host=localhost;port=2439' OPTION scriptversion='Lab01'; dbinit center.db dbinit remote.db dbeng9 center.db dbeng9 remote.db dbmlsrv9 -c "dsn=center" -x tcpip -o mlserver.mls -v+ -dl -za -zu+ -tu dbmlsync -c "dsn=remote" -o dbmlsync.out -v -e "SendColumnNames=ON"
[]'s
I assume you are talking about the download, not the upload... the download_cursor does not have any WHERE clause so it will download the entire table every time:
download cursor: SELECT emp, id, description FROM TBIDENTIFIER
You might enjoy Chapter 7 Synchronizing in my book SQL Anywhere Studio 9 Developer's Guide.
Here's the SQL Anywhere 9 SQL code for a MobiLink demo; first, the consolidated database, then the remote...
Consolidated...
--------------------------------------------------------------------- SET OPTION PUBLIC.GLOBAL_DATABASE_ID = '0'; --------------------------------------------------------------------- BEGIN DROP TABLE t1; EXCEPTION WHEN OTHERS THEN END; CREATE TABLE t1 ( key_1 UNSIGNED BIGINT NOT NULL DEFAULT GLOBAL AUTOINCREMENT ( 1000000000 ), key_2 INTEGER NOT NULL DEFAULT 0, non_key_1 VARCHAR ( 100 ) NOT NULL DEFAULT '', non_key_2 VARCHAR ( 100 ) NOT NULL DEFAULT '', last_updated TIMESTAMP NOT NULL DEFAULT TIMESTAMP, PRIMARY KEY ( key_1, key_2 ) ); --------------------------------------------------------------------- BEGIN DROP TABLE t2; EXCEPTION WHEN OTHERS THEN END; CREATE TABLE t2 ( key_1 UNSIGNED BIGINT NOT NULL DEFAULT GLOBAL AUTOINCREMENT ( 1000000000 ), key_2 INTEGER NOT NULL DEFAULT 0, non_key_1 VARCHAR ( 100 ) NOT NULL DEFAULT '', non_key_2 VARCHAR ( 100 ) NOT NULL DEFAULT '', last_updated TIMESTAMP NOT NULL DEFAULT TIMESTAMP, PRIMARY KEY ( key_1, key_2 ) ); --------------------------------------------------------------------- CALL ml_add_table_script ( 'v1', 't1', 'upload_insert', NULL ); CALL ml_add_table_script ( 'v1', 't1', 'upload_insert', ' INSERT t1 ( key_1, key_2, non_key_1, non_key_2 ) VALUES ( ?, ?, ?, ? )' ); --------------------------------------------------------------------- CALL ml_add_table_script ( 'v1', 't1', 'upload_update', NULL ); CALL ml_add_table_script ( 'v1', 't1', 'upload_update', ' UPDATE t1 SET non_key_1 = ?, non_key_2 = ? WHERE key_1 = ? AND key_2 = ?' ); --------------------------------------------------------------------- CALL ml_add_table_script ( 'v1', 't1', 'upload_delete', NULL ); CALL ml_add_table_script ( 'v1', 't1', 'upload_delete', ' DELETE t1 WHERE key_1 = ? AND key_2 = ?' ); --------------------------------------------------------------------- CALL ml_add_table_script ( 'v1', 't1', 'download_cursor', NULL ); CALL ml_add_table_script ( 'v1', 't1', 'download_cursor', ' SELECT key_1, key_2, non_key_1, non_key_2 FROM t1 WHERE last_updated > ?' ); --------------------------------------------------------------------- CALL ml_add_table_script ( 'v1', 't2', 'upload_insert', NULL ); CALL ml_add_table_script ( 'v1', 't2', 'upload_insert', ' INSERT t2 ( key_1, key_2, non_key_1, non_key_2 ) VALUES ( ?, ?, ?, ? )' ); --------------------------------------------------------------------- CALL ml_add_table_script ( 'v1', 't2', 'upload_update', NULL ); CALL ml_add_table_script ( 'v1', 't2', 'upload_update', ' UPDATE t2 SET non_key_1 = ?, non_key_2 = ? WHERE key_1 = ? AND key_2 = ?' ); --------------------------------------------------------------------- CALL ml_add_table_script ( 'v1', 't2', 'upload_delete', NULL ); CALL ml_add_table_script ( 'v1', 't2', 'upload_delete', ' DELETE t2 WHERE key_1 = ? AND key_2 = ?' ); --------------------------------------------------------------------- CALL ml_add_table_script ( 'v1', 't2', 'download_cursor', NULL ); CALL ml_add_table_script ( 'v1', 't2', 'download_cursor', ' SELECT key_1, key_2, non_key_1, non_key_2 FROM t2 WHERE last_updated > ?' ); --------------------------------------------------------------------- INSERT t1 VALUES ( DEFAULT, 1, DEFAULT, DEFAULT, DEFAULT ); COMMIT; --------------------------------------------------------------------- INSERT t2 VALUES ( DEFAULT, 1, DEFAULT, DEFAULT, DEFAULT ); COMMIT;
Remote...
--------------------------------------------------------------------- SET OPTION PUBLIC.GLOBAL_DATABASE_ID = '1'; SET OPTION PUBLIC.DELETE_OLD_LOGS = 'DELAY'; --SET OPTION PUBLIC.DEFAULT_TIMESTAMP_INCREMENT = '1000000'; -- 1 sec --SET OPTION PUBLIC.TRUNCATE_TIMESTAMP_VALUES = 'ON'; --------------------------------------------------------------------- BEGIN REVOKE CONNECT FROM REMOTE_DBA; EXCEPTION WHEN OTHERS THEN END; GRANT CONNECT TO REMOTE_DBA IDENTIFIED BY SQL; GRANT REMOTE DBA TO REMOTE_DBA; --------------------------------------------------------------------- BEGIN DROP SYNCHRONIZATION SUBSCRIPTION TO p1 FOR "1"; EXCEPTION WHEN OTHERS THEN END; BEGIN DROP SYNCHRONIZATION USER "1"; EXCEPTION WHEN OTHERS THEN END; BEGIN DROP PUBLICATION p1; EXCEPTION WHEN OTHERS THEN END; --------------------------------------------------------------------- BEGIN DROP TABLE t1; EXCEPTION WHEN OTHERS THEN END; CREATE TABLE t1 ( key_1 UNSIGNED BIGINT NOT NULL DEFAULT GLOBAL AUTOINCREMENT ( 1000000000 ), key_2 INTEGER NOT NULL DEFAULT 0, non_key_1 VARCHAR ( 100 ) NOT NULL DEFAULT '', non_key_2 VARCHAR ( 100 ) NOT NULL DEFAULT '', last_updated TIMESTAMP NOT NULL DEFAULT TIMESTAMP, PRIMARY KEY ( key_1, key_2 ) ); --------------------------------------------------------------------- BEGIN DROP TABLE t2; EXCEPTION WHEN OTHERS THEN END; CREATE TABLE t2 ( key_1 UNSIGNED BIGINT NOT NULL DEFAULT GLOBAL AUTOINCREMENT ( 1000000000 ), key_2 INTEGER NOT NULL DEFAULT 0, non_key_1 VARCHAR ( 100 ) NOT NULL DEFAULT '', non_key_2 VARCHAR ( 100 ) NOT NULL DEFAULT '', last_updated TIMESTAMP NOT NULL DEFAULT TIMESTAMP, PRIMARY KEY ( key_1, key_2 ) ); --------------------------------------------------------------------- CREATE PUBLICATION p1 ( TABLE t1 ( key_1, key_2, non_key_1, non_key_2 ), TABLE t2 ( key_1, key_2, non_key_1, non_key_2 ) ); CREATE SYNCHRONIZATION USER "1" TYPE tcpip; CREATE SYNCHRONIZATION SUBSCRIPTION TO p1 FOR "1"; --------------------------------------------------------------------- INSERT t1 VALUES ( DEFAULT, 1, DEFAULT, DEFAULT, DEFAULT ); COMMIT; --------------------------------------------------------------------- INSERT t2 VALUES ( DEFAULT, 1, DEFAULT, DEFAULT, DEFAULT ); COMMIT;
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
I did some tests and it worked INSERT and UPDATE. Only that DELETE still could not make it work. Something more should be done?
Just for understanding Remote would be my main base? (Have a main base (main store) where all other stores (secondary stores) connect and perform sales. Upon failure of communication want to start a local database (in the secondary store that lost connection so they can work normally), so the connection is reestablished will be synchronization between the store and the secondary main base.
What I am trying to do is delete record in database Consolidated (secondary store) and even be deleted in remote database.
thanks
Other way around... the consolidated database is (usually) the central or main database, and there are multiple remote databases that (usually) have a subset of the data contained on the consolidated database; e.g., a head office (consolidated) database and multiple store (remote) databases.
But... maybe you want high availability instead of synchronization: primary, secondary and arbiter databases, with automatic failover (secondary becomes the primary) when a problem happens.
In other words, maybe you don't want Mobilink at all... see this Help topic, and this demo.
No, Database Mirroring (aka HA) was introduced with v10:
Version 9 has live backups, which is an Old School version of HA where you have "pretty fast" failover from the primary to the secondary. See the V9 Help file...
ASA Database Administration Guide
Backup and Data Recovery
Backup and recovery tasks
Making a live backup
Also see page 386 section 9.12.5 Live Log Backup in my book.
Returning to sync (not that I know everything now live on backup and mirroring database, I'm still learning :)) Tests carried out by the example of synchronization: When you deleted a record in the consolidated database was not deleted on the remote database. What did I do wrong? Should be created a table field (last_modified) Exemple:
DELETE FROM PROPOSAL WHERE last_modified <months (CURRENT TIMESTAMP, -1)
Downloading deletes is more difficult since you must keep a record of which rows were deleted so the MobiLink download_delete_cursor script can SELECT the primary keys of the deleted rows to be sent to the remote. The most popular technique is to maintain a "shadow table" where a row is inserted every time a row in the base table is deleted on the consolidated database. See page 246 in my book.
FYI, in version 10 and above, you can use the synchronization model feature (in the MobiLink plug-in for Sybase Central) to generate appropriate Mobilink synchronization scripts.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
68 | |
10 | |
10 | |
7 | |
6 | |
6 | |
6 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.