Transcript No Slide Title - SAP SQL Anywhere Forum

EM402
MobiLink Tips and
Techniques
David Fishburn
Principal Consultant
iAnywhere Solutions
[email protected]
1
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
2
Topics
Synchronization
The Task
Database Migration
Generating MobiLink Scripts
Script Customization
Remote Monitoring
3
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)
4
Replication Server Architecture
ORACLE
ASA
DB2
DBSRV6
Rep Server
DBLTM
Other
ASE
MAPI
FTP
SMTP
FILE
ASE
ASE LTM
ASA
5
VIM
SQL Remote
ASE
ASA
OR
MAPI
FTP
SMTP
VIM
MAPI
FILE
FTP
SMTP
FILE
ASA
ASA
6
VIM
SQL Remote with Replication
Server
Rep Server
ASE LTM
SSQueue
ASE
MAPI
FTP
SMTP
FILE
ASA
7
VIM
MobiLink
ASA, ASE, Microsoft, Oracle, IBM
HTTP, TCPIP
Serial
HotSync, Wireless
ASA, PalmOS, PocketPC, UltraLite
8
MobiLink and SQL Remote
ASA, ASE
SQL Remote
MobiLink
ASA, Palm, CE, Pagers, Phones
9
Synchronization Process
2. Data is downloaded
to the remote database
1. Data is uploaded
to the consolidated
database
10
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
11
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 Vail)
Changes are sent to the remote
12
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
13
Topics
Synchronization
The Task
Database Migration
Generating MobiLink Scripts
Script Customization
Remote Monitoring
14
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 
15
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
This is still expected
Customization of the MobiLink scripts will be required
• MobiLink was designed for these cases as well
16
Topics
Synchronization
The Task
Database Migration
Generating MobiLink Scripts
Script Customization
Remote Monitoring
17
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
18
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
19
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, MSAccess 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…
20
Setup Remote Data Access
Use Sybase Central to setup
21
Setup Remote Data Access
Setup via SQL statements
1.
2.
3.
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";
22
Database Migration (Short way)
Special one step ASA stored procedure will migrate
Schema
Data (optional)
Procedure
dbo.sa_migrate(
IN local_table_owner
IN server_name
IN table_name
IN owner_name
IN database_name
IN migrate_data
IN drop_proxy_tables
VARCHAR(128),
VARCHAR(128),
VARCHAR(128) DEFAULT NULL,
VARCHAR(128) DEFAULT NULL,
VARCHAR(128) DEFAULT NULL,
BIT DEFAULT 1,
BIT DEFAULT 1)
23
Procedure sa_migrate()
CALL sa_migrate( 'DBA', 'mss_cons',
'UL%', 'dbo', 'CustDB', 0, 1 );
DBA
mss_cons
UL%
dbo
CustDB
0
1
Create the tables under the DBA owner (ASA)
ASA Remote Server
Only create the tables that begin with ‘UL’
MSS owner of the tables
Which MSS database (there can be many)
Do not migrate data
Drop the proxy tables when finished
24
Verify Results
Check only UL% tables were created
Ensure no data was migrated
25
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
1.
2.
3.
4.
5.
6.
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
26
sa_migrate_create_remote_table_
list
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
27
Topics
Synchronization
The Task
Database Migration
Generating MobiLink Scripts
Script Customization
Remote Monitoring
28
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
29
Scripts
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
30
Script Events
For each table that is synchronized bidirectionally 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
31
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 = ?
32
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?
33
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
34
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
35
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)
36
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
37
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';
38
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’
39
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';
40
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
41
Generated Scripts
View with Sybase Central – MobiLink
Synchronization Server Plugin
42
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 = ?
43
The Task - Complete
Get it synchronizing in 10 minutes 
Did we do it?
Easy as 1-2-3
• Schema migration
• Script generation
• Initial synchronization
44
Topics
Synchronization
The Task
Database Migration
Generating MobiLink Scripts
Script Customization
Remote Monitoring
45
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
46
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
47
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
48
LastDownload Time
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
49
Initial LastDownload Time
If the remote has never synchronized before
The initial value for the LastDownload time is
• ‘1900/01/01 00:00’
50
Timestamp Based
Synchronization
Each download_cursor script is passed 2
parameters
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 >= ?
51
Timestamp Based
Synchronization
If your download_cursor does not require
the user name
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
52
Timestamp Based With ASA
begin_connection
create variable @EmployeeID integer;
create variable @LastDownload timestamp
begin_download
CALL SetParameters(?, ?, @EmployeeID, @LastDownload )
53
SetParameters
CREATE PROCEDURE SetParameters(
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;
54
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
55
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
56
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
57
Topics
Synchronization
The Task
Database Migration
Generating MobiLink Scripts
Script Customization
Remote Monitoring
58
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?
59
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
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
61
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
62
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
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;
64
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
65
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
66
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…)
67
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
68
DBMLSync Options
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';
69
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
70
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
EM403 MobiLink Best Design Practices
71
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?
72
iAnywhere Solutions Highlights
• Ask the Experts - about Mobile & Wireless Solutions
-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
73