Skip to Content

Synchronization with HANA - Part 2

If you came here directly, you can read the start of the tutorial at Part 1.

Step 6. Create A Single Table On HANA

Here's the HANA version of the Hello World table:

"%SQLANY16%\bin64\dbisql" -c "DSN=breckcarterHANA; USER=DBA; PASSWORD=HappyHANA1;" -hana

CREATE COLUMN TABLE hello_world (

   pkey           BIGINT NOT NULL,

   data_1         VARCHAR ( 20 ) DEFAULT '' NOT NULL,

   data_2         VARCHAR ( 20 ) DEFAULT '' NOT NULL,

   PRIMARY KEY ( pkey ) );

Here's a single row INSERT to represent "legacy data" on the HANA database; i.e., data that exists before MobiLink synchronization is started:

INSERT INTO hello_world VALUES ( 1, 'HANA', 'row' );

COMMIT;

SELECT * FROM hello_world ORDER BY pkey;

OUTPUT:

               PKEY DATA_1               DATA_2             

------------------- -------------------- --------------------

                  1 HANA                 row 

If your synchronization plans call for uploading only, from SQL Anywhere up to HANA, no schema changes to HANA application tables are required.

Exception: MobiLink uses primary key values to match rows when synchronizing, so if your HANA table doesn't have a primary key, or your application is in the habit of updating primary key values after inserting rows, you've got some work ahead of you... work that isn't discussed in this article.

However, if you want to synchronize data in both directions (up and down between SQL Anywhere and HANA) some additional custom schema objects are required on the HANA database. MobiLink has an arms-length relationship with the software used to run the consolidated database (in this case, HANA); in particular, MobiLink doesn't have a access to the HANA transaction log so it can't use that to keep track of which rows must be downloaded to SQL Anywhere because they have recently been inserted, updated or deleted.

One method to tell MobiLink which rows have been inserted or updated is to add a TIMESTAMP column to each HANA table, and write code to maintain the column value. That code isn't easy to write inside a HANA database, and it's dangerous code to put inside applications (what if you forget?), and besides, an extra column isn't going to help when the row's deleted... because, well, the row is gone.

A popular and effective method is to create tiny "shadow tables" for each HANA table that's being synchronized. The shadow tables each have the same primary key as the real table, plus other columns that identify when the corresponding row in the real table was inserted, updated or deleted. Two shadow tables are required, one for deleted rows and another for inserted and updated rows:

CREATE TABLE hello_world_deleted (

   pkey                 BIGINT NOT NULL,

   deleted_timestamp    TIMESTAMP NOT NULL,

   PRIMARY KEY ( pkey ) );

CREATE TABLE hello_world_upserted (

   pkey                 BIGINT NOT NULL,

   upserted_timestamp   TIMESTAMP NOT NULL,

   PRIMARY KEY ( pkey ) );

INSERT INTO hello_world_upserted

SELECT hello_world.pkey,

       CURRENT_TIMESTAMP

  FROM hello_world

          LEFT OUTER JOIN hello_world_upserted

          ON hello_world.pkey = hello_world_upserted.pkey

WHERE hello_world_upserted.pkey IS NULL;

COMMIT;

SELECT * FROM hello_world_upserted ORDER BY pkey;

OUTPUT:

               PKEY UPSERTED_TIMESTAMP  

------------------- ---------------------

                  1 2014-03-28 12:09:07.4

The INSERT statement above is run on HANA to populate one of the shadow tables with rows that already exist and should be downloaded on the first synchronization. The LEFT OUTER JOIN and WHERE clause aren't necessary when the shadow table is empty, but they do let you run the INSERT to "fill in the gaps" when somehow the shadow table becomes partially filled.

The word "upsert" means "update or insert", or more specifically "insert the row if you can, update the row if it already exists." MobiLink uses upsert operations to handle rows being downloaded from HANA to SQL Anywhere, which means MobiLink doesn't need to be told whether a downloaded row should be inserted or updated on SQL Anywhere, which in turn means only one shadow table is needed for both: hello_world_upserted.

Tip: The upsert operation isn't just a feature built in to MobiLink, it's actual SQL syntax you can use in your own code. In HANA you can use the UPSERT statement, and in SQL Anywhere it's coded as INSERT ON EXISTING UPDATE.

Here are the three triggers used to automatically maintain the two shadow tables:

CREATE TRIGGER hello_world_insert AFTER INSERT ON hello_world

REFERENCING NEW ROW AS inserted_row FOR EACH ROW

BEGIN

   INSERT INTO hello_world_upserted VALUES ( :inserted_row.pkey, CURRENT_TIMESTAMP );

   DELETE FROM hello_world_deleted WHERE pkey = :inserted_row.pkey;

END;


CREATE TRIGGER hello_world_update AFTER UPDATE ON hello_world

REFERENCING NEW ROW AS updated_row FOR EACH ROW

BEGIN

   UPDATE hello_world_upserted

      SET upserted_timestamp = CURRENT_TIMESTAMP   

    WHERE pkey = :updated_row.pkey;

END;


CREATE TRIGGER hello_world_delete BEFORE DELETE ON hello_world

REFERENCING OLD ROW AS deleted_row FOR EACH ROW

BEGIN

   DELETE FROM hello_world_upserted WHERE pkey = :deleted_row.pkey;

   INSERT INTO hello_world_deleted VALUES ( :deleted_row.pkey, CURRENT_TIMESTAMP );

END;

Tip: When coding HANA stored procedures and triggers, life will be good if you keep it simple. Don't expect all the features you may be used to in SQL Anywhere; e.g., there's no such thing as an INSTEAD_OF trigger, and you can't code CASE WHEN INSERTING ... WHEN UPDATING ... WHEN DELETING in a trigger. Also, it's important to read the fine print (the HANA SQL Help) where you will find nuggets like "Statement level trigger is only supported in row-store table." Column store tables are a big reason why folks use HANA in the first place, so that's why all the triggers in this article are coded FOR EACH ROW.

Here's another surprise: "Access of subject table which is table that trigger is defined on, is not allowed in trigger body, which means any insert/update/delete/replace/select for table which trigger is on is not allowed." In English, that means you can't code UPDATE hello_world inside a trigger on hello_world itself. That rule, and the rule that REFERENCING variables inside HANA triggers are read-only, are the reasons that the hello_world_upserted shadow table exists and not just hello_world_deleted. Otherwise, an upserted_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP column could be added to the hello_world table and a BEFORE UPDATE trigger could be used to maintain the value.


Step 7. Set Up The MobiLink Objects On HANA

The MobiLink server requires a whole sackload of "system objects" to be created in the HANA database; tables, views, sequences, procedures, all named "ml_whatever", all provided in one single SQL script delivered with SQL Anywhere 16:

C:\Program Files\SQL Anywhere 16\MobiLink\Setup\synchana.sql

-- ***************************************************************************

-- Copyright (c) 2013 SAP AG or an SAP affiliate company. All rights reserved.

-- ***************************************************************************

--

-- Create the MobiLink Server system tables and stored procedures in

-- an SAP HANA consolidated database.

--

create table ml_ldap_server (

    ldsrv_id integer not null,

    ldsrv_name varchar( 128 ) not null unique,

    search_url varchar( 1024 ) not null,

... and so on

Here's a batch ISQL command (as opposed to one that launches the ISQL GUI) that runs synchana.sql against your HANA database:

"%SQLANY16%\bin64\dbisql"^

  -c "DSN=breckcarterHANA; USER=DBA; PASSWORD=HappyHANA1;"^

  -hana^

  READ ENCODING Cp1252 "C:\Program Files\SQL Anywhere 16\MobiLink\Setup\synchana.sql"

If you run into the following problem, you will need to download SQL Anywhere 16 build 1761 or later to get a modified version of the synchana.sql file:

Alas, synchana.sql is not designed to be run twice:

The simplest way to run synchana.sql again is to first drop everything and start over, and the easiest way to do that is DROP USER DBA CASCADE; here's how:

Start the ISQL GUI with the SYSTEM user id.

"%SQLANY16%\bin64\dbisql" -c "DSN=breckcarterHANA; USER=SYSTEM; PASSWORD=manager;" -hana

-- Run these statements to drop and recreate the DBA user id.

DROP USER DBA CASCADE;

CREATE USER DBA PASSWORD SqlHappy1;

GRANT CREATE ANY ON SCHEMA SYSTEM TO DBA WITH GRANT OPTION;

GRANT ALTER      ON SCHEMA SYSTEM TO DBA WITH GRANT OPTION;

GRANT DROP       ON SCHEMA SYSTEM TO DBA WITH GRANT OPTION;

GRANT EXECUTE    ON SCHEMA SYSTEM TO DBA WITH GRANT OPTION;

GRANT SELECT     ON SCHEMA SYSTEM TO DBA WITH GRANT OPTION;

GRANT INSERT     ON SCHEMA SYSTEM TO DBA WITH GRANT OPTION;

GRANT UPDATE     ON SCHEMA SYSTEM TO DBA WITH GRANT OPTION;

GRANT DELETE     ON SCHEMA SYSTEM TO DBA WITH GRANT OPTION;

GRANT INDEX      ON SCHEMA SYSTEM TO DBA WITH GRANT OPTION;

GRANT CATALOG READ TO DBA WITH ADMIN OPTION;

Start the ISQL GUI with the DBA user id.

"%SQLANY16%\bin64\dbisql" -c "DSN=breckcarterHANA; USER=DBA; PASSWORD=SqlHappy1;" -hana

--Run this statement to change the password for DBA.

ALTER USER DBA PASSWORD HappyHANA1;

Run synchana.sql again.

"%SQLANY16%\bin64\dbisql"^

  -c "DSN=breckcarterHANA; USER=DBA; PASSWORD=HappyHANA1;"^

  -hana^

  READ ENCODING Cp1252 "C:\Program Files\SQL Anywhere 16\MobiLink\Setup\synchana.sq

Here's what synchana.sql looks like when it works:

That takes care of loading the MobiLink system objects onto HANA; now come the user-written MobiLink scripts for the hello_world table.

Note: If you're familiar with MobiLink Projects in Sybase Central and the associated synchronization wizard, you may be disappointed to learn that HANA isn't supported yet.

The runtime MobiLink client and server do work with HANA, it's just the GUI development tool that hasn't caught up yet. For that reason this article is going Old School and building the MobiLink scripts by hand. It's not that hard, just tedious... and for something as simple as Hello World it's not even tedious.

Tip: It's tempting to create a dummy SQL Anywhere database that looks like your HANA database and use that to make the MobiLink synchronization wizard generate your scripts. Sadly, that doesn't work very well at all... which should not come as much of a surprise. If it was going to work the wizard would already support HANA

The following pairs of "CALL ml_add_table_script" statements do delete-and-insert operations of strings that contain MobiLink event-handling SQL scripts. Later on, the MobiLink server will read and execute those scripts during a synchronization:

  • The upload_insert script will be executed on HANA for each new row uploaded from SQL Anywhere,
  • the upload_update script will be executed for each existing row that's uploaded,
  • the upload_delete script will be used to handle each row to be deleted from HANA,
  • the download_cursor script will be used to build that part of the download stream that contains new and updated rows to be sent to SQL Anywhere (note the shadow table usage in the INNER JOIN and WHERE clauses}, and
  • the download_delete_cursor will be used to build that part of the download stream that contains the primary keys for all the rows that need to be deleted (note that only the shadow table is used in this SELECT because the rows no longer exist in the real table).

To repeat: The following "CALL ml_add_table_script" statements are executed now, on HANA, to store SQL scripts inside the HANA database. Later on, during synchronization, those scripts will be read by the MobiLink server and executed on the HANA database.

"%SQLANY16%\bin64\dbisql" -c "DSN=breckcarterHANA; USER=DBA; PASSWORD=HappyHANA1;"

CALL ml_add_table_script  ( 'v1', 'hello_world', 'upload_insert', '' );

CALL ml_add_table_script  ( 'v1', 'hello_world', 'upload_insert', '

INSERT INTO hello_world

       ( pkey,

         data_1,

         data_2 )

VALUES ( {ml r.pkey},

         {ml r.data_1},

         {ml r.data_2} )' );

CALL ml_add_table_script  ( 'v1', 'hello_world', 'upload_update', '' );

CALL ml_add_table_script  ( 'v1', 'hello_world', 'upload_update', '

UPDATE hello_world

   SET data_1 = {ml r.data_1},

       data_2 = {ml r.data_2}

WHERE pkey = {ml r.pkey}' );

CALL ml_add_table_script  ( 'v1', 'hello_world', 'upload_delete', '' );

CALL ml_add_table_script  ( 'v1', 'hello_world', 'upload_delete', '

DELETE FROM hello_world

WHERE pkey = {ml r.pkey}' );

CALL ml_add_table_script  ( 'v1', 'hello_world', 'download_cursor', '' );

CALL ml_add_table_script  ( 'v1', 'hello_world', 'download_cursor', '

SELECT hello_world.pkey,

       hello_world.data_1,

       hello_world.data_2

  FROM hello_world

       INNER JOIN hello_world_upserted

               ON hello_world_upserted.pkey

                = hello_world.pkey

WHERE hello_world_upserted.upserted_timestamp

    >= {ml s.last_table_download}' );

CALL ml_add_table_script  ( 'v1', 'hello_world', 'download_delete_cursor', '' );

CALL ml_add_table_script  ( 'v1', 'hello_world', 'download_delete_cursor', '

SELECT pkey

  FROM hello_world_deleted

WHERE hello_world_deleted.deleted_timestamp

    >= {ml s.last_table_download}' );

COMMIT;

Tip: Don't pass NULL as the 4th argument in the CALL ml_add_table_script statements on HANA, pass the empty string '' instead.

Tip: Don't code multi-line /* comments */ in HANA.

Tip: Don't forget the COMMIT. That's not a HANA thing, it's a mistake folks make with every DBMS used with MobiLink: "I made that change, how come it's still doing the same thing?" Sure, you could turn on AutoCommit by checking "Commit after every statement" in the ISQL - Tools - Options... - SAP HANA - Execution tab, or you could rely on the fact that "Commit on exit or disconnect" is checked by default, but the former means you can never ROLLBACK after running the wrong update in ISQL, and relying on the latter is ... just ... spooky. In fact, unchecking "Commit on exit or disconnect" might be a good idea, it depends on how much control you want over your own destiny.

PS: The docs don't explicitly say "HANA runs in chained mode" but it does.

Note: The choice of USER=DBA to run these scripts determines "who owns" all the MobiLink system objects inside the HANA database, which (as discussed in an earlier Tip) also determines that UID=DBA will be used by the MobiLink server to connect to HANA.

Step 8. Create A SQL Anywhere Database

Here's a Windows command file that creates a new SQL Anywhere 16 database called "remo" on your local computer:

"%SQLANY16%\bin64\dbinit.exe" "C:\data\remo\remo.db"

PAUSE

Here's a command to start the database. It uses the dbspawn utility to launch the dbeng16 database engine, and the -o option tells SQL Anywhere where to save the diagnostic "console log" messages:

"%SQLANY16%\bin64\dbspawn.exe"^

  -f "%SQLANY16%\bin64\dbeng16.exe"^

  -o "C:\data\remo\dbeng16_log_remo.txt"^

  "C:\data\remo\remo.db"

PAUSE

If you click on the orange lightning bolt icon in the system tray, you can see the diagnostic messages in real time:

Step 9. Connect To The SQL Anywhere Database Via ISQL

Here's how you can start an ISQL session on the SQL Anywhere database:

"%SQLANY16%\bin64\dbisql" -c "SERVER=remo; DBN=remo; UID=DBA; PWD=sql;"

Step 10. Create A Matching Table On SQL Anywhere

Here's the SQL Anywhere version of the Hello World table:

CREATE TABLE hello_world (

   pkey           BIGINT NOT NULL DEFAULT GLOBAL AUTOINCREMENT ( 1000000000 ),

   data_1         VARCHAR ( 10 ) DEFAULT '' NOT NULL,

   data_2         VARCHAR ( 10 ) DEFAULT '' NOT NULL,

   PRIMARY KEY ( pkey ) );

The DEFAULT GLOBAL AUTOINCREMENT clause tells SQL Anywhere to generate partitioned values when inserting rows, virtually eliminating the possibility of primary key collisions when rows inserted on different databases are uploaded to HANA.

None of the shadow tables or related triggers are included on the SQL Anywhere side of things because their only purpose is to record when rows are changed on HANA.

Step 11. Set Up The MobiLink Objects On SQL Anywhere

-- Define the GLOBAL AUTOINCREMENT partition number.

SET OPTION PUBLIC.GLOBAL_DATABASE_ID = '1';

-- Create a limited-privilege user id for running dbmlsync.

CREATE USER REMOTE_DBA IDENTIFIED BY SQL;

GRANT ROLE SYS_RUN_REPLICATION_ROLE TO REMOTE_DBA

   WITH NO SYSTEM PRIVILEGE INHERITANCE;

-- Tell SQL Anywhere what's being synchronized.

CREATE PUBLICATION p1 (

   TABLE hello_world (

      pkey,

      data_1,

      data_2 ) );

CREATE SYNCHRONIZATION USER "1" TYPE tcpip;

CREATE SYNCHRONIZATION SUBSCRIPTION TO p1 FOR "1";

For more information on these and other SQL statements see dcx.sybase.com/index.html#sa160/en/dbreference/sql-statements-a-d.html.

Once synchronization has been "started" (the CREATE SYNCHRONIZATION SUBSCRIPTION statement has been run), SQL Anywhere will begin recording changes for the next synchronization. Here's one such change, a new row in hello_world that will be sent to HANA on the next (first) synch:

INSERT INTO hello_world ( data_1 ) VALUES ( 'How are' );

COMMIT;

SELECT * FROM hello_world ORDER BY pkey;

OUTPUT:

                pkey data_1     data_2    

-------------------- ---------- ----------

          1000000001 How are 

Here's the story on that funky pkey value 1000000001:

  • First, the CREATE TABLE ... DEFAULT GLOBAL AUTOINCREMENT partition size of 1000000000 dictated that values 1 through 1,000,000,000 are set aside for "partition number zero".
  • Second, the GLOBAL_DATABASE_ID = '1' option setting dictated that this database has "partition number one" which runs from 1,000,000,001 to 2,000,000,000.
  • Other SQL Anywhere databases will have to be assigned different (and globally unique) GLOBAL_DATABASE_ID values, like '2' for pkey values 2,000,000,001 through 3,000,000,000 and so on.
  • There's plenty of room for more databases because the BIGINT data type can hold up to 9,223,372,036,854,775,807 which in scientific terms is known as "a lot"
  • Every SQL Anywhere database will also have to have a globally unique CREATE SYNCHRONIZATION USER value, like "1", which was chosen for this database to match the GLOBAL_DATABASE_ID.
    Look, it doesn't matter how easy SQL Anywhere and MobiLink are to administer, a thousand databases is still a big deal and administrators need all the help they can get... like matching database ids and synchronization users 1, 2, 3, ...

  • HANA doesn't have GLOBAL AUTOINCREMENT but that's OK. Rows inserted on HANA can use a CREATE SEQUENCE START WITH 1 to effectively get "partition number zero" primary key values 1, 2, 3.
  • If you have to look it up every time you need to use GLOBAL AUTOINCREMENT, that's ok, you're not alone

Step 12. Start The MobiLink Server On The Local Computer

Here's a six-line command file for starting the MobiLink server on your local computer; the other 30 REM comments are a "cheat sheet" of interesting mlsrv16 options:

REM -c ...  for ODBC connection to consolidated database

REM -dl     puts diagnostic messages on screen

REM -e      specifies where to put uploaded log text data from remote dbmlsync runs

REM -o ...  where to put MobiLink console log text data

REM -os ... renames and starts new MobiLink server log text data file

REM -ppv 60  print (log) periodic monitor values every 60 seconds

REM -vcefhikmnopqrRstuU  all options (some redundancy)

REM -vcefhkmnopstuU logs everything except data and remote ID

REM   -v+   Turn on all logging options that increase verbosity.

REM   -vc   Show scripts.

REM   -ve   Show system event scripts.

REM   -vf   Show first-read errors.

REM   -vh   Show the remote schema.

REM   -vi   Show uploaded column values.

REM   -vk   Show cache growth and shrinkage.

REM   -vm   Show the duration of each synch phase.

REM   -vn   Show row-count summaries.

REM   -vo   Show SQL passthrough activity.

REM   -vp   Show progress offsets.

REM   -vq   Show downloaded column values.

REM   -vr   Show uploaded and downloaded column values.

REM   -vR   Show remote ID in each message.

REM   -vs   Show script names.

REM   -vt   Show the translated SQL scripts.

REM   -vu   Show undefined table scripts.

REM   -vU   Show user name in each message.

REM -x  tcpip(port=2439)  port for clients (default shown)

REM -zp     ignore some apparent timestamp differences.

REM -zu+    bypasses authentication

REM -zwd    suppress specific warning messages

"%SQLANY16%\bin64\mlsrv16.exe"^

  -c "DSN=breckcarterHANA; UID=DBA; PWD=HappyHANA1;"^

  -o "C:\data\remo\mlsrv16_log_cons.txt"^

  -os 10M^

  -vcefhkmnopstuU^

  -zu+

  • The -c option tells mlsrv16.exe how to connect to HANA via ODBC.
    Note: The -c string for connecting the MobiLink server to HANA uses the Old School parameters UID=DBA; PWD=HappyHANA1; rather than USER=DBA; PASSWORD=HappyHANA1;
  • The -o mlsrv16_log_cons.txt option specifies where mlsrv16.exe should write diagnostic information.
  • The -os 10M options tells mlsrv16.exe to rename mlsrv16_log_cons.txt when it reaches 10M in size, and start a new file.
    Tip: All production MobiLink setups should specify both -o and -os for the MobiLink server. The diagnostic output coming from the MobiLink server is the first place to look when synchronization problems occur, and it doesn't matter what software you use, if you have distributed your data across hundreds or thousands of databases you will eventually have problems.
  • The -vcefhkmnopstuU option is more verbose than even -v+, and getting lots of diagnostic information is very important... and "lots" is no exaggeration, hence the -os 10M. Nevertheless, this information is critical, even in production... arguably, it is more important in production when diagnosing synch problems. MobiLink is designed for central administration, and that means great care has been taken to make sure the -o file is useful... so use it!
  • The -zu+ option is a shortcut you might not be able to take, depending on your security environment: it tells the MobiLink server you don't want to bother with authentication when MobiLink clients connect to synchronize.

Here's what happens the MobiLink server can't connect to HANA, like when your CloudShare environment times out:

Here's what the MobiLink server should look like, happily connected to HANA and now just sitting there waiting for a synch request:

Step 13. Run A MobiLink Synchronization From The Local Computer

Here's a Windows command file for starting the MobiLink client dbmlsync on your local computer to run a synchronization:

REM -ap ... parameter string sent to authenticate_parameters script

REM -c ...  remote database connection string

REM -d      drop other connections which have conflicting locks

REM -dl     puts diagnostic messages on console

REM -e adr= MobiLink server address

REM -e lt=  LockTables, default ON

REM -e sa=  SendDownloadACK, default OFF

REM -e st=  SendTriggers, default OFF

REM -e sv=  ScriptVersion, default default

REM -e uo=  upload only, default OFF

REM -k      close window when done

REM -n      publication name

REM -o ...  where to put dbmlsync console messages

REM -os ... renames and starts new MobiLink client log text data file

REM -vcpnorsu logs everything

REM -vnosu  logs everything except data, -c string and password

REM -x      rename the transaction log to yymmddxx.LOG and restart it

REM Options specified via -e take precedence over options in the database.

"%SQLANY16%\bin64\dbmlsync.exe"^

  -c "SERVER=remo; DBN=remo; UID=REMOTE_DBA; PWD=SQL;"^

  -e "adr='host=localhost';sv=v1"^

  -o "C:\data\remo\dbmlsync_log_remo.txt"^

  -os 10M^

  -vnosu^

  -x

  • The -c option tells dbmlsync.exe how to connect to SQL Anywhere using a password-in-the-clear together with a limited-privilege user id.
  • The -e option provides a funky, doubly- (or triply?) nested, semicolon-delimited string of "extended options"
    • with the outer adr='...' and the inner host=localhost options telling dbmlsync.exe where to find the MobiLink server,

    • followed by the outer sv=v1 option which specifies the script version v1 that was used when the MobiLink scripts were set up earlier.
  • The -o dbmlsync_log_remo.txt option specifies where dbmlsync.exe should write diagnostic information.
  • The -os 10M option tells dbmlsync.exe to rename dbmlsync_log_remo.txt when it reaches 10M in size, and start a new file.
  • The -vnosu option sets the verbosity level of the diagnostic information. Generally speaking, the client side is not the first place to look when trying to diagnose a problem, but sometimes it helps.
  • The -x option tells dbmlsync.exe to control the growth of the SQL Anywhere transaction log file by renaming and restarting it on every sync. This article doesn't show it, but you can also use the delete_old_logs database option to control the proliferation of teeny tiny log files... which you get when you use -x.

This is what it looks like when the synchronization fails; for example, when the MobiLink server isn't running:

Here's what a successful synch looks like from the MobiLink client's point of view:

Step 14. Show The Synchronization Results On HANA And SQL Anywhere

Here's proof that the first synch did what it was supposed to:

  • The pkey = 1 row was downloaded from HANA and inserted on SQL Anywhere, and
  • the pkey = 1000000001 row was uploaded from SQL Anywhere and inserted on HANA.

SELECT *  FROM hello_world ORDER BY pkey;

-- On HANA...

               PKEY DATA_1               DATA_2              

------------------- -------------------- --------------------

                  1 HANA                 row                 

         1000000001 How are                                  

-- On SQL Anywhere...

                pkey data_1     data_2    

-------------------- ---------- ----------

                   1 HANA       row       

          1000000001 How are   

All Done! (But Wait!)

In the context of a Hello World database, two more synchs and two more rows qualify as "system testing"... so here goes:

  • Update one row on each database and run dbmlsync.exe a second time to bring HANA and SQL Anywhere into agreement once more, then
  • insert a new row on each database and run dbmlsync.exe yet again...

-- Before synch #2, on HANA...

UPDATE hello_world SET data_2 = 'There!' WHERE pkey = 1;

COMMIT;

SELECT * FROM hello_world ORDER BY pkey;

               PKEY DATA_1               DATA_2              

         ---------- -------------------- --------------------

                  1 HANA                 There!              

         1000000001 How are       

-- Before synch #2, on SQL Anywhere...

UPDATE hello_world SET data_2 = 'you?' WHERE pkey = 1000000001;

COMMIT;

SELECT * FROM hello_world ORDER BY pkey;

                pkey data_1     data_2    

          ---------- ---------- ----------

                   1 HANA       row       

          1000000001 How are    you?      

---------------------------------------------------------------------

-- Run synch #2.

---------------------------------------------------------------------

-- After synch #2, on HANA...

               PKEY DATA_1               DATA_2              

         ---------- -------------------- --------------------

                  1 HANA                 There!              

         1000000001 How are              you?     

-- After synch #2, on SQL Anywhere...

                pkey data_1     data_2    

          ---------- ---------- ----------

                   1 HANA       There!    

          1000000001 How are    you?     

---------------------------------------------------------------------

-- Before synch #3, on HANA...

INSERT INTO hello_world ( pkey, data_1, data_2 ) VALUES ( 2, 'second', 'row' );

COMMIT;

               PKEY DATA_1               DATA_2              

         ---------- -------------------- --------------------

                  1 HANA                 There!              

                  2 second               row                 

         1000000001 How are              you? 

-- Before synch #3, on SQL Anywhere...

INSERT INTO hello_world ( data_1, data_2 ) VALUES ( 'one', 'more row' );

COMMIT;


                pkey data_1     data_2    

          ---------- ---------- ----------

                   1 HANA       There!    

          1000000001 How are    you?      

          1000000002 one        more row  

---------------------------------------------------------------------

-- Run synch #3.

---------------------------------------------------------------------

-- After synch #3, on HANA...

               PKEY DATA_1               DATA_2              

         ---------- -------------------- --------------------

                  1 HANA                 There!              

                  2 second               row                 

         1000000001 How are              you?                

         1000000002 one                  more row   

-- After synch #3, on SQL Anywhere...

                pkey data_1     data_2    

          ---------- ---------- ----------

                   1 HANA       There!    

                   2 second     row       

          1000000001 How are    you?      

          1000000002 one        more row  

Did you notice the mistake? Not, not with MobiLink, everything went OK with synchronization. The mistake was that different maximum string lengths were declared for data_1 and data_2: VARCHAR ( 20 ) on HANA versus VARCHAR ( 10 ). One of the strengths of MobiLink is its ability to deal with minor schema differences without complaint (you can even write custom code to handle vast differences like normalized tables on one database versus denormalized on another). However, from an application design point of view, mistakes like this one can often spell disaster; it's important to carefully compare the schemas on the consolidated and remote databases to make sure any differences are acceptable.

Conclusions

There you have it... despite the many steps described in this article, and the many tips, it is easy to set up MobiLink to synchronize a SQL Anywhere database with HANA.

With the possible exception of having to "look stuff up", nothing's been left out. In particular, this article doesn't even skip the simple bits... which means it takes longer to read than perform the work.

In fact, after the first time through all the steps, it only takes a few minutes to run through them a second or third time. As promised, there are no time-sinks... and no crashes, which is rather remarkable for a mostly-new toolchain.

Tags: