EM402
Download
1 / 73

EM402 MobiLink Tips and Techniques - PowerPoint PPT Presentation


  • 75 Views
  • Uploaded on

EM402 MobiLink Tips and Techniques. David Fishburn Principal Consultant iAnywhere Solutions [email protected] 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 'EM402 MobiLink Tips and Techniques' - theo


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

EM402MobiLink Tips and Techniques


Objective

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


Topics

Synchronization possible

The Task

Database Migration

Generating MobiLink Scripts

Script Customization

Remote Monitoring

Topics


Synchronization

New technology (since April 1999) possible

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?


Replication server architecture

ORACLE possible

ASA

DB2

Rep Server

Other

ASE

DBSRV6

DBLTM

ASE LTM

ASE

MAPI

VIM

SMTP

FTP

FILE

Replication Server Architecture

ASA


Sql remote

MAPI possible

VIM

MAPI

VIM

SMTP

SMTP

FTP

FTP

FILE

FILE

SQL Remote

ASE

ASA

OR

ASA

ASA


Sql remote with replication server

Rep Server possible

ASE LTM

SSQueue

ASE

MAPI

VIM

SMTP

FTP

FILE

ASA

SQL Remote with Replication Server


Mobilink
MobiLink possible

ASA, ASE, Microsoft, Oracle, IBM

Serial

HTTP, TCPIP

HotSync, Wireless

ASA, PalmOS, PocketPC, UltraLite


Mobilink and sql remote
MobiLink and SQL Remote possible

ASA, ASE

SQL Remote

MobiLink

ASA, Palm, CE, Pagers, Phones


Synchronization process
Synchronization Process possible

2. Data is downloaded to the remote database

1. Data is uploaded to the consolidated database


Two main phases

Upload possible

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


Synchronization transactions

Upload stream possible

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


Mobilink scripts

Scripts control possible

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


Topics1

Synchronization possible

The Task

Database Migration

Generating MobiLink Scripts

Script Customization

Remote Monitoring

Topics


The task

Tips are best learned when we have a task to accomplish possible

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


The task1

Consolidated and remote have an identical schema possible

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


Topics2

Synchronization possible

The Task

Database Migration

Generating MobiLink Scripts

Script Customization

Remote Monitoring

Topics


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

Consolidated 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 Database Schema


Remote data access

Sybase feature that allows an ASA database

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


Setup remote data access
Setup Remote Data Access an ASA database

  • Use Sybase Central to setup


Setup remote data access1

Setup via SQL statements an ASA database

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


Database migration short way

Special one step ASA stored procedure will migrate an ASA database

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)


Procedure sa migrate

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


Verify results

Check only UL% tables were created 0, 1 );

Ensure no data was migrated

Verify Results


Database migration long way

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)


Sa migrate create remote table list

Will build the list of all tables that need to be migrated schema

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


Topics3

Synchronization schema

The Task

Database Migration

Generating MobiLink Scripts

Script Customization

Remote Monitoring

Topics


Script versions

MobiLink scripts are organized by script version schema

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


Scripts

Scripts are written using schema

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


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

Script Events


Example scripts

Scripts (standard SQL) following MobiLink events are required

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


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?

Generating MobiLink Scripts


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

MobiLink Special Mode


Verbosity

MobiLink has varying levels of verbosity that can be enabled automatically generate basic scripts for each of the tables

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


Dbmlsync

MobiLink client automatically generate basic scripts for each of the tables

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


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

Publication


Subscription

A subscription does the following the remote to synchronize

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


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’

DBMLSync Options


Setup dbmlsync

These commands are run against the remote database column names to MobiLink

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


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 generated

Run DBMLSync


Generated scripts
Generated Scripts (-za+)

  • View with Sybase Central – MobiLink Synchronization Server Plugin


Generated scripts1

The automatically generated scripts (-za+)

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


The task complete

Get it synchronizing in 10 minutes (-za+)

Did we do it?

Easy as 1-2-3

Schema migration

Script generation

Initial synchronization

The Task - Complete


Topics4

Synchronization (-za+)

The Task

Database Migration

Generating MobiLink Scripts

Script Customization

Remote Monitoring

Topics


All rows

Generated download_cursors are of this format (-za+)

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


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

Timestamp Based Requirement


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

Last_modified Column


Lastdownload time

New to Vail (8.0) indicating when the row was last changed

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


Initial lastdownload time

If the remote has never synchronized before indicating when the row was last changed

The initial value for the LastDownload time is

‘1900/01/01 00:00’

Initial LastDownload Time


Timestamp based synchronization

Each download_cursor script is passed 2 parameters indicating when the row was last changed

Synchronizing User Name

Last Download Timestamp for that user

Download scripts can be modified

SELECT order_id, …

FROM ULOrder

WHERE emp_id = ?

AND last_modified >= ?

Timestamp Based Synchronization


Timestamp based synchronization1

If your download_cursor does not require the user name indicating when the row was last changed

SELECT cust_id, …

FROM ULCustomer

WHERE ? IS NOT NULL

AND last_modified >= ?

The “? IS NOT NULL” is used as a place holder, since the second “?” is always the LastDownload time

Timestamp Based Synchronization


Timestamp based with asa

begin_connection indicating when the row was last changed

create variable @EmployeeID integer;

create variable @LastDownload timestamp

begin_download

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

Timestamp Based With ASA


Setparameters

CREATE PROCEDURE SetParameters( indicating when the row was last changed

IN sync_user_name VARCHAR(40),

IN last_download TIMESTAMP,

OUT o_sync_user_name VARCHAR(40),

OUT o_last_download TIMESTAMP )

BEGIN

set o_sync_user_name = sync_user_name;

set o_last_download = last_download;

END;

SetParameters


Timestamp based with variables

If your download_cursor with variables indicating when the row was last changed

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

Timestamp Based with Variables


Lastdownload time1

In version 7.x indicating when the row was last changed

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

LastDownload Time


Download acknowledgement

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

Now that Vail 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

Download Acknowledgement


Topics5

Synchronization the download BEFORE committing the download

The Task

Database Migration

Generating MobiLink Scripts

Script Customization

Remote Monitoring

Topics


Remote monitoring

Consider a project with 1000 deployed remote users the download BEFORE committing the download

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?

Remote Monitoring


Default error handling

Default behaviour the download BEFORE committing the download

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

Default Error Handling


Custom error handling

You can choose to handle the error the download BEFORE committing the download

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

Custom Error Handling


Error reporting

report_error script the download BEFORE committing the download

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 Reporting


Error handling scripts

CREATE TABLE sync_error_audit ( the download BEFORE committing the download

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 Handling Scripts


Error logging

CREATE PROCEDURE MLReportError( the download BEFORE committing the download

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;

Error Logging


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

Automated Notifications


Email sample

DECLARE rc INTEGER; at least)

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

Email Sample


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 Logs


Capturing remote logs1

MobiLink has a special provision for this view the remote DBMLSync log

-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

Capturing Remote Logs


Dbmlsync options1

In order for DBMLSync to send the logs to MobiLink an extended option must be enabled

SendOutputLogOnError=‘Yes’

CREATE SYNCHRONIZATION SUBSCRIPTION TO "routing"

FOR "50"

TYPE 'tcpip'

ADDRESS 'host=localhost;port=2439'

OPTION ScriptVersion='v1.0', SendDownloadAck='No', SendOutputLogOnError='Yes';

DBMLSync Options


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

Summary


Summary1

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

EM403 MobiLink Best Design Practices

Summary


Newsgroups

forums.sybase.com distributed environment

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?

Newsgroups


Ianywhere solutions highlights

Ask the Experts - about Mobile & Wireless Solutions distributed environment

-Mezzanine Level Room 15B

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:30pm Mobile & Wireless SDCC, Upper level, Room 11

Keynote - Enabling m-Business Solutions

Wednesday 1:30 pm - 3:00 pm

iAnywhere Solutions Developer Community

-Excellent resource for commonly asked questions, newsgroups, bug

fixes, newsletters, event listings - visit www.ianywhere.com/developer

iAnywhere Solutions Highlights


ad