M411 – Taking Your Enterprise Mobile in 10 Minutes
Download
1 / 70

M411 – Taking Your Enterprise Mobile in 10 Minutes - PowerPoint PPT Presentation


  • 67 Views
  • Uploaded on

M411 – Taking Your Enterprise Mobile in 10 Minutes. David Fishburn Principal Consultant iAnywhere Solutions [email protected] Objective. To outline a process that makes you productive as quickly as possible Learn techniques to take advantage of this technology

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' M411 – Taking Your Enterprise Mobile in 10 Minutes' - lida


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

M411 – Taking Your Enterprise Mobile in 10 Minutes

  • David Fishburn

    • Principal Consultant

    • iAnywhere Solutions

    • [email protected]


Objective
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
Topics

  • Synchronization

  • The Task

  • Database Migration

  • Generating MobiLink Scripts

  • Script Customization

  • Remote Monitoring


Synchronization
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)


Replication server architecture

ORACLE

ASA

DB2

Rep Server

Other

ASE

DBSRV6

DBLTM

ASE LTM

ASE

MAPI

VIM

SMTP

FTP

FILE

Replication Server Architecture

ASA


Sql remote

MAPI

VIM

MAPI

VIM

SMTP

SMTP

FTP

FTP

FILE

FILE

SQL Remote

ASE

ASA

OR

ASA

ASA


Sql remote with replication server

Rep Server

ASE LTM

SSQueue

ASE

MAPI

VIM

SMTP

FTP

FILE

ASA

SQL Remote with Replication Server


Mobilink
MobiLink

ASA, ASE, Microsoft, Oracle, IBM

HTTP, TCPIP

HotSync, Wireless

ASA, PalmOS, PocketPC, UltraLite


Mobilink and sql remote
MobiLink and SQL Remote

ASA, ASE

SQL Remote

MobiLink

ASA, Palm, CE, Pagers, Phones


Synchronization process
Synchronization Process

1. Data is uploaded to the consolidated database

2. Data is downloaded to the remote database


Two main phases
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
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
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


Topics1
Topics

  • Synchronization

  • The Task

  • Database Migration

  • Generating MobiLink Scripts

  • Script Customization

  • Remote Monitoring


The task
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 task1
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


Topics2
Topics

  • Synchronization

  • The Task

  • Database Migration

  • Generating MobiLink Scripts

  • Script Customization

  • Remote Monitoring


Consolidated database
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
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
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
Setup Remote Data Access

  • Use Sybase Central to setup


Setup remote data access1
Setup Remote Data Access

  • Use Sybase Central to setup


Database migration wizard
Database Migration Wizard

  • Use the Sybase Central migration tool


Verify results
Verify Results

  • Check only UL% tables were created

  • Ensure no data was migrated


Topics3
Topics

  • Synchronization

  • The Task

  • Database Migration

  • Generating MobiLink Scripts

  • Script Customization

  • Remote Monitoring


Script versions
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

  • 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
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
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
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 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
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
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
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
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
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
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
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
Generated Scripts

  • View with Sybase Central – MobiLink Synchronization Server Plugin


Generated scripts1
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
The Task - Complete

  • Get it synchronizing in 10 minutes 

    • Did we do it?

    • Easy as 1-2-3

      • Schema migration

      • Script generation

      • Initial synchronization


Topics4
Topics

  • Synchronization

  • The Task

  • Database Migration

  • Generating MobiLink Scripts

  • Script Customization

  • Remote Monitoring


All rows
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
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
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
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
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
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 synchronization1
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
Timestamp Based With ASA

  • begin_connection

    CREATE VARIABLE @EmployeeID integer;

    CREATE VARIABLE @LastDownload timestamp

  • begin_download

    CALL SetParameters(?, ?, @LastDownload, @EmployeeID )


Setparameters
SetParameters

CREATE PROCEDURE SetParameters(

IN i_last_download TIMESTAMP,

IN i_sync_user_name VARCHAR(40),

OUT o_last_download TIMESTAMP,

OUT o_sync_user_name VARCHAR(40),

)

BEGIN

set o_last_download = i_last_download;

set o_sync_user_name = i_sync_user_name;

END;


Timestamp based with variables
Timestamp Based with Variables

  • If your download_cursor with variables

    SELECT cust_id, …

    FROM ULCustomer

    WHERE last_modified >= @LastDownload

  • There are always many ways to do the same thing

    • Depends on the features of your consolidated RDBMS

    • Your preferences / standards

    • Scripts written in Java equalize the features of the other RDBMS’


Lastdownload time1
LastDownload Time

  • In version 7.x

    • The LastDownload time was NOT automatically provided

    • YOU had to maintain this value in a user table

      • It was retrieved in the begin_download event for the current synchronization

      • It was updated in the begin_download event (after retrieval)

      • This was an uncommitted transaction UNTIL MobiLink received a confirmation from the remote


Download acknowledgement
Download Acknowledgement

  • By default MobiLink will wait for the remote to acknowledge the download BEFORE committing the download

    • Now that 8.0 manages the LastDownload date, this is normally no longer required

    • To turn off use

      • Extended option – SendDownloadAck = ‘No’

      • Ultralite ul_synch_info.send_download_ack = false

    • I recommend turning this off


Topics5
Topics

  • Synchronization

  • The Task

  • Database Migration

  • Generating MobiLink Scripts

  • Script Customization

  • Remote Monitoring


Remote monitoring
Remote Monitoring

  • Consider a project with 1000 deployed remote users

    • How can you (the administrator) be certain all 1000 users are synchronizing without problems

    • If there are problems

      • How can you research it?

      • What information is available?

      • How can you capture more information?


Default error handling
Default Error Handling

  • Default behaviour

    • If a SQL error occurs while MobiLink is accessing the consolidated database

      • handle_error event is fired

      • report_error event is fired

      • Current transaction is rolled back

        • All rows affected by the upload OR

        • All rows affected by the download

      • Synchronization session is ended

      • Remote receives a error


Custom error handling
Custom Error Handling

  • You can choose to handle the error

    • handle_error connection level event

    • Stored procedure call

      • Requires a number of parameters

    • The return code from the stored procedure instructs MobiLink

      • 1000 - Ignore error OR

      • 3000 - Stop the synchronization request OR

      • 4000 - Stop the MobiLink server

  • I do not recommend handling the error

    • Design to prevent errors instead


Error reporting
Error Reporting

  • report_error script

    • Uses same parameters as handle_error (minimally)

    • Executed on a separate connection

    • Logging/auditing is committed outside of the upload/download transaction

      • Therefore the action is permanent

  • I highly recommend the use of the report_error


Error handling scripts
Error Handling Scripts

CREATE TABLE sync_error_audit (

id int DEFAULT autoincrement,

sync_user varchar(128),

table_name varchar(128),

error_msg long varchar,

error_date datetime DEFAULT current timestamp,

primary key( id )

);

call ml_add_connection_script( 'version', 'report_error',

'CALL MLReportError( ?, ?, ?, ?, ? )' );


Error logging
Error Logging

CREATE PROCEDURE MLReportError(

IN action integer,

IN error_code integer,

IN error_message varchar(1000),

IN user_name varchar(128),

IN in_table_name varchar(128) )

BEGIN

INSERT INTO sync_error_audit( sync_user, table_name, error_msg ) VALUES( user_name, in_table_name, error_message );

END;


Automated notifications
Automated Notifications

  • Many databases support email interfaces (ASA, ASE, Microsoft at least)

  • The MLReportError procedure could be enhance to automatically email an administrator (or group) of the error

  • Syntax/Names may vary for different RDBMS

    • Java Synchronization Logic can also be used with JavaMail to achieve the same effect


Email sample
Email Sample

DECLARE rc INTEGER;

rc=call xp_startmail(mail_user=‘MobiLink_Administrator');

//If successful logon to mail

IF rc=0 THEN

rc=call xp_sendmail(recipient='MobiLink_Notification_Group',

subject='MobiLink Synchronization Error: '+user_name,

"message"=error_msg);

//If mail sent successfully

IF rc=0 THEN

call xp_stopmail()

END IF

END IF


Capturing remote logs
Capturing Remote Logs

  • If errors occur during synchronization it is often useful to view the remote DBMLSync log

    • The log is on a remote machine, so how does the administrator get access to it?

    • The user must be contacted

      • Must locate the file

      • Mail it to the administrator

      • Or some other mechanism (Remote control…)


Capturing remote logs1
Capturing Remote Logs

  • MobiLink has a special provision for this

    • -e(t) file_name

    • If an error occurs during synchronization, the remote will send its DBMLSync log to MobiLink

    • MobiLink will store the remote log in this file

    • If used with the automated notifications, an administrator should be well “armed” to research the problem


Mobilink monitor
MobiLink Monitor

  • Allows you to monitor synchronizations

    • Real time

    • Offline

  • Can set watches to flag any of the properties supplied

    • Examples

      • Duration was > 4 minutes

      • Failed synchronizations

      • A certain number of conflicts

  • Allows you to determine if and where your attention should be focused



Summary
Summary

  • Database migration is very useful for creating remote database schemas fast and effectively

  • Script generation can save a great deal of time initially

    • Scripts must be customized

    • LastDownload functionality should be added as a minimum


Summary1
Summary

  • Remote Monitoring should always be considered in any distributed environment

    • Consider ways to automate notifications

      • Java can be used to extend that to email pages and so on

    • Any effort taken at the outset can save a great deal of time and effort if a problem arises

  • M413 MobiLink Design Best Practices


Newsgroups
Newsgroups

  • forums.sybase.com

    • sybase.public.sqlanywhere.general

    • sybase.public.sqlanywhere.linux

    • sybase.public.sqlanywhere.mobilink

    • sybase.public.sqlanywhere.product_futures_discussion

    • sybase.public.sqlanywhere.replication

    • sybase.public.sqlanywhere.ultralite

  • Questions?


ad