we are using several tens of DBs which replicate/synchronize using DBREMOTE/Mobilink. What is the best approach to centralize distributing update scripts (creating/altering procedures and triggers, and also normal insert/update statements). Currently we are using a non centralized method so we write the updates in .sql files, and then READ these files in each DB over dbisql. The only available way for centralizing (so far known for me), is to start those .sql files with PASSTHROUGH FOR ... and end it with PASSTHROUGH STOP, which has some restrictions/drawbacks. FYI, in some installations we have several levels in the DB-hierarchy (main Consolidate -> several Consolidates -> End clients), so the update should be installed once on main cons. and then to the below conses and later to the end clients. Any suggestions? |
For SQL Remote, we have used passthrough mode to apply schema changes and rare "data cleanup". However, that was always within a two-tier setup, i.e. without intermediate remote/cons databases. Testing is crucial here. actually we use the PASSTHROUGH sometimes for some small schema changes (and for two-tier setups). I think with hook procedures can we however extend it for multi level setups too. Does PASSTHROUGH work also for mobilink? One more problem with PASSTHROUGH is that it does not accept 'if' blocks! I tried once to encapsulate the 'if' blocks inside EXECUTE IMMEDIATE, but the EXECUTE IMMEDIATE itself was also not accepted within PASSTHROUGH!
(19 Apr '19, 05:56)
Baron
Replies hidden
Passthrough works with SQL Remote only, AFAIK. For statements that do not work in passthrough mode, one common way is to put that logic within a procedure and create and call that within passthrough mode. I guess this forum has some samples of that...
(19 Apr '19, 08:25)
Volker Barth
I don't know if this helps with passthrough, but... One general-purpose work-around is to add a BEGIN END block around a block of SQL that is otherwise not accepted in some context.
(19 Apr '19, 10:56)
Breck Carter
No, AFAIK that does not help with passthrough mode...
(19 Apr '19, 11:04)
Volker Barth
...and if I had read Reg's general advice here, I might have guessed that!
(19 Apr '19, 13:49)
Breck Carter
|
12 years later, the company (Simbex) still exists, the application (HITS) still exists, and the database described in this article still operates: Synchronizing Schema Changes and Managing Referential Integrity Violations. |