M411 – Taking Your Enterprise Mobile in 10 Minutes

M411 – Taking Your Enterprise Mobile in 10 Minutes • David Fishburn • Principal Consultant • iAnywhere Solutions • David.Fishburn@ianywhere.com

Objective • To outline a process that makes you productive as quickly as possible • Learn techniques to take advantage of this technology • Introduce some new 8.0 features and how they can help you

Topics • Synchronization • The Task • Database Migration • Generating MobiLink Scripts • Script Customization • Remote Monitoring

Synchronization? • New technology (since April 1999) • Sharing data between consolidated and remote (Synchronization vs Replication) • Summary of changes compared to all changes • Provides subsets of data defined by client or server • Existing Sybase technologies • Replication Server • SQL Remote (for ASE and ASA)

ORACLE ASA DB2 Rep Server Other ASE DBSRV6 DBLTM ASE LTM ASE MAPI VIM SMTP FTP FILE Replication Server Architecture ASA

MAPI VIM MAPI VIM SMTP SMTP FTP FTP FILE FILE SQL Remote ASE ASA OR ASA ASA

Rep Server ASE LTM SSQueue ASE MAPI VIM SMTP FTP FILE ASA SQL Remote with Replication Server

MobiLink ASA, ASE, Microsoft, Oracle, IBM HTTP, TCPIP HotSync, Wireless ASA, PalmOS, PocketPC, UltraLite

MobiLink and SQL Remote ASA, ASE SQL Remote MobiLink ASA, Palm, CE, Pagers, Phones

Synchronization Process 1. Data is uploaded to the consolidated database 2. Data is downloaded to the remote database

Two Main Phases • Upload • Only records that have changed are uploaded and applied to the consolidated • A table at a time • Inserts/Updates/Deletes • In an order than maintains referential integrity • Download • A table at a time

Synchronization Transactions • Upload stream • Changes from the remote are applied to the consolidated, followed by a commit • Summary of changes • Download stream • Data is pulled out of the consolidated via a SELECT statement YOU write • Transaction is committed (new to 8.0) • By default MobiLink waits for an ack • Changes are sent to the remote

MobiLink Scripts • Scripts control • What MobiLink does with the data uploaded from the remote • Determines which data is sent to the remotes • Script SQL • Standard SQL statements • INSERT, UPDATE, DELETE and SELECT statements

The Task • Tips are best learned when we have a task to accomplish • Start with a Microsoft SQL Server database (the consolidated) • Create an identical Adaptive Server Anywhere database (the remote) • Write synchronization scripts • Get it synchronizing in 10 minutes 

The Task • Consolidated and remote have an identical schema • In my experience this is true most of the time • If the consolidate and remote have different schemas • Customization of the MobiLink scripts will be required • MobiLink was designed for these cases as well

Consolidated Database • Lets assume your consolidated database is Microsoft SQL Server • It could just as easily be Oracle • Remote database must be • Adaptive Server Anywhere (ASA) • UltraLite • Compatible ASA schema must be created • Required for ASA remotes (for deployment) • Required to create an UltraLite database

Remote Database Schema • We need to migrate the schema from the Microsoft database to an ASA database • Use the following ASA features • Remote Data Access • Database Migration

Remote Data Access • Sybase feature that allows • Access data in relational databases such as Sybase (ASA and ASE), Oracle, and DB2. • Access desktop data such as Excel spreadsheets, MS-Access databases, FoxPro, and text files. • Access any other data source that supports an ODBC interface. • Perform joins between local and remote data. • Just to name a few uses…

Setup Remote Data Access • Use Sybase Central to setup

Database Migration Wizard • Use the Sybase Central migration tool

Verify Results • Check only UL% tables were created • Ensure no data was migrated

Script Versions • MobiLink scripts are organized by script version • Use any naming convention you like • Have as many versions as you like • Project_v100 • Project_v101 • Project_v102_dev • When a remote synchronizes, it must specify which version of the scripts it requires

Scripts • Scripts are written using • Standard SQL • INSERT, UPDATE, DELETE, SELECT statements • Java (New to 8.0) • Use the Java language to dynamically created SQL statements • Scripts are tied to events in the MobiLink Server • Each script is optional

Script Events • For each table that is synchronized bi-directionally the following MobiLink events are required • Upload_insert • Upload_delete • Upload_update • Download_cursor • If the upload scripts do not exist, no changes from the remote will be applied to the consolidated • Changes will not be uploaded again

Example Scripts • Scripts (standard SQL) • Download_cursor • SELECT prop_id, prop_text, last_modified FROM proposal • Upload_insert • INSERT INTO proposal( prop_id, prop_text, last_modified ) VALUES( ?, ?, ? ) • Upload_delete • DELETE FROM proposal WHERE prop_id = ? • Upload_update • UPDATE proposal SET prop_text = ?, last_modified = ? WHERE prop_id = ?

Generating MobiLink Scripts • Writing these scripts initially can be time consuming and error prone • The order of the columns must be the same as the order in the remote database • What happens if you need to write scripts for 200 tables?

MobiLink Special Mode • MobiLink can be started with a special switch that will automatically generate basic scripts for each of the tables • No scripts must exist prior to generation • If scripts do exist, MobiLink will do nothing • MobiLink switches • -za+ Allow generation of active scripts • -zu+ Allow automatic addition of users • DBMLSrv8 –za+ -zu+ -c DSN=mss_fft –ot cons.txt

Verbosity • MobiLink has varying levels of verbosity that can be enabled • Enabled via the –v command line switch • DBMLSRV8 –vcrsn • Normally use this when creating scripts • c – show the content of each script as it is executed • r – show the column values for each of the rows uploaded and downloaded • s – show the name of the event that is fired • n – show the row count summaries

DBMLSync • MobiLink client • Initiates synchronization with MobiLink • DBMLSync must know • A unique name for the remote database synchronizing • Required for recoverability • Which tables must be synchronized • Where the MobiLink server is (ie ADDRESS) • What protocol to use (ie TCPIP/HTTP)

Publication • A publication “publishes” which data is available for the remote to synchronize CREATE PUBLICATION "routing" ( TABLE routing, TABLE proposal( prop_id, prop_text, last_modified ) ); • Each publication can contain many tables • Column list is optional

Subscription • A subscription does the following • Uniquely identifies the remote • Supplies the “address” of the MobiLink server • Supplies the “protocol” that will be used to synchronize • Supplies any extended options for the remote CREATE SYNCHRONIZATION USER "50"; CREATE SYNCHRONIZATION SUBSCRIPTION TO "routing" FOR "50" TYPE 'tcpip' ADDRESS 'host=localhost;port=2439' OPTION ScriptVersion='v1.0';

DBMLSync Options • In order for scripts to be generated DBMLSync must send the column names to MobiLink • Column names are normally not required • Increase the communications overhead • DBMLSync must have an extended option enabled (first time only) • SendColumnNames=‘Yes’

Setup DBMLSync • These commands are run against the remote database CREATE PUBLICATION "routing" ( TABLE routing, TABLE proposal( prop_id, prop_text, last_modified ) ); CREATE SYNCHRONIZATION USER "50"; CREATE SYNCHRONIZATION SUBSCRIPTION TO "routing" FOR "50" TYPE 'tcpip' ADDRESS 'host=localhost;port=2439' OPTION ScriptVersion='v1.0', SendColumnNames='Yes';

Run DBMLSync • MobiLink has been setup to automatically generate scripts (-za+) • DBMLSync initiates a synchronization • DBMLSync sends extra information, including column names • DBMLSync -c DSN=remote -ot rem.txt • MobiLink will • Generate the scripts based on this information • Synchronize using the scripts it just created

Generated Scripts • View with Sybase Central – MobiLink Synchronization Server Plugin

Generated Scripts • The automatically generated scripts • Download_cursor • SELECT prop_id, prop_text, last_modified FROM proposal • Upload_insert • INSERT INTO proposal( prop_id, prop_text, last_modified )VALUES( ?, ?, ? ) • Upload_delete • DELETE FROM proposal WHERE prop_id = ? • Upload_update • UPDATE proposal SET prop_text = ?, last_modified = ? WHERE prop_id = ?

The Task - Complete • Get it synchronizing in 10 minutes  • Did we do it? • Easy as 1-2-3 • Schema migration • Script generation • Initial synchronization

All Rows • Generated download_cursors are of this format • SELECT prop_id, prop_text, last_modified FROM proposal • Assume 1000 rows in the table • If a user synchronizes a second time • Select will return all 1000 rows (no WHERE clause) • 1000 rows will be downloaded again • DBMLSync/Ultralite will update the existing values • Very inefficient

Timestamp Based Requirement • In order to download ONLY the rows that have changed since the remote last synchronized we need • A column on the table indicating the last time the row was modified • This column must be maintained by the database or application • Last time the user synchronized

Last_modified Column • Each table in the consolidated database requires a column indicating when the row was last changed • In ASA, you can create the column like this • Last_modified TIMESTAMP DEFAULT TIMESTAMP • The database engine will automatically maintain this column for you • In other RDBMS • Triggers are often required

LastDownload Time • New to 8.0 • The remote will now maintain the LastDownload time automatically • The remote will send this value to MobiLink as part of the synchronization • MobiLink supplies this value to the scripts • MobiLink automatically updates this value • The updated value will be included as part of the download • If the remote successfully receives the download, it also has the updated time • If the download fails, the old value will be uploaded and no data will be missed

Initial LastDownload Time • If the remote has never synchronized before • The initial value for the LastDownload time is • ‘1900/01/01 00:00’

Timestamp Based Synchronization • Each download_cursor script is passed 2 parameters • Last Download Timestamp for that user • Synchronizing User Name • Download scripts can be modified SELECT order_id, … FROM ULOrder WHERE last_modified >= ? AND emp_id = ?

Timestamp Based Synchronization • If your download_cursor does not require the last modified value SELECT cust_id, … FROM ULCustomer WHERE ? IS NOT NULL AND emp_id = ? • The “? IS NOT NULL” is used as a place holder, since the first “?” is always the LastDownload time

Timestamp Based With ASA • begin_connection CREATE VARIABLE @EmployeeID integer; CREATE VARIABLE @LastDownload timestamp • begin_download CALL SetParameters(?, ?, @LastDownload, @EmployeeID )

M411 – Taking Your Enterprise Mobile in 10 Minutes