Ultralite debug/trace log

[Former Member]

Hi all,

I'm using UDB on Windows Mobile as remote DB in a MobiLink synchronisation. I'm using a dbmlsync call to start the synchronisation. For a performance analysis I'd like to trace all database operations during a synchronisation (not the MobiLink log but all update/delete/insert/select operations). Is that possible?

TIA

Chris Werner

p.s. Using ASA 16

[Breck_Carter]

Are you asking about all the database operations being performed on the UDB, or all the database operations being performed on the consolidated database? If the latter, what DBMS are you using?

[Former Member]

About all the database operations performed on the UDB.

[philippefbertrand · Accepted solution]

Dbmlsync is for synchronizing SQL Anywhere databases and is not used with UltraLite. If you are using dbmlsync, experts like Breck and others will be attracted to your question if you edited the title to "dbmlsync debug/trace log".

If you are using UltraLite, you can monitor for events (see the DCX topic UltraLite - Database Management and Reference » UltraLite database tasks and features - http://dcx.sybase.com/index.html#sa160/en/uladmin/fo-db-objects-s-5626130.html). You can also turn on logging at the MobiLink server to document all the rows received and downloaded (assuming you are not using the direct row handling API).

Perhaps you meant to say that you are using ActiveSync to start the synchronization? (http://dcx.sybase.com/index.html#sa160/en/uladmin/fo-ul-hsas.html)

[Former Member · Accepted solution]

Thanks for your answer!

Yes, that's right I should have copied it from the script instead of guessing. I meant to say I'm using ulsync to start the synchronization (in this special case, otherwise synchronization is started via the ul engine from an application).

What I'm after is some kind of logfile showing the database actions the ul engine performs while synchronizing. I'm much more familiar with SQL Anywhere databases (hence my error in the first question). In SQL Anywhere I'd use the Log File option of the ODBC datasource to achieve what I'm after.

I'm aware of the ML server logging possibilities but that's not what I want because it shows actions on the MobiLink level. I'm not sure if monitoring for events can help me because there is no monitoring instance, just a cmd script calling ulsync.

TIA,

Chris Werner

[philippefbertrand · Accepted solution]

Where are you running ulsync?

[Former Member · Accepted solution]

For this is a test case I'm running ulsync on a Windows machine against a UDB. I hope to get some insights what's going on in the UDB engine on deletes & inserts versus updates (all issued by download_cursor and download_delete_cursor scripts on the consolidated machine). This is because there are some rumors about the ml download into a UDB saying it'd be better to delete all changed rows (using download_delete_cursor for all changed rows but not only deleted ones) and then inserting them again (using download_cursor as usually)as to updating those rows (which means having only deleted rows in the download_delete_cursor as usually). The real application will run on Windows CE but for the moment I'm interested in testing the synchronization on Windows.

[philippefbertrand · Accepted solution]

If you really wanted to optimize the performance, you would monitor the page reads and writes, and then, you would only get an answer suitable for a particular sync (set of downloaded rows) against a particular database.

It all comes down to how many update rows are being downloaded versus how many rows there are currently in the table (which has many indexes). It is feasible that a large number of updates would cause index and data pages to come in and out of the cache sufficiently that it would be simpler to truncate the table and download all the roads. You have to consider the cost of all that data on the wire. (also Ultralite has a dynamic cache so it will work hard to be good)

Conclusion: unless you are changing a most of rows in a table every sync, let MobiLink do deltas.

p.s. Order downloads by primary key

[Former Member · Accepted solution]

Hi Phillipe,

thanks for your answer. Your conclusion is my general opinion too, but in the past I was concerned with SQL Anywhere remote databases only, Ultralite is completely new to me. I found this technique to delete all changed rows using download_delete_cursor prior to inserting them on download_cursor in a project which was already started when I came in. When I asked about it the developer argued for it with udb client performance, fragmentation and main memory usage, going into details like read/write versus delete/write pages and the lack of an INSERT ON EXISTING DELETE statement in Ultralite. At the end something like "We did it this way all the time so if you dare to change it, just do it" came out and I gave in. For the moment 😉

Thanks again, Chris

[Breck_Carter · Accepted solution]

> lack of an INSERT ON EXISTING DELETE statement in Ultralite

I know, it's awful, isn't it? It's missing from SQL Anywhere too, and hardly a day goes by when I don't curse and knash my teeth because of that 🙂

[sorry, couldn't resist, no insult intended]

[Former Member · Accepted solution]

Oops, INSERT ON EXISTING DELETE sound like a Freudian slip 😉

[philippefbertrand]

Have you tried ulsync --log option?

[Former Member]

Not yet but I'm afraid this option will result in a logfile similar to the one generated by the -v option of dbmlsync which is not what I'm after. Anyway I'll give it a try when I'm back to office.

[Former Member]

Meanwhile I tried the --log=<logfilename> option and found the -v option for ulsync. Those options generate a rather poor output compared to the logging possibilities of a full sa client. Besides my original question: Are there any options to improve the logging of the synchronization of UDB remote databases?