1 / 73

EM402 MobiLink Tips and Techniques

EM402 MobiLink Tips and Techniques. David Fishburn Principal Consultant iAnywhere Solutions David.Fishburn@ianywhere.com. To outline a process that makes you productive as quickly as possible Learn techniques to take advantage of this technology

theo
Download Presentation

EM402 MobiLink Tips and Techniques

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. EM402MobiLink Tips and Techniques • David Fishburn • Principal Consultant • iAnywhere Solutions • David.Fishburn@ianywhere.com

  2. To outline a process that makes you productive as quickly as possible Learn techniques to take advantage of this technology Introduce some new Vail (8.0) features and how they can help you Primarily focus on the Vail release Objective

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

  4. 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) Synchronization?

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

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

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

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

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

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

  11. 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 Two Main Phases

  12. 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 Vail) Changes are sent to the remote Synchronization Transactions

  13. 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 MobiLink Scripts

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

  15. 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

  16. 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 This is still expected Customization of the MobiLink scripts will be required MobiLink was designed for these cases as well The Task

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

  18. 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 Consolidated Database

  19. 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 Database Schema

  20. 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… Remote Data Access

  21. Setup Remote Data Access • Use Sybase Central to setup

  22. Setup via SQL statements Create an ASA remote database DBINIT remote.db Define a remote server Mss_fft – ODBC DSN for SQL Server CREATE SERVER mss_cons CLASS 'mssodbc' USING 'mss_fft'; Create an external login for the Microsoft database Assume a user “fishburn” already exists in the MSS database CREATE EXTERNLOGIN "DBA" TO "mss_cons" REMOTE LOGIN "fishburn" IDENTIFIED BY "test"; Setup Remote Data Access

  23. Special one step ASA stored procedure will migrate Schema Data (optional) Procedure dbo.sa_migrate( IN local_table_owner VARCHAR(128), IN server_name VARCHAR(128), IN table_name VARCHAR(128) DEFAULT NULL, IN owner_name VARCHAR(128) DEFAULT NULL, IN database_name VARCHAR(128) DEFAULT NULL, IN migrate_data BIT DEFAULT 1, IN drop_proxy_tables BIT DEFAULT 1) Database Migration (Short way)

  24. CALL sa_migrate( 'DBA', 'mss_cons', 'UL%', 'dbo', 'CustDB', 0, 1 ); DBA Create the tables under the DBA owner (ASA) mss_cons ASA Remote Server UL% Only create the tables that begin with ‘UL’ dbo MSS owner of the tables CustDB Which MSS database (there can be many) 0 Do not migrate data 1 Drop the proxy tables when finished Procedure sa_migrate()

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

  26. Sometimes you need more flexibility when migrating the schema No convenient ‘UL%’ for the tables you need Errors during the migration process 6 stored procedures can be used instead sa_migrate_create_remote_table_list sa_migrate_create_tables sa_migrate_data sa_migrate_create_remote_fks_list sa_migrate_create_fks sa_migrate_drop_proxy_tables Database Migration (Long way)

  27. Will build the list of all tables that need to be migrated CALL sa_migrate_create_remote_table_list( 'mss_cons', null, 'dbo', 'CustDB' ); Populates the table, migrate_remote_table_list, with ALL tables Rows can be removed from this table before proceeding to the next step, sa_migrate_create_tables. These routines are re-runable, pick up where they last left off sa_migrate_create_remote_table_list

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

  29. 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 Script Versions

  30. Scripts are written using Standard SQL INSERT, UPDATE, DELETE, SELECT statements Java (New to Vail) EM404 New MobiLink Technology Scripts are tied to events in the MobiLink Server Each script is optional Scripts

  31. 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 Script Events

  32. 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 = ? Example Scripts

  33. 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? Generating MobiLink Scripts

  34. 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 MobiLink Special Mode

  35. 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 Verbosity

  36. 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) DBMLSync

  37. 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 Publication

  38. 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'; Subscription

  39. 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’ DBMLSync Options

  40. 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'; Setup DBMLSync

  41. 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 generated Run DBMLSync

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

  43. 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 = ? Generated Scripts

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

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

  46. 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 All Rows

  47. 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 Timestamp Based Requirement

  48. 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 Last_modified Column

  49. New to Vail (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 LastDownload Time

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

More Related