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 table structure changes

2 posts in Replication Last posting was on 2005-12-13 22:45:55.0Z
Steve Sneed Posted on 2005-12-13 21:37:23.0Z
From: "Steve Sneed" <ssneed@collect-max.com>
Newsgroups: advantage.Replication
Subject: Replication and table structure changes
Date: Tue, 13 Dec 2005 16:37:23 -0500
Lines: 38
Organization: JS Technologies
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1506
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1506
NNTP-Posting-Host: 66.255.168.190
Message-ID: <439f3f1d@solutions.advantagedatabase.com>
X-Trace: 13 Dec 2005 14:37:33 -0700, 66.255.168.190
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!66.255.168.190
Xref: solutions.advantagedatabase.com Advantage.Replication:83
Article PK: 1133941

I didn't find this issue referenced in the help and it's a biggie for our
app:

Our app's design pre-dates the .ADD mechanisms, and we have our own data
dictionary table (and a copy) in our DB that includes all of the structure
definitions for the tables and indexes of the DB and a version stamp. When
a structure change is needed - e.g. a new column is added to a table - a new
version of that datadict table is pushed to the users with the app update; a
process at startup compares the version stamps in the two tables and when
they're different, a restructure of the changed table(s) is performed. The
restructure process for any table can include code that modifies the table
contents, e.g. pre-populates a newly-created column with a default value.

Our app also auto-updates itself. When we send an update to our customers,
they only run the installer once, on the server, which pumps all new files
into an "Update" folder tree; the next time a user starts his/her copy of
the program on his workstation, it checks the server for needed updates and,
if found, they are installed without user intervention. When a datadict
change (above) is included in the update, the first user to run the app
automatically kicks off the DB restructure once the program files have
updated. All this is without user intervention or, indeed, any way for the
user to stop it (trust me - with our users, taking it out of their hands is
the safest thing we can do...)

We want to use replication to offload some server-intensive things like
running long reports to a second server for our biggest customers. However,
I can forsee big problems with all the above
auto-updating/auto-restructuring in a replication environment. If there
was a way to programatically disable the publication queue, then do the
restructure on both databases and then restart the publication queue, it
would eliminate those problems, but so far I haven't seen any way to do that
disable/reenable part programatically. If there's some way to do this,
could you point it out? Thanks!

Steve Sneed
JS Technologies


Mark Wilkins Posted on 2005-12-13 22:45:55.0Z
From: "Mark Wilkins" <tired@of.spam>
Newsgroups: advantage.Replication
References: <439f3f1d@solutions.advantagedatabase.com>
Subject: Re: Replication and table structure changes
Date: Tue, 13 Dec 2005 15:45:55 -0700
Lines: 42
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: 198.102.102.12
Message-ID: <439f4e70@solutions.advantagedatabase.com>
X-Trace: 13 Dec 2005 15:42:56 -0700, 198.102.102.12
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!198.102.102.12
Xref: solutions.advantagedatabase.com Advantage.Replication:84
Article PK: 1133942

Hi Steve,

That sounds like a pretty nice system for updating your clients. You are
correct that the restructuring would be a problem with replication at this
point; table structure changes are not currently replicated.

I think what you are looking for is a subscription property. Publications
are simply collections of tables to be replicated. It is the subscription
that is the "active" mechanism in the replication architecture. You can
disable subscriptions to prevent any more changes being put into replication
queues. It is possible to use the ACE API AdsDDSetSubscriptionProperty.
Probably the simpler thing is to change it via SQL; you can use
sp_ModifySubscriptionProperty. I think the following example works:

EXECUTE PROCEDURE sp_ModifySubscriptionProperty( 'mysub', 'enabled',
'false' );

Mark Wilkins
Advantage R&D

"Steve Sneed" <ssneed@collect-max.com> wrote in message
news:439f3f1d@solutions.advantagedatabase.com...
>I didn't find this issue referenced in the help and it's a biggie for our
> app:
>

<snip>

> auto-updating/auto-restructuring in a replication environment. If there
> was a way to programatically disable the publication queue, then do the
> restructure on both databases and then restart the publication queue, it
> would eliminate those problems, but so far I haven't seen any way to do
> that
> disable/reenable part programatically. If there's some way to do this,
> could you point it out? Thanks!
>
> Steve Sneed
> JS Technologies
>
>