Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Replication and DD update issues.

8 posts in Replication Last posting was on 2011-07-27 08:11:21.0Z
mike grace Posted on 2011-02-08 10:34:19.0Z
Date: Tue, 8 Feb 2011 10:34:19 +0000 (UTC)
Message-ID: <b9f6cef8f3e98cd9592e09c1245@devzone.advantagedatabase.com>
From: mike grace <mikeg@computastat-group.co.uk>
Subject: Replication and DD update issues.
Newsgroups: Advantage.Replication
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Content-Type: text/plain; charset=iso-8859-1; format=flowed
X-Newsreader: JetBrains Omea Reader 1098.1
NNTP-Posting-Host: 188.220.18.141
X-Trace: 8 Feb 2011 02:33:57 -0800, 188.220.18.141
Lines: 21
Path: solutions.advantagedatabase.com
Xref: solutions.advantagedatabase.com Advantage.Replication:435
Article PK: 1134289

Hi,

I have a situation where I have added a field to a table which is in a publication.
The update script adds the field and then updates the value (sets a default).

I do not want this field in my replicated table.

At the moment the replication queue gets filled with updates that I do not
want to happen. These then hold up replication.

Is there any way to tell replication to ignore updates to a table whilst
data dictionary modifications are in progress, i.e. put the sysem into maintenance
mode?

I have tried pausing replication but that doesn't do anything.

Regards

Mike


Lance Schmidt Posted on 2011-02-08 17:47:55.0Z
From: "Lance Schmidt" <LanceSc@ExtendSys.com>
Newsgroups: Advantage.Replication
References: <b9f6cef8f3e98cd9592e09c1245@devzone.advantagedatabase.com>
Subject: Re: Replication and DD update issues.
Date: Tue, 8 Feb 2011 10:47:55 -0700
Lines: 47
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5931
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5994
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: 10.6.199.121
Message-ID: <4d5181af$1@solutions.advantagedatabase.com>
X-Trace: 8 Feb 2011 09:47:27 -0800, 10.6.199.121
Path: solutions.advantagedatabase.com
Xref: solutions.advantagedatabase.com Advantage.Replication:437
Article PK: 1134291

Hi Mike,

Rather than pause the subscription you want to disable it.

EXECUTE PROCEDURE sp_ModifySubscriptionProperty( 'MySubscription',
'ENABLED', 'False' );

Pausing the subscription only prevents it from being sent to the target
database, the entries are still added to the queue.

In your situation I would modify the article to have an include list that
contains only the fields you want replicated. You could then modify the
table without unnecessary updates being sent.

Best Regards,

Lance Schmidt
Advantage R&D

"mike grace" <mikeg@computastat-group.co.uk> wrote in message
news:b9f6cef8f3e98cd9592e09c1245@devzone.advantagedatabase.com...
> Hi,
>
> I have a situation where I have added a field to a table which is in a
> publication. The update script adds the field and then updates the value
> (sets a default).
>
> I do not want this field in my replicated table.
>
> At the moment the replication queue gets filled with updates that I do not
> want to happen. These then hold up replication.
>
> Is there any way to tell replication to ignore updates to a table whilst
> data dictionary modifications are in progress, i.e. put the sysem into
> maintenance mode?
>
> I have tried pausing replication but that doesn't do anything.
>
> Regards
>
> Mike
>
>


Lance Schmidt Posted on 2011-02-09 18:29:20.0Z
From: "Lance Schmidt" <LanceSc@ExtendSys.com>
Newsgroups: Advantage.Replication
References: <4d5181af$1@solutions.advantagedatabase.com> <b9f6cef8f4408cd9656c680421b@devzone.advantagedatabase.com>
Subject: Re: Replication and DD update issues.
Date: Wed, 9 Feb 2011 11:29:20 -0700
Lines: 36
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5931
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5994
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: 10.6.199.121
Message-ID: <4d52dcdb$1@solutions.advantagedatabase.com>
X-Trace: 9 Feb 2011 10:28:43 -0800, 10.6.199.121
Path: solutions.advantagedatabase.com
Xref: solutions.advantagedatabase.com Advantage.Replication:439
Article PK: 1134293

Hi Mike,

Exactly. Use the include list when you only want a certain subset of
columns to be replicated. Any new columns added to the table will NOT be
replicated until being added to the include list.

Use the exclude list when you want to prevent a subset of columns from being
replicated. Any new columns added to the tables will be replicated after
the restructure.

Best Regards,

Lance Schmidt
Advantage R&D

"mike grace" <mikeg@computastat-group.co.uk> wrote in message
news:b9f6cef8f4408cd9656c680421b@devzone.advantagedatabase.com...
> Hello Lance,
>
> At the moment the article doesn't have any fields ticked in the include
> column but does have some ticked in the exclude column.
>
> Are you saying that at the moment, if nothing is ticked in the include
> column then all field data is replicated except for the excluded ones and
> that if I switch it to tick the included columns then any new columns I
> add will not automatically be included?
>
> Regards
>
>
> Mike
>
>


mike grace Posted on 2011-02-10 09:30:08.0Z
Date: Thu, 10 Feb 2011 09:30:08 +0000 (UTC)
Message-ID: <b9f6cef8f4968cd971c3dc72cd2@devzone.advantagedatabase.com>
From: mike grace <mikeg@computastat-group.co.uk>
Subject: Re: Replication and DD update issues.
Newsgroups: Advantage.Replication
References: <4d52dcdb$1@solutions.advantagedatabase.com>
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Content-Type: text/plain; charset=iso-8859-1; format=flowed
X-Newsreader: JetBrains Omea Reader 1098.1
NNTP-Posting-Host: 188.220.18.141
X-Trace: 10 Feb 2011 01:29:41 -0800, 188.220.18.141
Lines: 42
Path: solutions.advantagedatabase.com
Xref: solutions.advantagedatabase.com Advantage.Replication:441
Article PK: 1134294

Hello Lance,

Thanks for the clarification.

Regards


Mike

> Hi Mike,
>
> Exactly. Use the include list when you only want a certain subset of
> columns to be replicated. Any new columns added to the table will NOT
> be replicated until being added to the include list.
>
> Use the exclude list when you want to prevent a subset of columns from
> being replicated. Any new columns added to the tables will be
> replicated after the restructure.
>
> Best Regards,
>
> Lance Schmidt
> Advantage R&D
> "mike grace" <mikeg@computastat-group.co.uk> wrote in message
> news:b9f6cef8f4408cd9656c680421b@devzone.advantagedatabase.com...
>
>> Hello Lance,
>>
>> At the moment the article doesn't have any fields ticked in the
>> include column but does have some ticked in the exclude column.
>>
>> Are you saying that at the moment, if nothing is ticked in the
>> include column then all field data is replicated except for the
>> excluded ones and that if I switch it to tick the included columns
>> then any new columns I add will not automatically be included?
>>
>> Regards
>>
>> Mike
>>


mike grace Posted on 2011-07-13 15:16:42.0Z
Date: Wed, 13 Jul 2011 15:16:42 +0000 (UTC)
Message-ID: <b9f6cef8106178ce0f8f62ea658d@devzone.advantagedatabase.com>
From: mike grace <mikeg@computastat-group.co.uk>
Subject: Re: Replication and DD update issues.
Newsgroups: Advantage.Replication
References: <b9f6cef8f4968cd971c3dc72cd2@devzone.advantagedatabase.com>
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Content-Type: text/plain; charset=iso-8859-1; format=flowed
X-Newsreader: JetBrains Omea Reader 1098.1
NNTP-Posting-Host: 188.220.18.141
X-Trace: 13 Jul 2011 08:16:00 -0700, 188.220.18.141
Lines: 91
Path: solutions.advantagedatabase.com
Xref: solutions.advantagedatabase.com Advantage.Replication:459
Article PK: 1134312

Hello Lance,

ADS 10.10.0.6

Just found something strange.

I have an article which has a few columns ticked for inclusion.

I then ran a script which adds 3 fields to the table then sets a default i.e.

alter table add column .... POSITION 33;
update table set column = '';

alter table add column .... POSITION 34;
update table set column = '';


alter table add column .... POSITION 35;
update table set column = '';



When I ran the script, the second alter table statement failed with the error
that prevents modification to a table with entries in the replication log.

When I looked at the log table it had entried for the table.

Why should this have happened? These fields are new and will not be included
in the "include column" entry in the article.


I can only assume that replication is picking up that the table has been
modified but maybe hasn't created an update statement or could it be because
I am using the POSITION PARAMETER.


It causes a problem because I have to empty the log, then run the second
alter table/update, clear the log and then run the third alter table/update.


I think my answer may be to stop repliction with the execute procedure statement.
If so, one more question. What is the statement to turn it back on again?


Regards


Mike

> Hello Lance,
>
> Thanks for the clarification.
>
> Regards
>
> Mike
>
>> Hi Mike,
>>
>> Exactly. Use the include list when you only want a certain subset of
>> columns to be replicated. Any new columns added to the table will
>> NOT be replicated until being added to the include list.
>>
>> Use the exclude list when you want to prevent a subset of columns
>> from being replicated. Any new columns added to the tables will be
>> replicated after the restructure.
>>
>> Best Regards,
>>
>> Lance Schmidt
>> Advantage R&D
>> "mike grace" <mikeg@computastat-group.co.uk> wrote in message
>> news:b9f6cef8f4408cd9656c680421b@devzone.advantagedatabase.com...
>>> Hello Lance,
>>>
>>> At the moment the article doesn't have any fields ticked in the
>>> include column but does have some ticked in the exclude column.
>>>
>>> Are you saying that at the moment, if nothing is ticked in the
>>> include column then all field data is replicated except for the
>>> excluded ones and that if I switch it to tick the included columns
>>> then any new columns I add will not automatically be included?
>>>
>>> Regards
>>>
>>> Mike
>>>


Mark Wilkins Posted on 2011-07-26 16:12:42.0Z
From: "Mark Wilkins" <a@b.c>
Newsgroups: Advantage.Replication
References: <b9f6cef8f4968cd971c3dc72cd2@devzone.advantagedatabase.com> <b9f6cef8106178ce0f8f62ea658d@devzone.advantagedatabase.com>
In-Reply-To: <b9f6cef8106178ce0f8f62ea658d@devzone.advantagedatabase.com>
Subject: Re: Replication and DD update issues.
Date: Tue, 26 Jul 2011 10:12:42 -0600
Lines: 1
Organization: Sybase
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=response
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
Importance: Normal
X-Newsreader: Microsoft Windows Live Mail 14.0.8089.726
X-MimeOLE: Produced By Microsoft MimeOLE V14.0.8089.726
NNTP-Posting-Host: 10.6.199.122
Message-ID: <4e2ee765$1@solutions.advantagedatabase.com>
X-Trace: 26 Jul 2011 09:12:21 -0700, 10.6.199.122
Path: solutions.advantagedatabase.com
Xref: solutions.advantagedatabase.com Advantage.Replication:460
Article PK: 1134313

Hi Mike,

If I remember correctly, an update to a table (article) in a subscription
causes the record to be replicated regardless of whether the included fields
are updated or not. So the update statement to set the column to an empty
value after your script would result in putting an entry into the queue for
every record.

To avoid that during the table maintenance, you could disable and then
re-enable the subscription that the table is in:

execute procedure sp_ModifySubscriptionProperty( 'thesubscription',
'enabled', 'false' );

and then:

execute procedure sp_ModifySubscriptionProperty( 'thesubscription',
'enabled', 'true' );

While it is disabled, now rows will be added to the queue during the update.

Mark Wilkins
Advantage R&D

"mike grace" <mikeg@computastat-group.co.uk> wrote in message
news:b9f6cef8106178ce0f8f62ea658d@devzone.advantagedatabase.com...
> Hello Lance,
>
> ADS 10.10.0.6
>
> Just found something strange.
>
> I have an article which has a few columns ticked for inclusion.
>
> I then ran a script which adds 3 fields to the table then sets a default
> i.e.
>
> alter table add column .... POSITION 33;
> update table set column = '';
>
> alter table add column .... POSITION 34;
> update table set column = '';
>
>
> alter table add column .... POSITION 35;
> update table set column = '';
>
>
>
> When I ran the script, the second alter table statement failed with the
> error that prevents modification to a table with entries in the
> replication log.
>
> When I looked at the log table it had entried for the table.
>
> Why should this have happened? These fields are new and will not be
> included in the "include column" entry in the article.
>
>
> I can only assume that replication is picking up that the table has been
> modified but maybe hasn't created an update statement or could it be
> because I am using the POSITION PARAMETER.
>
>
> It causes a problem because I have to empty the log, then run the second
> alter table/update, clear the log and then run the third alter
> table/update.
>
>
> I think my answer may be to stop repliction with the execute procedure
> statement. If so, one more question. What is the statement to turn it back
> on again?
>
>
> Regards
>
>
> Mike
>
>
>
>> Hello Lance,
>>
>> Thanks for the clarification.
>>
>> Regards
>>
>> Mike
>>
>>> Hi Mike,
>>>
>>> Exactly. Use the include list when you only want a certain subset of
>>> columns to be replicated. Any new columns added to the table will
>>> NOT be replicated until being added to the include list.
>>>
>>> Use the exclude list when you want to prevent a subset of columns
>>> from being replicated. Any new columns added to the tables will be
>>> replicated after the restructure.
>>>
>>> Best Regards,
>>>
>>> Lance Schmidt
>>> Advantage R&D
>>> "mike grace" <mikeg@computastat-group.co.uk> wrote in message
>>> news:b9f6cef8f4408cd9656c680421b@devzone.advantagedatabase.com...
>>>> Hello Lance,
>>>>
>>>> At the moment the article doesn't have any fields ticked in the
>>>> include column but does have some ticked in the exclude column.
>>>>
>>>> Are you saying that at the moment, if nothing is ticked in the
>>>> include column then all field data is replicated except for the
>>>> excluded ones and that if I switch it to tick the included columns
>>>> then any new columns I add will not automatically be included?
>>>>
>>>> Regards
>>>>
>>>> Mike
>>>>
>
>


mike grace Posted on 2011-07-27 08:11:21.0Z
Date: Wed, 27 Jul 2011 08:11:21 +0000 (UTC)
Message-ID: <b9f6cef8107598ce1a54495f84d8@devzone.advantagedatabase.com>
From: mike grace <mikeg@computastat-group.co.uk>
Subject: Re: Replication and DD update issues.
Newsgroups: Advantage.Replication
References: <4e2ee765$1@solutions.advantagedatabase.com>
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Content-Type: text/plain; charset=iso-8859-1; format=flowed
X-Newsreader: JetBrains Omea Reader 1098.1
NNTP-Posting-Host: 188.220.18.141
X-Trace: 27 Jul 2011 01:10:59 -0700, 188.220.18.141
Lines: 118
Path: solutions.advantagedatabase.com
Xref: solutions.advantagedatabase.com Advantage.Replication:461
Article PK: 1134314

Hello Mark,

OK , I understand.

Regards


Mike

> Hi Mike,
>
> If I remember correctly, an update to a table (article) in a
> subscription causes the record to be replicated regardless of whether
> the included fields are updated or not. So the update statement to
> set the column to an empty value after your script would result in
> putting an entry into the queue for every record.
>
> To avoid that during the table maintenance, you could disable and then
> re-enable the subscription that the table is in:
>
> execute procedure sp_ModifySubscriptionProperty( 'thesubscription',
> 'enabled', 'false' );
>
> and then:
>
> execute procedure sp_ModifySubscriptionProperty( 'thesubscription',
> 'enabled', 'true' );
>
> While it is disabled, now rows will be added to the queue during the
> update.
>
> Mark Wilkins
> Advantage R&D
> "mike grace" <mikeg@computastat-group.co.uk> wrote in message
> news:b9f6cef8106178ce0f8f62ea658d@devzone.advantagedatabase.com...
>
>> Hello Lance,
>>
>> ADS 10.10.0.6
>>
>> Just found something strange.
>>
>> I have an article which has a few columns ticked for inclusion.
>>
>> I then ran a script which adds 3 fields to the table then sets a
>> default i.e.
>>
>> alter table add column .... POSITION 33;
>> update table set column = '';
>> alter table add column .... POSITION 34;
>> update table set column = '';
>> alter table add column .... POSITION 35;
>> update table set column = '';
>> When I ran the script, the second alter table statement failed with
>> the error that prevents modification to a table with entries in the
>> replication log.
>>
>> When I looked at the log table it had entried for the table.
>>
>> Why should this have happened? These fields are new and will not be
>> included in the "include column" entry in the article.
>>
>> I can only assume that replication is picking up that the table has
>> been modified but maybe hasn't created an update statement or could
>> it be because I am using the POSITION PARAMETER.
>>
>> It causes a problem because I have to empty the log, then run the
>> second alter table/update, clear the log and then run the third alter
>> table/update.
>>
>> I think my answer may be to stop repliction with the execute
>> procedure statement. If so, one more question. What is the statement
>> to turn it back on again?
>>
>> Regards
>>
>> Mike
>>
>>> Hello Lance,
>>>
>>> Thanks for the clarification.
>>>
>>> Regards
>>>
>>> Mike
>>>
>>>> Hi Mike,
>>>>
>>>> Exactly. Use the include list when you only want a certain subset
>>>> of columns to be replicated. Any new columns added to the table
>>>> will NOT be replicated until being added to the include list.
>>>>
>>>> Use the exclude list when you want to prevent a subset of columns
>>>> from being replicated. Any new columns added to the tables will be
>>>> replicated after the restructure.
>>>>
>>>> Best Regards,
>>>>
>>>> Lance Schmidt
>>>> Advantage R&D
>>>> "mike grace" <mikeg@computastat-group.co.uk> wrote in message
>>>> news:b9f6cef8f4408cd9656c680421b@devzone.advantagedatabase.com...
>>>>> Hello Lance,
>>>>>
>>>>> At the moment the article doesn't have any fields ticked in the
>>>>> include column but does have some ticked in the exclude column.
>>>>>
>>>>> Are you saying that at the moment, if nothing is ticked in the
>>>>> include column then all field data is replicated except for the
>>>>> excluded ones and that if I switch it to tick the included columns
>>>>> then any new columns I add will not automatically be included?
>>>>>
>>>>> Regards
>>>>>
>>>>> Mike
>>>>>