SAP SQL Anywhere

Question

I know how i can add a table to a publication:

ALTER PUBLICATION PublicationName ADD TABLE TableName

But how do I add a column inside a table into a publication, so that it synchronizes?

Thanks for your answer,

Some background should you be interested: I'm using EF (via code) to add columns to my remote DB and for some reason these columns are not added to the publication. I believe this is the case because this specific table has 1 column that is not being synchronized and by default new columns are also not added to sync. I need to do this programmatically.

Thank you !

Accepted Answer

Assumption : You currently have a table called Admin at the remote where you are successfully synchronizing a subset of columns against the consolidated database using script version 'v16'.

create table Admin (
  admin_id bigint default global autoincrement(1000000) primary key,
  data     varchar(64),
  no_send  integer default 0
);
create publication p1 ( table Admin( admin_id, data ) );

At the consolidated database, we will need to add the new column to the Admin table, and then define a new synchronization script version to handle the new column. Make sure to add a default to the new column to ensure that users still synchronizing with the 'v16' script version won't run into problems.

alter table Admin add data2 varchar(64) default NULL;

call ml_add_column( 'v16.1', 'Admin', 'admin_id', NULL );
call ml_add_column( 'v16.1', 'Admin', 'data', NULL );
call ml_add_column( 'v16.1', 'Admin', 'data2', NULL );

call ml_add_table_script( 'v16.1', 'Admin', 'upload_insert',
'insert into Admin(admin_id,data,data2) 
 values ({ml r.admin_id},{ml r.data},{ml r.data2})'
);

call ml_add_table_script( 'v16.1', 'Admin', 'upload_update',
'update Admin set data = {ml r.data}, 
                  data2 = {ml r.data2} 
              where admin_id = {ml r.admin_id}'
);

call ml_add_table_script( 'v16.1', 'Admin', 'upload_delete',
'delete from Admin where admin_id = {ml r.admin_id}'
);

call ml_add_table_script( 'v16.1', 'Admin', 'download_cursor',
'select admin_id, data, data2 from Admin
 where last_modified >= {ml s.last_table_download}'
);

call ml_add_table_script( 'v16.1', 'Admin', 'download_delete_cursor',
'select admin_id from Admin_del where del_time >= {ml s.last_table_download}'
);

At the remote, we'll use the START SYNCHONIZATION SCHEMA command, and then alter the table and publication involved.

start synchronization schema change for tables "Admin" set script version = 'v16.1';

alter table Admin add data2 varchar(64) default NULL;
alter publication p1 alter table Admin ( admin_id, data, data2 );

stop synchronization schema change;

The next time you synchronize after the schema change, dbmlsync will scan everything in the transaction log up to the "start synchronization schema change" and synchronize with the old schema and script version. If that succeeds, it will then scan everything after the "stop synchronization schema change" using the new schema and the new script version.

Reg

Answer 2

1

I use a script much like this:

START SYNCHRONIZATION SCHEMA CHANGE
FOR TABLES "tablename"
SET SCRIPT VERSION = 'script_version';

ALTER TABLE "tablename"
ADD "columnname" INT NULL;

COMMIT;

STOP SYNCHRONIZATION SCHEMA CHANGE;
COMMIT;

permanent link

answered 10 Jun '15, 07:05

Mike Killey
291●1●6●9
accept rate: 60%

edited 10 Jun '15, 07:16

You might want to put some kind of a check around the ALTER TABLE to check that you aren't adding a column that already exists.

(10 Jun '15, 07:07) Mike Killey

I should also add that this assumes that your sync scripts in your consolidated DB include the new column, or that you've created a new set of scripts - in which case the script_version value needs to reflect this.

(10 Jun '15, 07:15) Mike Killey

Hi,

I can add the column to the table. I just can't make the column be synchronized because in the specific table, there already is 1 column not being synchronized... Because of this, sql anywhere assumes that all added columns should not be synchronized by default (From what I'm seeing here)... Only way that I can this to work is to remove the table from the publication and add it again with specified columns.

(10 Jun '15, 07:18) vdcey

2

You'll need to alter the publication as well. See my answer for an example.

(10 Jun '15, 09:54) Reg Domaratzki

@Mike, I gave you a thumbs up for your effort. Thanks

(11 Jun '15, 05:22) vdcey

flat view

Answer 3

Currently this is all that works for me:

START SYNCHRONIZATION SCHEMA CHANGE FOR TABLES  MYTABLE  SET SCRIPT VERSION = 'ScriptVersion';
ALTER PUBLICATION MYPUBLICATION drop TABLE MYTABLE;
STOP SYNCHRONIZATION SCHEMA CHANGE;

START SYNCHRONIZATION SCHEMA CHANGE FOR TABLES  MYTABLE  SET SCRIPT VERSION = 'ScriptVersion';
ALTER PUBLICATION MYPUBLICATION add TABLE MYTABLE(column1, column2, ....);
STOP SYNCHRONIZATION SCHEMA CHANGE;

If there is a better solution, Happy to hear it.

SAP SQL Anywhere

How do I add a column inside a table into a publication ?

Follow this question

Related questions