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: asked 05 May '12, 08:15 Gandalf Calvin Allen |
Hi Guys, Thanks a lot to all for their valuable comments, no matter they are just guesses but they gave me idea to work with a different approach which i was considering worthless. I tried it with the sequence though i have to do some code rearrangement and modification. answered 07 May '12, 06:23 Gandalf Glad you got it working - and feel free to "accept" your own answer. For what I see, the credits w.r.t. COMMIT vs. STOP SYNC should go to Breck, not me:)
(07 May '12, 07:18)
Volker Barth
Replies hidden
1
The credit should go to Gandalf himself, who raised the issue in the original question and then discounted it: "... I have searched the documentation in which COMMIT is given after START SYNC command. I think it should not be the problem here." I agree that "it should not be the problem" but life isn't always agreeable :)
(07 May '12, 09:32)
Breck Carter
Well, in case it's "Gandalf the White", he won't need further credits:)
(07 May '12, 09:36)
Volker Barth
Did you change more than one thing? ...because my test indicates that it doesn't matter whether the COMMIT is before or after the START SYNCHRONIZATION DELETE.
(07 May '12, 15:03)
Breck Carter
Replies hidden
Hi Breck, I didn't change anything in sync model. Only the cleanup routine i rearranged. As in my solution i was performing all 40 DELETE statements required for me without committing them, then i START the sync and did the commit. this approach didn't upload any change happened due to DELETEs to consolidated database.
(08 May '12, 02:59)
Gandalf
I reran the test with 40 deletes and posted the code as a new answer.
(08 May '12, 08:52)
Breck Carter
|
Sorry Guys, but the order of 'commit' and 'start sync delete' statements doesn't make any difference. Nor does 'commit' reset or affect the stop-sync-delete status/flag. All that matters is which delete-mode is in effect when the delete statement itself is executed. (This is a little better than a wild guess, hopefully, since I've checked the code ;-) So, I'm afraid something more is going on here. What sometimes does cause confusion with stop-sync-delete is the following sequence:
What will be uploaded on the synchronize? The delete will not of course, but the insert of the row will. This is different than the following, where nothing is uploaded on the final synchronize:
The other normal cases are:
and
answered 07 May '12, 14:09 Tim McClements 1
...and we know that "reading the code" beats "testing the code" every time, right? <bg>
(07 May '12, 14:36)
Breck Carter
Replies hidden
1
...and a test using 12.0.1.3298 does agree with Tim's reading of the code: it doesn't matter where the COMMIT goes, before or after the START SYNCHRONIZATION DELETE; either way, the row deleted on the remote database after the STOP SYNCHRONIZATION DELETE is not deleted from the consolidated.
(07 May '12, 15:01)
Breck Carter
1
:-) (Truth be told I'm old enough that I did indeed monkey around a bit with a test as well before posting... But there's only so much time you know... ;-)
(07 May '12, 15:13)
Tim McClements
|
Here's the test I just ran using SQL Anywhere 12.0.1.3298. It shows that STOP SYNCHRONIZATION DELETE prevented 40 DELETE operations from being uploaded even though the COMMIT preceded the START SYNCHRONIZATION DELETE. 001_setup_cons_remo.bat "%SQLANY12%bin32dbinit.exe" cons.db 001s_script_to_setup_cons.sql
001s_script_to_setup_remo.sql
002_run_mlsrv12.bat "%SQLANY12%bin64mlsrv12.exe"^ -c "DSN=cons;UID=dba;PWD=sql"^ -o mlsrv12_log_cons.txt^ -os 10M^ -ppv 60^ -vcefhkmnopstuU^ -zu+ 003_run_dbmlsync.bat "%SQLANY12%bin32dbmlsync.exe"^ -c "ENG=remo;DBN=remo;UID=REMOTE_DBA;PWD=SQL"^ -e "adr='host=localhost';sv=v1"^ -o dbmlsync_log_remo.txt^ -os 10M^ -vnosu^ -x 004_test_STOP_SYNCHRONIZATION_DELETE.sql
answered 08 May '12, 08:51 Breck Carter |
Guys, i think i need to explain my whole Sync model scenario. 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. Thanks again to all for their valuable comments & explanation answered 08 May '12, 05:17 Gandalf 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.
(08 May '12, 05:28)
Volker Barth
Replies hidden
FWIW, to ask the ML experts: Besides the actual question - is my idea to handle this sitation as "territory realignment" a reasonable one?
(08 May '12, 12:03)
Volker Barth
1
@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".
(08 May '12, 12:48)
Graham Hurst
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.
(08 May '12, 13:49)
Bill Somers
@Volker: Yes it is a kind of territory management. 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.
(09 May '12, 06:12)
Gandalf
Replies hidden
Glad it works! Does
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...)
(09 May '12, 06:35)
Volker Barth
|
Wild guess: AFAIK, MobiLink scripts should never do a COMMIT or ROLLBACK - cf. the warning from the docs:
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...
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.
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..
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.