Please be aware that the SAP SQL Anywhere Forum will be shut down on August 29th, 2024 when all it's content will be migrated to the SAP Community.

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?

asked 18 Apr '19, 15:26

Baron's gravatar image

accept rate: 48%

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.

permanent link

answered 18 Apr '19, 19:05

Volker%20Barth's gravatar image

Volker Barth
accept rate: 34%

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
(19 Apr '19, 08:38) 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
showing 2 of 6 show all flat view

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.

permanent link

answered 18 Apr '19, 16:04

Breck%20Carter's gravatar image

Breck Carter
accept rate: 20%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 18 Apr '19, 15:26

question was seen: 1,284 times

last updated: 19 Apr '19, 13:51