EM 404 New Mobi Link Technology Jim Graham

EM 404 New Mobi. Link Technology Jim Graham Director of Engineering i. Anywhere Solutions jim. graham@ianywhere. com

Outline • Quick Mobi. Link review • 8. 0 New feature highlights • Mobi. Link futures

Mobi. Link

Mobi. Link Design Goals • • • Heterogeneous consolidated database Scalable and robust (tens of thousands) Manageable in large deployments Support handheld and wireless devices Flexible

How Synchronization Works • 1. Upload • ASA or Ultra. Lite keep track of all changes since previous synchronization • All changes are sent in a single upload stream • Mobi. Link applies changes in a single transaction • 2. Download • Scripts in the consolidated database determine changes that need to be downloaded • All changes are sent in a single download stream • 3. Acknowledge download

Mobi. Link Synchronization

Mobi. Link Scripts • Scripts define actions performed at the consolidated database at each stage or event during synchronization • begin_connection • for each synchronization: • • begin_synchronization receive and apply upload stream prepare and send download stream end_synchronization • end_connection

8. 0 New Feature Highlights • • • Last download timestamp Statement-based uploads Java synchronization logic Better remote ASA terminology Selective uploads (priority synchronization) Improved reporting

7. 0 Download Cursor • The download_cursor script is used to insert or update rows in the remote database SELECT emp_id, emp_fname, emp_lname FROM employee WHERE last_modified > @Last. Download • The download_deletes_cursor script is used to delete rows from the remote database SELECT emp_id FROM employee WHERE last_modified > @Last. Download AND status = ‘INACTIVE’

8. 0 Last Download Timestamp • The download_cursor script is used to insert or update rows in the remote database SELECT emp_id, emp_fname, emp_lname FROM employee WHERE last_modified > ? • The download_deletes_cursor script is used to delete rows from the remote database SELECT emp_id FROM employee WHERE last_modified > ? AND status = ‘INACTIVE’

Last Download Benefits • Last download time automatically stored in remote ASA or Ultra. Lite database and provided to scripts during subsequent upload • Less reliance on RDBMS functionality • Performance • Usability • No longer need download ack • Immediate commit of download transaction

8. 0 New Feature Highlights • • • Last download timestamp Statement-based uploads Java synchronization logic Better remote ASA terminology Selective uploads (priority synchronization) Improved reporting

The upload_cursor Script • The upload_cursor script is used to insert, update, or delete rows in the consolidated database • This is the primary script for uploading changes • Example: SELECT emp_id, emp_fname, emp_lname FROM employee WHERE emp_id = ?

Statement-based Upload Scripts • The upload_insert script is used to insert rows INSERT INTO employee (emp_id, emp_fname, emp_lname) VALUES ( ? , ? ) • The upload_update script is used to update rows UPDATE employee SET emp_fname = ? , emp_lname = ? WHERE emp_id = ? • The upload_delete script is used to delete rows DELETE FROM employee WHERE emp_id = ?

Statement-based Conflicts • The upload_fetch cursor for conflict detection • The insert_old_row script is used to insert the row from the remote database before the update • The insert_new_row script is used to insert the row from the remote database after the update • The resolve_conflict script is used to resolve the conflict

Statement-based Benefits • More understandable • Array operations improve performance • Single statement with multiple parameter sets • Stronger ODBC support

8. 0 New Feature Highlights • • • Last download timestamp Statement-based uploads Java synchronization logic Better remote ASA terminology Selective uploads (priority synchronization) Improved reporting

Java Synchronization Logic • Synchronization scripts written in Java rather than the native language of the consolidated database • Single Java class defines the synchronization logic for a complete synchronization • One public method for each event (Java method replaces SQL script) • Java methods are passed the same parameters as SQL scripts (table, user, last download) • Different classes can be used for different versions

Java Synchronization Logic

Java Details • • Mobi. Link Java API support classes Write your Java class and compile it Make your Java class available to Mobi. Link runs Java synchronization logic in the standard Sun VM (JRE 1. 3 installed) • One instance of your class is instantiated for each Mobi. Link connection to the consolidated database • One public method of your class is called for each event in the synchronization process • Methods can return SQL strings

Java Example public class Sales. Sync { private DBConnection. Contect _cc; public Sales. Sync( DBConnection. Context cc ) { _cc = cc; } public String Emp. Download( String user, Timestamp ts ) { String sql = “SELECT emp_id, emp_fname, emp_lname ” + “FROM employee WHERE last_modified >= ‘” + ts + “’”; return sql; } }

Java Example • javac -classpath c: sajavamlscript. jar; . ; Sales. java • call ml_add_java_table_script( “Sales 1. 0”, “employee”, “download_cursor”, “Sales. Sync. Emp. Download” ) • dbmlsrv 8 -c “dsn=cons” -o out. txt -sl java {-classpath. ; }

Java Flexibility • Mobi. Link JDBC bridge allows access to the database connection Mobi. Link is using to synchronize data • Ability to create additional connections to the consolidated database or to another database • Java instance variables share data across events • Java static variables share data across connections • java. lang. system. out. println to Mobi. Link output log • Full power of standard Java VM • Can mix Java and SQL synchronization logic

Java Benefits • Works around limitations in native procedural languages of RDBMS • Allows synchronization logic to be more portable across multiple RDBMS products • Well known mainstream language that is well suited to procedural logic • Re-use business logic • Better debugging tools available

8. 0 New Feature Highlights • • • Last download timestamp Statement-based uploads Java synchronization logic Better remote ASA terminology Selective uploads (priority synchronization) Improved reporting

7. 0 ASA Client Terminology • • • Reference database Synchronization template Synchronization site mlxtract. exe Synchronization definition

8. 0 ASA Client Terminology • Publication • Synchronization user • Synchronization subscription CREATE PUBLICATION Sales. Sync ( TABLE employee ); CREATE SYNCHRONIZATION USER sally TYPE ‘tcpip’ ADDRESS ‘host=Sales. Server’; CREATE SYNCHRONIZATION SUBSCRIPTION TO Sales. Sync FOR sally;

New Terminology Benefits • Simpler, easier to understand • Closer to SQL Remote • Groundwork for other new features • Multiple publications and subscriptions per user • Selective uploads • Use Mobi. Link to populate data • Still a bit backwards

8. 0 New Feature Highlights • • • Last download timestamp Statement-based uploads Java synchronization logic Better remote ASA terminology Selective uploads (priority synchronization) Improved reporting

Selective Upload • Multiple publications and subscriptions • Publications can overlap • ASA or Ultra. Lite can synchronize one or more publications at the same time • Articles in publications can identify a subset of the rows in a table using a WHERE clause • ASA publications can identify a subset of the columns in tables (all publications must have same column list) • Ultra. Lite does not support column subsets

High Priority Synchronization CREATE PUBLICATION Sales. Sync. High ( TABLE “order” WHERE priority = ‘high’, TABLE order_item i WHERE ‘high’ = (SELECT priority FROM “order” o WHERE o. id = i. id ) ); CREATE PUBLICATION Sales. Sync ( TABLE employee, TABLE “order”, TABLE order_item );

Selective Upload Scenarios • High priority wireless synchronization with lower priority cradle or LAN synchronization • Nightly synchronization with more frequent synchronization throughout the day • Request-based downloads • Multiple applications using a common database

Selective Upload & Last Download • Downloads have always been selective • Separate last download timestamp kept for each publication in remote database • Each synchronization, Mobi. Link uses the earliest last download time for all publications being synchronized • Could mean redundant download

8. 0 New Feature Highlights • • • Last download timestamp Statement-based uploads Java synchronization logic Better remote ASA terminology Selective uploads (priority synchronization) Improved reporting

Improved Reporting • New statistics events to record upload and download statistics (rows, errors, warnings, bytes) • Upload dbmlsync output log following an error • More detailed information following an error

More 8. 0 New Features • • Automatic script generation sa_migrate Limit worker threads applying upload streams Database connection timeout

Mobi. Link Futures • Message-based communication • Java synchronization logic to non-relational enterprise application servers • Mobi. Link monitor console

Resources Available • Other Tech. Wave sessions • My. Sybase: Mobile & Wireless Developer • newsgroups, white papers, technotes, FAQs • samples, patches • Award winning technical support • Training and assistance during the initial phases of your synchronization projects

i. Anywhere Solutions Highlights • Ask the Experts - about Mobile & Wireless Solutions -Mezzanine Level Room 15 B Mon. /Tues. 11: 30 am - 3: 30 pm; Wed. 11: 30 - 1: 30; Thurs. 9 am - 12 noon -Exhibit Hall - Demo Center (truck) exhibit hall hours • SIG (Special Interest Group) - Tuesday 5: 30 pm Mobile & Wireless SDCC, Upper level, Room 11 • Keynote - Enabling m-Business Solutions Wednesday 1: 30 pm - 3: 00 pm • i. Anywhere Solutions Developer Community -Excellent resource for commonly asked questions, newsgroups, bug fixes, newsletters, event listings - visit www. ianywhere. com/developer