cancel
Showing results for 
Search instead for 
Did you mean: 

Upload synchronization

2,386

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

Breck_Carter
Participant
0 Kudos

Please show us the MobiLink scripts in scriptversion 'Lab01'.

Is this the first time you have used MobiLink?

0 Kudos

Yes, it is the first time I try to use. I believe you are talking about this.

Detail: The scripts were generated "automatically", i did not write anything.

download cursor:

SELECT emp, id, description
  FROM TBIDENTIFIER

Upload Insert:

INSERT
  INTO TBIDENTIFIER( emp, id, description )
VALUES( ?, ?, ? )

Upload Delete:

DELETE
  FROM TBIDENTIFIER
 WHERE emp = ?
   AND id = ?

Upload Update :

UPDATE TBIDENTIFIER
   SET description = ?
 WHERE emp = ?
   AND id = ?

Accepted Solutions (1)

Accepted Solutions (1)

Breck_Carter
Participant

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;
0 Kudos

Thank you Mr Carter. Started today reading your book.

I will make tests with the code posted to understand the workings of Mobilink. Thank you again!

0 Kudos

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

Breck_Carter
Participant

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.

0 Kudos

I changed all the files to the ASANY9 and could not make it work? It is not possible to make Mirroring Database with ASANY9?

VolkerBarth
Contributor

No, Database Mirroring (aka HA) was introduced with v10:

From the v10 What's New in Version 10.0.0 docs

0 Kudos

oh my god, I was killing myself here: ( thank you

Breck_Carter
Participant

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.

Breck_Carter
Participant
0 Kudos

oops, sorry, forgot you were on V9. You really should upgrade to 12 or 16. Failing that, see my comment below about Live Log Backups.

0 Kudos

No problem Mr. Carter. This is part of learning. I'm learning. I go to the book. Thank you!

0 Kudos

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)
Breck_Carter
Participant
0 Kudos

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.

Answers (1)

Answers (1)

Former Member

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.

Sync models in version 16

Sync models in version 12

Sync models in version 11

Sync models in version 10

0 Kudos

oK, Thank you.