Updating publications in multiple remote DBs

[former_member329524]

Hello, all

I would like to ask what happens to active subscriptions in remote DB when publication changes.

I understand that when I have an update on a publication and I cannot know exactly what is the publications current state in each and every db, then I cannot use "drop/create" command pair (like I can use with procedures or triggers). That is because dropping a publication will drop all subscriptions as well.

So, I am trying to find a way to alter a publication, without knowing which articles it currently contains.

My question is: Can I first delete all articles in the publication and then add them again?

Will that deactivate the subscriptions?

[jeff_albion · Accepted solution]

Hi Arcady,

Technically, you cannot change publications while they have outstanding operations to be uploaded to the MobiLink server or have active subscriptions. In newer versions, you will see the error:

Could not execute statement.
This operation is only allowed within a synchronization schema change
SQLCODE=-1329, ODBC 3 State="HY000"
Line 1, column 1

In previous versions of MobiLink, there was a requirement to perform the following steps to issue a schema change:

1. Ensure a successful upload against the MobiLink server
2. Perform a schema / publication change (inside sp_hook_dbmlsync_schema_upgrade, if neccessary)
3. Synchronize (with a new script version, if required).

As you can guess, many customers were not happy about the pre-conditions of ensuring a successful upload before making schema changes. In version 12, we introduced START SYNCHRONIZATION SCHEMA CHANGE so that you can now do the following operations, even with active subscriptions and operations:

• You can alter a publication to change the column subsetting of any table in table-list.
• You can alter a publication to drop any table in table-list.
• You can alter any of the tables listed in table-list.

With this, we now allow a 'transitional sync' immediately following the changes where we upload using the previous definitions and then do another synchronization using the new definitions.

---

Can I first delete all articles in the publication and then add them again?

Yes.

Will that deactivate the subscriptions?

No, but depending on which version of MobiLink you're using, you may be restricted in your operations unless certain pre-conditions are first met.

Regards,

Jeff Albion

SAP Active Global Support

[former_member329524 · Accepted solution]

Thank you, Jeff

So, just to make it clear:

Can I drop the whole publication and then recreate it from a create publication sql? If yes, what happens to subscriptions during this process, do they get removed as well?

[jeff_albion · Accepted solution]

Hi Arcady,

>> Can I drop the whole publication and then recreate it from a create publication sql?

Yes, this should work:

DROP PUBLICATION p1;
CREATE PUBLICATION p1 ( table t1);

>> If yes, what happens to subscriptions during this process, do they get removed as well?

Yes, any referring synchronization subscriptions are removed if you drop the underlying publication they rely on. When you referring to modifying articles above, I was assuming you were dropping the individual articles, not the publications themselves.

Regards,

Jeff Albion

SAP Active Global Support