SQL Anywhere Mobile - Re: Upload Error in Mobilink Server -

Author

Re: Upload Error in Mobilink Server -

Greg Fenton

2005-04-19, 8:24 pm

Maggie wrote:
> Thanks for the Response.
> If We create an Another Unique Key like Autonumber, It will
> create the dublicated data in the Server.

This is why you *cannot* use Autoincrement by iteself. You could look
to using Global Autoincrement or using Composite Keys (an Autoincrement
along with a "site id" column) or one of the other techniques I posted
about.

In the ASA 9.x online docs, see:

MobiLink Administration Guide
Synchronization Techniques
- Maintaining unique primary keys

> Why the Mobilink Server is
> not updating instead it is trying to Insert the New Record.

Because *your* synchronization scripts are telling it to. The remote is
uploading the row as an INSERT (because it *is* an INSERT at the remote)
and so the UPLOAD_INSERT event fires. *Your* synchronization script
will need to detect that the row exists and do an UPDATE instead (if
this is how you really want to handle the situation...).

If your consolidated is an ASA database, you could add the ON EXISTING
UPDATE clause to your INSERT statement.

....HOWEVER, note that the primary goal of distributed database design is
to come up with a schema and use techniques such that duplicates are NOT
inserted into the system [though, as always, there are exceptions to
this rule].

>
> Each client will be a different mobilink User ( Unique ID )
>

This is where you could use the Unique ID as the "site id" column of a
two-part composite primary key [ PRIMARY KEY (id, site_id) ].

>
> Kindly Help us to solve this problems. as a temprory we have
> disabled the uploding part in our script.
>

Note that this is a fundamental synchronization design issue. I suggest
you read over the entire Synchronization Techniques section of the docs
to make sure there aren't other areas your solution might need to
(re)address.

greg.fenton
--
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/

Maggie

2005-04-20, 9:24 am

Hi,
Thanks for the Response.
When i tried this with the Trigger for that Table Before
Insert Event also it was not worked. What about that Sp i
sent in the Previous Post. Why it is not Working. I Have
tried with Merge Statement in the Oracle, It is throwing an
Error.
If no other Way we have to go for the additional Id (
Primary Key for that Table).
Pls Tell us the Solution.
With Regds,
Maggie.
> Maggie wrote:
>
> This is why you *cannot* use Autoincrement by iteself.
> You could look to using Global Autoincrement or using
> Composite Keys (an Autoincrement along with a "site id"
> column) or one of the other techniques I posted about.
>
> In the ASA 9.x online docs, see:
>
> MobiLink Administration Guide
> Synchronization Techniques
> - Maintaining unique primary keys
>
>
> Record.
>
> Because *your* synchronization scripts are telling it to.
> The remote is uploading the row as an INSERT (because it
> *is* an INSERT at the remote) and so the UPLOAD_INSERT
> event fires. *Your* synchronization script will need to
> detect that the row exists and do an UPDATE instead (if
> this is how you really want to handle the situation...).
>
> If your consolidated is an ASA database, you could add the
> ON EXISTING UPDATE clause to your INSERT statement.
>
>
> ...HOWEVER, note that the primary goal of distributed
> database design is to come up with a schema and use
> techniques such that duplicates are NOT inserted into the
> system [though, as always, there are exceptions to this
> rule].
>
> ID ) >
>
> This is where you could use the Unique ID as the "site id"
> column of a two-part composite primary key [ PRIMARY KEY
> (id, site_id) ].
>
>
>
> Note that this is a fundamental synchronization design
> issue. I suggest you read over the entire
> Synchronization Techniques section of the docs to make
> sure there aren't other areas your solution might need to
> (re)address.
>
> greg.fenton
> --
> Greg Fenton
> Consultant, Solution Services, iAnywhere Solutions
> --------
> Visit the iAnywhere Solutions Developer Community
> Whitepapers, TechDocs, Downloads
> http://www.ianywhere.com/developer/

Greg Fenton

2005-04-20, 11:24 am

Maggie wrote:
> When i tried this with the Trigger for that Table Before
> Insert Event also it was not worked.

What did you try?

> What about that Sp i
> sent in the Previous Post. Why it is not Working.

The error appears to be a problem with your PL/SQL syntax. Have you
tried calling that stored proc by hand in sql*plus or whatever?

> If no other Way we have to go for the additional Id (
> Primary Key for that Table).

Though a composite key is a valid solution, why are you not considering
Global Autoincrement? It is the recommended solution for the majority
of situations.

Also, I notice in your original post that you have set the Script
Version to 'default'. We highly recommend that you set that to your own
script version (e.g. '1.0' or 'MyApp_1.0' or anything) and use a new
Script Version for each new release of your remote application that
needs new synchronization logic or that modifies the schema of the
tables in the publication.

greg.fenton
--
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/

Maggie

2005-04-21, 11:24 am

Thanks for your Reply Greg,

We are currently using Oracle9i as our Consolidated database
and ASA for our Remote database.

In your reply dated April.19th , there is a mention on the
"INSERT ON EXISTS UPDATE" clause in the ASA INSERT
statements.

Could you pls help us with the equivalent code in ORACLE?

As an alternative we tried with an "IF exists then update
else insert" statements in a PLSQL procedure. The Procedure
worked fine when tested stand alone . Whereas it fires the
same Primary Key error when called through the Mobilink
Script.

Similarly , we tried with a BEFORE INSERT TRIGGER on that
specific Table of Oracle . Even this did not succeed. It
fired the same Primary key error.

What else could be a solution for Oracle database?
Why is the trigger failing ?
At what stage is this Primary Key error thrown ?
Is it By passing the Trigger??

Expecting your reply .

Thanks
Maggie

> Maggie wrote:
>
> What did you try?
>
>
> The error appears to be a problem with your PL/SQL syntax.
> Have you tried calling that stored proc by hand in
> sql*plus or whatever?
>
>
> Though a composite key is a valid solution, why are you
> not considering Global Autoincrement? It is the
> recommended solution for the majority of situations.
>
> Also, I notice in your original post that you have set the
> Script Version to 'default'. We highly recommend that
> you set that to your own script version (e.g. '1.0' or
> 'MyApp_1.0' or anything) and use a new Script Version for
> each new release of your remote application that needs
> new synchronization logic or that modifies the schema of
> the tables in the publication.
>
> greg.fenton
> --
> Greg Fenton
> Consultant, Solution Services, iAnywhere Solutions
> --------
> Visit the iAnywhere Solutions Developer Community
> Whitepapers, TechDocs, Downloads
> http://www.ianywhere.com/developer/

Greg Fenton

2005-04-21, 1:25 pm

Maggie wrote:
>
> Could you pls help us with the equivalent code in ORACLE?
>

I'm no PL/SQL person. I did some searches in Google for "oracle upsert
example" (an "insert or update" is often referred to as an "upsert") and
found a few things.

The first uses an IF statement:

http://www.oracledba.co.uk/tips/plsql_upsert.htm

The second seems to be an Oracle 9i PL/SQL statement called "merge":

http://download-west.oracle.com/doc....htm#SQLRF01606

Hope this helps,
greg.fenton
--
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/

Reg Domaratzki $iAnywhere Solutions$

2005-04-21, 1:25 pm

Here's a sample that uses the Oracle MERGE command. I pulled this from
something I cooked up a while ago trying to do performance differences
between three different ways of handling primary key violations on an Oracle
consolidated database.

1) Let the error occur, and return 1000 from handle_error script on primary
keys (table t1).
2) In the upload_insert event for the table, use the MERGE command to do the
equivalent of an INSERT ON EXISTING UPDATE command in ASA. This will
execute a merge command for each row uploaded (table t2).
3) In the upload_insert event for the table, insert the data into a holding
table and then execute a single merge command in the end_upload table event
to place the results in the real table (table t3).

My test inserted 5,000 identical rows in two remotes so the each row would
cause a PKEY violation on the consolidated when I synched my second remote,
but the first remote would synch cleanly.

I've attached the code I used to define the tables and scripts on the
consolidated database (oracle.sql) as well as the code to define the remote
databases (asa.sql). The file mlrep.bat was used to run the test, and you
can probably modify it a little and change the DSN names, userids, passwords
and server names if you're really interested, but you're likely just
interested in the scripts defined for upload_insert in the oracle.sql file.

For those that are interested, option #3 is fastest, but I found that the
MERGE command introduced a lot of blocking and deadlocking on the server
when there were multiple users synchronizing data at the same time, so it
might not be suitable in your environment. As Greg has pointed out numerous
times, the best way to solve this problem is to ensure that primary keys are
unique across your remote databases. You might also want to consider forced
conflicts on the consolidated database, and a good sample can be found in
the following on-line tech-doc :
http://www.ianywhere.com/whitepaper...idated_db.html. The customer
that I created this sample with eventually ended up using forced conflicts
instead of the MERGE command, because of concurrency issues with the Oracle
MERGE command.

--
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 "Platform Preview" and "Time Frame" to ALL

"Greg Fenton" <greg. fenton_NOSPAM_@ianyw
here.com> wrote in message
news:4267da11$1@foru
ms-2-dub...
> Maggie wrote:
>
> I'm no PL/SQL person. I did some searches in Google for "oracle upsert
> example" (an "insert or update" is often referred to as an "upsert") and
> found a few things.
>
> The first uses an IF statement:
>
> http://www.oracledba.co.uk/tips/plsql_upsert.htm
>
> The second seems to be an Oracle 9i PL/SQL statement called "merge":
>
>
>
http://download-west.oracle.com/doc....htm#SQLRF01606
>
>
> Hope this helps,
> greg.fenton
> --
> Greg Fenton
> Consultant, Solution Services, iAnywhere Solutions
> --------
> Visit the iAnywhere Solutions Developer Community
> Whitepapers, TechDocs, Downloads
> http://www.ianywhere.com/developer/

begin 666 oracle.sql
M1%)/4" !404),12!T,0T*+PT*#0
I#4D5!5$4@5$%"3$4@=#$@* T*("!P:V5Y
M(&EN=&5G97(@;F]T(&YU;&P@<')I;6%R>2!K97DL#0H@(&-O;#$@=F%R8VAA
M<B@S,"D-"BD-"B\-"@T*1%)/4" !404),12!T,@T*+PT*#0
I#4D5!5$4@5$%"
M3$4@=#(@* T*("!P:V5Y(&EN=&5G97(@;F]T(&YU;&P@<')I;6%R>2!K97DL
M#0H@(&-O;#(@=F%R8VAA<B@S,"D-"BD-"B\-"@T*1%)/4"!404),12!T,PT*
M+PT*#0I#4D5!5$4@5$%
"3$4@=#,@* T*("!P:V5Y(&EN=&5G97(@;F]T(&YU
M;&P@<')I;6%R>2!K97DL#0H@(&-O;#,@=F%R8VAA<B@S,"D-"BD-"B\-"@T*
M1%)/4" !404),12!T,U]T96UP#0
HO#0H-"D- 214%412!404),12!T,U]
T96UP
M("@-"B @<&ME>2!I;G1E9V5R(&YO="!N=6QL+ T*("!C;VPS('9A<F-H87(H
M,S I+ T*("!M;&ED('9A<F- H87(H,3(X*2!N;W0@;G5
L;"P-"B @<')I;6%R
M> 2!K97DH('!K97DL;6QI9
" I#0HI#0HO#0H-" @T*0U)%051%($]2(%)%4
$Q!
M0T4@4%)/0T5$55)%(%5,2&%N9&QE17)R;W(H#0H@("!A8W1I;VX@(" @(" @
M(" @(" @($]55" @(" @:6YT96=E<BP-"B @(&5R<F]R7V-O9&4@(" @(" @
M(" @24X@(" @("!I;G1E9V5R+ T*(" @97)R;W)?;65S<V%G92 @(" @("!)
M3B @(" @('9A<F-H87(R+ T*(" @=7-E<E]N86UE(" @(" @(" @("!)3B @
M(" @('9A<F-H87(R+ T*(" @=&%B;&5?;F%M92 @(" @(" @("!)3B @(" @
M('9A<F-H87(R("D-"D%3#0I"14=)3@T*(" @+2T@,# P,#$@:7,@3U)!+3 P
M,# Q.B!U;FEQ=64@8V]N<W1R86EN=" !T86)L92YC;VQU;6X@=F
EO;&%T960-
M"B @("TM(# R,CDQ(& ES($]202TP,C(Y,3H@:6
YT96=R:71Y(&-O;G-T<F%I
M;G0@<W1R+FYA;64@=FEO;& %T960@+2!P87)E;G0@:V
5Y(&YO="!F;W5N9 T*
M(" @:68@97)R;W)?8V]D92 ](#$@;W(@97)R;W)?8V]D92 ](#(R.3$@=&AE
M;@T*(" @(" @("TM(&EG;F]R92!T:&4@97)R;W(@86YD(&ME97 @9V]I;F<-
M"B @(" @("!A8W1I;VX@.CT@,3 P,#L-"B @(&5L<V4-"B @(" @(" M+2!A
M8F]R="!T:&4@<WEN8VAR;VYI>F%T:6]N#0H@(" @(" @86-T:6]N(#H](#,P
M,# [#0H@(" !E;F0@:68[#0I%3D0[#0
HO#0H-"D-214%412!/4B!215!,04-%
M(%!!0TM!1T4@4WEN8U5
S97(@05,-"B @($953D-424].($=E=$E$(%)%5%52
M3B!605)#2$%2,CL-"B @(%!204=-02!215-44DE#5%]2149%4D5.0T53("@-
M"B @(" @($=E=$E$+"!73D13+"!23D13+"!73E!3("D[#0H@("!04D]#1415
M4D4@4V5T240H('-Y;F-U<V5R7VED(%9!4D-(05(R("D[#0H@("!04D%'34$@
M4D535%))0U1?4D5&15)%3D-%4R H#0H@(" @("!3971)1"P@5TY$4RP@4DY$
M4R I.PT*14Y$(%-Y;F-5<V5R. PT*+PT*#0I#4D5!5$4@3
U(@4D503$%#12!0
M04-+04=%($)/1%D@4WEN8U5S97(@05,-"B @('-U7V5M<%]I9" @(" @(" @
M(%9!4D-(05(H,3(X*3L-"@T*(" @1E5. 0U1)3TX@1V5T240@4D54
55).(%9!
M4D-(05(R($E3#0H@("!"14=)3@T*(" @(" @4D5455).('-U7V5M<%]I9#L-
M"B @($5.1"!'971)1#L-"B @(%!23T-%1%5212!3971)1"@@<WEN8W5S97)?
M:60@5D%20TA!4C(@*2!
)4PT*(" @0D5'24X-"B @(" @('-U7V5M<%]I9" Z
M/2!S>6YC=7-E<E]I9#L-"B @($5.1"!3971)1#L-"D5.1"!3>6YC57-E<CL-
M"B\-"@T*#0ID96QE=& 4@9G)O;2!M;%]S=6)S8W
)I<'1I;VX-"B\-"F1E;&5T
M92!F<F]M(&UL7W5S97(-"B\-"@T*8V%L;"!M;%]A9&1?=&%B;&5?<V-R:7!T
M*" G=C$G+" G=#$G+" G9&]W;FQO861?8W5R<V]R)RPG<V5L96-T('!K97DL
M8V]L,2!F<F]M('0Q)RD-"B\-"F-A;&P@;6Q?861D7W1A8FQE7W-C<FEP="@@
M)W8Q)RP@)W0Q)RP@)W5
P;&]A9%]I;G-E<G0G+" G:6YS97)T(&EN=&\@=#$@
M5D%,5453*" _+" _("DG*0T*+PT*8V%L;"!M;%]A9&1?=&%B;&5?<V-R:7!T
M*" G=C$G+" G=#$G+" G=7!L;V%D7W5P9&%T92<L("=U<&1A=&4@=#$@<V5T
M(&-O;#$@/2 _('=H97)E('!K97D@/2 _)RD-"B\-"F-A;&P@;6Q?861D7W1A
M8FQE7W-C<FEP=" @@)W8Q)RP@)W0Q)RP@)W
5P;&]A9%]D96QE=&4G+" G9&5L
M971E(&9R;VT@=#$@=VAE<F4@<&ME>2 ](#\G*0T*+PT*8V%L;"!M;%]A9&1?
M=&%B;&5?<V-R:7!T*" G=C$G+" G=#(G+" G9&]W;FQO861?8W5R<V]R)RPG
M<V5L96-T('!K97DL8V]L,B!F<F]M('0R)RD-"B\-"F-A;&P@;6Q?861D7W1A
M8FQE7W-C<FEP=" @@)W8Q)RP@)W0R)RP@)W
5P;&]A9%]I;G-E<G0G+" G;65R
M9V4@:6YT;R!T,B!4('5
S:6YG("AS96QE8W0@/R!A<R!C,2P@/R!A<R!C,B!F
M<F]M(&1U86PI(%,@;VX@*%0N<&ME>2 ](%,N8S$I('=H96X@;6%
T8VAE9"!T
M:&5N('5P9&%T92!S970@5" YC;VPR(#T@4RYC,B!72$
5.(&YO="!M871C:&5D
M('1H96X@24Y315)4("@@5"YP:V5Y+"!4+F-O;#(@*2!604Q515,@*"!3+F,Q
M+"!3+F,R("DG("D[#0HO#0IC86QL(& UL7V%D9%]T86)L95]S8W
)I<'0H("=V
M,2<L("=T,B<L("=U<&QO861?=7!D871E)RP@)W5P9&%T92!T,B!S970@8V]L
M,B ](#\@=VAE<F4@<&ME>2 ](#\G*0T*+PT*8V%L;"!M;%]A9&1?=&%B;&5?
M<V-R:7!T*" G=C$G+" G=#(G+" G=7!L;V%D7V1E;&5T92<L("=D96QE=&4@
M9G)O;2!T,B!W:&5R92!P:V5Y(#T@/R<I#0HO#0IC86QL(&UL7V%D9%]T86)L
M95]S8W)I<'0H("=V,2<L("=T,R<L("=D;W=N;&]A9%]C=7)S;W(G+"=S96QE
M8W0@<&ME>2QC;VPS(& 9R;VT@=#,G*0T*+PT*8V
%L;"!M;%]A9&1?=&%B;&5?
M<V-R:7!T*" G=C$G+" G=#,G+" G=7!L;V%D7VEN<V5R="<L("=I;G-E<G0@
M:6YT;R!T,U]T96UP(%9
!3%5%4R@@/RP@/ RP@4WEN8U5S97(N1V5T2
40H*2 I
M)RD-"B\-"F-A;&P@;6Q?861D7W1A8FQE7W-C<FEP="@@)W8Q)RP@)W0S)RP@
M)W5P;&]A9%]U<&1A=&4G+" G=7!D871E('0S('-E="!C;VPS(#T@/R!W:&5R
M92!P:V5Y(#T@/R<I#0HO#0IC86QL(& UL7V%D9%]T86)L95]S8W
)I<'0H("=V
M,2<L("=T,R<L("=U<&QO861?9&5L971E)RP@)V1E;&5T92!F<F]M('0S('=H
M97)E('!K97D@/2 _)RD-"B\-"F-A;&P@;6Q?861D7W1A8FQE7W-C<FEP="@@
M)W8Q)RP@)W0S)RP@)V5
N9%]U<&QO860G+" G;65R9V4@:6YT;R!T,R!
4('5S
M:6YG("AS96QE8W0@<&ME>2P@8V]L,R!F<F]M('0S7W1E;7 @=VAE<F4@;6QI
M9" ](%-Y;F-5<V5R+D=E=$E$*"D@*2!3(&]N("A4+G!K97D@/2!3+G!K97DI
M('=H96X@;6%T8VAE9"!T:&5N('5P9&%T92!S970@5"YC;VPS(#T@4RYC;VPS
M(%=(14X@;F]T(&UA=&-H960@=&AE;B!)3E-%4E0@*"!4+G!K97DL(%0N8V]L
M,R I(%9!3%5%4R H(%,N<&ME>2P@4RYC;VPS("DG("D[#0HO#0H-"F-A;&P@
M;6Q?861D7V-O;FYE8W1I;VY?<V-R:7!T*" G=C$G+" G:&%N9&QE7V5R<F]R
M)RP@)V)E9VEN(%5,2&%N9& QE17)R;W(H(#\L(#\L(#
\L(#\L(#\@*3L@96YD
M.R<@*0T*+PT*8V%L;"!M;%]A9&1?8V]N;F5C=&EO;E]S8W)I<'0H("=V,2<L
M("=B96=I;E]S> 6YC:')O;FEZ871I;VXG+
" G8F5G:6X@4WEN8U5S97(
N4V5T
M240H(#\@*3L@96YD.R<@*0T*+PT*8V%L;"!M;%]A9&1?8V]N;F5C=&EO;E]S
M8W)I<'0H("=V,2<L("=B96=I;E]U<&QO860G+" G9&5L971E(&9R;VT@=#-?
M=&5M<"!W:&5R92!M;& ED(#T@4WEN8U5S97(N1V
5T240H*2<@*0T*+PT*8V%L
M;"!M;%]A9&1?8V]N;F5C=&EO;E]S8W)I<'0H("=V,2<L("=E;F1?=7!L;V%D
M)RP@)V1E;&5T92!F<F]M('0S7W1E;7 @=VAE<F4@;6QI9" ](%-Y;F-5<V5R
H+D=E=$E$*"DG("D-"B\-"@T*8V]M;6ET#0HO#0H-"G%U:70-"B\-"@``
`
end

begin 666 mlrep.bat
M9&)M;'-T;W @+7<-"F1B<W1O<" M>2 M8R B96YG/7)E;6]T97,[=6ED/61B
M83MP=V0]<W%L(@T*#0ID8F5R87-E("UY(')E;3%<<F5M,2YD8@T*9&)E<F%S
M92 M>2!R96TR7')E;3(N9&(-"F5C:&\@>7QD96P@<F5M,0T*96-H;R!Y?&1E
M;"!R96TR#0IE8VAO('E\9&5L('5N;&]A9 T*#0IR96UD:7(@<F5M,0T*<F5M
M9&ER(')E;3(-"G)M9&ER('5N;&]A9 T*#0IA='1R:6(@+7(@*B
XJ#0IA='1R
M:6(@*W(@;6QR97 N8F%T#0IA='1R:6(@*W(
@;6QS<6PN<W%L#0IA='1R:6(@
M*W(@;W)A8VQE+G-Q; T*871T<FEB("MR(&%S82YS<6P-"F%T=')I8B K<B!C
M;VYN96-T+F)A= T*871T<FEB("MR('-Y;F-H+F)A= T*96-H;R!Y?&1E;" J
M+BH-"F%T=')I8B M<B!M;')E<"YB870-"F%T=')I8B M<B!M;'-Q;"YS<6P-
M"F%T=')I8B M<B!C;VYN96-T+F)A= T*871T<FEB("UR('-Y;F-H+F)A= T*
M871T<FEB("UR(&]R86-L92YS<6P-"F%T=')I8B M<B!A<V$N<W%L#0H-"FUK
M9&ER(')E;3$-"FUK9&ER(')E;3(-"FUK9&ER('5N;&]A9 T*#0II9B!N;W0@
M97AI<W0@=&5M<$1"(&UK9& ER('1E;7!$0@T*:68@;F
]T(&5X:7-T('1E;7!$
M0EQC;VYS+F1B(&1B:6YI=" M<" T,#DV('1E;7!$0EQC;VY
S+F1B#0H-"F-O
M<'D@=&5M<$1"7&-O;G,N9&(@< F5M,5QR96TQ+F1B#0IC;
W!Y('1E;7!$0EQC
M;VYS+F1B(')E;3)<<F5M,BYD8@T*9&)L;V<@+70@<F5M,2YL;V<@<F5M,5QR
M96TQ+F1B#0ID8FQO9R M=" !R96TR+FQO9R!R96TR7'
)E;3(N9&(-"@T*<W%L
M<&QU<R!M;#@O;6%N86=E<D!P8V8Y:2! ;W)A8VQE+G-Q; T*<W1A<G0@9&)M
M;'-R=C@@+79C9FAN<'-T=2 M;W0@;6QS<G8X+G1X=" M8R B9'-N/6-O;G-?
M;W)A.3)?;65R.# R.W5I9#UM;#@[<'=D/6UA;F%G97(B#0IS=&%R=" O=V%I
M="!D8FES<6QC("U1("UC(")U:60]9&)A.W!W9#US<6P[96YG/7)E;6]T97,[
M9&)F/7)E;3%<<F5M,2YD8CMA=71O<W1O<#UN;SMS=&%R=#UD8G-R=C@@+6=D
M(&%L;" M>"!N;VYE(B!R96%D(&%S82YS<6P@,0T*<W1A<G0@+W=A:70@9&)I
M<W%L8R M42 M8R B=6ED/61B83MP=V0]<W%L.V5N9SUR96UO=&5S.V1B9CUR
M96TR7')E;3(N9&([875T;W-T;W ];F\[<W1A<G0]9&)S<G8X("UG9"!A;&P@
M+7@@;F]N92(@<F5A9"!A<V$N<W%L(#(-"F1B;6QU<V5R("UC(")D<VX]8V]N
M<U]O<F$Y,E]M97(X,#([=6ED/6UL.#MP=V0];6%N86=E<B(@+74@<F5M,2 M
M<"!S<6P-"F1B;6QU<V5R("UC(")D<VX]8V]N<U]O<F$Y,E]M97(X,#([=6ED
M/6UL.#MP=V0];6%N86=E<B(@+74@<F5M,B M<"!S<6P-"G!A=7-E#0H-"G-T
M87)T("]W86ET(&1B;6QS>6YC("UC(")E;F<]<F5M;W1E<SMD8FX]<F5M,3MU
M:60]9&)A.W!W9#US<6PB("UM<"!S<6P@+6\@<F5M,2YT>'0@+6L-"G-T87)T
M("]W86ET(&1B;6QS>6YC("UC(")E;F<]<F5M;W1E<SMD8FX]<F5M,CMU:60]
G9&)A.W!W9#US<6PB("UM<"!S<6P@+6\@<F5M,BYT>'0@+6L-"@T*
`
end

begin 666 asa.sql
M<&%R86UE=&5R('5S97)?;F%M93L-" @T*0U)%051%(%1!0DQ%(
'0Q("@-"B @
M<&ME>2!I;G1E9V5R(&YO="!N=6QL('!R:6UA<GD@:V5Y+ T*("!C;VPQ('9A
M<F-H87(H,S I+ T**3L-" @T*0U)%051%(%1!0DQ%(
'0R("@-"B @<&ME>2!I
M;G1E9V5R(&YO="!N=6QL('!R:6UA<GD@:V5Y+ T*("!C;VPR('9A<F-H87(H
M,S I+ T**3L-" @T*0U)%051%(%1!0DQ%(
'0S("@-"B @<&ME>2!I;G1E9V5R
M(&YO="!N=6QL('!R:6UA<GD@:V5Y+ T*("!C;VPS('9A<F-H87(H,S I+ T*
M*3L-"@T*#0I#4D5!5$4@4%)/0T5$55)%($%D9$1A=&$@* T*("!N=6U?<F]W
M<R!I;G1E9V5R( T**0T*0D5'24X-"B @9&5C;&%R92!L;V]P7W9A<B!I;G1E
M9V5R.PT*("!S970@;&]O<%]V87(@/2 P.PT*("!72$E,12!L;V]P7W9A<B \
M/2!N=6U?<F]W<R!,3T]0#0H@(" @:6YS97)T(&EN=&\@=#$@=F%L=65S("AL
M;V]P7W9A<BPG;VYE)RD[#0H@(" @:6YS97)T(&EN=&\@=#(@=F%L=65S("AL
M;V]P7W9A<BPG='=O)RD[#0H@(" @:6YS97)T(&EN=&\@=#,@=F%L=65S("AL
M;V]P7W9A<BPG=&AR964G*3L-"B @("!S970@;&]O<%]V87(@/2!L;V]P7W9A
M<B K(#$[#0H@($5.1"!,3T]0.PT*14Y$.PT*#0H-"D-214%412!054),24-!
M5$E/3B B1$)!(BXB=&5S='!U8B(@*"!404),12!T,2P@5$%"3$4@=#(L(%1!
M0DQ%('0S("D[#0I#4D5!5$4@4UE.0TA23TY)6D%424].(%5315(@(G)E;7MU
M<V5R7VYA;65](CL-"D-214%412!364Y#2%)/3DE:051)3TX@4U5"4T-225!4
M24].(%1/(")$0D$B+B)T97-T<'5B(@T*("!&3U(@(G)E;7MU<V5R7VYA;65]
M(B!465!%("=40U!)4"<@041$4D534R G:&]S=#UL;V-A;&AO<W0G($]05$E/
F3B!35CTG=C$G.PT*"0T*8V%L;"!!9&1$871A*" U,# P("D[#0H`
`
end

Maggie

2005-05-09, 3:24 am

Hi,
Thanks for the response. I Have tried with the Merge
statement in the Oracle. it is working fine. Thanks for the
timely help.
with regds,
Maggie.
> Here's a sample that uses the Oracle MERGE command. I
> pulled this from something I cooked up a while ago trying
> to do performance differences between three different ways
> of handling primary key violations on an Oracle
> consolidated database.
>
> 1) Let the error occur, and return 1000 from handle_error
> script on primary keys (table t1).
> 2) In the upload_insert event for the table, use the MERGE
> command to do the equivalent of an INSERT ON EXISTING
> UPDATE command in ASA. This will execute a merge command
> for each row uploaded (table t2). 3) In the upload_insert
> event for the table, insert the data into a holding table
> and then execute a single merge command in the end_upload
> table event to place the results in the real table (table
> t3).
>
> My test inserted 5,000 identical rows in two remotes so
> the each row would cause a PKEY violation on the
> consolidated when I synched my second remote, but the
> first remote would synch cleanly.
>
> I've attached the code I used to define the tables and
> scripts on the consolidated database (oracle.sql) as well
> as the code to define the remote databases (asa.sql). The
> file mlrep.bat was used to run the test, and you can
> probably modify it a little and change the DSN names,
> userids, passwords and server names if you're really
> interested, but you're likely just interested in the
> scripts defined for upload_insert in the oracle.sql file.
>
> For those that are interested, option #3 is fastest, but I
> found that the MERGE command introduced a lot of blocking
> and deadlocking on the server when there were multiple
> users synchronizing data at the same time, so it might not
> be suitable in your environment. As Greg has pointed out
> numerous times, the best way to solve this problem is to
> ensure that primary keys are unique across your remote
> databases. You might also want to consider forced
> conflicts on the consolidated database, and a good sample
> can be found in the following on-line tech-doc :
>
http://www.ianywhere.com/whitepaper...idated_db.html.
> The customer that I created this sample with eventually
> ended up using forced conflicts instead of the MERGE
> command, because of concurrency issues with the Oracle
> MERGE command.
>
> --
> 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 "Platform Preview" and "Time Frame" to ALL
>
> "Greg Fenton" <greg. fenton_NOSPAM_@ianyw
here.com> wrote in
> message news:4267da11$1@foru
ms-2-dub...
> called "merge": >
>
http://download-west.oracle.com/doc....htm#SQLRF01606
>
>
>
>
>
> [Attachment: oracle.sql]
> [Attachment: mlrep.bat]
> [Attachment: asa.sql]

Sponsored Links