Store actual Mobilink synchronisation status on the SQL Anywhere remote

[Former Member]

I have a Consolidated database with one Remote database (both SQL Anywhere 16 databases and Mobilink 16). I want to collect the Remote synchronization status (connection failed, last synchronization time, last error etc) and update it each time (changes) in a table on the remote database. What approaches/architecture/services on the remote do I have to use without using the dbmlsync API? My preference is to do it completely inside the remote database in SQL, is that possible?

[VolkerBarth]

So the status information in the SYSSYNC/SYSSYNC2 system views does not fulfill your requirements?

[Former Member]

Thank you Volker for your response. Yes it almost fulfill my requirements.... It's available on the remote and contains the last_download_time and last_upload_time. But do you know if there is also a system view that holds the the connection status/error?

[VolkerBarth]

I don't know of a system view - but that's just my limited ML knowledge.

However, you could make use of the ML system hook procedures to gather such information and store it in your own remote database - they are just particular stored procedures that are called automatically in the client database on certain sync events. Hooks like sp_hook_dbmlsync_communication_error or sp_hook_dbmlsync_sql_error (or the more general sp_hook_dbmlsync_all_error) seem appropriate to track such errors, and they should work with any method to start a ML synchronization, including the default dbmlsync utility...

[Former Member]

The "ML system hook procedures" are just part of the dbmlsync interface. I was hoping to be able to do the synchronisation with just a simple time sceduling Remote task in Sybase Central. No additional external functionality or interfacing required like the dbmlsync.

FYI the Remote database is a stand-alone Server with on top an IIS server for the interfacing with our clients. A dbmlsync server needs some setup interfacing I can't offer right now. Do you have (other) suggestion(s)? Do you think I can do it without the dbmlsync server? I'm not sure anymore if I can do it without dbmlsync?

[VolkerBarth]

How do you sync currently? What is a "dbmlsync server"? - AFAIK there's only the mobilink server (mlsrv12) on the consolidated site and a sync facility (the dbmlsync utility or an API-based approach) on the client.

I have had the impression that you call the DBMLSYNC utility for that purpose. If so, all you have to do is create an according hook procedure with the pre-defined name, and it will be called automatically by the utility. There's no additional "external functionality or interfacing required".

Do I have misunderstood your comment?

[jeff_albion]

FYI: dbmlsync now acts as a 'server' in some instances (when launched by the dbmlsync API, or via dbmlsync -sm "server mode"). So a 'dbmlsync server' is perfectly acceptable terminology - it accepts synchronization requests from C/C++ and .NET clients (and also the 'SYNCHRONIZE' SQL statement).

[VolkerBarth]

Thanks for the clarification, my ML experience seems somewhat outdated...

[jeff_albion]

No additional external functionality or interfacing required like the dbmlsync.

Let's start here: dbmlsync is required for SQL Anywhere to synchronize to MobiLink. It is the utility that scans for changes in SQL Anywhere and makes the outgoing network connection to the MobiLink server. The SQL Anywhere database cannot synchronize to MobiLink on its own.

The "ML system hook procedures" are just part of the dbmlsync interface

No, they are not part of the dbmlsync API. They are just pre-named SQL STORED PROCEDURES and are executed automatically when dbmlsync connects to the database to synchronize. Please see the documentation for their description. They are created as you would any other SQL stored procedure:

CREATE PROCEDURE sp_hook_dbmlsync_end()
BEGIN
   INSERT INTO status_table (status)
      SELECT value from #hook_dict where name = 'upload status';
END;

A dbmlsync server needs some setup interfacing I can't offer right now.

I'm not sure I understand this comment: dbmlsync is a utility that connects to the database using a standard client connection string (see "dbmlsync -c"). It should be as easy to connect this utility as any other database client to the database.

I was hoping to be able to do the synchronisation with just a simple time sceduling Remote task in Sybase Central. No additional external functionality or interfacing required like the dbmlsync.

How about we offer this the other way: dbmlsync has its own scheduling options using the extended parameter 'Schedule (SCH)'. So you can start dbmlsync as a system service, using the 'dbsvc' utility when the system starts up and just have it perform its own timed synchronizations inside its own service without any extra coding required inside the database.

[Former Member]

Thank you both Volker and Jeff for your reaction on my question. I'm a generic database developer/administrator and have experience with different databases vendors included Sybase but less with Mobilink (second project). Mobilink has so much to offer.. that makes it hard to understand the complete Mobilink context. So I have a question for Jeff.. Jeff can I run the dbmlsync -sm server in combination with a Remote scheduling synchronization task (in Sybase Central)? Is that what you suggesting in your previous answer?

Sorry I had not seen your reaction below yet..

[Former Member]

Thank you for this complete story about dbmlsync. I understand from your story that this dbmlsync solution is a better solution than the one with the "Remote task" in "Sybase Central". Thats clear for me, it has all the functionality I want.

But my remote database is only connected by an IIS server. There is no (other) application connected directly to the Remote database. That's my issue when I said "needs some setup interfacing". I don't have an application to connect to the dbmlsync server directly. Am I missing something Jeff?

[VolkerBarth]

You don't have to use an application to connect to the ML server - as Jeff has suggested, when using a database as a service (as in your IIS setup), it seems appropriate to have dbmlsync run as a system service, too, and to give it a fitting schedule...

[Former Member]

Nice, its clear than. Thank you Volker and thank you Jeff for your response and time to solve my issue. It was more than just a simple answer...

[jeff_albion]

All synchronization status information is generally available at run-time to the various dbmlsync event hooks (which are just pre-named stored procedures that are executed automatically as part of the dbmlsync execution), via the #hook_dict temporary table.

Specifically, you'll want to take a look at the various error-handling hooks, and sp_hook_dbmlsync_end to scan for status values of concern and use the hooks to store them in another table.