SQL Anywhere Mobile - sp_hook_dbmlsync

Author

sp_hook_dbmlsync_delay

Tony Lanzel

2005-10-27, 8:20 am

(I had entered a message about 15 minutes ago, but it still
hasn't shown up in the newsgroup yet, so I felt I had to
enter a new one).

ASA 9.0.2.3044

I'm having a problem with the sp_hook_dbmlsync_del
ay
procedure. The help docs state the following:

When successive synchronizations occur very quickly, such as
when you specify -n pub1 -n pub2, it is possible that
dbmlsync may start processing a synchronization when the
server is still processing the previous synchronization. In
this case, the second synchronization will fail with an
error indicating that concurrent synchronizations are not
allowed. If you run into this situation, you can define an
sp_hook_dbmlsync_del
ay stored procedure to create a delay
before each synchronization. Usually a few seconds to a
minute is a sufficient delay.

I'm working under this exact scenario with having two -n
publications in the same command line and running into the
mentioned problem of multiple syncs occurring. My problem
is I cannot get the sp_hook_dbmlsync_del
ay procedure to work
so it waits 10 seconds before EACH publication synchronizes.
What it wants to do is do all sleeping before the first
publication in the list.

Here's my procedure:

Create procedure sp_hook_dbmlsync_del
ay ()
begin
Update #hook_dict
set value = '10'
where name = 'delay duration';

Update #hook_dict
set value = '20'
where name = 'maximum accumulated delay';
end;

Here's some of the notes from the dbmlsync window:

I. 10/04 10:02:48.
I. 10/04 10:02:49. Synchronization delayed 10 seconds by
hook procedure sp_hook_dbmlsync_del
ay.
I. 10/04 10:02:49. Synchronization delayed 10 seconds by
hook procedure sp_hook_dbmlsync_del
ay.
I. 10/04 10:02:49. Next sync scheduled on Oct 04 10:02:59am
I. 10/04 10:02:49. Log scan starting at offset 0269813636
I. 10/04 10:02:49. Processing transaction logs from
directory "d:\db\logs"
I. 10/04 10:02:49. Processing transactions from active
transaction log
I. 10/04 10:02:49. Hovering at end of active log
I. 10/04 10:02:59. Synchronization delayed 10 seconds by
hook procedure sp_hook_dbmlsync_del
ay.
I. 10/04 10:02:59. Synchronization delayed 10 seconds by
hook procedure sp_hook_dbmlsync_del
ay.
I. 10/04 10:02:59. Next sync scheduled on Oct 04 10:03:09am
I. 10/04 10:02:59. Log scan starting at offset 0269854338
I. 10/04 10:02:59. Hovering at end of active log
I. 10/04 10:03:09. Begin synchronizing 'GENSYNC' for
MobiLink user 'MN001'

Reg Domaratzki \(iAnywhere Solutions\)

2005-10-27, 8:20 am

This is working as I would have expected. dbmlsync will call the delay
procedure before each synchronization, and each time, you've told it to wait
for 20 seconds, and to wake up every 10 seconds to see if the time is up and
we're ready to continue. I would expect to see a 20 second delay before
each synchronization. Using the same 'pub1' and 'pub2' samples from the
doc, and assuming that your dbmlsync command line always specifies -n pub1
first, the following stored procedure will wait five seconds before each
synchronization except the first one. Obviously, you'd need some more logic
in the hook if you can't guarantee the order of the publications, or if you
sometimes do -n pub1,pub2 or something similar.

create procedure sp_hook_dbmlsync_del
ay ()
begin
declare cur_pub varchar(128);

select value into cur_pub from #hook_dict where name = 'publication_0';
if( cur_pub <> 'pub1' ) then
Update #hook_dict set value = '5' where name = 'delay duration';
Update #hook_dict set value = '5' where name = 'maximum accumulated
delay';
end if;
end;

--
Reg Domaratzki, Sybase iAnywhere Solutions
Sybase Certified Professional - Sybase ASA Developer Version 8
Please reply only to the newsgroup

iAnywhere Developer Community : http://www.ianywhere.com/developer
iAnywhere Documentation : http://www.ianywhere.com/developer/product_manuals
ASA Patches and EBFs : http://downloads.sybase.com/swx/sdmain.stm
-> Choose SQL Anywhere Studio
-> Set filter to "Display ALL platforms IN ALL MONTHS"

<Tony Lanzel> wrote in message news:43429a95.4587.1681692777@sybase.com...
> (I had entered a message about 15 minutes ago, but it still
> hasn't shown up in the newsgroup yet, so I felt I had to
> enter a new one).
>
> ASA 9.0.2.3044
>
> I'm having a problem with the sp_hook_dbmlsync_del
ay
> procedure. The help docs state the following:
>
> When successive synchronizations occur very quickly, such as
> when you specify -n pub1 -n pub2, it is possible that
> dbmlsync may start processing a synchronization when the
> server is still processing the previous synchronization. In
> this case, the second synchronization will fail with an
> error indicating that concurrent synchronizations are not
> allowed. If you run into this situation, you can define an
> sp_hook_dbmlsync_del
ay stored procedure to create a delay
> before each synchronization. Usually a few seconds to a
> minute is a sufficient delay.
>
> I'm working under this exact scenario with having two -n
> publications in the same command line and running into the
> mentioned problem of multiple syncs occurring. My problem
> is I cannot get the sp_hook_dbmlsync_del
ay procedure to work
> so it waits 10 seconds before EACH publication synchronizes.
> What it wants to do is do all sleeping before the first
> publication in the list.
>
> Here's my procedure:
>
> Create procedure sp_hook_dbmlsync_del
ay ()
> begin
> Update #hook_dict
> set value = '10'
> where name = 'delay duration';
>
> Update #hook_dict
> set value = '20'
> where name = 'maximum accumulated delay';
> end;
>
> Here's some of the notes from the dbmlsync window:
>
> I. 10/04 10:02:48.
> I. 10/04 10:02:49. Synchronization delayed 10 seconds by
> hook procedure sp_hook_dbmlsync_del
ay.
> I. 10/04 10:02:49. Synchronization delayed 10 seconds by
> hook procedure sp_hook_dbmlsync_del
ay.
> I. 10/04 10:02:49. Next sync scheduled on Oct 04 10:02:59am
> I. 10/04 10:02:49. Log scan starting at offset 0269813636
> I. 10/04 10:02:49. Processing transaction logs from
> directory "d:\db\logs"
> I. 10/04 10:02:49. Processing transactions from active
> transaction log
> I. 10/04 10:02:49. Hovering at end of active log
> I. 10/04 10:02:59. Synchronization delayed 10 seconds by
> hook procedure sp_hook_dbmlsync_del
ay.
> I. 10/04 10:02:59. Synchronization delayed 10 seconds by
> hook procedure sp_hook_dbmlsync_del
ay.
> I. 10/04 10:02:59. Next sync scheduled on Oct 04 10:03:09am
> I. 10/04 10:02:59. Log scan starting at offset 0269854338
> I. 10/04 10:02:59. Hovering at end of active log
> I. 10/04 10:03:09. Begin synchronizing 'GENSYNC' for
> MobiLink user 'MN001'

Tony Lanzel

2005-10-27, 8:20 am

I'm having a hard time trying to figure this out. I did try
your procedure and it did work where it didn't sleep before
the first publication and did it for the second one. I
don't think it's fair of me to assume that -n's will always
be ran in a certain order and I plan to add more
publications to my list so I won't know what my maximum
delay will be.

When initially reading about this procedure, I was under the
impression that it would have been called immediately before
EACH publication synchronized, not called once for each
publication before any publication is synchronized.

Can you provide some example code about how to arrange it so
regardless of the publication name, I can have it sleep 10
seconds before each publication starts? It would be as
follows:

dbmlsync -n PUB2 -n PUB1 -n PUB3

DBMLSYNC starts up:
sleep 10 seconds
synchronize up/down PUB2
sleep 10 seconds
synchronize up/down PUB1
sleep 10 seconds
synchronize up/down PUB3
DBMLSYNC FINISHES

> This is working as I would have expected. dbmlsync will
> call the delay procedure before each synchronization, and
> each time, you've told it to wait for 20 seconds, and to
> wake up every 10 seconds to see if the time is up and
> we're ready to continue. I would expect to see a 20
> second delay before each synchronization. Using the same
> 'pub1' and 'pub2' samples from the doc, and assuming that
> your dbmlsync command line always specifies -n pub1 first,
> the following stored procedure will wait five seconds
> before each synchronization except the first one.
> Obviously, you'd need some more logic in the hook if you
> can't guarantee the order of the publications, or if you
> sometimes do -n pub1,pub2 or something similar.
>
> create procedure sp_hook_dbmlsync_del
ay ()
> begin
> declare cur_pub varchar(128);
>
> select value into cur_pub from #hook_dict where name =
> 'publication_0';
> if( cur_pub <> 'pub1' ) then
> Update #hook_dict set value = '5' where name = 'delay
> duration';
> Update #hook_dict set value = '5' where name =
> 'maximum accumulated delay';
> end if;
> end;
>
> --
> Reg Domaratzki, Sybase iAnywhere Solutions
> Sybase Certified Professional - Sybase ASA Developer
> Version 8 Please reply only to the newsgroup
>
> iAnywhere Developer Community :
> http://www.ianywhere.com/developer iAnywhere Documentation
> : http://www.ianywhere.com/developer/product_manuals ASA
> Patches and EBFs :
> http://downloads.sybase.com/swx/sdmain.stm
> -> Choose SQL Anywhere Studio
> -> Set filter to "Display ALL platforms IN ALL MONTHS"
>
>
> <Tony Lanzel> wrote in message
> enter a new one). >
> delay before each synchronization. Usually a few seconds
>
>

Tony Lanzel

2005-10-27, 8:20 am

I was able to figure this out for myself. Luckily, I am
keeping track of sync times in a table I call SYNC_LOG. In
my delay procedure, I query the most recent record in this
table and see if it ended within the past 10 seconds. If it
did, it sets the delay duration value to 10.
[color=darkred]
> I'm having a hard time trying to figure this out. I did
> try your procedure and it did work where it didn't sleep
> before the first publication and did it for the second
> one. I don't think it's fair of me to assume that -n's
> will always be ran in a certain order and I plan to add
> more publications to my list so I won't know what my
> maximum delay will be.
>
> When initially reading about this procedure, I was under
> the impression that it would have been called immediately
> before EACH publication synchronized, not called once for
> each publication before any publication is synchronized.
>
> Can you provide some example code about how to arrange it
> so regardless of the publication name, I can have it sleep
> 10 seconds before each publication starts? It would be as
> follows:
>
> dbmlsync -n PUB2 -n PUB1 -n PUB3
>
> DBMLSYNC starts up:
> sleep 10 seconds
> synchronize up/down PUB2
> sleep 10 seconds
> synchronize up/down PUB1
> sleep 10 seconds
> synchronize up/down PUB3
> DBMLSYNC FINISHES
>
>
> something similar. >
> platforms IN ALL MONTHS" >

Sponsored Links