Schema Changes on HDR Secondary in 9.4

Although the manual implies that DDL can only be performed on the primary server of an HDR pair in IDS 9.4, we are being told that you can actually break the replication, alter the schema on the secondary, and restore replication. The primary will then pass the data which has been added to the primary, storing any data which does not meet constraints in a violations table. Is this true? Is this documented? Has anyone used this with any success? Let me give you what we are told will happen. Imagine two servers, primary and secondary, running HDR. HDR is stopped and the secondary is brought into standard mode. A unique index is added to table A on the secondary, while users remain connected to the primary, inserting, deleting, and updating data in table A. When the index is ready, the secondary is brought back into quiescent, and then into primary mode. The primary is then restarted in secondary and sends over the data, though it does not receive the schema changes. Any table data which would violate the unique index is store in the violations table. The users connect to the secondary as the new primary. Finally, the original primary is replaced as a new secondary, following normal procedures, thus inheriting the schema changes. Disclaimers: A non-Informix-skilled representative of my company spoke to a number of representatives of IBM, then came to me with the information that this can be done in IDS 9.40. Details may have gotten lost or twisted along the way. The manual seems to imply, but not absolutely state, that the above is not correct. A search of comp.databases.informix came up empty. If I can get answers from IBM, I will post them. Sincerely, Christopher Coleman Steering Committee President Kansas City Informix Users Group www.iiug.org/kciug Database Analyst Pharmacy Division Mediware Information Systems, Inc.

I will be out of the office until Monday, March 20. If this is an urgent matter, please contact John Creegan or Dan Deeg. Thank you. ----------------- This message (including any attachments) contains confidential information intended for a specific individual and purpose, and is protected by law. If you are not the intended recipient, you should delete this message and are hereby notified that any disclosure,copying, or distribution of this message, or the taking of any action based on it, is strictly prohibited.

Christopher.... wrote: > Although the manual implies that DDL can only be performed on the primary > server of an HDR pair in IDS 9.4, we are being told that you can actually > break the replication, alter the schema on the secondary, and restore > replication. The primary will then pass the data which has been added to the > primary, storing any data which does not meet constraints in a violations > table. > > Is this true? > > Is this documented? > > Has anyone used this with any success? > > Let me give you what we are told will happen. > > Imagine two servers, primary and secondary, running HDR. HDR is stopped and > the secondary is brought into standard mode. A unique index is added to table > A on the secondary, while users remain connected to the primary, inserting, > deleting, and updating data in table A. When the index is ready, the secondary > is brought back into quiescent, and then into primary mode. The primary is > then restarted in secondary and sends over the data, though it does not > receive the schema changes. Any table data which would violate the unique > index is store in the violations table. The users connect to the secondary as > the new primary. Finally, the original primary is replaced as a new secondary, > following normal procedures, thus inheriting the schema changes. > The final steps of the procedure are quite murky, and I'm not at all clear that this will actually do what's intended - whatever that may be. However let me be quite clear on this: the above is a recipe for disaster, or at the very least overlapping extents. What happens if your users on the (once) primary insert so much data that the table gets a new extent, and that happens to be (and this is actually quite likely) on the same portion of the chunk in which you have created the index on the (once) secondary? I'll tell you what: when the primary (now secondary) sends the PTEXTEND and the secondary (now primary) tries to roll that forward, this latter will AF. That aside, I would have thought that the difference in logid & logpos on the archive reserved pages, would force the (new) primary to throw a 'server in incompatible state' message when receiving the onmode -d > Disclaimers: A non-Informix-skilled representative of my company spoke to a > number of representatives of IBM, then came to me with the information that > this can be done in IDS 9.40. Details may have gotten lost or twisted along > the way. The manual seems to imply, but not absolutely state, that the above > is not correct. A search of comp.databases.informix came up empty. > > If I can get answers from IBM, I will post them. > > Sincerely, > > Christopher Coleman > > Steering Committee President > Kansas City Informix Users Group > www.iiug.org/kciug > > Database Analyst > Pharmacy Division > Mediware Information Systems, Inc. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Ciao, Marco ______________________________________________________________________________ Marco Greco /UK /IBM Standard disclaimers apply! Structured Query Scripting Language http://www.4glworks.com/sqsl.htm 4glworks http://www.4glworks.com Informix on Linux http://www.4glworks.com/ifmxlinux.htm

Marco wrote: > The final steps of the procedure are quite murky, Yes, I would have to agree. That is why I was certain to include the disclaimer, as I cannot see through the murk. This whole process makes me very uncomfortable because I feel like a mathematician looking at a series of formulas with "Then a miracle occurs" in the middle of the blackboard. > ...I'm not at all clear that this will actually do what's intended - > whatever that may be. Our ultimate goal is upgrades without downtime, or at least with less than 30 minutes. Our customers are hospitals, and the nursing staff will not accept the downtime necessary to reorganize tables, build indexes, etc. If you were the patient, you might not like it, either. > However let me be quite clear on this: the above is a recipe for > disaster, or at the very least overlapping extents. That is the way it sounded to me, and your objections about logid & logpos were my very first thoughts, while PTEXTEND came second. I am willing to believe IBM has worked around these technical issues, but I want to see documentation, or at least hear from someone who has done it. Actually, I still want to see documentation, because I am still going to worry about that AF. IBM had better promise that it will not happen, and that they will fix it when it does. Sincerely, Christopher Coleman Steering Committee President Kansas City Informix Users Group www.iiug.org/kciug Database Analyst Pharmacy Division Mediware Information Systems, Inc.

Here is information we received from IBM. Remember my disclaimer? I did some additional research and it turns out that HDR will not work for what we discussed. ER in IDS 10.0 with the Schema Evolution feature is a possibility, however I need to have a deeper conversation with R&D about it. I'll dig in a bit deeper for you over the next few days. It turns out that I was confusing the Schema Evolution feature of ER with HDR, so I do apologize. Schema Evolution replicates schema changes, but the thing I need to research is whether or not you can break the replication to perform the upgrades, then resync. There are a couple of questions I have such as whether or not Schema Evolution is required for your situation. It may be that the regular ER in IDS 9.40 would work fine, but as I said I need to dig a little deeper. So, this is why we check, even when we hear something from the vendor. Anyone know a good way to alter schemas with 0 downtime? Sincerely, Christopher Coleman Steering Committee President Kansas City Informix Users Group www.iiug.org/kciug Database Analyst Pharmacy Division Mediware Information Systems, Inc.

Christopher, what you describe in not possible in HDR, and you are correctly interpreting the manual. Most likely you will get a "primary and secondary out of sync" error when trying what you decribe below. Maybe the "number of representatives at IBM" were all talking about ER. HTH, Karl ---- Karl Ostner IBM Informix Development Information Management "Christopher...." <Christopher.Cole man@mediware.com> To Sent by: ids@iiug.org ids-bounces@iiug. cc org Subject Schema Changes on HDR Secondary in 03/07/2006 12:12 9.4 [6475] AM Please respond to ids Although the manual implies that DDL can only be performed on the primary server of an HDR pair in IDS 9.4, we are being told that you can actually break the replication, alter the schema on the secondary, and restore replication. The primary will then pass the data which has been added to the primary, storing any data which does not meet constraints in a violations table. Is this true? Is this documented? Has anyone used this with any success? Let me give you what we are told will happen. Imagine two servers, primary and secondary, running HDR. HDR is stopped and the secondary is brought into standard mode. A unique index is added to table A on the secondary, while users remain connected to the primary, inserting, deleting, and updating data in table A. When the index is ready, the secondary is brought back into quiescent, and then into primary mode. The primary is then restarted in secondary and sends over the data, though it does not receive the schema changes. Any table data which would violate the unique index is store in the violations table. The users connect to the secondary as the new primary. Finally, the original primary is replaced as a new secondary, following normal procedures, thus inheriting the schema changes. Disclaimers: A non-Informix-skilled representative of my company spoke to a number of representatives of IBM, then came to me with the information that this can be done in IDS 9.40. Details may have gotten lost or twisted along the way. The manual seems to imply, but not absolutely state, that the above is not correct. A search of comp.databases.informix came up empty. If I can get answers from IBM, I will post them. Sincerely, Christopher Coleman Steering Committee President Kansas City Informix Users Group www.iiug.org/kciug Database Analyst Pharmacy Division Mediware Information Systems, Inc. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Christopher It really all depends on what schema changes you need. Alter table to add or remove columns now happens immediately (if you can get 2 seconds exclusive access) and many column type changes can happen immediately. However connected programs will terminate if they encounter a changed table! Index builds under 10 no longer require exclusive access, they can happen on-line. Keith -----Original Message----- From: CHRISTOPHER.... [mailto:christopher.coleman@mediware.com] Sent: Wednesday, March 08, 2006 11:38 PM To: ids@iiug.org Subject: Re: Schema Changes on HDR Secondary in 9.4 [6484] Here is information we received from IBM. Remember my disclaimer? I did some additional research and it turns out that HDR will not work for what we discussed. ER in IDS 10.0 with the Schema Evolution feature is a possibility, however I need to have a deeper conversation with R&D about it. I'll dig in a bit deeper for you over the next few days. It turns out that I was confusing the Schema Evolution feature of ER with HDR, so I do apologize. Schema Evolution replicates schema changes, but the thing I need to research is whether or not you can break the replication to perform the upgrades, then resync. There are a couple of questions I have such as whether or not Schema Evolution is required for your situation. It may be that the regular ER in IDS 9.40 would work fine, but as I said I need to dig a little deeper. So, this is why we check, even when we hear something from the vendor. Anyone know a good way to alter schemas with 0 downtime? Sincerely, Christopher Coleman Steering Committee President Kansas City Informix Users Group www.iiug.org/kciug Database Analyst Pharmacy Division Mediware Information Systems, Inc. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. ******************************************************************************** ** This message is sent in strict confidence for the addressee only. It may contain legally privileged information. The contents are not to be disclosed to anyone other than the addressee. Unauthorised recipients are requested to preserve this confidentiality and to advise the sender immediately of any error in transmission. This footnote also confirms that this email message has been swept for the presence of computer viruses, however we cannot guarantee that this message is free from such problems. ******************************************************************************** **