Whether you are designing a new database or preparing an existing one to be used as a MobiLink consolidated database, you must install the MobiLink system tables in that database. SQL Anywhere provides setup scripts for SAP Adaptive Server Enterprise, Oracle, Microsoft SQL Server, and IBM DB2. A setup script is not required for SQL Anywhere databases.
MobiLink system tables store information for MobiLink users, tables, scripts, and script versions in the consolidated database. You will probably not directly access these tables, but you alter them when you perform actions such as adding synchronization scripts.
ODBC connections and drivers
To carry out synchronization, the MobiLink synchronization server needs an ODBC connection to the consolidated database. You must have an ODBC driver for your server and you must create an ODBC data source for the database on the machine on which your MobiLink synchronization server is running. For a list of supported drivers, see Recommended ODBC Drivers for MobiLink at https://archive.sap.com/documents/docs/DOC-67711.
Writing synchronization scripts
There are two types of events that occur during synchronization and for which you need to write synchronization scripts:
-
Connection events that perform global tasks required during every synchronization
-
Table events that are associated with a specific table and perform tasks related to modifying data in that table
At the connection level, the sequence of major events is as follows:
begin_connection
begin_synchronization
begin_upload
end_upload
prepare_for_download
begin_download
end_download
end_synchronization
end_connectionWhen a synchronization request occurs, the begin_connection event is fired. When all synchronization requests for the current script version have been completed, the end_connection event is fired. Typically you place initialization and cleanup code in the scripts for these events, such as variable declaration and database cleanup.
Apart from begin_connection and end_connection, all of these events take the MobiLink user name stored in the ml_user table in the consolidated database as a parameter. You can use parameters in your scripts by placing question marks where the parameter value should be substituted.
To make scripts in SQL Anywhere databases easier to read, you might declare a variable in the begin_connection script, then set it to the value of ml_username in the begin_synchronization script.
For example, in begin_connection:
CREATE VARIABLE @sync_user VARCHAR(128);
In begin_synchronization:
SET @sync_user = ?
The begin_synchronization and end_synchronization events are fired before and after changes are applied to the remote and consolidated databases.
The begin_upload event marks the beginning of the upload transaction. Applicable inserts and updates to the consolidated database are performed for all remote tables, then rows are deleted as applicable for all remote tables. After end_upload, upload changes are committed.
If you do not want to delete rows from the consolidated database, do not write scripts for the upload_delete event, or use the STOP SYNCHRONIZATION DELETE statement in your PowerScript code. For more information, see Deleting rows from the remote database only.
The begin_download event marks the beginning of the download transaction. Applicable deletes are performed for all remote tables, and then rows are added as applicable for all remote tables in the download_cursor. After end_download, download changes are committed. These events have the date of the last download as a parameter.
Other connection-level events can also occur, such as handle_error, report_error, and synchronization_statistics. For a complete list of events and examples of their use, see the chapter on synchronization events in the MobiLink Administration Guide.
Many of the connection events that occur between the begin_synchronization and end_synchronization events, such as begin_download and end_upload, also have table equivalents. These and other overall table events might be used for tasks such as creating an intermediate table to hold changes or printing information to a log file.
You can also script table events that apply to each row in the table. For row-level events, the order of the columns in your scripts must match the order in which they appear in the CREATE TABLE statement in the remote database, and the column names in the scripts must refer to the column names in the consolidated database.
Generating default scripts
Although there are several row-level events, most tables need scripts for three upload events (for INSERT, UPDATE, and DELETE) and one download event. To speed up the task of creating these four scripts for every table, you can generate scripts for them automatically by running the -- create a synchronization model -- task from the MobiLink plug-in in SQL Central.
For information on the MobiLink plug-in, see the online MobiLink Getting Started book.
The MobiLink plug-in allows you to add more functionality to default scripts than default scripts generated in earlier versions of MobiLink. However, if you are using ASA 8 or ASA 9 instead of SQL Anywhere 10, 11, 12, 16, or 17, you can still generate default synchronization scripts by starting the MobiLink synchronization server with the -za switch and setting the SendColumnNames extended option for dbmlsync.
The following procedure describes how to generate ASA 8 or 9 synchronizations scripts from the PowerBuilder UI.
To generate ASA 8 or 9 synchronization scripts automatically from PowerBuilder:
-
Expand the ODBC Utilities folder in the Database painter and double-click the MobiLink Synchronization Server item.
The MobiLink Synchronize Server Options dialog box displays.
-
Select Adaptive Server Anywhere 8 or 9 from the MobiLink Version drop-down list.
You enable the Automatic Script Generation check box.
-
Select the Automatic Script Generation check box in the MobiLink Synchronize Server Options dialog box and click OK to start the server.
You can open this dialog box from the Utilities folder in the Database painter or the Database Profiles dialog box.
-
In your application, enter SendColumnNames=ON in the Extended text box on the Settings page of the w_appname_sync_options window.
You must have at least one publication, user, and subscription defined in the remote database. If you have more than one publication or user, you must use the -n and/or -u switches to specify which subscription you want to work with.
If there are existing scripts in the consolidated database, MobiLink does nothing. If there are no existing scripts, MobiLink generates them for all tables specified in the publication. The scripts control the upload and download of data to and from your client and consolidated databases.
If the column names on the remote and consolidated database differ, the generated scripts must be modified to match the names on the consolidated database.
You can also generate ASA 8 or 9 synchronization scripts from a command prompt. Start the server using the -za switch, then run dbmlsync and set the SendColumnNames extended option to on. For example:
dbmlsrv9 -c "dsn=masterdb" -za dbmlsync -c "dsn=remotedb" -e SendColumnNames=ON
Generated scripts
The following table shows sample default scripts generated by the MobiLink plug-in in SQL Central. The scripts are generated for a table named emp with the columns emp_id, emp_name, and dept_id. The primary key is emp_id. The generated download scripts use a timestamp based download.
|
Script name |
Script |
|---|---|
|
upload_insert |
INSERT INTO "GROUP1"."emp" ( "emp_id", "emp_name", "dept_id" ) VALUES ( {ml r."emp_id"}, {ml r."emp_name"}, {ml r."dept_id"} ) |
|
upload_update |
UPDATE "GROUP1"."emp" SET "emp_name" = {ml r."emp_name"}, "dept_id" = {ml r."dept_id"} WHERE "emp_id" = {ml r."emp_id"} |
|
upload_delete |
DELETE FROM "GROUP1"."emp" WHERE "emp_id" = {ml r."emp_id"} |
|
download_cursor |
SELECT "GROUP1"."emp"."emp_id", "GROUP1"."emp"."emp_name", "GROUP1"."emp"."dept_id" FROM "GROUP1"."emp" WHERE "GROUP1"."emp"."last_modified" >= {ml s.last_table_download} |
|
download_delete_cursor |
SELECT "emp_del"."emp_id FROM "emp_del" WHERE "emp_del"."last_modified" >= {ml s.last_table_download} |
The scripts that you generate with the MobiLink plug-in constitute a synchronization model. After you create a synchronization model, you must use the -- Deploy the synchronization model -- task of the plug-in to deploy the scripts to consolidated and remote databases or to SQL files.
The following table shows the scripts that are generated for the same table using the -za command switch for the ASA 9 MobiLink synchronization server. The scripts generated for downloading data perform "snapshot" synchronization. A complete image of the table is downloaded to the remote database. Typically you need to edit these scripts to limit the data transferred.
For more information, see Limiting data downloads.
|
Script name |
Script |
|---|---|
|
upload_insert |
INSERT INTO emp (emp_id, emp_name, dept_id) VALUES (?,?,?) |
|
upload_update |
UPDATE emp SET emp_name = ?, dept_id = ? WHERE emp_id=? |
|
upload_delete |
DELETE FROM emp WHERE emp_id=? |
|
download_cursor |
SELECT emp_id, emp_name, dept_id FROM emp |
Before modifying any scripts, you should test the synchronization process to make sure that the generated scripts behave as expected. Performing a test after each modification will help you narrow down errors.
You can view and modify existing scripts and write new ones in the MobiLink Synchronization plug-in in SQL Central (formerly known as Sybase Central). These procedures describe how to connect to the plug-in and write scripts, and how to add a user to the consolidated database.
To connect to a consolidated database in SQL Central:
-
Start SQL Central and select Connections>Connect with MobiLink 11 from the menu bar.
-
On the Identification page in the Connect to Consolidated Database dialog box, select or browse to a data source name or file, and click OK.
When you expand the node for a consolidated database in the MobiLink Synchronization plug-in, you see folders with the following labels: Tables, Connection Scripts, Synchronized Tables, Users, Versions, and Notifications. All the procedures in this section begin by opening one of these folders.
Script versions
Scripts are organized into groups called script versions. By specifying a particular version, MobiLink clients can select which set of synchronization scripts is used to process the upload stream and prepare the download stream. If you want to define different versions for scripts, you must add a script version to the consolidated database before you add scripts for it.
If you create two different versions, make sure that you have scripts for all required events in both versions.
To add a script version:
-
Open the Versions folder, then select File>New>Version from the SQL Central menu bar.
-
In the Create Script Version wizard, provide a name for the version and optionally a description, then click Finish.
SQL Central creates the new version and gives it a unique integer identifier.
Adding synchronized tables and scripts
Scripts added for connection events are executed for every synchronization. Scripts added for table events are executed when a specific table has been modified. You must specify that a table is synchronized before you can add scripts for it.
To add a table for synchronization:
-
Open the Synchronized Tables folder and select File>New>Synchronized Table.
-
Specify a remote table name you want to synchronize or select a table in the consolidated database that has the same name as a table in the remote database.
-
Click Finish.
To add a script to a synchronized table:
-
Double-click a table name in the Synchronized Tables folder, then select File>New>Table Script.
-
In the Create Table Script wizard, select the version for which you want to add a script, select the event you want to cause the script to execute, and click Next.
-
Choose to create a new script definition and the language (SQL, Java, or .NET) in which you want to write the definition, or select an existing script version that you want to share for the new script.
-
Click Finish.
-
Type your script in the editor that displays, then save and close the file.
For example, if you want to remove rows that have been shipped from the Order table in a remote database, you can place the following SELECT statement in the download_delete_cursor event, where order_id is the primary key column. The first parameter to this event is the last_download timestamp. It is used here to supply the value for a last_modified column:
SELECT order_id FROM Order WHERE status = 'Shipped' AND last_modified >= ?
For more information about using the download_delete_cursor event, see the section on "Writing download_delete_cursor scripts" in the online MobiLink - Server Administration book.
To add a connection-level script:
-
Open the Connection Scripts folder and select File>New>Connection Script from the menu bar.
-
Follow steps 2 to 5 in the previous procedure.
Adding users
You can add users directly to the ml_user table in the consolidated database, then provide the user names and optional passwords to your users. To add a user, open the Users folder, select File>New>User, and complete the Create User wizard.
You also have to add at least one user name to each remote database, as described in Creating MobiLink users.
