STOP SYNCHRONIZATION DELETE not working

[VolkerBarth]

I am developing an iPad application with Client-Consolidated DB synchronization model. After synchronization I am performing a cleanup operation which deletes irrelevant records from client db. My cleanup procedure has 40 delete statements. The workflow for this cleanup is:
1. STOP SYNCHRONIZATION DELETE
2. Run all the delete queries.
3. COMMIT
4. START SYNCHRONIZATION DELETE
I have searched the documentation in which COMMIT is given after START SYNC command. I think it should not be the problem here.
I have tested this logic by executing through ULPreparedStatement::ExecuteStatement() and from the inbuilt C++ API function connection->stopSynchronizationDelete(). In both cases I am getting a true return value, but from the server log I can see that it is trying to upload those deletes to server.

[VolkerBarth]

Wild guess: AFAIK, MobiLink scripts should never do a COMMIT or ROLLBACK - cf. the warning from the docs:

Caution

There should be no implicit or explicit commit or rollback in your SQL synchronization scripts or the procedures or triggers that are called from your SQL synchronization scripts. COMMIT or ROLLBACK statements within SQL scripts alter the transactional nature of the synchronization steps. If you use them, MobiLink cannot guarantee the integrity of your data in the event of a failure.

[VolkerBarth]

Well, I may be wrong with my comment above, as the samples for STOP SYNCHRONIZATION DELETE surely do use a COMMIT, as you have stated yourself. Somewhat irritating...

[Former Member]

Stop Synchronization Delete happens on the remotes, and is not usually in the context of Sync. (At least I've never used it there, not sure, might be able to use it in some sp_hook procedures.) Anyway, that's why the COMMIT/ROLLBACK restrictions don't apply here. Sync scripts are most often used on the consolidated side.

I've been able to make this work under the normal db, but can't say I've tried it using UltraLite. I do notice that the documentation examples follow the pattern: STOP SYNCHRONIZATION DELETE; run the deletes -- no commit issued START SYNCHRONIZATION DELETE; COMMIT;

My usage has had the STOP, deletes, START in a sproc with the caller of the sproc calling the commit which matches the example patterns. I wouldn't have necessarily expected an extra commit to matter, but I could envision it being important. Might be worth a try.

[VolkerBarth]

Sorry, just forget about the non-sense I've written above - Ron has made the point clear: "Stop Synchronization Delete happens on the remotes", so my hints to the sync scripts on the cons are totally off-topic. - I won't delete my wrong comments, for the single reason that I believe one can learn something from wrong conclusions, as well..

[Breck_Carter]

Another wild guess: The COMMIT turns off the STOP SYNCHRONIZATION DELETE which then causes the deletes to be processed for upload. If so, this would fall into the category of undocumented bug, er, surprise, um, feature.

Here's a clue: the comment "-- including the stopping of the deletes" in the Help at http://dcx.sybase.com/index.html#1201/en/dbreference/start-synchronization-delete-statement.html implies that commit/rollback does have a direct effect on STOP SYNCHRONIZATION DELETE, and therefore should be executed after the START.

[Former Member]

Guys, i think i need to explain my whole Sync model scenario.
1. I have Client DB which can be divided in 3 set of tables lets say : 1)SET-A 2)SET-B 3) SET-C
2. SET-A - Daily transaction for my activities. INSERT, DELETE,UPDATE of new records.
3. SET-B - Lookup tables only. data will depend on my current working region(geographical) and will be downloaded from server. No insert , update is allowed in this set.
4. SET-C - Same as point 3.
5. Now if from backend my region got changed i will get new data set but previous data will also be there in remote DB, i need to delete those records. I will be performing 40 delete statements for that. But that data is relevant for other(it is common data) Users and should not be deleted from server.
6. I have 3 Sync publications lets say :- 1) Sync1 2) Sync2 3) Sync3
7. Very first time i will run all 3 publications which gives me complete DB.
8. Subsequently i will Run Sync2 or Sync3 with Sync1 based upon a logic.
9. Let say region change happened for me, i will run Sync1 & Sync2, and i need to delete unwanted records in client DB.
10. Next time i will run Sync1 with Sync3 and don't want the deletes to reflect on server.

That's it. Now when i was trying with the approach i mentioned in question, from the Mobilink server log i was able to see that sync is trying to upload those DELETEs on it(i have tested it for 3 different mlreoteids). But with the approach i used in solution it does not happen which solved my problem.
@Tim : So for a second lets say COMMIT affect the STOP sync command behavior and we have a solution, so in your case1 if you execute only one delete statement it won't produce this scenario because STOP got altered but we are not doing any DELETE after that.
But if it does not affect the STOP command, then i have no clue what's going on there as I am an XCode developer and don't know much about DB, Sync Models processing, this is my first project involved with DB.

Thanks again to all for their valuable comments & explanation

[VolkerBarth]

IMHO, the replacement of "unfitting" regional data with "fitting one" simply looks like some kínd of "territory realignment" problem, which is a common situation for mobile databases.

Here, you would not need to delete the data explicitly on the remote but would rely on a download_cursor script to get the "fresh data" and a "download_delete_cursor" script to delete the "old data".

Cf. this doc page.

[VolkerBarth]

FWIW, to ask the ML experts:

Besides the actual question - is my idea to handle this sitation as "territory realignment" a reasonable one?

[Former Member]

@Volker: IMHO the removal of "unfitting data" in territory realignment can be handled either way (download_delete_cursor or non-sync deletes).

I think it is often solved as you suggest, since the new territory data is coming from the consolidated anyway. An advantage is that the business logic of who-get-what-data is all in the consolidated synchronization scripts. Disadvantages are that the download_delete_cursor needs to be more complicated (i.e. not just generated from a sync model) and that there would be more data on the network, since it would have to download the PKs of all the "unfitting data".

[former_SQLA_member1694884]

Are you deleting everything in the table? If so, you can do that efficiently by downloading a truncate table request. You can do that with a download_delete_cursor script that returns a single row of all NULLs. If you don't want to use a script you can use the TRUNCATE TABLE statement wherever you are currently doing your DELETEs. I don't think TRUNCATE TABLE is ever synced.

[Former Member]

@Volker: Yes it is a kind of territory management.
@Breck : Thanks for posting the code, i don't know why i am facing the issue. Anyway i got it working. Happy Ending....

No my client is not accepting any changes to apply at back-end system. I am not deleting all record from those tables, so i can't go with TRUNCATE command.

[VolkerBarth]

Glad it works! Does

"not accepting any changes to apply at back-end system"

mean you are not allowed to add/modify ML download scripts at the backend?

(I'm just asking as those scripts usually won't classify as "changes in the backend" - they simply define what is sent to the remotes...)