Skip to end of metadata
Go to start of metadata

Purpose

The purpose of this page is to discuss MobiLink synchronization profiles and how they can be created and used.

Overview

Synchronization profiles are a feature in SQL Anywhere that enables SQL Anywhere and UltraLite synchronization options to be stored in the remote database. This can significantly reduce the complexity of the command line or statement used to start synchronization of the remote database. Moreover, it allows the synchronization configuration to reside in the database rather than in the application. Storing synchronization information in the database is more beneficial than storing it in the application as updates to the information do not require a new application to be deployed, rather just an update to the database.

A synchronization profile can be specified when calling the SQL Anywhere client (dbmlsync), the UltraLite SYNCHRONIZE statement or when evoking synchronization through the dbmlsync API. A synchronization profile can be dropped or modified once it has been created.

Creating a Synchronization Profile

Use the following syntax to create a synchronization profile for a MobiLink client:

CREATE SYNCHRONIZATION PROFILE sync-profile-name sync-options;

The sync-profile-name uniquely identifies the synchronization profile in the remote database. The sync-options must be a valid options string for either a SQL Anywhere client or UltraLite client as described in the Synchronization Profile Options section.

Dropping a Synchronizaton Profile

Use the following syntax to drop a synchronization profile in the remote database:

DROP SYNCHRONIZATION PROFILE sync-profile-name;

The synchronization profile specified by the sync-profile-name must already exist in the remote database.

Altering a Synchronization Profile

Use the following syntax to alter a synchronization profile in the remote database:

ALTER SYNCHRONIZATION PROFILE sync-profile-name {REPLACE | MERGE} sync-options;

The synchronization profile specified by the sync-profile-name must already exist in the remote database.

When REPLACE is used, the alter synchronization profile statement replaces the existing synchronization profile with the specified sync-options for the given sync-profile-name. This is equivalent to executing a drop synchronization profile followed by a create synchronization profile.

The examples below show that altering a SQL Anywhere profile is equivalent to dropping and creating a synchronization profile:

ALTER SYNCHRONIZATION PROFILE example REPLACE 'Publication=pub1;Verbosity=HIGH';

is equivalent to

DROP SYNCHRONIZATION PROFILE example;
CREATE SYNCHRONIZATION PROFILE example 'Publication=pub1;Verbosity=HIGH';

When MERGE is used, the alter synchronization profile statement adds the new sync-options to the existing synchronization profile. If an option in the sync-options is already specified in the existing synchronization profile, this existing option is replaced with the new value specified in the sync-options string.

For example, executing the following statements:

CREATE SYNCHRONIZATION PROFILE example
'Publication=pub1;Verbosity=BASIC';

ALTER SYNCHRONIZATION PROFILE MERGE
'P
ublication=pub2;UploadOnly=ON;Verbosity=HIGH';

is equivalent to

CREATE SYNCHRONIZATION PROFILE example
'Publication=pub2;UploadOnly=ON;Verbosity=HIGH';

Note that the above syntax for the example synchronization profile only applies to SQL Anywhere clients. The Publication and Verbosity profile options only exist for SQL Anywhere clients. For a full list of options for SQL Anywhere client and UltraLite client profile options, see the Synchronization Profile Option Strings section.

For UltraLite clients, there is additional functionality when modifying synchronization profiles. For UltraLite clients only, to remove an option from the profile string, simply enter the option name with an equals sign but without a value. For example, consider the following statements:

CREATE SYNCHRONIZATION PROFILE ul_example
'ScriptVersion=ul_script_v1;Publications=pub1'

then

ALTER SYNCHRONIZATION PROFILE ul_example MERGE
'ScriptVersion=;'

is equivalent to

CREATE SYNCHRONIZATION PROFILE ul_example
'Publications=pub1'

Note that synchronization profile strings can contain sub-option lists. Consider the following example for UltraLite:

CREATE SYNCHRONIZATION PROFILE ul_example
'MobiLinkUid=admin;Stream=HTTP(host-ww.sybase.com;port=2439)');

The host and port values are a sub-option list. They are grouped together by using parantheses (either round or curly) and are sepcified under the Stream option value. For UltraLite clients only, sub-option values can also be added or removed with the ALTER statement. The following statement adds compression to the stream parameters, removes the port from the list and fixes the typo in the host value:

ALTER SYNCHRONIZATION PROFILE ul_example MERGE
'Stream.compression=zlib;Stream.port=;Stream.host=www.sybase.com';

Notice that sub-options are referenced in the form parent_option_name.sub_option_name.

Note that the above syntax for ul_example synchronization profile only applies to UltraLite clients. SQL Anywhere clients do not have the ability to specify option=; to remove a profile option in an existing synchronization profile. SQL Anywhere clients also do not have the ability to modify sub-options of a profile option for an existing synchronization profile. For these types of operations with SQL Anywhere clients, a DROP SYNCHRONIZATION PROFILE followed by a CREATE SYNCHRONIZATION PROFILE is required.

Synchronization Profile Option Strings

The synchronization profile option string is a semicolon-delimited list of elements in the form of <option name>=<option value>.

The tables below describes the valid <option name> and its corresponding <option value> for SQL Anywhere clients and UltraLite clients. SQL Anywhere client options have a long form and short form name that can be used interchangeably.

SQL Anywhere Clients

The following options can be specified when creating a synchronization profile for a SQL Anywhere client:

Long NameShort NameValid ValuesDescription
AuthParmsapStringSame as -ap command line option
ApplyDnldFilebaStringSame as -ba command line option
ContinueDownloaddcBooleanSame as -dc command line option
CreateDnldFilebcStringSame as -bc command line option
DnldFileExtra be String Same as -be command line option 
DownloadOnly ds Boolean Same as -ds command line option 
DownloadReadSize drs Integer Same as -drs command line option 
ExtOpt String Same as -e command line option 
IgnoreHookErrors eh Boolean Same as -eh command line option 
IgnoreSchedulingisBooleanSame as -is command line option
KillConnectionsdBooleanSame as -d command line option
LogRenameSizexInteger optionally followed by K or MSame as -x command line option. Setting LogRenameSize=0 is equivalent to specifying -x on the command line. Specifying LogRenameSize=<val> is equivalent to specifying -x <val> on the command line.
MobiLinkPwdmpStringSame as -mp command line option
MLUseruStringSame as -u command line option
NewMLPasswordmnStringSame as -mn command line option
PingpiBooleanSame as -pi command line option
PublicationnStringSame as -n command line option, except unlike the command line option, publication can only be specified once in a sync profile. The command line option can be specified multiple times.
RemoteProgressGreaterraBooleanSame as -ra command line option
RemoteProgressLessrbBooleanSame as -rb command line option
TransactionalUploadtuBooleanSame as -tu command line option
UpdateGenNumbgBooleanSame as -bg command line option
UploadOnlyuoBooleanSame as -uo command line option
UploadRowCnturcIntegerSame as -urc command line option
Verbosity String: a comma separated list of options

Controls dbmlsync verbosity. Similar to -v command line option. The value must be a comma separated list of one or more of the following options each of which corresponds to an existing -v option as described below:
BASIC: equivalent to -v
HIGH: equivalent to -v+
CONNECT_STR: equivalent to -vc
ROW_CNT: -equivalent to -vn
OPTIONS: equivalent to -vo
ML_PASSWORD: equivalent to -vp
ROW_DATA: equivalent to -vr
HOOK: equivalent to -vs

 For string options that take a Boolean value, setting the option to TRUE is equivalent to specifying the following options on the SQL Anywhere client command line (dbmlsync):

  • Options to specify TRUE: ON, 1, YES, TRUE
  • Options to specify FALSE: OFF, 0, NO, FALSE

UltraLite Clients

The following options can be specified when creating a synchronization profile for UltraLite clients:

Profile OptionsValid ValuesDescription
AllowDownloadDupRowsBooleanThis option prevents errors from being raised when multiple rows are downloaded that have the same primary key. This can be used to allow inconsistent data to be synchronized without causing the synchronization to fail. The default value is FALSE.
Auth ParmsString: a comma separated list of optionsSame as Authentication Parameters synchronization parameters
CheckpointStoreBooleanSame as Checkpoint Store synchronization parameter
ContinueDownloadBooleanSame as Resuming failed downloads
DisableConcurrencyBooleanSame as Disable Concurrency synchronization parameter
DownloadOnlyBooleanSame as Download Only synchronization parameter
KeepPartialDownloadBooleanSame as Keep Partial Download synchronization parameter
MobiLinkPwdStringSame as Password synchronization parameter
MobiLinkUidStringSame as User Name synchronization parameter
NewMobiLinkPwdStringSame as Password synchronization parameter
PingBooleanSame as Ping synchronization parameter
PublicationsString: a comma separated list of optionsSame as Publications synchronization parameter. If this parameter is blank (default) or set to an asterick (*), then all tables in the UltraLite database are synchronized
ScriptVersionStringSame as version synchronization paramter. By default, if there is no script version specified, then 'default' is used during the synchronization
SendColumnNamesStringSame as Send Column Names synchronization parameter
SendDownloadACKBooleanSame as Send Download Acknowledgement synchronization parameter
StreamString with sub-listSame as Stream Type synchronization parameter
 TableOrderString: a comma separated list of options Same as Table Order synchronization parameter 
UploadOnly String Same as Upload Only synchronization parameter 

 For string options that take  Boolean value, setting the option to TRUE/FALASE is equivalent to specifying the following synchronization parameters for UltraLite clients:

  • Options to specify TRUE: ON, 1, YES, TRUE
  • Options to specify FALSE: OFF, 0, NO, FALSE

Using Synchronization Profiles

SQL Anywhere Clients

In SQL Anywhere, there is a dbmlsync command line option -sp <sync profile>. When the -sp option is used on the dbmlsync command line, the options that exist in the synchronization profile name specified by <sync profile> are added to the command line for execution. If the same options specified on the dbmlsync command line exist in the synchronization profile, the command line overrides the options specified in the synchronization profile.

Synchronization profiles are also used with the Dbmlsync API. When calling the method public UInt32 Sync(string profile_name, string extra_opts) for .NET or DBSC_SyncHdl Sync(const char *profile_name, const char *extra_opts) for C++, the synchronization profile can be passed in as the first parameter. Additional parameters can be specified in the second parameter of the method call that will override any existing options in the synchronization profile.

For more details on the Dbmlsync API, see the Related Documents section.

UltraLite Client

In SQL Anywhere, there is an UltraLite SYNCHRONIZE statement. With the SYNCHRONIZE statement, the synchronization is configured according to the parameters in the synchronization profile, or the parameters can be specified in the statement itself with the USING option.

For example:

SYNCHRONIZE PROFILE ul_example

will synchronize using the synchronization profile ul_example. The following:

CREATE SYNCHRONIZATION PROFILE ul_example
'Publications=pub1'

SYNCHRONIZE PROFILE ul_example MERGE
'Publications=pub2;MobiLinkPwd=sql'

will override the Publications to use pub2 and add the MobiLink password to the synchronization options for this synchronization. It is also possible to simply synchronize with a list of parameters and no profile name. For example:

SYNCHRONIZE USING
'MobiLinkUid=admin;MobiLinkPwd=sql;Stream=TCPIP(host=192.169.1.1);ScriptVersion=Test'

This initiates a synchronization with the options listed in the string.

Demo

This demonstration of synchronization profiles makes use of a simple database schema consisting of four tables: Admin, Inventory, Parent and Child. In the consolidated database, there are shadow tables for each of the four base tables to handle deleted rows and necessary table scripts to handle uploads and downloads. The remote database is created with the same schema. The four tables exist in publication pub1 with synchronization user rem100. The SQL Anywhere client (dbmlsync) is used to perform the synchronization with a SQL Anywhere remote database.

The script for the demo can be found in the attachments for this document: SynchronizationProfiles.zip

Creating the SQL Anywhere Consolidated Database

The SQL Anywhere consolidated database is created by executing the setup.bat file located in the |SynchronizationProfiles|cons directory. Executing the setup.bat file will:

  • Initialize the SQL Anywhere consolidated database MLcons.db
  • Create a user DSN profile_cons with connection parameters for the MLcons database
  • Start the MLcons database
  • Execute the syncsa.sql script to create the MobiLink system objects
  • Execute the cons_chema.sql script found in the \SynchronizationProfiles\cons directory to create the consolidated database schema in the MLcons database
  • Insert test data into the MLcons database
  • Start the MobiLink server

Creating the SQL Anywhere Remote Database and Synchronization Profile

 

The SQL Anywhere remote database is created by executing the setup.bat file located in the \SynchronizationProfiles\rem directory. Executing the setup.bat file will:

  • Initialize the SQL Anywhere remote database MLrem.db
  • Create a user DSN profile_rem with connection parameters for the MLrem database
  • Start the MLrem database
  • Execute the rem_schema.sql script found in the \SynchronizationProfiles\rem directory to create the remote database schema in the MLrem database. The rem_schema.sql script creates a synchronization profile with the following definition:
         CREATE SYNCHRONIZATION PROFILE profile1
         'Verbosity=HIGH;Publication=pub1;MobiLinkPwd=sql;ExtOpt={
         CommunicationType=tcpip;CommunicationAddress=''host=localhost;
         port=2439'';ScriptVersion=profile_v1}';
  • Insert test data into the MLrem database
  • Start dbmlsync with the -sp profile1 option specified to complete an initial synchronization with the MobiLink server. The following dbmlsync command line is executed:
         dbmlsync -c "DSN=profile_rem" -sp profile1 -o rem.txt 

For new test data to get inserted into the database and a new synchronization to occur, execute the sync.bat file located in the \SynchronizationProfiles\rem directory.

Steps to Run the Demo

The following steps will run the demo using the provided batch files:

  1. \SynchronizationProfiles\cons\setup.bat
  2. \SynchronizationProfiles\rem\setup.bat
  3. \SynchronizationProfiles\rem\sync.bat

Related Content

Related Documents

Dbmlsync API

Related SAP Notes/KBAs

 

  • No labels