Re: Oracle 8i and Oracle 9i Lite Sync

Karthik wrote:

[snip!]
>
>
>
> Thanks a lot for the reply. Now that i know that exact requirements, i
> would like to get an opinion abt this solution
>
> there is an existing java web application that is running on field
> reps' laptops [on tomcat]. Currently an java database [hsql] is used
> as DB. there is a severe bottle neck with the existing system in terms
> of performance. so they want to upgrade to Oracle lite.. why Lite..
> bcoz few years down the line, they want to use PDA, cell phones.. so
> do not want to double work. They also do a daily sync with the central
> oracle server [9i] using the java app .This reads data from the
> existing db and "inserts" it onto the oracle central server. once
> again this is also a time consuming proc. these "idle" times of reps
> has to be "minimised". Rewriting the java app with something else is
> not a solution. that is ruled out.

Well, that's a pity, as it could very well be the cause of your performance problems.
What do you mean by quote-inserts-quote? Pseudo-insert? What's that?

>
> so,
> 1. a logic to do sync between oracle lite and oracle db

Advanced Replication, probably with conflict resolution. No, it's not simple, but can be set up to ensure that the following scenario does have a happy end: 1) Rep A comes around, and synchronizes; as a result, a meeting with client Z is set for Tuesday 15:00 next. (insert into meetings values clientid, attendants[], meetingdate old_meetingdate=NULL)

2) Client Z calls the head office, and reschedules the meeting to 10:00
(update meetings set meetingdate=..., old_meetingdate=meetingdate where clientid=... and meetingdate=...; old_meetingdate=Tuesday, 15:00)

3) Rep B comes around, and synchronizes; as a result, a meeting with client Z is set for 10:00 Tuesday next. (old_meetingdate=Tuesday, 15:00)
Rep B calls client A to confirm the meeting, however, during this call, an new aspect comes up, and client Z wants another engineer to attend as well - meeting rescheduled for Monday, 10:00. (exception when no_data_found then
update meetings set meetingdate=..., old_meetingdate=meetingdate where clientid=... and old_meetingdate=...; old_meetingdate=Tuesday, 10:00)

4) Luckily, Rep A calls in during the weekend; his s/w crashed? (update meetings set meetingdate=...
where clientid=... and old_meetingdate=...) No old_meetingdate to match (his last known meeting was on Tuesday, 15:00, not tuesday, 10:00 as in the central database now.).
Conflict resolution will recognize the fact there's no previous matching record, and act according to what you defined.
If a "central server is always right" scenario was implemented, Rep A's meeting would be rescheduled to Monday, 10:00.
> 2. normal usage of db in a typical 3 tier web system

No problem, either. Just make sure foreign keys in the database (you DO have relations, don't you?!?) are deferrable, initially deferred.

-- 

Regards,
Frank van Bortel