Archive for category Synchronization

Using Mobilink in a Production Environment

Default Mobilink synchronization is set to the scenario when the data are downloaded once and stay on the mobile device as long as it is used. The changes from the remote database (the one on the device) are synchronized in upload sessions and the data from the central (consolidated) database are sent to the device in download sessions.

However this mode arises some problematic questions like:

  • how to handle multiple users on the same device? Should the data be downloaded for all of them?
  • How to backup existing data?
  • What to do to make the device failure and database corruption the less painful?

To solve these problems and make the whole procedure simple the following scenario can be used: the working data is downloaded to the mobile device at the beginning of a shift and uploaded to the central database at the end of a shift (logout). Data should not stay on the device between shifts.

This gives us several advantages:

  • the data changed at the device won’t be lost in a case of the mobile device hardware failure
  • the data will be uploaded from the mobile device in time (outdated data can produce unexpected conflicts in the central database)
  • uploading the data together with the logout ensures that only the logged person is responsible for the data uploaded from the device.

In such conditions it makes sense:

  • to start the day with the download synchronization (remote database is empty and filled with the data prepared on the consolidated side)
  • to perform full synchronizations during the day if necessary
  • to use upload synchronization at the end of a shift (logout), and truncate all the tables after the synchronization was successfully finished.

Download synchronization

In general case of continuous synchronization download synchronization should know, which rows to download to remote database (so that the same row is not downloaded if it already exists on remote side). Last_download parameter of download_cursor and download_delete_cursor event can be used to distinguish the rows that were updated since the last synchronization. Obviously in this case all the tables, which are supposed to be downloaded, should have a timestamp field showing the time, when the row was updated last. This might be quite undesirable, as it affects the database model.

The solution of cleaning remote database before downloading new data gives you a possibility to avoid this problem. On the other side it will have a performance impact of downloading the whole work database before each shift. So this solution can be used if the time available to prepare the remote database for the shift is enough for complete database download.

Upload synchronization

Mobilink upload implementation is based on the log file that ASA database can create, keeping track of all the changes done to it. Actually database log is a packed SQL command file. It can be easily translated into SQL command sequence using

dbtran [ options transaction-log [ SQL-file ]

For more information see “The Log Translation utility” from “Adaptive Server Anywhere Database Administration Guide”

Obviously the log gives a full and handy information about the changes occurred in the database. Before synchronizing mobilink client (dbmlsync) scans through the database log to collect the changes.

Dealing with mobile devices, which have limited storage resources, you might be interested, how to prevent log file from growing eternally. Here ASA provides a special option

dbmlsync -x

Which automatically truncates the log file after successful synchronization. Unfortunately truncating the log it creates its backup, which will still occupy some space and should be deleted intelligently when the updates from it are synchronized to the consolidated database.

You can use dbtran [logfile] –y NULL utility to find out start and end offsets. And you can use

select first progress from sys.syssync where progress is not null order by progress asc;

to find the lowest unsynchronized offset.

An alternative approach (and simpler one as well) is to truncate the log file after the data is successfully uploaded to the consolidated database. In this case we will also have to clean synchronization offsets from the remote database. The easiest way to do this is to delete synchronization subscription:

DROP SYNCHRONIZATION SUBSCRIPTION TO “MY_PUBLICATION” FOR “USER”;

Don’t forget to recreate it for the following synchronizations.

Note:

Information about mobilink users and their subscriptions is kept in sys.syssync table in remote database.

You will also have to delete synchronization offset from the consolidated database. The best place to do that is end_synchronization event handler. You can call the following procedure to fulfill the task:

CREATE OR REPLACE PROCEDURE End_Sync(ML_USER IN VARCHAR2,

SYNC_OK IN NUMBER) AS

BEGIN

DELETE FROM ML_SUBSCRIPTION WHERE USER_ID = (SELECT USER_ID FROM ML_USER WHERE NAME = ML_USER);

COMMIT;

END;


Truncating the log makes Mobilink see the database as unchanged. Even if the data is different from the consolidated database you can’t synchronize these changes using Mobilink technology. So it is very important to check that the synchronization was successful before truncating the log.


rc = pb_run_dbmlsync(exe_name, is_publication_name, is_mluser, is_mlpassword, connect_string, other_arguments, is_sync_class_name )

rc value should be 0 for successful synchronization.
//check if it finished without mistakes
if (IsNull(rc) = true) then

return -100
elseif ( rc = 0) then
// sometimes Status is wrong but sync exits without error
if (Pos(w_xstratamobile_sync.mle_status.Text, “Status = 6”) <> 0 &
OR Pos(w_xstratamobile_sync.mle_status.Text, “Status = 1”)<> 0) then
rc = 2
return rc
end if
else
return rc
end if

Authenticating Users

In production environment it is very important to ensure that the person using the mobile application is authenticated. This will prevent data corruption by unauthorized users.

In fact 2 different authentication systems are needed:

  • mobilink access system
  • system access system.

Actually they can be combined, so that the user having the right to synchronize will also have the right to use the system.

The authentication system should be flexible and easily manageable. Obviously the best solution is to keep and manage this information in consolidated database. It is fairly easy to accomplish for the system access system. The authentication information can be kept in consolidated database and downloaded to the remote database during synchronization.

It is more difficult to manage mobilink access information from the central database.

One solution is to introduce unauthorized synchronization, which will download mobilink authentication information to the remote database.

Another way to do that is to set mobilink users manually on each device (using registry storage or device id).

However for both these solutions we should remember that mobilink user becomes a part of the remote database, so if we do not want to generate all the possible mobilink users in the distribution database and replace the database each time the change to the mobilink authentication information occurs, we will need to generate them dynamically.

This can be done just before the download synchronization (user name and password should be supplied by user on authentication stage or should be taken from the device information – registry or device id):

 

long l_ret = 0

string s_Aux

s_Aux = ‘CREATE SYNCHRONIZATION USER “‘ +s_User +'”‘

EXECUTE IMMEDIATE :s_Aux using SQLCA;

if (SQLCA.sqlcode <> 0) then

MessageBox(“Error”,”Unable to create synchroniation user”)

ROLLBACK using SQLCA;

return -1

end if

s_Aux = ‘CREATE SYNCHRONIZATION SUBSCRIPTION TO “MY_PUBLICATION” FOR “‘+ s_User +'” TYPE TCPIP’

EXECUTE IMMEDIATE :s_Aux using SQLCA;

if (SQLCA.sqlcode <> 0) then

MessageBox(“Error”,”Unable to create subscription”)

ROLLBACK using SQLCA;

return -1

end if

COMMIT USING SQLCA;

return l_Ret

In order to keep database clean the user should be deleted after upload synchronization:

s_Aux = ‘DROP SYNCHRONIZATION SUBSCRIPTION TO “MY_PUBLICATION” FOR “‘ + s_User + ‘”‘

EXECUTE IMMEDIATE :s_Aux using SQLCA;

if (SQLCA.sqlcode <> 0) then

MessageBox(“Error”,SQLCA.sqlerrtext)

return -1

end if

s_Aux = ‘DROP SYNCHRONIZATION USER “‘ + s_User + ‘”‘

EXECUTE IMMEDIATE :s_Aux using SQLCA;

if (SQLCA.sqlcode <> 0) then

MessageBox(“Error”,SQLCA.sqlerrtext)

return -1

end if

COMMIT USING SQLCA;

Note: the examples above also take care of creating and deleting of synchronization subscription, which is necessary for maintaining database log (see previous chapter).

Handling Errors

Mobilink provides 2 different events for error-handling:

  • handle_error (is triggered by all SQL errors)
  • handle_odbc_error (is triggered by ODBC driver errors)

Both events provide action_code parameter which can be used to control the behavior after the error occurred:

  • 1000 Skip the current row and continue processing.
  • 3000 Rollback the current transaction and cancel the current synchronization. This is the default action code, and is used when no handle error script is defined or this script causes an error.
  • 4000 Rollback the current transaction, cancel the synchronization, and shut down the MobiLink synchronization server.

If your event handler does not change the action_code it will have the default value – 3000

Typical usage of event handling scripts is logging errors:

 

CREATE OR REPLACE PROCEDURE Ml_Handle_Error( n_IN_ACTION_CDE IN OUT NUMERIC,

n_ERR_CODE NUMERIC,

S_ERR_MESSAGE VARCHAR2,

S_ML_USERNAME VARCHAR2,

S_ML_TABLE VARCHAR2

) AS

BEGIN

DECLARE

DT_GENDATE DATE;

N_OUT_ACTION_CDE NUMERIC;

BEGIN

SELECT SYSDATE INTO DT_GENDATE FROM DUAL;

N_OUT_ACTION_CDE := N_IN_ACTION_CDE;

INSERT INTO ML_ERRORS ( IN_ACTION_CDE, OUT_ACTION_CDE, ERR_CODE, ERR_TYPE, ERR_MESSAGE,

ML_USERNAME, ML_TABLE, GEN_DATE)

VALUES

(N_IN_ACTION_CDE, N_OUT_ACTION_CDE, N_ERR_CODE, ‘SQL’, S_ERR_MESSAGE,

S_ML_USERNAME, S_ML_TABLE, DT_GENDATE);

COMMIT;

END;

END;

And for ODBC errors:

CREATE OR REPLACE PROCEDURE Ml_Handle_Odbc_Error( n_IN_ACTION_CDE IN OUT NUMERIC,

s_ODBC_STATE VARCHAR2,

S_ERR_MESSAGE VARCHAR2,

S_ML_USERNAME VARCHAR2,

S_ML_TABLE VARCHAR2

) AS

BEGIN

DECLARE

DT_GENDATE DATE;

N_OUT_ACTION_CDE NUMERIC;

BEGIN

SELECT SYSDATE INTO DT_GENDATE FROM DUAL;

N_OUT_ACTION_CDE := N_IN_ACTION_CDE;

INSERT INTO ML_ERRORS ( IN_ACTION_CDE, OUT_ACTION_CDE, ERR_TYPE, ERR_MESSAGE,

ML_USERNAME, ML_TABLE, GEN_DATE, ODBC_STATE)

VALUES

(N_IN_ACTION_CDE, N_OUT_ACTION_CDE, ‘ODBC’, S_ERR_MESSAGE,

S_ML_USERNAME, S_ML_TABLE, DT_GENDATE, s_ODBC_STATE);

COMMIT;

END;

END;

You can also use the event handler to skip some errors silently – for example you can skip SQL errors for duplicates on primary key, when users create the same value independently, but there is no risk of loosing data selecting any of the created values arbitrarily.

Note: the syntax of the error handler script call in ML_SCRIPT :

{ call ml_handle_error(?,?,?,?,?)}

The curly brackets are mandatory in this call.

handle_error and handle_odbc_error can catch only part of mobilink errors. For example they won’t catch errors caused by connection problems.

For general synchronization errors you can use the error status, which is sent to end_synchronization event:

End_synchronization(ml_username, sync_ok)

Where sync_ok is 1 for successful synchronization and 0 otherwise.

In order to track failed syncs from the central database we can create a special table. For example:

CREATE TABLE PDA_SYNC_HIST

(

PDA_SYNC_DATE DATE NOT NULL,

USER_ID VARCHAR2(20 BYTE) NOT NULL,

PDA_SYNC_COMMENT VARCHAR2(100 BYTE),

PDA_SYNC_STATUS VARCHAR2(20 BYTE),

PDA_SYNC_COMPL DATE

)

Begin and end synchronization events can be used to populate this table:

CREATE OR REPLACE PROCEDURE Begin_Sync(ML_USER IN VARCHAR2) AS

BEGIN

DECLARE

dt DATE;

BEGIN

SELECT SYSDATE INTO DT FROM DUAL;

INSERT INTO PDA_SYNC_HIST (PDA_SYNC_DATE, USER_ID, PDA_SYNC_COMMENT, PDA_SYNC_STATUS)

VALUES

(DT,ML_USER,,‘BEGIN SYNC’);

END;

END;

/

CREATE OR REPLACE PROCEDURE End_Sync(ml_user IN VARCHAR2,

SYNC_OK IN NUMBER) AS

BEGIN

DECLARE

DT DATE;

BEGIN

SELECT SYSDATE INTO DT FROM DUAL;

— UPDATE PDA SYNC TABLE

IF SYNC_OK = 1 THEN

UPDATE PDA_SYNC_HIST SET PDA_SYNC_STATUS = ‘END SYNC’, PDA_SYNC_COMPL=DT

WHERE USER_ID, = ML_USER AND PDA_SYNC_STATUS = ‘BEGIN SYNC’;

ELSE

UPDATE PDA_SYNC_HIST SET PDA_SYNC_COMMENT = ‘SYNC ERRORS’

WHERE USER_ID = ML_USER AND PDA_SYNC_STATUS = ‘BEGIN SYNC’;

END IF;

COMMIT;

END;

END;

/

These procedures do not ensure exact synchronization tracking: End_Sync will close all the open synchronizations. But actually that is enough as the integrity of synchronizations is ensured by Mobilink itself.

Leave a comment