Hi,

Hopefully I can explain this properly...

Currently we have a working Mobilink (v12.0.1) setup with Windows Mobile clients - and has been running happily for some time. Now though, I need to update one of the tables to add an additional column. I have no problems with creating a new sync script version, or with updating remote databases to use the new script version - this is all fine.

My question is this - how do I go about changing my 'new database script' (where I create new DB's for new clients) to point just that updated table at 'v2' instead of 'v1', whilst leaving all the other tables using 'v1'?

Currently that script creates all the tables, then the publication / ML user. Then last of all it creates the synchronization subscription, which sets the script version as 'v1'. But if I've got a single table that needs to use 'v2', how can I specify that?

I don't really want to have to put all of the table scripts into 'v2' if I don't have to, although I can see that this is one such solution.

Thanks,

Mike.

asked 19 Feb '14, 06:52

Mike%20Killey's gravatar image

Mike Killey
291169
accept rate: 60%

Would it be as simple as doing this after creating the synchronization subscription:

START SYNCHRONIZATION SCHEMA CHANGE
FOR TABLES "my_updated_table"
SET SCRIPT VERSION = 'v2';
STOP SYNCHRONIZATION SCHEMA CHANGE;

??

Or is there something else I should additionally do?

(19 Feb '14, 07:00) Mike Killey

I wouldn't suggest having a single remote database synchronize using two different script versions. Defining synchronization scripts for all tables in 'v2' in the consolidated database and having newly created remote databases (with the extra column) synchronize using the 'v2' script version is the correct way to handle this.

If a remote database is using the "old" schema (i.e. updated table does not have the extra column), it should synchronize using the "old" script version ('v1' in your example). If a remote database is using the "new" schema (i.e. updated table does have the extra column), it should synchronize using the "new" script version ('v2' in your example).

The START SYNCHRONIZATION SCHEMA CHANGE (and subsequant STOP SYNCHRONIZATION SCHEMA CHANGE) command is used when you alter the schema of the remote database and move to a different script version.

Reg

Aside : Copying a script version in a consolidated database is easy. I've attached a simple SQL Anywhere stored procedure that makes a copy of a script version in an SQL Anywhere consolidated database. After you've copied 'v1' to 'v2' modify the scripts for my_updated_table in 'v2', and you're ready to go.

create procedure CopyScriptVersion ( in @orig varchar(128), in @dest varchar(128) )
begin
    declare cur_table cursor for 
        select event,table_name,script_language,script 
          from ml_table_scripts
         where version = @orig;
    declare cur_conn cursor for 
        select event,script_language,script 
          from ml_connection_scripts
         where version = @orig;
    declare cur_col cursor for 
          select mlt.name, mlc.name, mlc.type
            from ml_column mlc, ml_script_version mlsv, ml_table mlt
           where mlsv.version_id = mlc.version_id
             and mlt.table_id = mlc.table_id
             and mlsv.name = @orig
        order by mlt.name, mlc.idx;
    declare @event       varchar(128);
    declare @table_name  varchar(128);
    declare @script_lang varchar(128);
    declare @column_name varchar(128);
    declare @type        varchar(128);
    declare @script      long varchar;

open cur_table;
    fetch first cur_table into @event,@table_name,@script_lang,@script;
    while ( sqlcode = 0 ) loop
        call ml_add_lang_table_script( @dest, @table_name, @event, @script_lang, @script );
        fetch next cur_table into @event,@table_name,@script_lang,@script;
    end loop;
    close cur_table;

open cur_conn;
    fetch first cur_conn into @event,@script_lang,@script;
    while ( sqlcode = 0 ) loop
        call ml_add_lang_connection_script( @dest, @event, @script_lang, @script );
        fetch next cur_conn into @event,@script_lang,@script;
    end loop;
    close cur_conn;

open cur_col;
    fetch first cur_col into @table_name, @column_name, @type;
    while ( sqlcode = 0 ) loop
        call ml_add_column( @dest, @table_name, @column_name, @type );
        fetch next cur_col into @table_name, @column_name, @type;
    end loop;
    close cur_col;

end;

permanent link

answered 19 Feb '14, 08:47

Reg%20Domaratzki's gravatar image

Reg Domaratzki
5.2k33575
accept rate: 41%

Thanks Reg, exactly the clarification I needed.

I shall proceed to create a complete 'v2' containing all scripts for 'v1', albeit with the change to the affected table. I keep all of the scripts for an entire version in a source controlled file, so it's trivial to make a copy and change to make a 'v2'.

(19 Feb '14, 08:59) Mike Killey

One last question on this:

At the moment I'm using START SYNCHRONIZATION SCHEMA CHANGE (and corresponding STOP) to create the new column, and to set the SCRIPT VERSION for just that table to 'v2'. But would it be possible / preferable to update the entire remote databse to use v2? So that would mean updating not just the script versions for the tables, but also for the SYNCHRONIZAION SUBSCRIPTION? If so, how would I do this last bit?

(19 Feb '14, 09:07) Mike Killey
Replies hidden

AFAIK, you could

  • either list ALL synchronized tables in the FOR TABLES clause of the START SYNCHRONIZATION SCHEMA CHANGE
  • or (preferrably) use ALTER SYNCHRONIZATION SUBSCRIPTION TO ... FOR ... SET SCRIPT VERSION = 'v2'
(19 Feb '14, 09:22) Volker Barth
1

When you use START SYNCHRONIZATION SCHEMA CHANGE and provide a list of tables, you are not changing the script version you use for that specific table, but you are changing the script version for every SYNCHRONIZATION SUBSCRIPTION defined in the remote database that includes that tables in the list.

If your remote database includes a single publication and a single SYNCHRONIZATION SUBSCRIPTION, then you've already done the last bit.

(19 Feb '14, 09:52) Reg Domaratzki

I don't believe this is true. See my response to this same comment from Mike.

(19 Feb '14, 09:54) Reg Domaratzki

So what you're saying Reg is that this:

START SYNCHRONIZATION SCHEMA CHANGE
FOR TABLES "dbo"."trip_item"
SET SCRIPT VERSION = 'v2';
ALTER TABLE "dbo"."trip_item"
ADD "delivery_group" INTEGER NULL;
STOP SYNCHRONIZATION SCHEMA CHANGE;

Will result in the entire subscription being updated to use 'v2', despite the fact that I'm only listing a single table? If so, then the START SYNC.. statement is misleading. The tables listed are only for the sake of the schema change, but the SCRIPT VERSION is for the entire subscription. Is that what you mean?

(19 Feb '14, 10:26) Mike Killey
Replies hidden

OK, you've got the code, I can only try to understand the docs:)

To clarify: Do you mean ALTER SYNCHRONIZATION SUBSCRIPTION TO ... FOR ... SET SCRIPT VERSION = 'v2' would only be helpful to change the script version when one does not have to change the remote schema? Or is it not recommended at all, and START SYNCHRONIZATION SCHEMA CHANGE is generally recommended here, even for a schema that does not need modifications?

(19 Feb '14, 10:31) Volker Barth

I can see this now. I've run:

SELECT * FROM SYS.SYSSYNC

and I can see that my subscription is now set to 'v2'. So yes, calling SET SCRIPT VERSION even when only ALTERing a single table, does indeed update the subscription. So going back to my question - can I get away with only defining the 'v2' scripts for that table - the answer has to be no as the subscription will be expecting 'v2' scripts for every table in that subscription.

To my mind, the documentation (and usage) of the FOR TABLES clause of START SYNCHRONIZATION SCHEMA CHANGE is confusing, misleading and vague.

(19 Feb '14, 12:03) Mike Killey
1

To my mind, the documentation (and usage) of the FOR TABLES clause of START SYNCHRONIZATION SCHEMA CHANGE is confusing, misleading and vague.

Given my obvious misunderstanding (as corrected by Reg), I'd surely share your opinion. Feel free to leave an according comment on DCX...

(19 Feb '14, 12:29) Volker Barth
1

You will need to define 'v2' scripts for all the tables in the synchronization subscription(s) affected by the START SYNCHRONIZATION SCHEMA CHANGE command. I was a little vague in my first reply because I didn't know whether you'd defined multiple publications or mutltiple synchronization subscriptions in the remote database. Given that you eventually figured out what was going on by checking the rows in the SYS.SYSSYNC table, I probably could have answered with a little more detail initially.

I didn't find the documentation vague, but then again, as a SQL Anywhere developer I have a pretty solid understand of the product. I'd love a comment in DCX (http://dcx.sybase.com/index.html#1201/en/dbreference/start-synchronization-schema-change-statement.html*d5e63371) that explains what you (or Volker) found vague about the documentation so we can improve it.

(19 Feb '14, 12:41) Reg Domaratzki

Thanks Reg, all clear now.

(19 Feb '14, 12:46) Mike Killey
showing 3 of 11 show all flat view
Your answer
toggle preview

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "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:

×290

question asked: 19 Feb '14, 06:52

question was seen: 592 times

last updated: 19 Feb '14, 12:46